From 293d6214bc04810459c3f120aca5ea545d07b045 Mon Sep 17 00:00:00 2001 From: Joe Wildish Date: Sun, 18 Mar 2018 12:04:59 +0000 Subject: [PATCH] SQL ASSERTION prototype --- doc/src/sgml/ddl.sgml | 31 + doc/src/sgml/ref/allfiles.sgml | 3 + doc/src/sgml/ref/alter_assertion.sgml | 139 ++++ doc/src/sgml/ref/create_assertion.sgml | 133 ++++ doc/src/sgml/ref/drop_assertion.sgml | 115 +++ doc/src/sgml/reference.sgml | 3 + src/backend/catalog/aclchk.c | 33 + src/backend/catalog/information_schema.sql | 60 +- src/backend/catalog/namespace.c | 121 +++ src/backend/catalog/objectaddress.c | 11 + src/backend/catalog/pg_constraint.c | 20 +- src/backend/commands/alter.c | 6 + src/backend/commands/constraint.c | 1090 +++++++++++++++++++++++++++- src/backend/commands/event_trigger.c | 2 + src/backend/nodes/copyfuncs.c | 14 + src/backend/nodes/equalfuncs.c | 12 + src/backend/parser/gram.y | 80 +- src/backend/parser/parse_agg.c | 2 + src/backend/parser/parse_expr.c | 2 + src/backend/parser/parse_func.c | 4 + src/backend/tcop/utility.c | 20 + src/backend/utils/cache/lsyscache.c | 19 + src/bin/psql/command.c | 3 + src/bin/psql/describe.c | 92 +++ src/bin/psql/describe.h | 3 + src/bin/psql/help.c | 1 + src/bin/psql/tab-complete.c | 30 +- src/include/catalog/namespace.h | 1 + src/include/catalog/pg_constraint.h | 3 +- src/include/catalog/pg_constraint_fn.h | 2 +- src/include/catalog/pg_proc.h | 4 + src/include/commands/constraint.h | 10 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 11 + src/include/parser/parse_node.h | 1 + src/include/utils/acl.h | 1 + src/include/utils/lsyscache.h | 1 + src/test/regress/expected/assertions.out | 779 ++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/assertions.sql | 775 ++++++++++++++++++++ 41 files changed, 3572 insertions(+), 69 deletions(-) create mode 100644 doc/src/sgml/ref/alter_assertion.sgml create mode 100644 doc/src/sgml/ref/create_assertion.sgml create mode 100644 doc/src/sgml/ref/drop_assertion.sgml create mode 100644 src/include/commands/constraint.h create mode 100644 src/test/regress/expected/assertions.out create mode 100644 src/test/regress/sql/assertions.sql diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 3a54ba9d5a..81fde6403a 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -917,6 +917,37 @@ CREATE TABLE circles ( of the type specified in the constraint declaration. + + + Assertions + + + assertion + + + + An assertion is a constraint that is not part of a table + definition. An assertion can define constraints that evaluate the + data across multiple rows of a table beyond what unique and + exclusion constraints can do, and assertions can look at the data + in multiple tables. + + + + An assertion is a separate schema object and is created with the + command CREATE ASSERTION. The constraint + expression is written as a CHECK clause like in + check constraints. For instance, to ensure that there is always + at least one entry in the product table: + +CREATE ASSERTION products_not_empty CHECK ((SELECT count(*) FROM products) > 0); + + Assertions will often involve aggregate functions computed over + entire tables. Note, however, that this kind of assertion can be + quite inefficient and should only be used on tables that are small + and change rarely. + + diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 22e6893211..57badaf157 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -7,6 +7,7 @@ Complete list of usable sgml source files in this directory. + @@ -60,6 +61,7 @@ Complete list of usable sgml source files in this directory. + @@ -107,6 +109,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/alter_assertion.sgml b/doc/src/sgml/ref/alter_assertion.sgml new file mode 100644 index 0000000000..b06417f740 --- /dev/null +++ b/doc/src/sgml/ref/alter_assertion.sgml @@ -0,0 +1,139 @@ + + + + + + ALTER ASSERTION + + + + ALTER ASSERTION + 7 + SQL - Language Statements + + + + ALTER ASSERTION + change the definition of an assertion + + + + +ALTER ASSERTION name RENAME TO new_name +ALTER ASSERTION name OWNER TO new_owner +ALTER ASSERTION name SET SCHEMA new_schema + + + + + Description + + + ALTER ASSERTION changes the definition of an + assertion. + + + + You must own the assertion to use ALTER ASSERTION. To + change the schema of an assertion, you must also have + CREATE privilege on the new schema. To alter + the owner, you must also be a direct or indirect member of the new + owning role, and that role must have CREATE + privilege on the assertion's schema. (These restrictions enforce + that altering the owner doesn't do anything you couldn't do by + dropping and recreating the assertion. However, a superuser can + alter ownership of any assertion anyway.) + + + + + Parameters + + + + name + + + The name (optionally schema-qualified) of an existing assertion. + + + + + + new_name + + + The new name of the assertion. + + + + + + new_owner + + + The new owner of the assertion. + + + + + + new_schema + + + The new schema for the assertion. + + + + + + + + Examples + + + To rename the assertuib check_size + to check_count: + +ALTER ASSERTION check_size RENAME TO check_count; + + + + + To change the owner of the assertion check_size + to joe: + +ALTER ASSERTION check_size OWNER TO joe; + + + + + To move the assertion check_size into + schema myschema: + +ALTER ASSERTION check_size SET SCHEMA myschema; + + + + + + Compatibility + + + There is no ALTER ASSERTION statement in the SQL + standard. + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/ref/create_assertion.sgml b/doc/src/sgml/ref/create_assertion.sgml new file mode 100644 index 0000000000..b36d1b4ecb --- /dev/null +++ b/doc/src/sgml/ref/create_assertion.sgml @@ -0,0 +1,133 @@ + + + + + CREATE ASSERTION + + + + CREATE ASSERTION + 7 + SQL - Language Statements + + + + CREATE ASSERTION + define a new assertion + + + + +CREATE ASSERTION name CHECK ( name ) [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] + + + + + Description + + + CREATE ASSERTION creates an assertion. An + assertion is a check constraint that is independent of a table row + and a table. It can therefore be used to enforce more complex + constraints across multiple table rows and across multiple tables. + + + + + Parameters + + + + name + + + The name (optionally schema-qualified) of the assertion to + create. Assertions use the same namespace as constraints on + tables. + + + + + + CHECK ( expression ) + + + The CHECK clause specifies an expression producing a + Boolean result which the database must satisfy at all times for a + data change operation to succeed. Expressions evaluating + to TRUE or UNKNOWN succeed. Should the result of a data change + operation produce a FALSE result an error exception is + raised and the change is not made. + + + + The check expression typically involves subselects in order to + read data from tables. See the examples below. + + + + + + DEFERRABLE + NOT DEFERRABLE + INITIALLY IMMEDIATE + INITIALLY DEFERRED + + + These clauses control the deferrability of the constraint. See + for an explanation. + + + + + + + + Notes + + + More specialized constraint forms such as table check constraints, + foreign-key constraints, or exclusion constraints should be used + instead when applicable, because they will be more efficient. + + + + Assertion checks are not specially optimized. For example, + checking the row count of a large table in an assertion will be + just as slow as implementing the check manually. + + + + + Examples + + + Check that the table table1 has at most 30 rows: + +CREATE ASSERTION table1_max30 CHECK ((SELECT count(*) FROM table1) <= 30); + + + + + + Compatibility + + + CREATE ASSERTION conforms to the SQL standard. + The PostgreSQL implementation has certain restrictions on what + check expressions are allowed in assertions. + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/ref/drop_assertion.sgml b/doc/src/sgml/ref/drop_assertion.sgml new file mode 100644 index 0000000000..39a60a0d29 --- /dev/null +++ b/doc/src/sgml/ref/drop_assertion.sgml @@ -0,0 +1,115 @@ + + + + + + DROP ASSERTION + + + + DROP ASSERTION + 7 + SQL - Language Statements + + + + DROP ASSERTION + remove an assertion + + + + +DROP ASSERTION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] + + + + + Description + + + DROP ASSERTION removes an existing assertion. To + execute this command the current user must be the owner of the + assertion. + + + + + Parameters + + + + + IF EXISTS + + + Do not throw an error if the assertion does not exist. A notice + is issued in this case. + + + + + + name + + + The name (optionally schema-qualified) of an existing assertion. + + + + + + CASCADE + + + Automatically drop objects that depend on the assertion. + + + + + + RESTRICT + + + Refuse to drop the assertion if any objects depend on it. This + is the default. + + + + + + + + Examples + + + To remove the assertion check_size: + +DROP ASSERTION check_size; + + + + + + Compatibility + + + This command conforms to the SQL standard, except that the standard + only allows one assertion to be dropped per command, and apart from + the IF EXISTS option, which is + a PostgreSQL extension. + + + + + See Also + + + + + + + + diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index d27fb414f7..9300f2d9fc 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -35,6 +35,7 @@ &abort; &alterAggregate; + &alterAssertion; &alterCollation; &alterConversion; &alterDatabase; @@ -88,6 +89,7 @@ ©Table; &createAccessMethod; &createAggregate; + &createAssertion; &createCast; &createCollation; &createConversion; @@ -135,6 +137,7 @@ &do; &dropAccessMethod; &dropAggregate; + &dropAssertion; &dropCast; &dropCollation; &dropConversion; diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 0648539796..e804e6d493 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -3362,6 +3362,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_AGGREGATE: msg = gettext_noop("permission denied for aggregate %s"); break; + case OBJECT_ASSERTION: + msg = gettext_noop("permission denied for assertion %s"); + break; case OBJECT_COLLATION: msg = gettext_noop("permission denied for collation %s"); break; @@ -3493,6 +3496,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_AGGREGATE: msg = gettext_noop("must be owner of aggregate %s"); break; + case OBJECT_ASSERTION: + msg = gettext_noop("must be owner of assertion %s"); + break; case OBJECT_COLLATION: msg = gettext_noop("must be owner of collation %s"); break; @@ -5211,6 +5217,33 @@ pg_collation_ownercheck(Oid coll_oid, Oid roleid) return has_privs_of_role(roleid, ownerId); } +/* + * Ownership check for a constraint (specified by OID). + */ +bool +pg_constraint_ownercheck(Oid constr_oid, Oid roleid) +{ + HeapTuple tuple; + //Oid ownerId; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constr_oid)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("constraint with OID %u does not exist", constr_oid))); + + //FIXME: ownerId = ((Form_pg_constraint) GETSTRUCT(tuple))->conowner; + + ReleaseSysCache(tuple); + + //return has_privs_of_role(roleid, ownerId); + return true; // for now +} + /* * Ownership check for a conversion (specified by OID). */ diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index f4e69f4a26..23cc0759a3 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -285,7 +285,20 @@ GRANT SELECT ON administrable_role_authorizations TO PUBLIC; * ASSERTIONS view */ --- feature not supported +CREATE VIEW assertions AS + SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, + CAST(n.nspname AS sql_identifier) AS constraint_schema, + CAST(con.conname AS sql_identifier) AS constraint_name, + CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS is_deferrable, + CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS initially_deferred + FROM pg_namespace n, pg_constraint con + WHERE n.oid = con.connamespace + AND con.conrelid = 0 AND con.contypid = 0; + -- TODO: AND pg_has_role(con.conowner, 'USAGE'); + +GRANT SELECT ON assertions TO PUBLIC; /* @@ -790,7 +803,7 @@ CREATE VIEW constraint_column_usage AS CAST(cstrname AS sql_identifier) AS constraint_name FROM ( - /* check constraints */ + /* assertions and check constraints */ SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c WHERE nr.oid = r.relnamespace @@ -802,7 +815,7 @@ CREATE VIEW constraint_column_usage AS AND d.objid = c.oid AND c.connamespace = nc.oid AND c.contype = 'c' - AND r.relkind IN ('r', 'p') + AND r.relkind IN ('r', 'p', 'v') AND NOT a.attisdropped UNION ALL @@ -842,20 +855,41 @@ GRANT SELECT ON constraint_column_usage TO PUBLIC; CREATE VIEW constraint_table_usage AS SELECT CAST(current_database() AS sql_identifier) AS table_catalog, - CAST(nr.nspname AS sql_identifier) AS table_schema, - CAST(r.relname AS sql_identifier) AS table_name, + CAST(tblschema AS sql_identifier) AS table_schema, + CAST(tblname AS sql_identifier) AS table_name, CAST(current_database() AS sql_identifier) AS constraint_catalog, - CAST(nc.nspname AS sql_identifier) AS constraint_schema, - CAST(c.conname AS sql_identifier) AS constraint_name + CAST(cstrschema AS sql_identifier) AS constraint_schema, + CAST(cstrname AS sql_identifier) AS constraint_name + + FROM ( + /* assertions and check constraints */ + SELECT DISTINCT nr.nspname, r.relname, r.relowner, nc.nspname, c.conname + FROM pg_namespace nr, pg_class r, + pg_depend d, pg_namespace nc, pg_constraint c + WHERE nr.oid = r.relnamespace + AND d.refobjid = r.oid + AND c.connamespace = nc.oid + AND d.objid = c.oid + AND d.refclassid = 'pg_catalog.pg_class'::regclass + AND d.classid = 'pg_catalog.pg_constraint'::regclass + AND c.contype = 'c' + AND r.relkind IN ('r', 'p', 'v') - FROM pg_constraint c, pg_namespace nc, - pg_class r, pg_namespace nr + UNION ALL - WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid - AND ( (c.contype = 'f' AND c.confrelid = r.oid) + /* unique/primary key constraints */ + SELECT nr.nspname, r.relname, r.relowner, nc.nspname, c.conname + FROM pg_constraint c, pg_namespace nc, + pg_class r, pg_namespace nr + WHERE c.connamespace = nc.oid + AND r.relnamespace = nr.oid + AND ( (c.contype = 'f' AND c.confrelid = r.oid) OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) ) - AND r.relkind IN ('r', 'p') - AND pg_has_role(r.relowner, 'USAGE'); + AND r.relkind IN ('r', 'p') + + ) AS x (tblschema, tblname, tblowner, cstrschema, cstrname) + + WHERE pg_has_role(x.tblowner, 'USAGE'); GRANT SELECT ON constraint_table_usage TO PUBLIC; diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c index 52dd400b96..0bb1e31eff 100644 --- a/src/backend/catalog/namespace.c +++ b/src/backend/catalog/namespace.c @@ -27,6 +27,7 @@ #include "catalog/objectaccess.h" #include "catalog/pg_authid.h" #include "catalog/pg_collation.h" +#include "catalog/pg_constraint.h" #include "catalog/pg_conversion.h" #include "catalog/pg_conversion_fn.h" #include "catalog/pg_namespace.h" @@ -57,9 +58,11 @@ #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/syscache.h" +#include "utils/fmgroids.h" #include "utils/varlena.h" + /* * The namespace search path is a possibly-empty list of namespace OIDs. * In addition to the explicit list, implicitly-searched namespaces @@ -3483,6 +3486,124 @@ PopOverrideSearchPath(void) } +static Oid +get_assertion_oid_internal(Relation pg_constraint, char *assertion_name, Oid namespaceId, List *name) +{ + SysScanDesc scan; + ScanKeyData skey[4]; + HeapTuple tuple; + Oid conOid = InvalidOid; + + ScanKeyInit(&skey[0], + Anum_pg_constraint_conname, + BTEqualStrategyNumber, F_NAMEEQ, + PointerGetDatum(assertion_name)); + + ScanKeyInit(&skey[1], + Anum_pg_constraint_connamespace, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(namespaceId)); + + ScanKeyInit(&skey[2], + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + InvalidOid); + + ScanKeyInit(&skey[3], + Anum_pg_constraint_contypid, + BTEqualStrategyNumber, F_OIDEQ, + InvalidOid); + + scan = systable_beginscan(pg_constraint, InvalidOid, false, + NULL, 4, skey); + + /* + * Fetch the constraint tuple from pg_constraint. There may be + * more than one match, because constraints are not required to + * have unique names; if so, error out. + */ + while (HeapTupleIsValid(tuple = systable_getnext(scan))) + { + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple); + + if (OidIsValid(con->conrelid) || OidIsValid(con->contypid)) + ereport(ERROR, + (errmsg("constraint \"%s\" is not an assertion", + NameListToString(name)))); + + if (strcmp(NameStr(con->conname), assertion_name) == 0) + { + if (OidIsValid(conOid)) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("there are multiple assertions named \"%s\"", + NameListToString(name)))); + conOid = HeapTupleGetOid(tuple); + } + } + + systable_endscan(scan); + + return conOid; +} + +/* + * get_assertion_oid + * Find an assertion with the specified name. + * Returns constraint's OID. + */ +Oid +get_assertion_oid(List *name, bool missing_ok) +{ + char *schemaname; + char *assertion_name; + Oid namespaceId; + Relation pg_constraint; + Oid conOid = InvalidOid; + + DeconstructQualifiedName(name, &schemaname, &assertion_name); + + pg_constraint = heap_open(ConstraintRelationId, AccessShareLock); + + if (schemaname) + { + namespaceId = LookupExplicitNamespace(schemaname, missing_ok); + conOid = get_assertion_oid_internal(pg_constraint, assertion_name, namespaceId, name); + } + else + { + ListCell *l; + + recomputeNamespacePath(); + + foreach(l, activeSearchPath) + { + namespaceId = lfirst_oid(l); + + if (namespaceId == myTempNamespace) + continue; /* do not look in temp namespace */ + + conOid = get_assertion_oid_internal(pg_constraint, assertion_name, namespaceId, name); + + if (OidIsValid(conOid)) + break; + } + } + + heap_close(pg_constraint, AccessShareLock); + + /* If no such constraint exists, complain */ + if (!OidIsValid(conOid) && !missing_ok) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("assertion \"%s\" does not exist", + NameListToString(name)))); + + return conOid; +} + + + /* * get_collation_oid - find a collation by possibly qualified name * diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index 119297b33a..1c9f186d02 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -899,6 +899,11 @@ get_object_address(ObjectType objtype, Node *object, address.objectId = LookupOperWithArgs(castNode(ObjectWithArgs, object), missing_ok); address.objectSubId = 0; break; + case OBJECT_ASSERTION: + address.classId = ConstraintRelationId; + address.objectId = get_assertion_oid(castNode(List, object), missing_ok); + address.objectSubId = 0; + break; case OBJECT_COLLATION: address.classId = CollationRelationId; address.objectId = get_collation_oid(castNode(List, object), missing_ok); @@ -2117,6 +2122,7 @@ pg_get_object_address(PG_FUNCTION_ARGS) case OBJECT_FOREIGN_TABLE: case OBJECT_COLUMN: case OBJECT_ATTRIBUTE: + case OBJECT_ASSERTION: case OBJECT_COLLATION: case OBJECT_CONVERSION: case OBJECT_STATISTIC_EXT: @@ -2277,6 +2283,11 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, aclcheck_error(ACLCHECK_NOT_OWNER, objtype, strVal((Value *) object)); break; + case OBJECT_ASSERTION: + if (!pg_constraint_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, objtype, + NameListToString(castNode(List, object))); + break; case OBJECT_COLLATION: if (!pg_collation_ownercheck(address.objectId, roleid)) aclcheck_error(ACLCHECK_NOT_OWNER, objtype, diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 731c5e4317..08e3e4f9c8 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -429,6 +429,13 @@ ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId, found = true; break; } + else if (conCat == CONSTRAINT_ASSERTION + && con->conrelid == InvalidOid + && con->contypid == InvalidOid) + { + found = true; + break; + } } systable_endscan(conscan); @@ -600,8 +607,7 @@ RemoveConstraintById(Oid conId) * but we have no such concept at the moment. */ } - else - elog(ERROR, "constraint %u is not of a known type", conId); + /* Else it's an assertion; nothing special for that. */ /* Fry the constraint itself */ CatalogTupleDelete(conDesc, &tup->t_self); @@ -657,6 +663,16 @@ RenameConstraintById(Oid conId, const char *newname) (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("constraint \"%s\" for domain %s already exists", newname, format_type_be(con->contypid)))); + if (!OidIsValid(con->conrelid) && + !OidIsValid(con->contypid) && + ConstraintNameIsUsed(CONSTRAINT_ASSERTION, + InvalidOid, + con->connamespace, + newname)) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("assertion \"%s\" already exists", + newname))); /* OK, do the rename --- tuple is a copy, so OK to scribble on it */ namestrcpy(&(con->conname), newname); diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c index 0d63866fb0..5cb6e0fe7d 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -41,6 +41,7 @@ #include "commands/alter.h" #include "commands/collationcmds.h" #include "commands/conversioncmds.h" +#include "commands/constraint.h" #include "commands/dbcommands.h" #include "commands/defrem.h" #include "commands/event_trigger.h" @@ -326,6 +327,9 @@ ExecRenameStmt(RenameStmt *stmt) { switch (stmt->renameType) { + case OBJECT_ASSERTION: + return RenameAssertion(stmt); + case OBJECT_TABCONSTRAINT: case OBJECT_DOMCONSTRAINT: return RenameConstraint(stmt); @@ -491,6 +495,7 @@ ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt, /* generic code path */ case OBJECT_AGGREGATE: + case OBJECT_ASSERTION: case OBJECT_COLLATION: case OBJECT_CONVERSION: case OBJECT_FUNCTION: @@ -838,6 +843,7 @@ ExecAlterOwnerStmt(AlterOwnerStmt *stmt) /* Generic cases */ case OBJECT_AGGREGATE: + case OBJECT_ASSERTION: case OBJECT_COLLATION: case OBJECT_CONVERSION: case OBJECT_FUNCTION: diff --git a/src/backend/commands/constraint.c b/src/backend/commands/constraint.c index 90f19ad3dd..172508510f 100644 --- a/src/backend/commands/constraint.c +++ b/src/backend/commands/constraint.c @@ -11,6 +11,8 @@ * *------------------------------------------------------------------------- */ + + #include "postgres.h" #include "catalog/index.h" @@ -20,6 +22,40 @@ #include "utils/rel.h" #include "utils/tqual.h" +#include "access/htup_details.h" +#include "catalog/dependency.h" +#include "catalog/namespace.h" +#include "catalog/pg_aggregate.h" +#include "catalog/pg_constraint.h" +#include "catalog/pg_constraint_fn.h" +#include "catalog/pg_operator.h" +#include "catalog/pg_proc.h" +#include "catalog/pg_language.h" +#include "catalog/pg_trigger.h" +#include "catalog/pg_type.h" +#include "commands/constraint.h" +#include "executor/functions.h" +#include "executor/spi.h" +#include "miscadmin.h" +#include "nodes/makefuncs.h" +#include "nodes/nodeFuncs.h" +#include "optimizer/planner.h" +#include "parser/analyze.h" +#include "parser/parse_coerce.h" +#include "parser/parse_expr.h" +#include "parser/parse_node.h" +#include "parser/parse_relation.h" +#include "parser/parser.h" +#include "parser/parsetree.h" +#include "rewrite/rewriteHandler.h" +#include "tcop/tcopprot.h" +#include "utils/acl.h" +#include "utils/lsyscache.h" +#include "utils/snapmgr.h" +#include "utils/syscache.h" +#include "utils/ruleutils.h" +#include "utils/fmgroids.h" + /* * unique_key_recheck - trigger function to do a deferred uniqueness check. @@ -39,15 +75,15 @@ unique_key_recheck(PG_FUNCTION_ARGS) { TriggerData *trigdata = castNode(TriggerData, fcinfo->context); const char *funcname = "unique_key_recheck"; - HeapTuple new_row; + HeapTuple new_row; ItemPointerData tmptid; - Relation indexRel; - IndexInfo *indexInfo; - EState *estate; + Relation indexRel; + IndexInfo *indexInfo; + EState *estate; ExprContext *econtext; TupleTableSlot *slot; - Datum values[INDEX_MAX_KEYS]; - bool isnull[INDEX_MAX_KEYS]; + Datum values[INDEX_MAX_KEYS]; + bool isnull[INDEX_MAX_KEYS]; /* * Make sure this is being called as an AFTER ROW trigger. Note: @@ -57,15 +93,15 @@ unique_key_recheck(PG_FUNCTION_ARGS) if (!CALLED_AS_TRIGGER(fcinfo)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), - errmsg("function \"%s\" was not called by trigger manager", - funcname))); + errmsg("function \"%s\" was not called by trigger manager", + funcname))); if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) || !TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), - errmsg("function \"%s\" must be fired AFTER ROW", - funcname))); + errmsg("function \"%s\" must be fired AFTER ROW", + funcname))); /* * Get the new data that was inserted/updated. @@ -78,9 +114,9 @@ unique_key_recheck(PG_FUNCTION_ARGS) { ereport(ERROR, (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), - errmsg("function \"%s\" must be fired for INSERT or UPDATE", - funcname))); - new_row = NULL; /* keep compiler quiet */ + errmsg("function \"%s\" must be fired for INSERT or UPDATE", + funcname))); + new_row = NULL; /* keep compiler quiet */ } /* @@ -194,3 +230,1031 @@ unique_key_recheck(PG_FUNCTION_ARGS) return PointerGetDatum(NULL); } + + +static void +test_assertion_expr(char *name, char *expression) +{ + char *sql; + int returnCode; + bool isNull; + Datum value; + + if (SPI_connect() != SPI_OK_CONNECT) + elog(ERROR, "SPI connect failed when executing ASSERTION statement"); + + sql = psprintf("SELECT %s", expression); + returnCode = SPI_exec(sql, 1); + + if (returnCode > 0 && SPI_tuptable != NULL) + { + value = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isNull); + if (isNull) + ereport(ERROR, + (errcode(ERRCODE_CHECK_VIOLATION), + errmsg("assertion \"%s\" truth is unknown", name))); + else if (!isNull && !DatumGetBool(value)) + ereport(ERROR, + (errcode(ERRCODE_CHECK_VIOLATION), + errmsg("assertion \"%s\" violated", name))); + } + else + elog(ERROR, "unexpected SPI result when executing ASSERTION statement"); + + SPI_finish(); +} + + +Datum +assertion_check(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + const char *funcname = "assertion_check"; + Oid constraintOid; + HeapTuple tup; + Datum adatum; + bool isNull; + + /* + * Make sure this is being called as an AFTER STATEMENT trigger. Note: + * translatable error strings are shared with ri_triggers.c, so resist the + * temptation to fold the function name into them. + */ + if (!CALLED_AS_TRIGGER(fcinfo)) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("function \"%s\" was not called by trigger manager", + funcname))); + + if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) || + !TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event)) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("function \"%s\" must be fired AFTER STATEMENT", + funcname))); + + constraintOid = trigdata->tg_trigger->tgconstraint; + tup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constraintOid)); + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for constraint %u", constraintOid); + + // XXX bogus + adatum = SysCacheGetAttr(CONSTROID, tup, + Anum_pg_constraint_consrc, &isNull); + if (isNull) + elog(ERROR, "constraint %u has null consrc", constraintOid); + + test_assertion_expr(get_constraint_name(constraintOid), TextDatumGetCString(adatum)); + + ReleaseSysCache(tup); + return PointerGetDatum(NULL); +} + +#define NO_FUNC (0) +#define OTHER_FUNC (1 << 0) +#define MIN_AGG_FUNC (1 << 1) +#define MAX_AGG_FUNC (1 << 2) +#define COUNT_AGG_FUNC (1 << 3) +#define EVERY_AGG_FUNC (1 << 4) +#define BOOL_AND_AGG_FUNC (1 << 5) +#define BOOL_OR_AGG_FUNC (1 << 6) + +#define EQ_COMPARISONS (bms_make_singleton(ROWCOMPARE_EQ)) +#define NE_COMPARISONS (bms_make_singleton(ROWCOMPARE_NE)) +#define LTE_COMPARISONS (bms_add_member(bms_make_singleton(ROWCOMPARE_LE), ROWCOMPARE_LT)) +#define GTE_COMPARISONS (bms_add_member(bms_make_singleton(ROWCOMPARE_GE), ROWCOMPARE_GT)) + +#define MatchesOnly(a,b) (((a) & (b)) && !((a) & (~(b)))) +#define CanOptimise(s) (!bms_is_empty(s) && \ + (bms_is_subset(s, EQ_COMPARISONS) || \ + bms_is_subset(s, NE_COMPARISONS) || \ + bms_is_subset(s, LTE_COMPARISONS) || \ + bms_is_subset(s, GTE_COMPARISONS) )) + + +// TODO review TODOs in the tests +// TODO assertions should allow unknown according to the SQL specification +// TODO traversal into views needs rethinking - dependencies? +// TODO pg_dump support + +/* + * DML operations that could affect the truth of an assertion. Only + * INSERT and DELETE are considered as part of the labeling algorithm. + * UPDATEs are inferred using different logic. + */ +typedef enum DmlOp +{ + INSERT, + DELETE, + INSERT_DELETE +} DmlOp; + + +/* + * A context used to capture operations that may invalidate an assertion. + */ +typedef struct AssertionInfo +{ + DmlOp label; /* current invaliding operation */ + Expr *expr; /* current Expression node */ + List *rtable; /* current range tables */ + List *operators; /* current operators */ + SetOperation setOp; /* current set-operation */ + bool invert; /* ... */ + bool inView; /* if the walker has entered a view */ + bool inTargetEntry; /* if the walker has entered a TargetEntry node */ + bool inComparison; /* if the walker has entered a comparison operation */ + bool inExists; /* if the walker has entered an EXISTS node */ + + List *dependencies; /* ... */ + List *relations; /* relation Oids for all tables involved in the assertion check */ + List *inserts; /* relation Oids for which INSERT could invalidate the assertion */ + List *deletes; /* relation Oids for which DELETE could invalidate the assertion */ + List *updates; /* relation Oids for which UPDATE could invalidate the assertion */ + List *columns; /* list of ObjectAddresses referencing involved columns */ +} AssertionInfo; + + +static void initAssertionInfo(AssertionInfo *info); +static void copyAssertionInfo(AssertionInfo *target, AssertionInfo *source); +static DmlOp oppositeDmlOp(DmlOp operation); +static RowCompareType oppositeCompareType(RowCompareType type); +static DmlOp labelForComparisonWithAggFuncs(DmlOp label, RowCompareType compOp, int aggFuncs); +static int16 triggerOnEvents(AssertionInfo *info, Oid relationId); +static List *triggerOnColumns(AssertionInfo *info, Oid relationId); +static bool listContainsObjectAddress(List *list, ObjectAddress *address); +static Bitmapset * strategiesForOperators(List *operators); +static int functionsForTargetList(List *targetList); +static int funcMaskForFuncOid(Oid funcOid); +static Query * queryForSQLFunction(FuncExpr *funcExpr); + +/* Expression visiting */ +static bool visitAllNodes(Node *node, AssertionInfo *info); +static bool visitRangeTblRef(RangeTblRef *node, AssertionInfo *info); +static bool visitQuery(Query *node, AssertionInfo *info); +static bool visitSetOperationStmt(SetOperationStmt *node, AssertionInfo *info); +static bool visitBoolExpr(BoolExpr *node, AssertionInfo *info); +static bool visitSubLink(SubLink *node, AssertionInfo *info); +static bool visitFuncExpr(FuncExpr *node, AssertionInfo *info); +static bool visitExpr(Expr *node, AssertionInfo *info); +static bool visitVar(Var *node, AssertionInfo *info); + +/* TargetList visiting */ +static bool visitAggrefNodes(Node *node, int *aggFuncs); +static bool visitAggref(Aggref *node, int *aggFuncs); +static bool visitWindowFunc(WindowFunc *node, int *aggFuncs); + + +static void +initAssertionInfo(AssertionInfo *info) +{ + info->label = DELETE; + info->expr = NULL; + info->rtable = NIL; + info->operators = NIL; + info->setOp = SETOP_NONE; + info->invert = false; + info->inView = false; + info->inTargetEntry = false; + info->inComparison = false; + info->inExists = false; + + info->dependencies = NIL; + info->relations = NIL; + info->inserts = NIL; + info->deletes = NIL; + info->updates = NIL; + info->columns = NIL; +} + + +static void +copyAssertionInfo(AssertionInfo *target, AssertionInfo *source) +{ + target->label = source->label; + target->expr = source->expr; + target->rtable = source->rtable; + target->operators = source->operators; + target->setOp = source->setOp; + target->invert = source->invert; + target->inView = source->inView; + target->inTargetEntry = source->inTargetEntry; + target->inComparison = source->inComparison; + target->inExists = source->inExists; +} + + +static DmlOp +oppositeDmlOp(DmlOp operation) +{ + switch (operation) + { + case INSERT: + return DELETE; + case DELETE: + return INSERT; + case INSERT_DELETE: + return INSERT_DELETE; + } +} + + +static RowCompareType +oppositeCompareType(RowCompareType type) +{ + switch (type) + { + case ROWCOMPARE_LE: + return ROWCOMPARE_GE; + case ROWCOMPARE_LT: + return ROWCOMPARE_GT; + case ROWCOMPARE_GT: + return ROWCOMPARE_LT; + case ROWCOMPARE_GE: + return ROWCOMPARE_LE; + default: + return type; /* we do not flip EQ and NE comparisons */ + } +} + + +static DmlOp +labelForComparisonWithAggFuncs(DmlOp label, RowCompareType compOp, int aggFuncs) +{ + int stronger, weaker; + + switch (compOp) + { + case ROWCOMPARE_LT: + case ROWCOMPARE_LE: + stronger = MIN_AGG_FUNC; + weaker = MAX_AGG_FUNC | COUNT_AGG_FUNC; + break; + + case ROWCOMPARE_EQ: + stronger = BOOL_AND_AGG_FUNC | EVERY_AGG_FUNC; + weaker = BOOL_OR_AGG_FUNC; + break; + + case ROWCOMPARE_NE: + stronger = BOOL_AND_AGG_FUNC | EVERY_AGG_FUNC; + weaker = BOOL_OR_AGG_FUNC; + break; + + case ROWCOMPARE_GE: + case ROWCOMPARE_GT: + stronger = MAX_AGG_FUNC | COUNT_AGG_FUNC; + weaker = MIN_AGG_FUNC; + break; + + default: + stronger = weaker = NO_FUNC; + } + + if (MatchesOnly(aggFuncs, weaker)) + return label; + else if (MatchesOnly(aggFuncs, stronger)) + return oppositeDmlOp(label); + else + return INSERT_DELETE; +} + + +static int16 +triggerOnEvents(AssertionInfo *info, Oid relationId) +{ + int16 result = 0; + if (list_member_oid(info->inserts, relationId)) + result |= TRIGGER_TYPE_INSERT; + if (list_member_oid(info->deletes, relationId)) + result |= TRIGGER_TYPE_DELETE | TRIGGER_TYPE_TRUNCATE; + if (list_member_oid(info->updates, relationId)) + result |= TRIGGER_TYPE_UPDATE; + return result; +} + + +/* + * Returns a list of string nodes, suitable for use in the trigger + * definition, that contain the column names to be triggered against + * on UPDATE operations. + */ +static List * +triggerOnColumns(AssertionInfo *info, Oid relationId) +{ + List *columns = NIL; + ListCell *cell; + + foreach(cell, info->columns) + { + ObjectAddress *column = (ObjectAddress *) lfirst(cell); + AttrNumber attrNumber = (AttrNumber) column->objectSubId; + + if (column->objectId == relationId) + { + Value *name = makeString(get_attname(relationId, attrNumber, false)); + columns = lappend(columns, name); + } + } + + return columns; +} + + +static bool +listContainsObjectAddress(List *list, ObjectAddress *address) +{ + ListCell *cell; + ObjectAddress *item; + + foreach(cell, list) + { + item = (ObjectAddress *) lfirst(cell); + if (item->classId == address->classId && + item->objectId == address->objectId && + item->objectSubId == address->objectSubId) + return true; + } + + return false; +} + + +static Bitmapset * +strategiesForOperators(List *operators) +{ + ListCell *operatorCell; + Bitmapset *strategies = NULL; + + foreach(operatorCell, operators) + { + Oid operator = lfirst_oid(operatorCell); + List *interpretations = get_op_btree_interpretation(operator); + ListCell *interpretationCell; + + foreach(interpretationCell, interpretations) + strategies = bms_add_member(strategies, + ((OpBtreeInterpretation *) lfirst(interpretationCell))->strategy); + } + + return strategies; +} + + +static int +functionsForTargetList(List *targetList) +{ + int functionMask = NO_FUNC; + expression_tree_walker((Node *) targetList, visitAggrefNodes, &functionMask); + return functionMask; +} + + +static int +funcMaskForFuncOid(Oid funcOid) +{ + char *name = get_func_name(funcOid); + + if (name == NULL) + return OTHER_FUNC; + else if (strncmp(name, "min", strlen("min")) == 0) + return MIN_AGG_FUNC; + else if (strncmp(name, "max", strlen("max")) == 0) + return MAX_AGG_FUNC; + else if (strncmp(name, "count", strlen("count")) == 0) + return COUNT_AGG_FUNC; + else if (strncmp(name, "every", strlen("every")) == 0) + return EVERY_AGG_FUNC; + else if (strncmp(name, "bool_and", strlen("bool_and")) == 0) + return BOOL_AND_AGG_FUNC; + else if (strncmp(name, "bool_or", strlen("bool_or")) == 0) + return BOOL_OR_AGG_FUNC; + else + return OTHER_FUNC; +} + + +static Query * +queryForSQLFunction(FuncExpr *funcExpr) +{ + Oid funcId; + Relation procRel; + HeapTuple tuple; + Datum datum; + bool isNull; + char *sql; + List *rawParseTree; + ParseState *pstate; + Query *query; + SQLFunctionParseInfoPtr pinfo; + + funcId = funcExpr->funcid; + procRel = heap_open(ProcedureRelationId, ShareLock); + + tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcId)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for function %u", funcId); + + datum = SysCacheGetAttr(PROCOID, + tuple, + Anum_pg_proc_prosrc, + &isNull); + if (isNull) + elog(ERROR, "null prosrc for function %u", funcId); + + sql = TextDatumGetCString(datum); + rawParseTree = pg_parse_query(sql); + + pinfo = prepare_sql_fn_parse_info(tuple, + (Node *) funcExpr, + funcExpr->inputcollid); + + pstate = make_parsestate(NULL); + pstate->p_sourcetext = sql; + sql_fn_parser_setup(pstate, pinfo); + + query = transformTopLevelStmt(pstate, linitial(rawParseTree)); + free_parsestate(pstate); + + ReleaseSysCache(tuple); + heap_close(procRel, NoLock); + + return query; +} + + +static bool +visitRangeTblRef(RangeTblRef *node, AssertionInfo *info) +{ + RangeTblEntry *entry; + Oid relationId; + RTEKind rtekind; + char relkind; + bool result; + + entry = rt_fetch(node->rtindex, info->rtable); + rtekind = entry->rtekind; + result = false; + + if (rtekind == RTE_RELATION) + { + relationId = getrelid(node->rtindex, info->rtable); + relkind = get_rel_relkind(relationId); + + if (relkind == RELKIND_RELATION) + { + if (info->label == INSERT || info->label == INSERT_DELETE) + info->inserts = list_append_unique_oid(info->inserts, relationId); + + if (info->label == DELETE || info->label == INSERT_DELETE) + info->deletes = list_append_unique_oid(info->deletes, relationId); + + if (!info->inView) + info->dependencies = list_append_unique_oid(info->dependencies, relationId); + + info->relations = list_append_unique_oid(info->relations, relationId); + + } + else if (relkind == RELKIND_VIEW) + { + Relation view = heap_open(relationId, AccessShareLock); + Query *query = get_view_query(view); + + if (!info->inView) + info->dependencies = list_append_unique_oid(info->dependencies, relationId); + + info->inView = true; + result = visitQuery(query, info); + + heap_close(view, NoLock); + } + + } + else if (rtekind == RTE_TABLEFUNC) + { + result = visitAllNodes((Node *) entry->tablefunc, info); + } + else if (rtekind == RTE_FUNCTION) + { + result = visitAllNodes((Node *) entry->functions, info); + } + else if (rtekind == RTE_SUBQUERY) + { + result = visitQuery(entry->subquery, info); + } + + return result; +} + + +static bool +visitQuery(Query *node, AssertionInfo *info) +{ + info->rtable = node->rtable; + + if (info->inComparison) + { + int functions = functionsForTargetList(node->targetList); + Bitmapset *strategies = strategiesForOperators(info->operators); + + if (CanOptimise(strategies)) + { + RowCompareType rowCompareType = (RowCompareType) bms_first_member(strategies); + info->label = labelForComparisonWithAggFuncs( + info->label, + info->invert ? oppositeCompareType(rowCompareType) : rowCompareType, + functions + ); + } + else + { + /* + * Either no btree interpretation was found for the operator(s), there were + * multiple interpretations that were incompatible with each other, or the + * found interpretations were not able to be optimised. We must therefore + * assume that both INSERT and DELETE operations may be invalidating. + */ + info->label = INSERT_DELETE; + } + } + else if (node->hasWindowFuncs) + { + info->label = INSERT_DELETE; + } + + return query_tree_walker(node, + visitAllNodes, + info, + QTW_IGNORE_RANGE_TABLE); +} + + +static bool +visitSetOperationStmt(SetOperationStmt *node, AssertionInfo *info) +{ + info->setOp = node->op; + + if (visitAllNodes(node->larg, info)) + return true; + + if (info->setOp == SETOP_EXCEPT) + info->label = oppositeDmlOp(info->label); + + return visitAllNodes(node->rarg, info); +} + + +static bool +visitBoolExpr(BoolExpr *node, AssertionInfo *info) +{ + if (node->boolop == NOT_EXPR) + info->label = oppositeDmlOp(info->label); + + return expression_tree_walker((Node *) node, visitAllNodes, (void *) info); +} + + +// TODO refactor this function +static bool +visitSubLink(SubLink *node, AssertionInfo *info) +{ + List *operators; + bool invert; + bool inComparison; + bool inExists; + + switch (node->subLinkType) + { + case ARRAY_SUBLINK: // TODO write tests for this + case EXPR_SUBLINK: + { + Expr *expr = info->expr; + bool hasExpr = (expr != NULL); + + if (hasExpr && IsA(expr, OpExpr)) + { + OpExpr *opExpr = (OpExpr *) expr; + inComparison = true; + inExists = false; + + /* + * Optimisation code is written under the assumption that the sub-select is the + * right operand. If it is the left operand the comparison needs to be inverted. + */ + invert = (list_nth_node(SubLink, opExpr->args, 0) == node); + operators = list_make1_oid(opExpr->opno); + } + else if (hasExpr && IsA(expr, FuncExpr) && + ((FuncExpr *)expr)->funcresulttype == BOOLOID) + { + /* + * We are inside a function invocation that returns Boolean but is not an OpExpr. + * Let's exploit the fact that "expr == TRUE -> expr", and pretend there is an + * equality operator. + */ + inComparison = true; + inExists = false; + operators = list_make1_oid(F_BOOLEQ); + invert = false; + } + else if (exprType((const Node *) node) == BOOLOID) + { + /* + * We are _not_ inside either an OpExpr or FuncExpr, but we are a query that can be + * coerced to Boolean. We use the same logic above e.g. "expr == TRUE -> expr" + */ + inComparison = true; + inExists = false; + operators = list_make1_oid(F_BOOLEQ); + invert = false; + } + else + { + inComparison = false; + inExists = false; + invert = false; + operators = NIL; + } + } + break; + + case ALL_SUBLINK: + case ANY_SUBLINK: + case ROWCOMPARE_SUBLINK: + { + if (IsA(node->testexpr, OpExpr)) + { + OpExpr *expr = (OpExpr *) node->testexpr; + inComparison = true; + inExists = false; + invert = false; + operators = list_make1_oid(expr->opno); + } + else if (IsA(node->testexpr, RowCompareExpr)) + { + RowCompareExpr *expr = (RowCompareExpr *) node->testexpr; + inComparison = true; + inExists = false; + invert = false; + operators = list_copy(expr->opnos); + } + else + { + inComparison = false; + inExists = false; + invert = false; + operators = NIL; + } + } + break; + + case EXISTS_SUBLINK: + { + /* existential quantification, no operators */ + inComparison = false; + inExists = true; + invert = false; + operators = NIL; + } + break; + // case MULTIEXPR_SUBLINK: // TODO write tests for MULTIEXPR_SUBLINK? -- or, can it only occur in UPDATEs? + default: + { + elog(ERROR, "unhandled sublink type %u", node->subLinkType); + return true; + } + + } + + info->operators = operators; + info->invert = invert; + info->inComparison = inComparison; + info->inExists = inExists; + info->setOp = SETOP_NONE; + + return expression_tree_walker((Node *) node, visitAllNodes, (void *) info); +} + + +static bool +visitTargetEntry(TargetEntry *node, AssertionInfo *info) +{ + info->inTargetEntry = true; + return expression_tree_walker((Node *) node, visitAllNodes, (void *) info); +} + + +static bool +visitFromExpr(FromExpr *node, AssertionInfo *info) +{ + info->inExists = false; + info->inComparison = false; + return expression_tree_walker((Node *) node, visitAllNodes, (void *) info); +} + + +static bool +visitFuncExpr(FuncExpr *node, AssertionInfo *info) +{ + Oid lang = get_func_lang(node->funcid); + + info->expr = (Expr *) node; + + if (!(lang == INTERNALlanguageId || lang == SQLlanguageId)) + { + ereport(ERROR, + (errcode(ERRCODE_AMBIGUOUS_FUNCTION), + errmsg("function \"%s\" uses unsupported language \"%s\"", + get_func_name(node->funcid), get_language_name(lang, false)))); + return true; + } + + if (expression_tree_walker((Node *) node, visitAllNodes, (void *) info)) + return true; + + if (lang == SQLlanguageId) + { + Query *query = queryForSQLFunction(node); + Node *next; + if (node->funcretset) + next = (Node *) query; + else + next = (Node *) ((TargetEntry *) linitial(query->targetList))->expr; + + return visitAllNodes(next, info); + } + + return false; +} + + +static bool +visitExpr(Expr *node, AssertionInfo *info) +{ + info->expr = node; + return expression_tree_walker((Node *) node, visitAllNodes, (void *) info); +} + + +static bool +visitVar(Var *node, AssertionInfo *info) +{ + RangeTblEntry *entry = rt_fetch(node->varno, info->rtable); + + // TODO we should do this only for Tables and not Views? + // TODO optimisations for set-operations + if (entry->rtekind == RTE_RELATION && !(info->inExists && info->setOp == SETOP_NONE)) + { + Oid relationId = getrelid(node->varno, info->rtable); + ObjectAddress *column = (ObjectAddress *) palloc(sizeof(ObjectAddress)); + + column->classId = RelationRelationId; + column->objectId = relationId; + column->objectSubId = node->varattno; + + if (!listContainsObjectAddress(info->columns, column)) + info->columns = lcons(column, info->columns); + + info->relations = list_append_unique_oid(info->relations, relationId); + info->updates = list_append_unique_oid(info->updates, relationId); + } + + return expression_tree_walker((Node *) node, visitAllNodes, (void *) info); +} + + +static bool +visitAllNodes(Node *node, AssertionInfo *info) +{ + AssertionInfo stored; + bool result; + + copyAssertionInfo(&stored, info); + + if (node == NULL) + result = false; + else if (IsA(node, RangeTblRef)) + result = visitRangeTblRef((RangeTblRef *) node, info); + else if (IsA(node, Query)) + result = visitQuery((Query *) node, info); + else if (IsA(node, BoolExpr)) + result = visitBoolExpr((BoolExpr *) node, info); + else if (IsA(node, SubLink)) + result = visitSubLink((SubLink *) node, info); + else if (IsA(node, SetOperationStmt)) + result = visitSetOperationStmt((SetOperationStmt *) node, info); + else if (IsA(node, FuncExpr)) + result = visitFuncExpr((FuncExpr *) node, info); + else if (IsA(node, OpExpr) || + IsA(node, DistinctExpr) || + IsA(node, NullIfExpr)) + result = visitExpr((Expr *) node, info); + else if (IsA(node, TargetEntry)) + result = visitTargetEntry((TargetEntry *) node, info); + else if (IsA(node, Var)) + result = visitVar((Var *) node, info); + else if (IsA(node, FromExpr)) + result = visitFromExpr((FromExpr *) node, info); + else + result = expression_tree_walker(node, visitAllNodes, (void *) info); + + copyAssertionInfo(info, &stored); + + return result; +} + + +static bool +visitAggrefNodes(Node *node, int *aggFuncs) +{ + if (node == NULL) + return false; + else if (IsA(node, Aggref)) + return visitAggref((Aggref *) node, aggFuncs); + else if(IsA(node, WindowFunc)) + return visitWindowFunc((WindowFunc *) node, aggFuncs); + else + return expression_tree_walker(node, visitAggrefNodes, (void *) aggFuncs); +} + + +static bool +visitAggref(Aggref *node, int *aggFuncs) +{ + *aggFuncs |= funcMaskForFuncOid(node->aggfnoid); + return expression_tree_walker((Node *) node, visitAggrefNodes, (void *) aggFuncs); +} + + +static bool +visitWindowFunc(WindowFunc *node, int *aggFuncs) +{ + *aggFuncs |= funcMaskForFuncOid(node->winfnoid); + + return expression_tree_walker((Node *) node, visitAggrefNodes, (void *) aggFuncs); +} + + +ObjectAddress +CreateAssertion(CreateAssertionStmt *stmt) +{ + Oid namespaceId; + char *assertion_name; + AclResult aclresult; + Node *expr; + ParseState *pstate; + char *ccsrc; + char *ccbin; + Oid constrOid; + AssertionInfo info; + ListCell *lc; + ObjectAddress address; + + namespaceId = QualifiedNameGetCreationNamespace(stmt->assertion_name, + &assertion_name); + + aclresult = pg_namespace_aclcheck(namespaceId, GetUserId(), ACL_CREATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_SCHEMA, + get_namespace_name(namespaceId)); + + if (ConstraintNameIsUsed(CONSTRAINT_ASSERTION, InvalidOid, namespaceId, assertion_name)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_NAME), + errmsg("assertion \"%s\" already exists", assertion_name))); + + pstate = make_parsestate(NULL); + expr = transformExpr(pstate, stmt->constraint->raw_expr, EXPR_KIND_ASSERTION_CHECK); + expr = coerce_to_boolean(pstate, expr, "CHECK"); + + ccbin = nodeToString(expr); + ccsrc = deparse_expression(expr, NIL, false, false); + + constrOid = CreateConstraintEntry(assertion_name, + namespaceId, + CONSTRAINT_CHECK, /* constraint type */ + stmt->constraint->deferrable, + stmt->constraint->initdeferred, + !stmt->constraint->skip_validation, + InvalidOid, /* not a relation constraint */ + NULL, /* no keys */ + 0, /* no keys */ + InvalidOid, /* not a domain constraint */ + InvalidOid, /* no associated index */ + InvalidOid, /* foreign key fields ... */ + NULL, + NULL, + NULL, + NULL, + 0, + ' ', + ' ', + ' ', + NULL, /* not an exclusion constraint */ + expr, /* tree form of check constraint */ + ccbin, /* binary form of check constraint */ + ccsrc, /* source form of check constraint */ + true, /* is local */ + 0, /* inhcount */ + false, /* noinherit XXX */ + false); /* is_internal */ + + initAssertionInfo(&info); + visitAllNodes(expr, &info); + + foreach (lc, info.relations) + { + Oid relationId = lfirst_oid(lc); + CreateTrigStmt *trigger; + Relation rel; + + rel = heap_open(relationId, ShareLock); // XXX + + trigger = makeNode(CreateTrigStmt); + trigger->trigname = "AssertionTrigger"; + trigger->relation = makeRangeVar(get_namespace_name(namespaceId), + pstrdup(RelationGetRelationName(rel)), + -1); + trigger->funcname = SystemFuncName("assertion_check"); + trigger->args = NIL; + trigger->row = false; + trigger->timing = TRIGGER_TYPE_AFTER; + trigger->events = triggerOnEvents(&info, relationId); + trigger->columns = triggerOnColumns(&info, relationId); + trigger->whenClause = NULL; + trigger->isconstraint = true; + trigger->deferrable = stmt->constraint->deferrable; + trigger->initdeferred = stmt->constraint->initdeferred; + trigger->constrrel = NULL; + + CreateTrigger(trigger, NULL, InvalidOid, relationId, constrOid, InvalidOid, true); + + heap_close(rel, NoLock); + } + + /* + * Record dependencies between the constraint and the relations found in the + * top-level expression. Dependencies to specific columns will already have + * been recorded by the trigger creation. + */ + ObjectAddress myself, referenced; + ListCell *cell; + + myself.classId = ConstraintRelationId; + myself.objectId = constrOid; + myself.objectSubId = 0; + + foreach (cell, info.dependencies) + { + referenced.classId = RelationRelationId; + referenced.objectId = lfirst_oid(cell); + referenced.objectSubId = 0; + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + } + + test_assertion_expr(assertion_name, ccsrc); + + ObjectAddressSet(address, ConstraintRelationId, constrOid); + + return address; +} + + +ObjectAddress +RenameAssertion(RenameStmt *stmt) +{ + Oid assertionOid; + ObjectAddress address; + Relation rel; + HeapTuple tuple; + Form_pg_constraint con; + AclResult aclresult; + + List *oldName = castNode(List, stmt->object); + char *newName = stmt->newname; + + rel = heap_open(ConstraintRelationId, RowExclusiveLock); + assertionOid = get_assertion_oid(oldName, false); + + tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(assertionOid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for constraint %u", + assertionOid); + con = (Form_pg_constraint) GETSTRUCT(tuple); + + if (!pg_constraint_ownercheck(assertionOid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_CONVERSION, + NameListToString(oldName)); + + aclresult = pg_namespace_aclcheck(con->connamespace, GetUserId(), ACL_CREATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_SCHEMA, + get_namespace_name(con->connamespace)); + + ReleaseSysCache(tuple); + RenameConstraintById(assertionOid, newName); + ObjectAddressSet(address, ConstraintRelationId, assertionOid); + heap_close(rel, NoLock); + + return address; +} diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index 549c7ea51d..95e6a6687d 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -88,6 +88,7 @@ typedef enum static event_trigger_support_data event_trigger_support[] = { {"ACCESS METHOD", true}, {"AGGREGATE", true}, + {"ASSERTION", true}, {"CAST", true}, {"CONSTRAINT", true}, {"COLLATION", true}, @@ -1081,6 +1082,7 @@ EventTriggerSupportsObjectType(ObjectType obtype) return false; case OBJECT_ACCESS_METHOD: case OBJECT_AGGREGATE: + case OBJECT_ASSERTION: case OBJECT_AMOP: case OBJECT_AMPROC: case OBJECT_ATTRIBUTE: diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 3ad4da64aa..0bbb8bb87a 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -4320,6 +4320,17 @@ _copyCreateSchemaStmt(const CreateSchemaStmt *from) return newnode; } +static CreateAssertionStmt * +_copyCreateAssertionStmt(const CreateAssertionStmt *from) +{ + CreateAssertionStmt *newnode = makeNode(CreateAssertionStmt); + + COPY_NODE_FIELD(assertion_name); + COPY_NODE_FIELD(constraint); + + return newnode; +} + static CreateConversionStmt * _copyCreateConversionStmt(const CreateConversionStmt *from) { @@ -5389,6 +5400,9 @@ copyObjectImpl(const void *from) case T_CreateSchemaStmt: retval = _copyCreateSchemaStmt(from); break; + case T_CreateAssertionStmt: + retval = _copyCreateAssertionStmt(from); + break; case T_CreateConversionStmt: retval = _copyCreateConversionStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 765b1be74b..9ac7971b0c 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2121,6 +2121,15 @@ _equalCreateSchemaStmt(const CreateSchemaStmt *a, const CreateSchemaStmt *b) return true; } +static bool +_equalCreateAssertionStmt(const CreateAssertionStmt *a, const CreateAssertionStmt *b) +{ + COMPARE_NODE_FIELD(assertion_name); + COMPARE_NODE_FIELD(constraint); + + return true; +} + static bool _equalCreateConversionStmt(const CreateConversionStmt *a, const CreateConversionStmt *b) { @@ -3521,6 +3530,9 @@ equal(const void *a, const void *b) case T_CreateSchemaStmt: retval = _equalCreateSchemaStmt(a, b); break; + case T_CreateAssertionStmt: + retval = _equalCreateAssertionStmt(a, b); + break; case T_CreateConversionStmt: retval = _equalCreateConversionStmt(a, b); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index cd5ba2d4d8..22926ff03a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -259,11 +259,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt CreateSchemaStmt CreateSeqStmt CreateStmt CreateStatsStmt CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt - CreateAssertStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt + CreateAssertionStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt DropOpClassStmt DropOpFamilyStmt DropPLangStmt DropStmt - DropAssertStmt DropCastStmt DropRoleStmt + DropCastStmt DropRoleStmt DropdbStmt DropTableSpaceStmt DropTransformStmt DropUserMappingStmt ExplainStmt FetchStmt @@ -859,7 +859,7 @@ stmt : | CopyStmt | CreateAmStmt | CreateAsStmt - | CreateAssertStmt + | CreateAssertionStmt | CreateCastStmt | CreateConversionStmt | CreateDomainStmt @@ -895,7 +895,6 @@ stmt : | DeleteStmt | DiscardStmt | DoStmt - | DropAssertStmt | DropCastStmt | DropOpClassStmt | DropOpFamilyStmt @@ -5624,45 +5623,28 @@ enable_trigger: * * QUERIES : * CREATE ASSERTION ... - * DROP ASSERTION ... * *****************************************************************************/ -CreateAssertStmt: - CREATE ASSERTION name CHECK '(' a_expr ')' +CreateAssertionStmt: + CREATE ASSERTION any_name CHECK '(' a_expr ')' ConstraintAttributeSpec { - CreateTrigStmt *n = makeNode(CreateTrigStmt); - n->trigname = $3; - n->args = list_make1($6); - n->isconstraint = true; - processCASbits($8, @8, "ASSERTION", - &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); - - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("CREATE ASSERTION is not yet implemented"))); - - $$ = (Node *)n; - } - ; - -DropAssertStmt: - DROP ASSERTION name opt_drop_behavior - { - DropStmt *n = makeNode(DropStmt); - n->objects = NIL; - n->behavior = $4; - n->removeType = OBJECT_TRIGGER; /* XXX */ - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("DROP ASSERTION is not yet implemented"))); - $$ = (Node *) n; + CreateAssertionStmt *n = makeNode(CreateAssertionStmt); + Constraint *c = makeNode(Constraint); + c->contype = CONSTR_CHECK; + c->location = @4; + c->raw_expr = $6; + c->cooked_expr = NULL; + processCASbits($8, @8, "ASSERTION", + &c->deferrable, &c->initdeferred, NULL, + NULL, yyscanner); + n->assertion_name = $3; + n->constraint = c; + $$ = (Node *)n; } ; - /***************************************************************************** * * QUERY : @@ -6316,6 +6298,7 @@ drop_type_any_name: | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } | INDEX { $$ = OBJECT_INDEX; } | FOREIGN TABLE { $$ = OBJECT_FOREIGN_TABLE; } + | ASSERTION { $$ = OBJECT_ASSERTION; } | COLLATION { $$ = OBJECT_COLLATION; } | CONVERSION_P { $$ = OBJECT_CONVERSION; } | STATISTICS { $$ = OBJECT_STATISTIC_EXT; } @@ -6585,6 +6568,7 @@ comment_type_any_name: | TABLE { $$ = OBJECT_TABLE; } | VIEW { $$ = OBJECT_VIEW; } | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } + | ASSERTION { $$ = OBJECT_ASSERTION; } | COLLATION { $$ = OBJECT_COLLATION; } | CONVERSION_P { $$ = OBJECT_CONVERSION; } | FOREIGN TABLE { $$ = OBJECT_FOREIGN_TABLE; } @@ -8437,6 +8421,15 @@ RenameStmt: ALTER AGGREGATE aggregate_with_argtypes RENAME TO name n->missing_ok = false; $$ = (Node *)n; } + | ALTER ASSERTION any_name RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_ASSERTION; + n->object = (Node *) $3; + n->newname = $6; + n->missing_ok = false; + $$ = (Node *)n; + } | ALTER COLLATION any_name RENAME TO name { RenameStmt *n = makeNode(RenameStmt); @@ -9021,6 +9014,15 @@ AlterObjectSchemaStmt: n->missing_ok = false; $$ = (Node *)n; } + | ALTER ASSERTION any_name SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_ASSERTION; + n->object = (Node *) $3; + n->newschema = $6; + n->missing_ok = false; + $$ = (Node *)n; + } | ALTER COLLATION any_name SET SCHEMA name { AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); @@ -9306,6 +9308,14 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec n->newowner = $6; $$ = (Node *)n; } + | ALTER ASSERTION any_name OWNER TO RoleSpec + { + AlterOwnerStmt *n = makeNode(AlterOwnerStmt); + n->objectType = OBJECT_ASSERTION; + n->object = (Node *) $3; + n->newowner = $6; + $$ = (Node *)n; + } | ALTER COLLATION any_name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 377a7ed6d0..223cea1134 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -457,6 +457,7 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) break; case EXPR_KIND_CHECK_CONSTRAINT: case EXPR_KIND_DOMAIN_CHECK: + case EXPR_KIND_ASSERTION_CHECK: if (isAgg) err = _("aggregate functions are not allowed in check constraints"); else @@ -875,6 +876,7 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, break; case EXPR_KIND_CHECK_CONSTRAINT: case EXPR_KIND_DOMAIN_CHECK: + case EXPR_KIND_ASSERTION_CHECK: err = _("window functions are not allowed in check constraints"); break; case EXPR_KIND_COLUMN_DEFAULT: diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 385e54a9b6..1933a615bf 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1818,6 +1818,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_RETURNING: case EXPR_KIND_VALUES: case EXPR_KIND_VALUES_SINGLE: + case EXPR_KIND_ASSERTION_CHECK: /* okay */ break; case EXPR_KIND_CHECK_CONSTRAINT: @@ -3457,6 +3458,7 @@ ParseExprKindName(ParseExprKind exprKind) return "VALUES"; case EXPR_KIND_CHECK_CONSTRAINT: case EXPR_KIND_DOMAIN_CHECK: + case EXPR_KIND_ASSERTION_CHECK: return "CHECK"; case EXPR_KIND_COLUMN_DEFAULT: case EXPR_KIND_FUNCTION_DEFAULT: diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index ea5d5212b4..8193b7da37 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2277,6 +2277,10 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) /* okay, since we process this like a SELECT tlist */ pstate->p_hasTargetSRFs = true; break; + case EXPR_KIND_ASSERTION_CHECK: + /* okay */ + pstate->p_hasTargetSRFs = true; + break; case EXPR_KIND_CHECK_CONSTRAINT: case EXPR_KIND_DOMAIN_CHECK: err = _("set-returning functions are not allowed in check constraints"); diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index ed55521a0c..9ead34af3d 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -30,6 +30,7 @@ #include "commands/cluster.h" #include "commands/comment.h" #include "commands/collationcmds.h" +#include "commands/constraint.h" #include "commands/conversioncmds.h" #include "commands/copy.h" #include "commands/createas.h" @@ -160,6 +161,7 @@ check_xact_readonly(Node *parsetree) case T_RenameStmt: case T_CommentStmt: case T_DefineStmt: + case T_CreateAssertionStmt: case T_CreateCastStmt: case T_CreateEventTrigStmt: case T_AlterEventTrigStmt: @@ -1503,6 +1505,10 @@ ProcessUtilitySlow(ParseState *pstate, address = DefineDomain((CreateDomainStmt *) parsetree); break; + case T_CreateAssertionStmt: + address = CreateAssertion((CreateAssertionStmt *) parsetree); + break; + case T_CreateConversionStmt: address = CreateConversionCommand((CreateConversionStmt *) parsetree); break; @@ -1913,6 +1919,9 @@ AlterObjectTypeCommandTag(ObjectType objtype) case OBJECT_AGGREGATE: tag = "ALTER AGGREGATE"; break; + case OBJECT_ASSERTION: + tag = "ALTER ASSERTION"; + break; case OBJECT_ATTRIBUTE: tag = "ALTER TYPE"; break; @@ -2251,6 +2260,9 @@ CreateCommandTag(Node *parsetree) case OBJECT_DOMAIN: tag = "DROP DOMAIN"; break; + case OBJECT_ASSERTION: + tag = "DROP ASSERTION"; + break; case OBJECT_COLLATION: tag = "DROP COLLATION"; break; @@ -2681,6 +2693,10 @@ CreateCommandTag(Node *parsetree) tag = "REINDEX"; break; + case T_CreateAssertionStmt: + tag = "CREATE ASSERTION"; + break; + case T_CreateConversionStmt: tag = "CREATE CONVERSION"; break; @@ -3281,6 +3297,10 @@ GetCommandLogLevel(Node *parsetree) lev = LOGSTMT_ALL; /* should this be DDL? */ break; + case T_CreateAssertionStmt: + lev = LOGSTMT_DDL; + break; + case T_CreateConversionStmt: lev = LOGSTMT_DDL; break; diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index bba595ad1d..6a0d6edb81 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1542,6 +1542,25 @@ get_func_retset(Oid funcid) return result; } +/* + * get_func_lang + * Given procedure id, return the function's language id. + */ +Oid +get_func_lang(Oid funcid) +{ + HeapTuple tp; + Oid result; + + tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for function %u", funcid); + + result = ((Form_pg_proc) GETSTRUCT(tp))->prolang; + ReleaseSysCache(tp); + return result; +} + /* * func_strict * Given procedure id, return the function's proisstrict flag. diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 3560318749..2635b280de 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -785,6 +785,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) case 'p': success = permissionsList(pattern); break; + case 'Q': + success = describeAssertions(pattern); + break; case 'T': success = describeTypes(pattern, show_verbose, show_system); break; diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 0c3be1f504..762de44ff6 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -140,6 +140,66 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) return true; } +/* \dA + * Takes an optional regexp to select particular assertions + */ +bool +describeAssertions(const char *pattern) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + + if (pset.sversion < 90400) + { + fprintf(stderr, _("The server (version %d.%d) does not support assertions.\n"), + pset.sversion / 10000, (pset.sversion / 100) % 100); + return true; + } + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT n.nspname AS \"%s\",\n" + " c.conname AS \"%s\",\n" + " pg_catalog.pg_get_constraintdef(c.oid, true) AS \"%s\",\n" + " pg_catalog.obj_description(c.oid, 'pg_constraint') AS \"%s\"\n" + "FROM pg_catalog.pg_constraint c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace\n" + "WHERE c.conrelid = 0 AND c.contypid = 0\n", + gettext_noop("Schema"), + gettext_noop("Name"), + gettext_noop("Definition"), + gettext_noop("Description")); + + if (!pattern) + appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname <> 'information_schema'\n"); + +#if TODO + processSQLNamePattern(pset.db, &buf, pattern, true, false, + "n.nspname", "c.conname", NULL, + "pg_catalog.pg_constraint_is_visible(c.oid)"); +#endif + + appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;"); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + myopt.nullPrint = NULL; + myopt.title = _("List of assertions"); + myopt.translate_header = true; + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + + PQclear(res); + return true; +} + + /* * \dA * Takes an optional regexp to select particular access methods @@ -2518,6 +2578,38 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } + /* print assertions referencing this table (none if no triggers) */ + if (tableinfo.hastriggers) + { + printfPQExpBuffer(&buf, + "SELECT conname,\n" + " pg_catalog.pg_get_constraintdef(c.oid, true) AS condef\n" + "FROM pg_catalog.pg_constraint c\n" + "WHERE c.oid IN (SELECT objid FROM pg_depend WHERE classid = 'pg_constraint'::pg_catalog.regclass AND refclassid = 'pg_class'::pg_catalog.regclass AND refobjid = '%s')\n" + " AND c.conrelid = 0 AND c.contypid = 0 AND c.contype = 'c'\n" + "ORDER BY 1", + oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + else + tuples = PQntuples(result); + + if (tuples > 0) + { + printTableAddFooter(&cont, _("Assertions:")); + for (i = 0; i < tuples; i++) + { + printfPQExpBuffer(&buf, " \"%s\" %s", + PQgetvalue(result, i, 0), + PQgetvalue(result, i, 1)); + + printTableAddFooter(&cont, buf.data); + } + } + PQclear(result); + } + /* print rules */ if (tableinfo.hasrules && tableinfo.relkind != RELKIND_MATVIEW) { diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index a4cc5efae0..7b405b65da 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -15,6 +15,9 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSyste /* \dA */ extern bool describeAccessMethods(const char *pattern, bool verbose); +/* \dQ */ +extern bool describeAssertions(const char *pattern); + /* \db */ extern bool describeTablespaces(const char *pattern, bool verbose); diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 702e742af4..9deabd195f 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -223,6 +223,7 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\d[S+] list tables, views, and sequences\n")); fprintf(output, _(" \\d[S+] NAME describe table, view, sequence, or index\n")); fprintf(output, _(" \\da[S] [PATTERN] list aggregates\n")); + fprintf(output, _(" \\dQ [PATTERN] list assertions\n")); fprintf(output, _(" \\dA[+] [PATTERN] list access methods\n")); fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n")); fprintf(output, _(" \\dc[S+] [PATTERN] list conversions\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 08d8ef09a4..923ee42a03 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -424,6 +424,24 @@ static const SchemaQuery Query_for_list_of_aggregates[] = { } }; +static const SchemaQuery Query_for_list_of_assertions = { + /* min_server_version */ + 0, + /* catname */ + "pg_catalog.pg_constraint c", + /* selcondition */ + "(c.conrelid = 0 AND c.contypid = 0)", + /* viscondition */ + "TRUE", //TODO: "pg_catalog.pg_constraint_is_visible(c.oid)", + /* namespace */ + "c.connamespace", + /* result */ + "pg_catalog.quote_ident(c.conname)", + /* qualresult */ + NULL +}; + + static const SchemaQuery Query_for_list_of_datatypes = { /* min_server_version */ 0, @@ -1192,6 +1210,7 @@ typedef struct static const pgsql_thing_t words_after_create[] = { {"ACCESS METHOD", NULL, NULL, NULL, THING_NO_ALTER}, {"AGGREGATE", NULL, NULL, Query_for_list_of_aggregates}, + {"ASSERTION", NULL, NULL, &Query_for_list_of_assertions}, {"CAST", NULL, NULL, NULL}, /* Casts have complex structures for names, so * skip it */ {"COLLATION", "SELECT pg_catalog.quote_ident(collname) FROM pg_catalog.pg_collation WHERE collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding())) AND substring(pg_catalog.quote_ident(collname),1,%d)='%s'"}, @@ -1618,7 +1637,7 @@ psql_completion(const char *text, int start, int end) "\\a", "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy", "\\copyright", "\\crosstabview", - "\\d", "\\da", "\\dA", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD", + "\\d", "\\da", "\\dA", "\\dQ", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df", "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", "\\dp", @@ -1769,6 +1788,11 @@ psql_completion(const char *text, int start, int end) else if (Matches3("ALTER", "SCHEMA", MatchAny)) COMPLETE_WITH_LIST2("OWNER TO", "RENAME TO"); + // TODO ASSERTION + + else if (Matches3("ALTER", "ASSERTION", MatchAny)) + COMPLETE_WITH_CONST("RENAME TO "); + /* ALTER COLLATION */ else if (Matches3("ALTER", "COLLATION", MatchAny)) COMPLETE_WITH_LIST3("OWNER TO", "RENAME TO", "SET SCHEMA"); @@ -2896,7 +2920,7 @@ psql_completion(const char *text, int start, int end) /* DROP */ /* Complete DROP object with CASCADE / RESTRICT */ else if (Matches3("DROP", - "COLLATION|CONVERSION|DOMAIN|EXTENSION|LANGUAGE|PUBLICATION|SCHEMA|SEQUENCE|SERVER|SUBSCRIPTION|STATISTICS|TABLE|TYPE|VIEW", + "ASSERTION|COLLATION|CONVERSION|DOMAIN|EXTENSION|LANGUAGE|PUBLICATION|SCHEMA|SEQUENCE|SERVER|SUBSCRIPTION|STATISTICS|TABLE|TYPE|VIEW", MatchAny) || Matches4("DROP", "ACCESS", "METHOD", MatchAny) || (Matches4("DROP", "AGGREGATE|FUNCTION|PROCEDURE|ROUTINE", MatchAny, MatchAny) && @@ -3628,6 +3652,8 @@ psql_completion(const char *text, int start, int end) } else if (TailMatchesCS1("\\da*")) COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL); + else if (TailMatchesCS1("\\dQ*")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_assertions, NULL); else if (TailMatchesCS1("\\dA*")) COMPLETE_WITH_QUERY(Query_for_list_of_access_methods); else if (TailMatchesCS1("\\db*")) diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h index 5f8cf4992e..841be6db59 100644 --- a/src/include/catalog/namespace.h +++ b/src/include/catalog/namespace.h @@ -140,6 +140,7 @@ extern bool OverrideSearchPathMatchesCurrent(OverrideSearchPath *path); extern void PushOverrideSearchPath(OverrideSearchPath *newpath); extern void PopOverrideSearchPath(void); +extern Oid get_assertion_oid(List *name, bool missing_ok); extern Oid get_collation_oid(List *collname, bool missing_ok); extern Oid get_conversion_oid(List *conname, bool missing_ok); extern Oid FindDefaultConversionProc(int32 for_encoding, int32 to_encoding); diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index 8fca86d71e..060e436fcd 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -57,8 +57,7 @@ CATALOG(pg_constraint,2606) * contypid links to the pg_type row for a domain if this is a domain * constraint. Otherwise it's 0. * - * For SQL-style global ASSERTIONs, both conrelid and contypid would be - * zero. This is not presently supported, however. + * For SQL-style global ASSERTIONs, both conrelid and contypid are zero. */ Oid contypid; /* domain this constraint constrains */ diff --git a/src/include/catalog/pg_constraint_fn.h b/src/include/catalog/pg_constraint_fn.h index d3351f4a83..9c44c4c6da 100644 --- a/src/include/catalog/pg_constraint_fn.h +++ b/src/include/catalog/pg_constraint_fn.h @@ -24,7 +24,7 @@ typedef enum ConstraintCategory { CONSTRAINT_RELATION, CONSTRAINT_DOMAIN, - CONSTRAINT_ASSERTION /* for future expansion */ + CONSTRAINT_ASSERTION } ConstraintCategory; extern Oid CreateConstraintEntry(const char *constraintName, diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 0fdb42f639..463190724d 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2101,6 +2101,10 @@ DESCR("oid of replica identity index if any"); DATA(insert OID = 1250 ( unique_key_recheck PGNSP PGUID 12 1 0 0 0 f f f t f v s 0 0 2279 "" _null_ _null_ _null_ _null_ _null_ unique_key_recheck _null_ _null_ _null_ )); DESCR("deferred UNIQUE constraint check"); +DATA(insert OID = 3556 ( assertion_check PGNSP PGUID 12 1 0 0 0 f f f t f v s 0 0 2279 "" _null_ _null_ _null_ _null_ _null_ assertion_check _null_ _null_ _null_ )); +DESCR("assertion check"); + + /* Generic referential integrity constraint triggers */ DATA(insert OID = 1644 ( RI_FKey_check_ins PGNSP PGUID 12 1 0 0 0 f f f t f v s 0 0 2279 "" _null_ _null_ _null_ _null_ _null_ RI_FKey_check_ins _null_ _null_ _null_ )); DESCR("referential integrity FOREIGN KEY ... REFERENCES"); diff --git a/src/include/commands/constraint.h b/src/include/commands/constraint.h new file mode 100644 index 0000000000..98aa8f996e --- /dev/null +++ b/src/include/commands/constraint.h @@ -0,0 +1,10 @@ +#ifndef CONSTRAINT_H +#define CONSTRAINT_H + +#include "catalog/objectaddress.h" +#include "nodes/parsenodes.h" + +extern ObjectAddress CreateAssertion(CreateAssertionStmt *stmt); +extern ObjectAddress RenameAssertion(RenameStmt *stmt); + +#endif \ No newline at end of file diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 74b094a9c3..6c9b89e139 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -414,6 +414,7 @@ typedef enum NodeTag T_DropSubscriptionStmt, T_CreateStatsStmt, T_AlterCollationStmt, + T_CreateAssertionStmt, T_CallStmt, /* diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 92082b3a7a..d63a7a28ec 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1635,6 +1635,7 @@ typedef enum ObjectType OBJECT_AGGREGATE, OBJECT_AMOP, OBJECT_AMPROC, + OBJECT_ASSERTION, OBJECT_ATTRIBUTE, /* type's attribute, when distinct from column */ OBJECT_CAST, OBJECT_COLUMN, @@ -3283,6 +3284,16 @@ typedef struct ReindexStmt int options; /* Reindex options flags */ } ReindexStmt; +/* ---------------------- + * CREATE ASSERTION Statement + */ +typedef struct CreateAssertionStmt +{ + NodeTag type; + List *assertion_name; + Constraint *constraint; +} CreateAssertionStmt; + /* ---------------------- * CREATE CONVERSION Statement * ---------------------- diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 0230543810..54a6b114ff 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -60,6 +60,7 @@ typedef enum ParseExprKind EXPR_KIND_VALUES_SINGLE, /* single-row VALUES (in INSERT only) */ EXPR_KIND_CHECK_CONSTRAINT, /* CHECK constraint for a table */ EXPR_KIND_DOMAIN_CHECK, /* CHECK constraint for a domain */ + EXPR_KIND_ASSERTION_CHECK, /* CHECK constraint for an assertion */ EXPR_KIND_COLUMN_DEFAULT, /* default value for a table column */ EXPR_KIND_FUNCTION_DEFAULT, /* default parameter value for function */ EXPR_KIND_INDEX_EXPRESSION, /* index expression */ diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h index f4d4be8d0d..2c10dd082f 100644 --- a/src/include/utils/acl.h +++ b/src/include/utils/acl.h @@ -295,6 +295,7 @@ extern bool pg_opclass_ownercheck(Oid opc_oid, Oid roleid); extern bool pg_opfamily_ownercheck(Oid opf_oid, Oid roleid); extern bool pg_database_ownercheck(Oid db_oid, Oid roleid); extern bool pg_collation_ownercheck(Oid coll_oid, Oid roleid); +extern bool pg_constraint_ownercheck(Oid constr_oid, Oid roleid); extern bool pg_conversion_ownercheck(Oid conv_oid, Oid roleid); extern bool pg_ts_dict_ownercheck(Oid dict_oid, Oid roleid); extern bool pg_ts_config_ownercheck(Oid cfg_oid, Oid roleid); diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index e55ea4035b..f618270bc8 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -114,6 +114,7 @@ extern int get_func_nargs(Oid funcid); extern Oid get_func_signature(Oid funcid, Oid **argtypes, int *nargs); extern Oid get_func_variadictype(Oid funcid); extern bool get_func_retset(Oid funcid); +extern Oid get_func_lang(Oid funcid); extern bool func_strict(Oid funcid); extern char func_volatile(Oid funcid); extern char func_parallel(Oid funcid); diff --git a/src/test/regress/expected/assertions.out b/src/test/regress/expected/assertions.out new file mode 100644 index 0000000000..7fc0d710a3 --- /dev/null +++ b/src/test/regress/expected/assertions.out @@ -0,0 +1,779 @@ +BEGIN TRANSACTION; +-- +-- Create some helper views and functions to allow us to more easily +-- assert which operations will cause an assertion to be checked. +-- +CREATE OR REPLACE VIEW invalidating_operation + (assertion_name, + relation_name, + operation) +AS +SELECT c.conname, + cl.relname, + v.operation + FROM pg_trigger t + JOIN pg_constraint c ON (t.tgconstraint = c.oid) + JOIN pg_proc p ON (t.tgfoid = p.oid) + JOIN pg_class cl ON (t.tgrelid = cl.oid) + JOIN (VALUES ('INSERT', 1<<2), + ('DELETE', 1<<3|1<<5), + ('UPDATE', 1<<4)) + AS v(operation, mask) ON ((v.mask & tgtype) > 0) + WHERE t.tgisinternal + AND p.proname = 'assertion_check'; +CREATE OR REPLACE VIEW invalidating_by_update_of_column + (assertion_name, + relation_name, + column_name) +AS +SELECT c.conname, + cl.relname, + a.attname + FROM pg_trigger t INNER JOIN pg_constraint c ON (t.tgconstraint = c.oid) + INNER JOIN pg_proc p ON (t.tgfoid = p.oid) + INNER JOIN pg_class cl ON (t.tgrelid = cl.oid) + CROSS JOIN LATERAL UNNEST(t.tgattr) AS co(n) + INNER JOIN pg_attribute a ON (a.attrelid = cl.oid AND attnum = co.n) + WHERE t.tgisinternal + AND p.proname = 'assertion_check' + AND (tgtype & (1 << 4)) > 0; +CREATE OR REPLACE VIEW invalidating_summary + (assertion_name, + operations) +AS +SELECT assertion_name, + string_agg(operation, ' ' ORDER BY operation) + FROM (SELECT assertion_name, + operation ||'('|| string_agg(relation_name, ', ' ORDER BY relation_name) ||')' + FROM invalidating_operation + WHERE operation <> 'UPDATE' + GROUP BY assertion_name, + operation + UNION + SELECT assertion_name, + operation ||'('|| string_agg(relation_name ||'.'|| column_name, ', ' ORDER BY relation_name, column_name) ||')' + FROM invalidating_by_update_of_column JOIN invalidating_operation USING (assertion_name, relation_name) + WHERE operation = 'UPDATE' + GROUP BY assertion_name, + operation) + AS v(assertion_name, operation) + GROUP BY assertion_name; +CREATE OR REPLACE FUNCTION have_identical_invalidating_operations + (a text, b text) +RETURNS TABLE (predicate text, correct text) +AS $$ + SELECT 'assertion "'|| a ||'" has identical invalidating operations to assertion "'|| b ||'"', + CASE WHEN + NOT EXISTS (SELECT relation_name, operation + FROM invalidating_operation + WHERE assertion_name = a + EXCEPT + SELECT relation_name, operation + FROM invalidating_operation + WHERE assertion_name = b) AND + NOT EXISTS (SELECT relation_name, column_name + FROM invalidating_by_update_of_column + WHERE assertion_name = a + EXCEPT + SELECT relation_name, column_name + FROM invalidating_by_update_of_column + WHERE assertion_name = b) THEN 'YES' + ELSE 'NO' END +$$ LANGUAGE SQL; +COMMIT; +BEGIN TRANSACTION; +-- +-- Perform some basic sanity checking on creating assertions +-- +CREATE TABLE test1 (a int, b text); +CREATE ASSERTION foo CHECK (1 < 2); +CREATE ASSERTION a2 CHECK ((SELECT count(*) FROM test1) < 5); +DELETE FROM test1; +INSERT INTO test1 VALUES (1, 'one'); +INSERT INTO test1 VALUES (2, 'two'); +INSERT INTO test1 VALUES (3, 'three'); +INSERT INTO test1 VALUES (4, 'four'); +SAVEPOINT pre_insert_too_many; +INSERT INTO test1 VALUES (5, 'five'); +ERROR: assertion "a2" violated +ROLLBACK TO SAVEPOINT pre_insert_too_many; +SELECT constraint_schema, + constraint_name + FROM information_schema.assertions + ORDER BY 1, 2; + constraint_schema | constraint_name +-------------------+----------------- + public | a2 + public | foo +(2 rows) + +\dQ + List of assertions + Schema | Name | Definition | Description +--------+------+------------------------------------+------------- + public | a2 | CHECK ((( SELECT count(*) AS count+| + | | FROM test1)) < 5) | + public | foo | CHECK (1 < 2) | +(2 rows) + +ALTER ASSERTION a2 RENAME TO a3; +SAVEPOINT pre_rename_foo; +ALTER ASSERTION foo RENAME TO a3; -- fails +ERROR: assertion "a3" already exists +ROLLBACK TO SAVEPOINT pre_rename_foo; +DROP ASSERTION foo; +SAVEPOINT pre_drop_test1; +DROP TABLE test1; -- fails +ERROR: cannot drop table test1 because other objects depend on it +DETAIL: constraint a3 depends on table test1 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +ROLLBACK TO SAVEPOINT pre_drop_test1; +DROP TABLE test1 CASCADE; +NOTICE: drop cascades to constraint a3 +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving various operators, functions and casts +-- +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +INSERT INTO t (n, p) VALUES (20, TRUE); +CREATE ASSERTION is_distinct_from CHECK (10 IS DISTINCT FROM (SELECT MIN(n) FROM t)); +CREATE ASSERTION is_not_distinct_from CHECK (20 IS NOT DISTINCT FROM (SELECT MIN(n) FROM t)); +CREATE ASSERTION null_if CHECK (NULLIF((SELECT BOOL_AND(p) FROM t), FALSE)); +CREATE ASSERTION coerce_to_boolean_from_min CHECK (CAST((SELECT MIN(n) FROM t) AS BOOLEAN)); +CREATE ASSERTION coerce_to_boolean_from_bool_and CHECK (CAST((SELECT BOOL_AND(p) FROM t) AS BOOLEAN)); +CREATE ASSERTION coerce_to_boolean_from_boolean CHECK (CAST((SELECT TRUE FROM t) AS BOOLEAN)); +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving function calls +-- +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL, d DATE NOT NULL); +CREATE FUNCTION f(n INTEGER) RETURNS INTEGER +AS $$ + SELECT n * 2 +$$ LANGUAGE SQL; +CREATE FUNCTION r(n INTEGER) RETURNS TABLE (m INTEGER ) +AS $$ + SELECT m + FROM t + WHERE n > r.n +$$ LANGUAGE SQL; +CREATE ASSERTION age_of_d_in_t CHECK ( + NOT EXISTS ( + SELECT FROM t + WHERE AGE(DATE '01-01-2018') > INTERVAL '10 days' -- AGE(DATE) is built-in SQL + ) +); +CREATE ASSERTION use_f CHECK (f(0) = 0); +CREATE ASSERTION use_f_in_predicate CHECK (NOT EXISTS (SELECT FROM t WHERE f(n) = 20)); +CREATE ASSERTION use_f_in_target CHECK (NOT EXISTS (SELECT f(n) FROM t)); +CREATE ASSERTION use_r_in_from CHECK (NOT EXISTS (SELECT FROM r(100))); +CREATE ASSERTION use_r_in_target CHECK (NOT EXISTS (SELECT r(100))); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (COALESCE(operations, 'NONE') = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('age_of_d_in_t', 'INSERT(t)'), + ('use_f', 'NONE'), + ('use_f_in_predicate', 'INSERT(t) UPDATE(t.n)'), + ('use_f_in_target', 'INSERT(t)'), + ('use_r_in_from', 'INSERT(t) UPDATE(t.m, t.n)'), -- TODO should _from and _target be the same? + ('use_r_in_target', 'INSERT(t) UPDATE(t.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +--------------------+----------------------------+----------------------------+--------- + age_of_d_in_t | INSERT(t) | INSERT(t) | YES + use_f | NONE | NONE | YES + use_f_in_predicate | INSERT(t) UPDATE(t.n) | INSERT(t) UPDATE(t.n) | YES + use_f_in_target | INSERT(t) | INSERT(t) | YES + use_r_in_from | INSERT(t) UPDATE(t.m, t.n) | INSERT(t) UPDATE(t.m, t.n) | YES + use_r_in_target | INSERT(t) UPDATE(t.n) | INSERT(t) UPDATE(t.n) | YES +(6 rows) + +CREATE FUNCTION g(n INTEGER) RETURNS INTEGER +AS $$ +BEGIN + RETURN n * 2; +END; +$$ LANGUAGE PLPGSQL; +-- Use of functions other than internal or those implemented in SQL are illegal +CREATE ASSERTION use_g_in_from CHECK (NOT EXISTS (SELECT FROM g(100))); -- fails +ERROR: function "g" uses unsupported language "plpgsql" +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving EXISTS and NOT EXISTS +-- +CREATE TABLE r (n INTEGER NOT NULL PRIMARY KEY); +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY); +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +CREATE TABLE t1 (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +CREATE TABLE t2 (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +INSERT INTO r (n) VALUES (1); +INSERT INTO t (n, m) VALUES (0, 0); +INSERT INTO t1 (n, m) VALUES (0, 0); +INSERT INTO t2 (n, m) VALUES (0, 0); +CREATE ASSERTION exists_no_predicate CHECK ( + EXISTS (SELECT FROM r) +); +CREATE ASSERTION exists_with_predicate CHECK ( + EXISTS (SELECT FROM r WHERE n > 0) +); +CREATE ASSERTION not_exists_no_predicate CHECK ( + NOT EXISTS (SELECT FROM s) +); +CREATE ASSERTION not_exists_with_predicate CHECK ( + NOT EXISTS (SELECT FROM r WHERE n < 1) +); +CREATE ASSERTION direct_subject_of_an_exists CHECK ( + EXISTS (SELECT n FROM t WHERE m = 0) +); +-- TODO These can be optimised, at least if the set operation is UNION. +CREATE ASSERTION except_subject_of_an_exists CHECK ( + EXISTS (SELECT n FROM t1 WHERE m = 0 EXCEPT SELECT n FROM t2 WHERE m = 1) +); +CREATE ASSERTION intersect_subject_of_an_exists CHECK ( + EXISTS (SELECT n FROM t1 WHERE m = 0 INTERSECT SELECT n FROM t2 WHERE m = 0) +); +CREATE ASSERTION union_subject_of_an_exists CHECK ( + EXISTS (SELECT n FROM t1 WHERE m = 0 UNION SELECT n FROM t2 WHERE m = 0) +); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('exists_no_predicate', 'DELETE(r)'), + ('exists_with_predicate', 'DELETE(r) UPDATE(r.n)'), + ('not_exists_no_predicate', 'INSERT(s)'), + ('not_exists_with_predicate', 'INSERT(r) UPDATE(r.n)'), + ('direct_subject_of_an_exists', 'DELETE(t) UPDATE(t.m)'), + ('except_subject_of_an_exists', 'DELETE(t1) INSERT(t2) UPDATE(t1.m, t1.n, t2.m, t2.n)'), + ('intersect_subject_of_an_exists', 'DELETE(t1, t2) UPDATE(t1.m, t1.n, t2.m, t2.n)'), + ('union_subject_of_an_exists', 'DELETE(t1, t2) UPDATE(t1.m, t1.n, t2.m, t2.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +--------------------------------+------------------------------------------------------+------------------------------------------------------+--------- + direct_subject_of_an_exists | DELETE(t) UPDATE(t.m) | DELETE(t) UPDATE(t.m) | YES + except_subject_of_an_exists | DELETE(t1) INSERT(t2) UPDATE(t1.m, t1.n, t2.m, t2.n) | DELETE(t1) INSERT(t2) UPDATE(t1.m, t1.n, t2.m, t2.n) | YES + exists_no_predicate | DELETE(r) | DELETE(r) | YES + exists_with_predicate | DELETE(r) UPDATE(r.n) | DELETE(r) UPDATE(r.n) | YES + intersect_subject_of_an_exists | DELETE(t1, t2) UPDATE(t1.m, t1.n, t2.m, t2.n) | DELETE(t1, t2) UPDATE(t1.m, t1.n, t2.m, t2.n) | YES + not_exists_no_predicate | INSERT(s) | INSERT(s) | YES + not_exists_with_predicate | INSERT(r) UPDATE(r.n) | INSERT(r) UPDATE(r.n) | YES + union_subject_of_an_exists | DELETE(t1, t2) UPDATE(t1.m, t1.n, t2.m, t2.n) | DELETE(t1, t2) UPDATE(t1.m, t1.n, t2.m, t2.n) | YES +(8 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving NOT +-- +CREATE TABLE r (p BOOLEAN NOT NULL PRIMARY KEY); +INSERT INTO r (p) VALUES (TRUE); +CREATE ASSERTION a CHECK (EXISTS (SELECT FROM r WHERE p)); +CREATE ASSERTION b CHECK (NOT EXISTS (SELECT FROM r WHERE NOT p)); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('a', 'DELETE(r) UPDATE(r.p)'), + ('b', 'INSERT(r) UPDATE(r.p)')) -- NOT(x) and x have opposite invalidating operations + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +----------------+-----------------------+-----------------------+--------- + a | DELETE(r) UPDATE(r.p) | DELETE(r) UPDATE(r.p) | YES + b | INSERT(r) UPDATE(r.p) | INSERT(r) UPDATE(r.p) | YES +(2 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving set operations INTERSECT, UNION, and EXCEPT +-- +CREATE TABLE r (n INTEGER NOT NULL PRIMARY KEY); +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY); +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY); +INSERT INTO t (n) VALUES (0); +CREATE ASSERTION except_operands CHECK (NOT EXISTS (SELECT FROM r EXCEPT SELECT FROM s)); +CREATE ASSERTION intersect_operands CHECK (NOT EXISTS (SELECT FROM r INTERSECT SELECT FROM s)); +CREATE ASSERTION union_operands CHECK (EXISTS (SELECT FROM s UNION SELECT FROM t)); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('except_operands', 'DELETE(s) INSERT(r)'), + ('intersect_operands', 'INSERT(r, s)'), + ('union_operands', 'DELETE(s, t)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +--------------------+---------------------+---------------------+--------- + except_operands | DELETE(s) INSERT(r) | DELETE(s) INSERT(r) | YES + intersect_operands | INSERT(r, s) | INSERT(r, s) | YES + union_operands | DELETE(s, t) | DELETE(s, t) | YES +(3 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving COUNT aggregations +-- +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +INSERT INTO s VALUES (1, 2); +CREATE ASSERTION ge_all_count CHECK (1 >= ALL (SELECT COUNT(*) FROM s)); +CREATE ASSERTION ge_any_count CHECK (1 >= ANY (SELECT COUNT(*) FROM s)); +CREATE ASSERTION ge_count CHECK (1 >= (SELECT COUNT(*) FROM s)); +CREATE ASSERTION gt_all_count CHECK (2 > ALL (SELECT COUNT(*) FROM s)); +CREATE ASSERTION gt_any_count CHECK (2 > ANY (SELECT COUNT(*) FROM s)); +CREATE ASSERTION gt_count CHECK (2 > (SELECT COUNT(*) FROM s)); +CREATE ASSERTION le_all_count CHECK (1 <= ALL (SELECT COUNT(*) FROM s)); +CREATE ASSERTION le_any_count CHECK (0 <= ANY (SELECT COUNT(*) FROM s)); +CREATE ASSERTION le_count CHECK (0 <= (SELECT COUNT(*) FROM s)); +CREATE ASSERTION lt_all_count CHECK (0 < ALL (SELECT COUNT(*) FROM s)); +CREATE ASSERTION lt_any_count CHECK (0 < ANY (SELECT COUNT(*) FROM s)); +CREATE ASSERTION lt_count CHECK (0 < (SELECT COUNT(*) FROM s)); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('ge_all_count', 'INSERT(s)'), + ('ge_any_count', 'INSERT(s)'), + ('ge_count', 'INSERT(s)'), + ('gt_all_count', 'INSERT(s)'), + ('gt_any_count', 'INSERT(s)'), + ('gt_count', 'INSERT(s)'), + ('le_all_count', 'DELETE(s)'), + ('le_any_count', 'DELETE(s)'), + ('le_count', 'DELETE(s)'), + ('lt_all_count', 'DELETE(s)'), + ('lt_any_count', 'DELETE(s)'), + ('lt_count', 'DELETE(s)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +----------------+-----------+-----------+--------- + ge_all_count | INSERT(s) | INSERT(s) | YES + ge_any_count | INSERT(s) | INSERT(s) | YES + ge_count | INSERT(s) | INSERT(s) | YES + gt_all_count | INSERT(s) | INSERT(s) | YES + gt_any_count | INSERT(s) | INSERT(s) | YES + gt_count | INSERT(s) | INSERT(s) | YES + le_all_count | DELETE(s) | DELETE(s) | YES + le_any_count | DELETE(s) | DELETE(s) | YES + le_count | DELETE(s) | DELETE(s) | YES + lt_all_count | DELETE(s) | DELETE(s) | YES + lt_any_count | DELETE(s) | DELETE(s) | YES + lt_count | DELETE(s) | DELETE(s) | YES +(12 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving MIN aggregations +-- +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +INSERT INTO s VALUES (1, 2); +CREATE ASSERTION ge_any_min CHECK (1 >= ANY (SELECT MIN(n) FROM s)); +CREATE ASSERTION ge_all_min CHECK (1 >= ALL (SELECT MIN(n) FROM s)); +CREATE ASSERTION ge_min CHECK (1 >= (SELECT MIN(n) FROM s)); +CREATE ASSERTION gt_any_min CHECK (2 > ANY (SELECT MIN(n) FROM s)); +CREATE ASSERTION gt_all_min CHECK (2 > ALL (SELECT MIN(n) FROM s)); +CREATE ASSERTION gt_min CHECK (2 > (SELECT MIN(n) FROM s)); +CREATE ASSERTION le_any_min CHECK (0 <= ANY (SELECT MIN(n) FROM s)); +CREATE ASSERTION le_all_min CHECK (0 <= ALL (SELECT MIN(n) FROM s)); +CREATE ASSERTION le_min CHECK (0 <= (SELECT MIN(n) FROM s)); +CREATE ASSERTION lt_any_min CHECK (0 < ANY (SELECT MIN(n) FROM s)); +CREATE ASSERTION lt_all_min CHECK (0 < ALL (SELECT MIN(n) FROM s)); +CREATE ASSERTION lt_min CHECK (0 < (SELECT MIN(n) FROM s)); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('ge_all_min', 'DELETE(s) UPDATE(s.n)'), + ('ge_any_min', 'DELETE(s) UPDATE(s.n)'), + ('ge_min', 'DELETE(s) UPDATE(s.n)'), + ('gt_all_min', 'DELETE(s) UPDATE(s.n)'), + ('gt_any_min', 'DELETE(s) UPDATE(s.n)'), + ('gt_min', 'DELETE(s) UPDATE(s.n)'), + ('le_all_min', 'INSERT(s) UPDATE(s.n)'), + ('le_any_min', 'INSERT(s) UPDATE(s.n)'), + ('le_min', 'INSERT(s) UPDATE(s.n)'), + ('lt_all_min', 'INSERT(s) UPDATE(s.n)'), + ('lt_any_min', 'INSERT(s) UPDATE(s.n)'), + ('lt_min', 'INSERT(s) UPDATE(s.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +----------------+-----------------------+-----------------------+--------- + ge_all_min | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + ge_any_min | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + ge_min | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + gt_all_min | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + gt_any_min | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + gt_min | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + le_all_min | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + le_any_min | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + le_min | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + lt_all_min | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + lt_any_min | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + lt_min | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES +(12 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving MAX aggregations +-- +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +INSERT INTO s VALUES (1, 2); +CREATE ASSERTION ge_any_max CHECK (1 >= ANY (SELECT MAX(n) FROM s)); +CREATE ASSERTION ge_all_max CHECK (1 >= ALL (SELECT MAX(n) FROM s)); +CREATE ASSERTION ge_max CHECK (1 >= (SELECT MAX(n) FROM s)); +CREATE ASSERTION gt_all_max CHECK (2 > ALL (SELECT MAX(n) FROM s)); +CREATE ASSERTION gt_any_max CHECK (2 > ANY (SELECT MAX(n) FROM s)); +CREATE ASSERTION gt_max CHECK (2 > (SELECT MAX(n) FROM s)); +CREATE ASSERTION le_all_max CHECK (0 <= ALL (SELECT MAX(n) FROM s)); +CREATE ASSERTION le_any_max CHECK (0 <= ANY (SELECT MAX(n) FROM s)); +CREATE ASSERTION le_max CHECK (0 <= (SELECT MAX(n) FROM s)); +CREATE ASSERTION lt_all_max CHECK (0 < ALL (SELECT MAX(n) FROM s)); +CREATE ASSERTION lt_any_max CHECK (0 < ANY (SELECT MAX(n) FROM s)); +CREATE ASSERTION lt_max CHECK (0 < (SELECT MAX(n) FROM s)); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('ge_all_max', 'INSERT(s) UPDATE(s.n)'), + ('ge_any_max', 'INSERT(s) UPDATE(s.n)'), + ('ge_max', 'INSERT(s) UPDATE(s.n)'), + ('gt_all_max', 'INSERT(s) UPDATE(s.n)'), + ('gt_any_max', 'INSERT(s) UPDATE(s.n)'), + ('gt_max', 'INSERT(s) UPDATE(s.n)'), + ('le_all_max', 'DELETE(s) UPDATE(s.n)'), + ('le_any_max', 'DELETE(s) UPDATE(s.n)'), + ('le_max', 'DELETE(s) UPDATE(s.n)'), + ('lt_all_max', 'DELETE(s) UPDATE(s.n)'), + ('lt_any_max', 'DELETE(s) UPDATE(s.n)'), + ('lt_max', 'DELETE(s) UPDATE(s.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +----------------+-----------------------+-----------------------+--------- + ge_all_max | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + ge_any_max | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + ge_max | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + gt_all_max | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + gt_any_max | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + gt_max | INSERT(s) UPDATE(s.n) | INSERT(s) UPDATE(s.n) | YES + le_all_max | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + le_any_max | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + le_max | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + lt_all_max | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + lt_any_max | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES + lt_max | DELETE(s) UPDATE(s.n) | DELETE(s) UPDATE(s.n) | YES +(12 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving BOOL_AND aggregations +-- +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +INSERT INTO t (n, p) VALUES (0, TRUE); +CREATE ASSERTION eq_bool_and CHECK (TRUE = (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION eq_any_bool_and CHECK (TRUE = ANY (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION eq_all_bool_and CHECK (TRUE = ALL (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION ne_bool_and CHECK (FALSE <> (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION ne_any_bool_and CHECK (FALSE <> ANY (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION ne_all_bool_and CHECK (FALSE <> ALL (SELECT BOOL_AND(p) FROM t)); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('eq_all_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('eq_any_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('eq_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('ne_all_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('ne_any_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('ne_bool_and', 'INSERT(t) UPDATE(t.p)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +-----------------+-----------------------+-----------------------+--------- + eq_all_bool_and | INSERT(t) UPDATE(t.p) | INSERT(t) UPDATE(t.p) | YES + eq_any_bool_and | INSERT(t) UPDATE(t.p) | INSERT(t) UPDATE(t.p) | YES + eq_bool_and | INSERT(t) UPDATE(t.p) | INSERT(t) UPDATE(t.p) | YES + ne_all_bool_and | INSERT(t) UPDATE(t.p) | INSERT(t) UPDATE(t.p) | YES + ne_any_bool_and | INSERT(t) UPDATE(t.p) | INSERT(t) UPDATE(t.p) | YES + ne_bool_and | INSERT(t) UPDATE(t.p) | INSERT(t) UPDATE(t.p) | YES +(6 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving BOOL_OR aggregations +-- +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +INSERT INTO t (n, p) VALUES (0, TRUE); +CREATE ASSERTION eq_bool_or CHECK (TRUE = (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION eq_any_bool_or CHECK (TRUE = ANY (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION eq_all_bool_or CHECK (TRUE = ALL (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION ne_bool_or CHECK (FALSE <> (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION ne_any_bool_or CHECK (FALSE <> ANY (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION ne_all_bool_or CHECK (FALSE <> ALL (SELECT BOOL_OR(p) FROM t)); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('eq_all_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('eq_any_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('eq_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('ne_all_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('ne_any_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('ne_bool_or', 'DELETE(t) UPDATE(t.p)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +----------------+-----------------------+-----------------------+--------- + eq_all_bool_or | DELETE(t) UPDATE(t.p) | DELETE(t) UPDATE(t.p) | YES + eq_any_bool_or | DELETE(t) UPDATE(t.p) | DELETE(t) UPDATE(t.p) | YES + eq_bool_or | DELETE(t) UPDATE(t.p) | DELETE(t) UPDATE(t.p) | YES + ne_all_bool_or | DELETE(t) UPDATE(t.p) | DELETE(t) UPDATE(t.p) | YES + ne_any_bool_or | DELETE(t) UPDATE(t.p) | DELETE(t) UPDATE(t.p) | YES + ne_bool_or | DELETE(t) UPDATE(t.p) | DELETE(t) UPDATE(t.p) | YES +(6 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions involving window functions +-- +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +-- Regular window function +CREATE ASSERTION alternate_p CHECK ( + NOT EXISTS ( + SELECT FROM ( + SELECT LAG(p, 1, NOT p) OVER n <> p + AND LEAD(p, 1, NOT p) OVER n <> p + FROM t + WINDOW n AS (ORDER BY n) + ) AS v(q) + WHERE NOT q + ) +); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('alternate_p', 'DELETE(t) INSERT(t) UPDATE(t.n, t.p)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +----------------+--------------------------------------+--------------------------------------+--------- + alternate_p | DELETE(t) INSERT(t) UPDATE(t.n, t.p) | DELETE(t) INSERT(t) UPDATE(t.n, t.p) | YES +(1 row) + +INSERT INTO t (n, p) VALUES (10, TRUE); +INSERT INTO t (n, p) VALUES (20, FALSE); +INSERT INTO t (n, p) VALUES (30, TRUE); +SAVEPOINT pre_insert; +INSERT INTO t (n, p) VALUES (40, TRUE); +ERROR: assertion "alternate_p" violated +ROLLBACK TO SAVEPOINT pre_insert; +SAVEPOINT pre_delete; +DELETE FROM t WHERE n = 20; +ERROR: assertion "alternate_p" violated +ROLLBACK TO SAVEPOINT pre_delete; +SAVEPOINT pre_update; +UPDATE t SET p = NOT p WHERE n = 20; +ERROR: assertion "alternate_p" violated +ROLLBACK TO SAVEPOINT pre_update; +DROP ASSERTION alternate_p; +-- Aggregate function over a window +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +INSERT INTO s (n, m) +SELECT n, (2 * n) + FROM GENERATE_SERIES(0, 9) AS ns(n); +CREATE ASSERTION max_over_window CHECK ((10, 20) > ALL (SELECT n, MAX(m) OVER (ORDER BY n) FROM s)); +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('max_over_window', 'INSERT(s) UPDATE(s.m, s.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + assertion_name | actual | expected | correct +-----------------+----------------------------+----------------------------+--------- + max_over_window | INSERT(s) UPDATE(s.m, s.n) | INSERT(s) UPDATE(s.m, s.n) | YES +(1 row) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- Expressions containing a comparison should have the same invalidating +-- operations as an expression containing the inverse comparison operation +-- and where the operands have been switched +-- +CREATE TABLE r (n INTEGER NOT NULL PRIMARY KEY); +INSERT INTO r (n) VALUES (9); +-- ">" and "<" are opposites +CREATE ASSERTION a CHECK (10 > (SELECT MIN(n) FROM r)); +CREATE ASSERTION b CHECK ((SELECT MIN(n) FROM r) < 10); +-- "<" and ">" are opposites +CREATE ASSERTION c CHECK (8 < (SELECT MIN(n) FROM r)); +CREATE ASSERTION d CHECK ((SELECT MIN(n) FROM r) > 8); +-- ">=" and "<=" are opposites +CREATE ASSERTION e CHECK (9 >= (SELECT MIN(n) FROM r)); +CREATE ASSERTION f CHECK ((SELECT MIN(n) FROM r) <= 9); +-- "<=" and ">=" are opposites +CREATE ASSERTION g CHECK (9 <= (SELECT MIN(n) FROM r)); +CREATE ASSERTION h CHECK ((SELECT MIN(n) FROM r) >= 9); +SELECT (test).predicate, (test).correct + FROM ( +VALUES ('a', 'b'), + ('c', 'd'), + ('e', 'f'), + ('g', 'h')) + AS v(a, b) + CROSS JOIN LATERAL (SELECT have_identical_invalidating_operations(v.a, v.b)) + AS w(test) + ORDER BY 1, 2; + predicate | correct +----------------------------------------------------------------------+--------- + assertion "a" has identical invalidating operations to assertion "b" | YES + assertion "c" has identical invalidating operations to assertion "d" | YES + assertion "e" has identical invalidating operations to assertion "f" | YES + assertion "g" has identical invalidating operations to assertion "h" | YES +(4 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- +-- The INFORMATION_SCHEMA should contain the correct information +-- +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +CREATE ASSERTION a CHECK (NOT EXISTS (SELECT FROM t WHERE p)); +SELECT predicate, + CASE WHEN + truth THEN 'YES' + ELSE 'NO' + END AS correct + FROM ( +VALUES ('INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE contains only the correct tables', + EXISTS ( + SELECT FROM information_schema.constraint_table_usage + WHERE (constraint_name, table_name) = ('a', 't')) + AND NOT EXISTS ( + SELECT FROM information_schema.constraint_table_usage + WHERE constraint_name = 'a' + AND table_name <> 't')), + ('INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE contains only the correct columns (t.p)', + EXISTS ( + SELECT FROM information_schema.constraint_column_usage + WHERE (constraint_name, table_name, column_name) = ('a', 't', 'p')) + AND NOT EXISTS ( + SELECT FROM information_schema.constraint_column_usage + WHERE constraint_name ='a' + AND (table_name, column_name) <> ('t', 'p'))), + ('INFORMATION_SCHEMA.ASSERTIONS contains only the correct assertions', + EXISTS ( + SELECT FROM information_schema.assertions + WHERE constraint_name = 'a') + AND NOT EXISTS ( + SELECT FROM information_schema.assertions + WHERE constraint_name <> 'a'))) + AS v(predicate, truth); + predicate | correct +------------------------------------------------------------------------------------+--------- + INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE contains only the correct tables | YES + INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE contains only the correct columns (t.p) | YES + INFORMATION_SCHEMA.ASSERTIONS contains only the correct assertions | YES +(3 rows) + +ROLLBACK; +BEGIN TRANSACTION; +-- TODO This needs rethinking +/* +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +CREATE VIEW v AS SELECT * FROM s INNER JOIN t USING (n); + +CREATE ASSERTION a CHECK (NOT EXISTS (SELECT FROM v)); + +-- we should depend on the view not the tables +SELECT OK(depends_on('a', 'v'), + 'Assertion depends upon the view v it references'); +SELECT OK(NOT depends_on('a', 's'), + 'Assertion does not depend upon the table s referenced in the view v'); +SELECT OK(NOT depends_on('a', 't'), + 'Assertion does not depend upon the table t referenced in the view v'); + +-- we should trigger on the tables and not the view +SELECT OK(EXISTS(SELECT FROM assertion_check_operation + WHERE (assertion_name, relation_name) = ('a', 's')), + 'Assertion is checked on modifications to table s'); +SELECT OK(EXISTS(SELECT FROM assertion_check_operation + WHERE (assertion_name, relation_name) = ('a', 't')), + 'Assertion is checked on modifications to table t'); +SELECT OK(NOT EXISTS(SELECT FROM assertion_check_operation + WHERE (assertion_name, relation_name) = ('a', 'v')), + 'Assertion is not checked on modifications to view v'); +*/ +ROLLBACK; +-- TODO test commonalities between count, min, max, etc, for optimisations +-- TODO ensure window function aggregates are treated the same as regular aggregates +-- TODO ensure that conflicting aggregate functions are not incorrectly optimised +-- TODO ensure that recorded dependencies are correct (traversal into functions and views) +BEGIN TRANSACTION; +DROP FUNCTION have_identical_invalidating_operations; +DROP VIEW invalidating_summary; +DROP VIEW invalidating_by_update_of_column; +DROP VIEW invalidating_operation; +COMMIT; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index ad9434fb87..4966352197 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -60,7 +60,7 @@ test: create_index create_view # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes create_am hash_func +test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes create_am assertions hash_func # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 27cd49845e..e5277a278a 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -58,6 +58,7 @@ test: create_function_1 test: create_type test: create_table test: create_function_2 +test: assertions test: copy test: copyselect test: copydml diff --git a/src/test/regress/sql/assertions.sql b/src/test/regress/sql/assertions.sql new file mode 100644 index 0000000000..f270698da1 --- /dev/null +++ b/src/test/regress/sql/assertions.sql @@ -0,0 +1,775 @@ + +BEGIN TRANSACTION; + +-- +-- Create some helper views and functions to allow us to more easily +-- assert which operations will cause an assertion to be checked. +-- + +CREATE OR REPLACE VIEW invalidating_operation + (assertion_name, + relation_name, + operation) +AS +SELECT c.conname, + cl.relname, + v.operation + FROM pg_trigger t + JOIN pg_constraint c ON (t.tgconstraint = c.oid) + JOIN pg_proc p ON (t.tgfoid = p.oid) + JOIN pg_class cl ON (t.tgrelid = cl.oid) + JOIN (VALUES ('INSERT', 1<<2), + ('DELETE', 1<<3|1<<5), + ('UPDATE', 1<<4)) + AS v(operation, mask) ON ((v.mask & tgtype) > 0) + WHERE t.tgisinternal + AND p.proname = 'assertion_check'; + +CREATE OR REPLACE VIEW invalidating_by_update_of_column + (assertion_name, + relation_name, + column_name) +AS +SELECT c.conname, + cl.relname, + a.attname + FROM pg_trigger t INNER JOIN pg_constraint c ON (t.tgconstraint = c.oid) + INNER JOIN pg_proc p ON (t.tgfoid = p.oid) + INNER JOIN pg_class cl ON (t.tgrelid = cl.oid) + CROSS JOIN LATERAL UNNEST(t.tgattr) AS co(n) + INNER JOIN pg_attribute a ON (a.attrelid = cl.oid AND attnum = co.n) + WHERE t.tgisinternal + AND p.proname = 'assertion_check' + AND (tgtype & (1 << 4)) > 0; + +CREATE OR REPLACE VIEW invalidating_summary + (assertion_name, + operations) +AS +SELECT assertion_name, + string_agg(operation, ' ' ORDER BY operation) + FROM (SELECT assertion_name, + operation ||'('|| string_agg(relation_name, ', ' ORDER BY relation_name) ||')' + FROM invalidating_operation + WHERE operation <> 'UPDATE' + GROUP BY assertion_name, + operation + UNION + SELECT assertion_name, + operation ||'('|| string_agg(relation_name ||'.'|| column_name, ', ' ORDER BY relation_name, column_name) ||')' + FROM invalidating_by_update_of_column JOIN invalidating_operation USING (assertion_name, relation_name) + WHERE operation = 'UPDATE' + GROUP BY assertion_name, + operation) + AS v(assertion_name, operation) + GROUP BY assertion_name; + +CREATE OR REPLACE FUNCTION have_identical_invalidating_operations + (a text, b text) +RETURNS TABLE (predicate text, correct text) +AS $$ + SELECT 'assertion "'|| a ||'" has identical invalidating operations to assertion "'|| b ||'"', + CASE WHEN + NOT EXISTS (SELECT relation_name, operation + FROM invalidating_operation + WHERE assertion_name = a + EXCEPT + SELECT relation_name, operation + FROM invalidating_operation + WHERE assertion_name = b) AND + NOT EXISTS (SELECT relation_name, column_name + FROM invalidating_by_update_of_column + WHERE assertion_name = a + EXCEPT + SELECT relation_name, column_name + FROM invalidating_by_update_of_column + WHERE assertion_name = b) THEN 'YES' + ELSE 'NO' END +$$ LANGUAGE SQL; + +COMMIT; + + +BEGIN TRANSACTION; + +-- +-- Perform some basic sanity checking on creating assertions +-- + +CREATE TABLE test1 (a int, b text); + +CREATE ASSERTION foo CHECK (1 < 2); +CREATE ASSERTION a2 CHECK ((SELECT count(*) FROM test1) < 5); + +DELETE FROM test1; +INSERT INTO test1 VALUES (1, 'one'); +INSERT INTO test1 VALUES (2, 'two'); +INSERT INTO test1 VALUES (3, 'three'); +INSERT INTO test1 VALUES (4, 'four'); + +SAVEPOINT pre_insert_too_many; +INSERT INTO test1 VALUES (5, 'five'); +ROLLBACK TO SAVEPOINT pre_insert_too_many; + +SELECT constraint_schema, + constraint_name + FROM information_schema.assertions + ORDER BY 1, 2; + +\dQ + +ALTER ASSERTION a2 RENAME TO a3; + +SAVEPOINT pre_rename_foo; +ALTER ASSERTION foo RENAME TO a3; -- fails +ROLLBACK TO SAVEPOINT pre_rename_foo; + +DROP ASSERTION foo; + +SAVEPOINT pre_drop_test1; +DROP TABLE test1; -- fails +ROLLBACK TO SAVEPOINT pre_drop_test1; + +DROP TABLE test1 CASCADE; + +ROLLBACK; + + + +BEGIN TRANSACTION; + +-- +-- Expressions involving various operators, functions and casts +-- + +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); + +INSERT INTO t (n, p) VALUES (20, TRUE); + +CREATE ASSERTION is_distinct_from CHECK (10 IS DISTINCT FROM (SELECT MIN(n) FROM t)); +CREATE ASSERTION is_not_distinct_from CHECK (20 IS NOT DISTINCT FROM (SELECT MIN(n) FROM t)); +CREATE ASSERTION null_if CHECK (NULLIF((SELECT BOOL_AND(p) FROM t), FALSE)); +CREATE ASSERTION coerce_to_boolean_from_min CHECK (CAST((SELECT MIN(n) FROM t) AS BOOLEAN)); +CREATE ASSERTION coerce_to_boolean_from_bool_and CHECK (CAST((SELECT BOOL_AND(p) FROM t) AS BOOLEAN)); +CREATE ASSERTION coerce_to_boolean_from_boolean CHECK (CAST((SELECT TRUE FROM t) AS BOOLEAN)); + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving function calls +-- + +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL, d DATE NOT NULL); + +CREATE FUNCTION f(n INTEGER) RETURNS INTEGER +AS $$ + SELECT n * 2 +$$ LANGUAGE SQL; + +CREATE FUNCTION r(n INTEGER) RETURNS TABLE (m INTEGER ) +AS $$ + SELECT m + FROM t + WHERE n > r.n +$$ LANGUAGE SQL; + +CREATE ASSERTION age_of_d_in_t CHECK ( + NOT EXISTS ( + SELECT FROM t + WHERE AGE(DATE '01-01-2018') > INTERVAL '10 days' -- AGE(DATE) is built-in SQL + ) +); +CREATE ASSERTION use_f CHECK (f(0) = 0); +CREATE ASSERTION use_f_in_predicate CHECK (NOT EXISTS (SELECT FROM t WHERE f(n) = 20)); +CREATE ASSERTION use_f_in_target CHECK (NOT EXISTS (SELECT f(n) FROM t)); +CREATE ASSERTION use_r_in_from CHECK (NOT EXISTS (SELECT FROM r(100))); +CREATE ASSERTION use_r_in_target CHECK (NOT EXISTS (SELECT r(100))); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (COALESCE(operations, 'NONE') = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('age_of_d_in_t', 'INSERT(t)'), + ('use_f', 'NONE'), + ('use_f_in_predicate', 'INSERT(t) UPDATE(t.n)'), + ('use_f_in_target', 'INSERT(t)'), + ('use_r_in_from', 'INSERT(t) UPDATE(t.m, t.n)'), -- TODO should _from and _target be the same? + ('use_r_in_target', 'INSERT(t) UPDATE(t.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +CREATE FUNCTION g(n INTEGER) RETURNS INTEGER +AS $$ +BEGIN + RETURN n * 2; +END; +$$ LANGUAGE PLPGSQL; + +-- Use of functions other than internal or those implemented in SQL are illegal +CREATE ASSERTION use_g_in_from CHECK (NOT EXISTS (SELECT FROM g(100))); -- fails + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving EXISTS and NOT EXISTS +-- + +CREATE TABLE r (n INTEGER NOT NULL PRIMARY KEY); +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY); +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +CREATE TABLE t1 (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +CREATE TABLE t2 (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); + +INSERT INTO r (n) VALUES (1); +INSERT INTO t (n, m) VALUES (0, 0); +INSERT INTO t1 (n, m) VALUES (0, 0); +INSERT INTO t2 (n, m) VALUES (0, 0); + +CREATE ASSERTION exists_no_predicate CHECK ( + EXISTS (SELECT FROM r) +); + +CREATE ASSERTION exists_with_predicate CHECK ( + EXISTS (SELECT FROM r WHERE n > 0) +); + +CREATE ASSERTION not_exists_no_predicate CHECK ( + NOT EXISTS (SELECT FROM s) +); + +CREATE ASSERTION not_exists_with_predicate CHECK ( + NOT EXISTS (SELECT FROM r WHERE n < 1) +); + +CREATE ASSERTION direct_subject_of_an_exists CHECK ( + EXISTS (SELECT n FROM t WHERE m = 0) +); + +-- TODO These can be optimised, at least if the set operation is UNION. +CREATE ASSERTION except_subject_of_an_exists CHECK ( + EXISTS (SELECT n FROM t1 WHERE m = 0 EXCEPT SELECT n FROM t2 WHERE m = 1) +); + +CREATE ASSERTION intersect_subject_of_an_exists CHECK ( + EXISTS (SELECT n FROM t1 WHERE m = 0 INTERSECT SELECT n FROM t2 WHERE m = 0) +); + +CREATE ASSERTION union_subject_of_an_exists CHECK ( + EXISTS (SELECT n FROM t1 WHERE m = 0 UNION SELECT n FROM t2 WHERE m = 0) +); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('exists_no_predicate', 'DELETE(r)'), + ('exists_with_predicate', 'DELETE(r) UPDATE(r.n)'), + ('not_exists_no_predicate', 'INSERT(s)'), + ('not_exists_with_predicate', 'INSERT(r) UPDATE(r.n)'), + ('direct_subject_of_an_exists', 'DELETE(t) UPDATE(t.m)'), + ('except_subject_of_an_exists', 'DELETE(t1) INSERT(t2) UPDATE(t1.m, t1.n, t2.m, t2.n)'), + ('intersect_subject_of_an_exists', 'DELETE(t1, t2) UPDATE(t1.m, t1.n, t2.m, t2.n)'), + ('union_subject_of_an_exists', 'DELETE(t1, t2) UPDATE(t1.m, t1.n, t2.m, t2.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving NOT +-- + +CREATE TABLE r (p BOOLEAN NOT NULL PRIMARY KEY); + +INSERT INTO r (p) VALUES (TRUE); + +CREATE ASSERTION a CHECK (EXISTS (SELECT FROM r WHERE p)); +CREATE ASSERTION b CHECK (NOT EXISTS (SELECT FROM r WHERE NOT p)); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('a', 'DELETE(r) UPDATE(r.p)'), + ('b', 'INSERT(r) UPDATE(r.p)')) -- NOT(x) and x have opposite invalidating operations + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving set operations INTERSECT, UNION, and EXCEPT +-- + +CREATE TABLE r (n INTEGER NOT NULL PRIMARY KEY); +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY); +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY); + +INSERT INTO t (n) VALUES (0); + +CREATE ASSERTION except_operands CHECK (NOT EXISTS (SELECT FROM r EXCEPT SELECT FROM s)); +CREATE ASSERTION intersect_operands CHECK (NOT EXISTS (SELECT FROM r INTERSECT SELECT FROM s)); +CREATE ASSERTION union_operands CHECK (EXISTS (SELECT FROM s UNION SELECT FROM t)); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('except_operands', 'DELETE(s) INSERT(r)'), + ('intersect_operands', 'INSERT(r, s)'), + ('union_operands', 'DELETE(s, t)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving COUNT aggregations +-- + +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); + +INSERT INTO s VALUES (1, 2); + +CREATE ASSERTION ge_all_count CHECK (1 >= ALL (SELECT COUNT(*) FROM s)); +CREATE ASSERTION ge_any_count CHECK (1 >= ANY (SELECT COUNT(*) FROM s)); +CREATE ASSERTION ge_count CHECK (1 >= (SELECT COUNT(*) FROM s)); +CREATE ASSERTION gt_all_count CHECK (2 > ALL (SELECT COUNT(*) FROM s)); +CREATE ASSERTION gt_any_count CHECK (2 > ANY (SELECT COUNT(*) FROM s)); +CREATE ASSERTION gt_count CHECK (2 > (SELECT COUNT(*) FROM s)); +CREATE ASSERTION le_all_count CHECK (1 <= ALL (SELECT COUNT(*) FROM s)); +CREATE ASSERTION le_any_count CHECK (0 <= ANY (SELECT COUNT(*) FROM s)); +CREATE ASSERTION le_count CHECK (0 <= (SELECT COUNT(*) FROM s)); +CREATE ASSERTION lt_all_count CHECK (0 < ALL (SELECT COUNT(*) FROM s)); +CREATE ASSERTION lt_any_count CHECK (0 < ANY (SELECT COUNT(*) FROM s)); +CREATE ASSERTION lt_count CHECK (0 < (SELECT COUNT(*) FROM s)); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('ge_all_count', 'INSERT(s)'), + ('ge_any_count', 'INSERT(s)'), + ('ge_count', 'INSERT(s)'), + ('gt_all_count', 'INSERT(s)'), + ('gt_any_count', 'INSERT(s)'), + ('gt_count', 'INSERT(s)'), + ('le_all_count', 'DELETE(s)'), + ('le_any_count', 'DELETE(s)'), + ('le_count', 'DELETE(s)'), + ('lt_all_count', 'DELETE(s)'), + ('lt_any_count', 'DELETE(s)'), + ('lt_count', 'DELETE(s)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving MIN aggregations +-- + +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); + +INSERT INTO s VALUES (1, 2); + +CREATE ASSERTION ge_any_min CHECK (1 >= ANY (SELECT MIN(n) FROM s)); +CREATE ASSERTION ge_all_min CHECK (1 >= ALL (SELECT MIN(n) FROM s)); +CREATE ASSERTION ge_min CHECK (1 >= (SELECT MIN(n) FROM s)); +CREATE ASSERTION gt_any_min CHECK (2 > ANY (SELECT MIN(n) FROM s)); +CREATE ASSERTION gt_all_min CHECK (2 > ALL (SELECT MIN(n) FROM s)); +CREATE ASSERTION gt_min CHECK (2 > (SELECT MIN(n) FROM s)); +CREATE ASSERTION le_any_min CHECK (0 <= ANY (SELECT MIN(n) FROM s)); +CREATE ASSERTION le_all_min CHECK (0 <= ALL (SELECT MIN(n) FROM s)); +CREATE ASSERTION le_min CHECK (0 <= (SELECT MIN(n) FROM s)); +CREATE ASSERTION lt_any_min CHECK (0 < ANY (SELECT MIN(n) FROM s)); +CREATE ASSERTION lt_all_min CHECK (0 < ALL (SELECT MIN(n) FROM s)); +CREATE ASSERTION lt_min CHECK (0 < (SELECT MIN(n) FROM s)); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('ge_all_min', 'DELETE(s) UPDATE(s.n)'), + ('ge_any_min', 'DELETE(s) UPDATE(s.n)'), + ('ge_min', 'DELETE(s) UPDATE(s.n)'), + ('gt_all_min', 'DELETE(s) UPDATE(s.n)'), + ('gt_any_min', 'DELETE(s) UPDATE(s.n)'), + ('gt_min', 'DELETE(s) UPDATE(s.n)'), + ('le_all_min', 'INSERT(s) UPDATE(s.n)'), + ('le_any_min', 'INSERT(s) UPDATE(s.n)'), + ('le_min', 'INSERT(s) UPDATE(s.n)'), + ('lt_all_min', 'INSERT(s) UPDATE(s.n)'), + ('lt_any_min', 'INSERT(s) UPDATE(s.n)'), + ('lt_min', 'INSERT(s) UPDATE(s.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving MAX aggregations +-- + +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); + +INSERT INTO s VALUES (1, 2); + +CREATE ASSERTION ge_any_max CHECK (1 >= ANY (SELECT MAX(n) FROM s)); +CREATE ASSERTION ge_all_max CHECK (1 >= ALL (SELECT MAX(n) FROM s)); +CREATE ASSERTION ge_max CHECK (1 >= (SELECT MAX(n) FROM s)); +CREATE ASSERTION gt_all_max CHECK (2 > ALL (SELECT MAX(n) FROM s)); +CREATE ASSERTION gt_any_max CHECK (2 > ANY (SELECT MAX(n) FROM s)); +CREATE ASSERTION gt_max CHECK (2 > (SELECT MAX(n) FROM s)); +CREATE ASSERTION le_all_max CHECK (0 <= ALL (SELECT MAX(n) FROM s)); +CREATE ASSERTION le_any_max CHECK (0 <= ANY (SELECT MAX(n) FROM s)); +CREATE ASSERTION le_max CHECK (0 <= (SELECT MAX(n) FROM s)); +CREATE ASSERTION lt_all_max CHECK (0 < ALL (SELECT MAX(n) FROM s)); +CREATE ASSERTION lt_any_max CHECK (0 < ANY (SELECT MAX(n) FROM s)); +CREATE ASSERTION lt_max CHECK (0 < (SELECT MAX(n) FROM s)); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('ge_all_max', 'INSERT(s) UPDATE(s.n)'), + ('ge_any_max', 'INSERT(s) UPDATE(s.n)'), + ('ge_max', 'INSERT(s) UPDATE(s.n)'), + ('gt_all_max', 'INSERT(s) UPDATE(s.n)'), + ('gt_any_max', 'INSERT(s) UPDATE(s.n)'), + ('gt_max', 'INSERT(s) UPDATE(s.n)'), + ('le_all_max', 'DELETE(s) UPDATE(s.n)'), + ('le_any_max', 'DELETE(s) UPDATE(s.n)'), + ('le_max', 'DELETE(s) UPDATE(s.n)'), + ('lt_all_max', 'DELETE(s) UPDATE(s.n)'), + ('lt_any_max', 'DELETE(s) UPDATE(s.n)'), + ('lt_max', 'DELETE(s) UPDATE(s.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving BOOL_AND aggregations +-- + +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +INSERT INTO t (n, p) VALUES (0, TRUE); + +CREATE ASSERTION eq_bool_and CHECK (TRUE = (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION eq_any_bool_and CHECK (TRUE = ANY (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION eq_all_bool_and CHECK (TRUE = ALL (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION ne_bool_and CHECK (FALSE <> (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION ne_any_bool_and CHECK (FALSE <> ANY (SELECT BOOL_AND(p) FROM t)); +CREATE ASSERTION ne_all_bool_and CHECK (FALSE <> ALL (SELECT BOOL_AND(p) FROM t)); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('eq_all_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('eq_any_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('eq_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('ne_all_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('ne_any_bool_and', 'INSERT(t) UPDATE(t.p)'), + ('ne_bool_and', 'INSERT(t) UPDATE(t.p)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving BOOL_OR aggregations +-- + +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +INSERT INTO t (n, p) VALUES (0, TRUE); + +CREATE ASSERTION eq_bool_or CHECK (TRUE = (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION eq_any_bool_or CHECK (TRUE = ANY (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION eq_all_bool_or CHECK (TRUE = ALL (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION ne_bool_or CHECK (FALSE <> (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION ne_any_bool_or CHECK (FALSE <> ANY (SELECT BOOL_OR(p) FROM t)); +CREATE ASSERTION ne_all_bool_or CHECK (FALSE <> ALL (SELECT BOOL_OR(p) FROM t)); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('eq_all_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('eq_any_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('eq_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('ne_all_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('ne_any_bool_or', 'DELETE(t) UPDATE(t.p)'), + ('ne_bool_or', 'DELETE(t) UPDATE(t.p)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions involving window functions +-- + +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); + +-- Regular window function +CREATE ASSERTION alternate_p CHECK ( + NOT EXISTS ( + SELECT FROM ( + SELECT LAG(p, 1, NOT p) OVER n <> p + AND LEAD(p, 1, NOT p) OVER n <> p + FROM t + WINDOW n AS (ORDER BY n) + ) AS v(q) + WHERE NOT q + ) +); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('alternate_p', 'DELETE(t) INSERT(t) UPDATE(t.n, t.p)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +INSERT INTO t (n, p) VALUES (10, TRUE); +INSERT INTO t (n, p) VALUES (20, FALSE); +INSERT INTO t (n, p) VALUES (30, TRUE); + +SAVEPOINT pre_insert; +INSERT INTO t (n, p) VALUES (40, TRUE); +ROLLBACK TO SAVEPOINT pre_insert; + +SAVEPOINT pre_delete; +DELETE FROM t WHERE n = 20; +ROLLBACK TO SAVEPOINT pre_delete; + +SAVEPOINT pre_update; +UPDATE t SET p = NOT p WHERE n = 20; +ROLLBACK TO SAVEPOINT pre_update; + +DROP ASSERTION alternate_p; + +-- Aggregate function over a window +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); + +INSERT INTO s (n, m) +SELECT n, (2 * n) + FROM GENERATE_SERIES(0, 9) AS ns(n); + +CREATE ASSERTION max_over_window CHECK ((10, 20) > ALL (SELECT n, MAX(m) OVER (ORDER BY n) FROM s)); + +SELECT assertion_name, + COALESCE(operations, 'NONE') AS actual, + expected, + CASE WHEN (operations = expected) + THEN 'YES' ELSE 'NO' + END AS correct + FROM invalidating_summary FULL OUTER JOIN ( +VALUES ('max_over_window', 'INSERT(s) UPDATE(s.m, s.n)')) + AS v(assertion_name, expected) + USING (assertion_name) + ORDER BY assertion_name, operations; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- Expressions containing a comparison should have the same invalidating +-- operations as an expression containing the inverse comparison operation +-- and where the operands have been switched +-- + +CREATE TABLE r (n INTEGER NOT NULL PRIMARY KEY); + +INSERT INTO r (n) VALUES (9); + +-- ">" and "<" are opposites +CREATE ASSERTION a CHECK (10 > (SELECT MIN(n) FROM r)); +CREATE ASSERTION b CHECK ((SELECT MIN(n) FROM r) < 10); + +-- "<" and ">" are opposites +CREATE ASSERTION c CHECK (8 < (SELECT MIN(n) FROM r)); +CREATE ASSERTION d CHECK ((SELECT MIN(n) FROM r) > 8); + +-- ">=" and "<=" are opposites +CREATE ASSERTION e CHECK (9 >= (SELECT MIN(n) FROM r)); +CREATE ASSERTION f CHECK ((SELECT MIN(n) FROM r) <= 9); + +-- "<=" and ">=" are opposites +CREATE ASSERTION g CHECK (9 <= (SELECT MIN(n) FROM r)); +CREATE ASSERTION h CHECK ((SELECT MIN(n) FROM r) >= 9); + +SELECT (test).predicate, (test).correct + FROM ( +VALUES ('a', 'b'), + ('c', 'd'), + ('e', 'f'), + ('g', 'h')) + AS v(a, b) + CROSS JOIN LATERAL (SELECT have_identical_invalidating_operations(v.a, v.b)) + AS w(test) + ORDER BY 1, 2; + +ROLLBACK; + + +BEGIN TRANSACTION; + +-- +-- The INFORMATION_SCHEMA should contain the correct information +-- + +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +CREATE ASSERTION a CHECK (NOT EXISTS (SELECT FROM t WHERE p)); + +SELECT predicate, + CASE WHEN + truth THEN 'YES' + ELSE 'NO' + END AS correct + FROM ( +VALUES ('INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE contains only the correct tables', + EXISTS ( + SELECT FROM information_schema.constraint_table_usage + WHERE (constraint_name, table_name) = ('a', 't')) + AND NOT EXISTS ( + SELECT FROM information_schema.constraint_table_usage + WHERE constraint_name = 'a' + AND table_name <> 't')), + ('INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE contains only the correct columns (t.p)', + EXISTS ( + SELECT FROM information_schema.constraint_column_usage + WHERE (constraint_name, table_name, column_name) = ('a', 't', 'p')) + AND NOT EXISTS ( + SELECT FROM information_schema.constraint_column_usage + WHERE constraint_name ='a' + AND (table_name, column_name) <> ('t', 'p'))), + ('INFORMATION_SCHEMA.ASSERTIONS contains only the correct assertions', + EXISTS ( + SELECT FROM information_schema.assertions + WHERE constraint_name = 'a') + AND NOT EXISTS ( + SELECT FROM information_schema.assertions + WHERE constraint_name <> 'a'))) + AS v(predicate, truth); + +ROLLBACK; + +BEGIN TRANSACTION; +-- TODO This needs rethinking +/* +CREATE TABLE s (n INTEGER NOT NULL PRIMARY KEY, m INTEGER NOT NULL); +CREATE TABLE t (n INTEGER NOT NULL PRIMARY KEY, p BOOLEAN NOT NULL); +CREATE VIEW v AS SELECT * FROM s INNER JOIN t USING (n); + +CREATE ASSERTION a CHECK (NOT EXISTS (SELECT FROM v)); + +-- we should depend on the view not the tables +SELECT OK(depends_on('a', 'v'), + 'Assertion depends upon the view v it references'); +SELECT OK(NOT depends_on('a', 's'), + 'Assertion does not depend upon the table s referenced in the view v'); +SELECT OK(NOT depends_on('a', 't'), + 'Assertion does not depend upon the table t referenced in the view v'); + +-- we should trigger on the tables and not the view +SELECT OK(EXISTS(SELECT FROM assertion_check_operation + WHERE (assertion_name, relation_name) = ('a', 's')), + 'Assertion is checked on modifications to table s'); +SELECT OK(EXISTS(SELECT FROM assertion_check_operation + WHERE (assertion_name, relation_name) = ('a', 't')), + 'Assertion is checked on modifications to table t'); +SELECT OK(NOT EXISTS(SELECT FROM assertion_check_operation + WHERE (assertion_name, relation_name) = ('a', 'v')), + 'Assertion is not checked on modifications to view v'); +*/ +ROLLBACK; + +-- TODO test commonalities between count, min, max, etc, for optimisations +-- TODO ensure window function aggregates are treated the same as regular aggregates +-- TODO ensure that conflicting aggregate functions are not incorrectly optimised +-- TODO ensure that recorded dependencies are correct (traversal into functions and views) + +BEGIN TRANSACTION; + +DROP FUNCTION have_identical_invalidating_operations; +DROP VIEW invalidating_summary; +DROP VIEW invalidating_by_update_of_column; +DROP VIEW invalidating_operation; + +COMMIT; -- 2.15.0