Exclusion Constraints

>From 615f43ae291de545b350369c7b4f3397aff4d75e Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 14 Nov 2013 22:13:56 -0500 Subject: [PATCH] SQL assertions prototype --- doc/src/sgml/ddl.sgml | 31 +++ doc/src/sgml/ref/allfiles.sgml | 3 + doc/src/sgml/ref/alter_assertion.sgml | 138 ++++++++++++ doc/src/sgml/ref/create_assertion.sgml | 133 ++++++++++++ doc/src/sgml/ref/drop_assertion.sgml | 114 ++++++++++ doc/src/sgml/reference.sgml | 3 + src/backend/catalog/aclchk.c | 31 +++ src/backend/catalog/information_schema.sql | 15 +- src/backend/catalog/namespace.c | 120 +++++++++++ src/backend/catalog/objectaddress.c | 10 + src/backend/catalog/pg_constraint.c | 20 +- src/backend/commands/alter.c | 6 + src/backend/commands/constraint.c | 335 +++++++++++++++++++++++++++++ src/backend/commands/event_trigger.c | 2 + src/backend/nodes/copyfuncs.c | 14 ++ src/backend/nodes/equalfuncs.c | 12 ++ src/backend/parser/gram.y | 73 ++++--- src/backend/parser/parse_agg.c | 2 + src/backend/parser/parse_expr.c | 2 + src/backend/tcop/utility.c | 20 ++ src/bin/psql/command.c | 3 + src/bin/psql/describe.c | 91 ++++++++ src/bin/psql/describe.h | 3 + src/bin/psql/help.c | 1 + src/bin/psql/tab-complete.c | 35 ++- src/include/catalog/namespace.h | 1 + src/include/catalog/pg_constraint.h | 5 +- src/include/catalog/pg_proc.h | 3 + src/include/commands/constraint.h | 10 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 12 ++ src/include/parser/parse_node.h | 1 + src/include/utils/acl.h | 2 + src/include/utils/builtins.h | 1 + src/test/regress/expected/assertions.out | 58 +++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/assertions.sql | 31 +++ 38 files changed, 1303 insertions(+), 42 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 bae2e97..e85e2ea 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -912,6 +912,37 @@ Exclusion Constraints 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 5846974..5ca4067 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -7,6 +7,7 @@ + @@ -51,6 +52,7 @@ + @@ -91,6 +93,7 @@ + diff --git a/doc/src/sgml/ref/alter_assertion.sgml b/doc/src/sgml/ref/alter_assertion.sgml new file mode 100644 index 0000000..14fe015 --- /dev/null +++ b/doc/src/sgml/ref/alter_assertion.sgml @@ -0,0 +1,138 @@ + + + + + ALTER ASSERTION + 7 + SQL - Language Statements + + + + ALTER ASSERTION + change the definition of an assertion + + + + ALTER 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 0000000..8ae4ec8 --- /dev/null +++ b/doc/src/sgml/ref/create_assertion.sgml @@ -0,0 +1,133 @@ + + + + + CREATE ASSERTION + 7 + SQL - Language Statements + + + + CREATE ASSERTION + define a new assertion + + + + CREATE 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 0000000..9ecfc3e --- /dev/null +++ b/doc/src/sgml/ref/drop_assertion.sgml @@ -0,0 +1,114 @@ + + + + + DROP ASSERTION + 7 + SQL - Language Statements + + + + DROP ASSERTION + remove an assertion + + + + DROP 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 14e217a..a749e74 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -35,6 +35,7 @@ SQL Commands &abort; &alterAggregate; + &alterAssertion; &alterCollation; &alterConversion; &alterDatabase; @@ -79,6 +80,7 @@ SQL Commands &commitPrepared; ©Table; &createAggregate; + &createAssertion; &createCast; &createCollation; &createConversion; @@ -119,6 +121,7 @@ SQL Commands &discard; &do; &dropAggregate; + &dropAssertion; &dropCast; &dropCollation; &dropConversion; diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 06aa766..a371e73 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -3291,6 +3291,8 @@ static const char *const no_priv_msg[MAX_ACL_KIND] = gettext_noop("permission denied for operator family %s"), /* ACL_KIND_COLLATION */ gettext_noop("permission denied for collation %s"), + /* ACL_KIND_CONSTRAINT */ + gettext_noop("permission denied for constraint %s"), /* ACL_KIND_CONVERSION */ gettext_noop("permission denied for conversion %s"), /* ACL_KIND_TABLESPACE */ @@ -3337,6 +3339,8 @@ static const char *const not_owner_msg[MAX_ACL_KIND] = gettext_noop("must be owner of operator family %s"), /* ACL_KIND_COLLATION */ gettext_noop("must be owner of collation %s"), + /* ACL_KIND_CONSTRAINT */ + gettext_noop("must be owner of constraint %s"), /* ACL_KIND_CONVERSION */ gettext_noop("must be owner of conversion %s"), /* ACL_KIND_TABLESPACE */ @@ -4983,6 +4987,33 @@ pg_collation_ownercheck(Oid coll_oid, Oid roleid) } /* + * 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). */ bool diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index c5f7a8b..ba5bccb 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -268,7 +268,20 @@ CREATE VIEW administrable_role_authorizations AS * 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; /* diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c index 4434dd6..1bb5b24 100644 --- a/src/backend/catalog/namespace.c +++ b/src/backend/catalog/namespace.c @@ -22,9 +22,11 @@ #include "access/htup_details.h" #include "access/xact.h" #include "catalog/dependency.h" +#include "catalog/indexing.h" #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" @@ -49,11 +51,13 @@ #include "utils/acl.h" #include "utils/builtins.h" #include "utils/catcache.h" +#include "utils/fmgroids.h" #include "utils/guc.h" #include "utils/inval.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/syscache.h" +#include "utils/tqual.h" /* @@ -3256,6 +3260,122 @@ 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 9011190..a14bd72 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -547,6 +547,11 @@ get_object_address(ObjectType objtype, List *objname, List *objargs, missing_ok, -1); address.objectSubId = 0; break; + case OBJECT_ASSERTION: + address.classId = ConstraintRelationId; + address.objectId = get_assertion_oid(objname, missing_ok); + address.objectSubId = 0; + break; case OBJECT_COLLATION: address.classId = CollationRelationId; address.objectId = get_collation_oid(objname, missing_ok); @@ -1164,6 +1169,11 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_NAMESPACE, NameListToString(objname)); break; + case OBJECT_ASSERTION: + if (!pg_constraint_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CONSTRAINT, + NameListToString(objname)); + break; case OBJECT_COLLATION: if (!pg_collation_ownercheck(address.objectId, roleid)) aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_COLLATION, diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 5021420..5851626 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -428,6 +428,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); @@ -601,8 +608,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 */ simple_heap_delete(conDesc, &tup->t_self); @@ -658,6 +664,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 b62ec70..8efc6af 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -38,6 +38,7 @@ #include "catalog/pg_ts_template.h" #include "commands/alter.h" #include "commands/collationcmds.h" +#include "commands/constraint.h" #include "commands/conversioncmds.h" #include "commands/dbcommands.h" #include "commands/defrem.h" @@ -304,6 +305,9 @@ ExecRenameStmt(RenameStmt *stmt) { switch (stmt->renameType) { + case OBJECT_ASSERTION: + return RenameAssertion(stmt->object, stmt->newname); + case OBJECT_CONSTRAINT: return RenameConstraint(stmt); @@ -409,6 +413,7 @@ ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt) /* generic code path */ case OBJECT_AGGREGATE: + case OBJECT_ASSERTION: case OBJECT_COLLATION: case OBJECT_CONVERSION: case OBJECT_FUNCTION: @@ -703,6 +708,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 f2cdc27..9ef1f55 100644 --- a/src/backend/commands/constraint.c +++ b/src/backend/commands/constraint.c @@ -13,11 +13,32 @@ */ #include "postgres.h" +#include "access/htup_details.h" +#include "catalog/dependency.h" #include "catalog/index.h" +#include "catalog/namespace.h" +#include "catalog/pg_constraint.h" +#include "catalog/pg_trigger.h" +#include "commands/constraint.h" #include "commands/trigger.h" #include "executor/executor.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/parser.h" +#include "parser/parsetree.h" +#include "utils/acl.h" #include "utils/builtins.h" +#include "utils/lsyscache.h" #include "utils/rel.h" +#include "utils/snapmgr.h" +#include "utils/syscache.h" #include "utils/tqual.h" @@ -188,3 +209,317 @@ unique_key_recheck(PG_FUNCTION_ARGS) return PointerGetDatum(NULL); } + + +Datum +assertion_check(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + const char *funcname = "assertion_check"; + Oid constraintOid; + HeapTuple tup; + Datum adatum; + bool isNull; + char *query; + int ret; + + /* + * 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); + + SPI_connect(); + query = psprintf("SELECT %s", TextDatumGetCString(adatum)); + ret = SPI_exec(query, 1); + + if (ret > 0 && SPI_tuptable != NULL) + { + Datum val = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isNull); + if (!isNull && DatumGetBool(val) == false) + ereport(ERROR, + (errcode(ERRCODE_CHECK_VIOLATION), + errmsg("assertion \"%s\" violated", + get_constraint_name(constraintOid)))); + } + else + elog(ERROR, "unexpected SPI result"); + + SPI_finish(); + ReleaseSysCache(tup); + return PointerGetDatum(NULL); +} + + +struct collect_used_tables_context +{ + List *rtable; + List *rels; +}; + +static bool +collect_used_tables_walker(Node *node, struct collect_used_tables_context *context) +{ + if (node == NULL) + return false; + + if (IsA(node, RangeTblRef)) + { + RangeTblRef *ref; + Oid relid; + + ref= (RangeTblRef *) node; + relid = getrelid(ref->rtindex, context->rtable); + context->rels = list_append_unique_oid(context->rels, relid); + return false; + } + + if (IsA(node, Query)) + { + Query *query; + List *old_rtable; + bool result; + + query = (Query *) node; + old_rtable = context->rtable; + context->rtable = query->rtable; + result = query_tree_walker(query, collect_used_tables_walker, context, 0); + context->rtable = old_rtable; + return result; + } + + return expression_tree_walker(node, collect_used_tables_walker, (void *) context); +} + +void +CreateAssertion(CreateAssertionStmt *stmt) +{ + Oid namespaceId; + char *assertion_name; + AclResult aclresult; + Node *expr; + ParseState *pstate; + char *ccsrc; + char *ccbin; + Oid constrOid; + struct collect_used_tables_context context; + ListCell *lc; + + namespaceId = QualifiedNameGetCreationNamespace(stmt->assertion_name, + &assertion_name); + + aclresult = pg_namespace_aclcheck(namespaceId, GetUserId(), ACL_CREATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_NAMESPACE, + get_namespace_name(namespaceId)); + + // TODO: check constraint 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 */ + + context.rtable = NIL; + context.rels = NIL; + collect_used_tables_walker(expr, &context); + + foreach (lc, context.rels) + { + Oid relid = lfirst_oid(lc); + CreateTrigStmt *trigger; + Relation rel; + ObjectAddress myself, + referenced; + + rel = heap_open(relid, 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 = TRIGGER_TYPE_INSERT | TRIGGER_TYPE_UPDATE | TRIGGER_TYPE_DELETE | TRIGGER_TYPE_TRUNCATE; + trigger->columns = NIL; + trigger->whenClause = NULL; + trigger->isconstraint = true; + trigger->deferrable = stmt->constraint->deferrable; + trigger->initdeferred = stmt->constraint->initdeferred; + trigger->constrrel = NULL; + + CreateTrigger(trigger, NULL, constrOid, InvalidOid, true); + + heap_close(rel, NoLock); + + /* + * Record a dependency between the constraint and the table. + */ + + myself.classId = ConstraintRelationId; + myself.objectId = constrOid; + myself.objectSubId = 0; + + referenced.classId = RelationRelationId; + referenced.objectId = relid; + referenced.objectSubId = 0; + + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + } +} + + +void +DropAssertion(DropStmt *drop) +{ + ObjectAddresses *objects; + ListCell *cell; + + /* + * First we identify all the assertions, then we delete them in a single + * performMultipleDeletions() call. This is to avoid unwanted DROP + * RESTRICT errors if one of the assertions depends on another. (Not that + * that is very likely, but we may as well do this consistently.) + */ + objects = new_object_addresses(); + + foreach(cell, drop->objects) + { + List *name = (List *) lfirst(cell); + Oid assertionOid; + HeapTuple tuple; + Form_pg_constraint con; + ObjectAddress object; + + assertionOid = get_assertion_oid(name, drop->missing_ok); + + if (!OidIsValid(assertionOid)) + { + ereport(NOTICE, + (errmsg("assertion \"%s\" does not exist, skipping", + NameListToString(name)))); + continue; + } + + tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(assertionOid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for constraint %u", + assertionOid); + con = (Form_pg_constraint) GETSTRUCT(tuple); + + /* Permission check: must own constraint or its namespace */ + if (!pg_constraint_ownercheck(assertionOid, GetUserId()) && + !pg_namespace_ownercheck(con->connamespace, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CONSTRAINT, + NameStr(con->conname)); + + object.classId = ConstraintRelationId; + object.objectId = assertionOid; + object.objectSubId = 0; + + add_exact_object_address(&object, objects); + + ReleaseSysCache(tuple); + } + + performMultipleDeletions(objects, drop->behavior, 0); + + free_object_addresses(objects); +} + + +Oid +RenameAssertion(List *name, const char *newname) +{ + Oid assertionOid; + Relation rel; + HeapTuple tuple; + Form_pg_constraint con; + AclResult aclresult; + + rel = heap_open(ConstraintRelationId, RowExclusiveLock); + + assertionOid = get_assertion_oid(name, false); + + tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(assertionOid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for constraint %u", + assertionOid); + con = (Form_pg_constraint) GETSTRUCT(tuple); + + /* must be owner */ + if (!pg_constraint_ownercheck(assertionOid, GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CONVERSION, + NameListToString(name)); + + /* must have CREATE privilege on namespace */ + aclresult = pg_namespace_aclcheck(con->connamespace, GetUserId(), ACL_CREATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_NAMESPACE, + get_namespace_name(con->connamespace)); + + ReleaseSysCache(tuple); + + RenameConstraintById(assertionOid, newname); + + heap_close(rel, NoLock); + + return assertionOid; +} diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index 328e2a8..8aa48ef 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -68,6 +68,7 @@ typedef enum static event_trigger_support_data event_trigger_support[] = { {"AGGREGATE", true}, + {"ASSERTION", true}, {"CAST", true}, {"CONSTRAINT", true}, {"COLLATION", true}, @@ -914,6 +915,7 @@ EventTriggerSupportsObjectType(ObjectType obtype) /* no support for event triggers on event triggers */ return false; case OBJECT_AGGREGATE: + case OBJECT_ASSERTION: case OBJECT_ATTRIBUTE: case OBJECT_CAST: case OBJECT_COLUMN: diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 1733da6..7076bf4 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3668,6 +3668,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) { @@ -4453,6 +4464,9 @@ copyObject(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 7b29812..7dd5ce4 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1874,6 +1874,15 @@ _equalCreateSchemaStmt(const CreateSchemaStmt *a, const CreateSchemaStmt *b) } 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) { COMPARE_NODE_FIELD(conversion_name); @@ -2921,6 +2930,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 11f6291..5ce530c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -227,11 +227,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt - CreateAssertStmt CreateTrigStmt CreateEventTrigStmt + CreateAssertionStmt CreateTrigStmt CreateEventTrigStmt CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt DropGroupStmt DropOpClassStmt DropOpFamilyStmt DropPLangStmt DropStmt - DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt + DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt DropUserStmt DropdbStmt DropTableSpaceStmt DropFdwStmt DropForeignServerStmt DropUserMappingStmt ExplainStmt FetchStmt GrantStmt GrantRoleStmt IndexStmt InsertStmt ListenStmt LoadStmt @@ -738,7 +738,7 @@ stmt : | ConstraintsSetStmt | CopyStmt | CreateAsStmt - | CreateAssertStmt + | CreateAssertionStmt | CreateCastStmt | CreateConversionStmt | CreateDomainStmt @@ -769,7 +769,6 @@ stmt : | DeleteStmt | DiscardStmt | DoStmt - | DropAssertStmt | DropCastStmt | DropFdwStmt | DropForeignServerStmt @@ -4601,45 +4600,29 @@ enable_trigger: * * QUERIES : * CREATE ASSERTION ... - * DROP ASSERTION ... * *****************************************************************************/ -CreateAssertStmt: - CREATE ASSERTION name CHECK '(' a_expr ')' - ConstraintAttributeSpec +CreateAssertionStmt: + CREATE ASSERTION any_name CHECK '(' a_expr ')' ConstraintAttributeSpec { - CreateTrigStmt *n = makeNode(CreateTrigStmt); - n->trigname = $3; - n->args = list_make1($6); - n->isconstraint = TRUE; + 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", - &n->deferrable, &n->initdeferred, NULL, + &c->deferrable, &c->initdeferred, NULL, NULL, yyscanner); - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("CREATE ASSERTION is not yet implemented"))); - + n->assertion_name = $3; + n->constraint = c; $$ = (Node *)n; } ; -DropAssertStmt: - DROP ASSERTION name opt_drop_behavior - { - DropStmt *n = makeNode(DropStmt); - n->objects = NIL; - n->arguments = 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; - } - ; - /***************************************************************************** * @@ -5190,6 +5173,7 @@ drop_type: TABLE { $$ = OBJECT_TABLE; } | EVENT TRIGGER { $$ = OBJECT_EVENT_TRIGGER; } | TYPE_P { $$ = OBJECT_TYPE; } | DOMAIN_P { $$ = OBJECT_DOMAIN; } + | ASSERTION { $$ = OBJECT_ASSERTION; } | COLLATION { $$ = OBJECT_COLLATION; } | CONVERSION_P { $$ = OBJECT_CONVERSION; } | SCHEMA { $$ = OBJECT_SCHEMA; } @@ -5400,6 +5384,7 @@ comment_type: | TYPE_P { $$ = OBJECT_TYPE; } | VIEW { $$ = OBJECT_VIEW; } | MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; } + | ASSERTION { $$ = OBJECT_ASSERTION; } | COLLATION { $$ = OBJECT_COLLATION; } | CONVERSION_P { $$ = OBJECT_CONVERSION; } | TABLESPACE { $$ = OBJECT_TABLESPACE; } @@ -6889,6 +6874,14 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args 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 = $3; + n->newname = $6; + $$ = (Node *)n; + } | ALTER COLLATION any_name RENAME TO name { RenameStmt *n = makeNode(RenameStmt); @@ -7363,6 +7356,14 @@ AlterObjectSchemaStmt: n->missing_ok = false; $$ = (Node *)n; } + | ALTER ASSERTION any_name SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_ASSERTION; + n->object = $3; + n->newschema = $6; + $$ = (Node *)n; + } | ALTER COLLATION any_name SET SCHEMA name { AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); @@ -7591,6 +7592,14 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $7; $$ = (Node *)n; } + | ALTER ASSERTION any_name OWNER TO RoleId + { + AlterOwnerStmt *n = makeNode(AlterOwnerStmt); + n->objectType = OBJECT_ASSERTION; + n->object = $3; + n->newowner = $6; + $$ = (Node *)n; + } | ALTER COLLATION any_name OWNER TO RoleId { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 98cb58a..0e6bd60 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -251,6 +251,7 @@ transformAggregateCall(ParseState *pstate, Aggref *agg, break; case EXPR_KIND_CHECK_CONSTRAINT: case EXPR_KIND_DOMAIN_CHECK: + case EXPR_KIND_ASSERTION_CHECK: err = _("aggregate functions are not allowed in check constraints"); break; case EXPR_KIND_COLUMN_DEFAULT: @@ -526,6 +527,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 68b711d..ec8046f 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1459,6 +1459,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_OFFSET: case EXPR_KIND_RETURNING: case EXPR_KIND_VALUES: + case EXPR_KIND_ASSERTION_CHECK: /* okay */ break; case EXPR_KIND_CHECK_CONSTRAINT: @@ -2626,6 +2627,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/tcop/utility.c b/src/backend/tcop/utility.c index 6a7bf0d..d9a739c 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -28,6 +28,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" @@ -194,6 +195,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: @@ -1276,6 +1278,10 @@ ProcessUtilitySlow(Node *parsetree, DefineDomain((CreateDomainStmt *) parsetree); break; + case T_CreateAssertionStmt: + CreateAssertion((CreateAssertionStmt *) parsetree); + break; + case T_CreateConversionStmt: CreateConversionCommand((CreateConversionStmt *) parsetree); break; @@ -1574,6 +1580,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; @@ -1885,6 +1894,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; @@ -2255,6 +2267,10 @@ CreateCommandTag(Node *parsetree) tag = "REINDEX"; break; + case T_CreateAssertionStmt: + tag = "CREATE ASSERTION"; + break; + case T_CreateConversionStmt: tag = "CREATE CONVERSION"; break; @@ -2798,6 +2814,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/bin/psql/command.c b/src/bin/psql/command.c index d0e25a9..c6b9aac 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -358,6 +358,9 @@ exec_command(const char *cmd, case 'a': success = describeAggregates(pattern, show_verbose, show_system); break; + case 'A': + success = describeAssertions(pattern); + break; case 'b': success = describeTablespaces(pattern, show_verbose); break; diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index ceda13e..f128388 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -129,6 +129,65 @@ 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, false); + termPQExpBuffer(&buf); + if (!res) + return false; + + myopt.nullPrint = NULL; + myopt.title = _("List of assertions"); + myopt.translate_header = true; + + printQuery(res, &myopt, pset.queryFout, pset.logfile); + + PQclear(res); + return true; +} + /* \db * Takes an optional regexp to select particular tablespaces */ @@ -1926,6 +1985,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, false); + 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 != 'm') { diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 09b6237..7b83d61 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -12,6 +12,9 @@ /* \da */ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); +/* \dA */ +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 30530f2..410a7f5 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -203,6 +203,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, _(" \\dA [PATTERN] list assertions\n")); fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n")); fprintf(output, _(" \\dc[S+] [PATTERN] list conversions\n")); fprintf(output, _(" \\dC[+] [PATTERN] list casts\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index f6c1aaf..efdadd5 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -247,6 +247,21 @@ static const SchemaQuery Query_for_list_of_aggregates = { NULL }; +static const SchemaQuery Query_for_list_of_assertions = { + /* 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 = { /* catname */ "pg_catalog.pg_type t", @@ -727,6 +742,7 @@ typedef struct static const pgsql_thing_t words_after_create[] = { {"AGGREGATE", NULL, &Query_for_list_of_aggregates}, + {"ASSERTION", NULL, &Query_for_list_of_assertions}, {"CAST", 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'"}, @@ -857,7 +873,7 @@ psql_completion(char *text, int start, int end) static const char *const backslash_commands[] = { "\\a", "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy", "\\copyright", - "\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\df", + "\\d", "\\da", "\\dA", "\\db", "\\dc", "\\dC", "\\dd", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\df", "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL", "\\dn", "\\do", "\\dp", "\\drds", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\e", "\\echo", "\\ef", "\\encoding", @@ -927,7 +943,7 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev3_wd, "TABLE") != 0) { static const char *const list_ALTER[] = - {"AGGREGATE", "COLLATION", "CONVERSION", "DATABASE", "DEFAULT PRIVILEGES", "DOMAIN", + {"AGGREGATE", "ASSERTION", "COLLATION", "CONVERSION", "DATABASE", "DEFAULT PRIVILEGES", "DOMAIN", "EXTENSION", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "FUNCTION", "GROUP", "INDEX", "LANGUAGE", "LARGE OBJECT", "MATERIALIZED VIEW", "OPERATOR", "ROLE", "RULE", "SCHEMA", "SERVER", "SEQUENCE", "TABLE", @@ -967,6 +983,16 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_LIST(list_ALTERGEN); } + /* ALTER ASSERTION */ + else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && + pg_strcasecmp(prev2_wd, "ASSERTION") == 0) + { + static const char *const list_ALTERGEN[] = + {"OWNER TO", "RENAME TO", "SET SCHEMA", NULL}; + + COMPLETE_WITH_LIST(list_ALTERGEN); + } + /* ALTER COLLATION */ else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && pg_strcasecmp(prev2_wd, "COLLATION") == 0) @@ -2398,7 +2424,8 @@ psql_completion(char *text, int start, int end) /* DROP object with CASCADE / RESTRICT */ else if ((pg_strcasecmp(prev3_wd, "DROP") == 0 && - (pg_strcasecmp(prev2_wd, "COLLATION") == 0 || + (pg_strcasecmp(prev2_wd, "ASSERTION") == 0 || + pg_strcasecmp(prev2_wd, "COLLATION") == 0 || pg_strcasecmp(prev2_wd, "CONVERSION") == 0 || pg_strcasecmp(prev2_wd, "DOMAIN") == 0 || pg_strcasecmp(prev2_wd, "EXTENSION") == 0 || @@ -3271,6 +3298,8 @@ psql_completion(char *text, int start, int end) else if (strncmp(prev_wd, "\\da", strlen("\\da")) == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL); + else if (strncmp(prev_wd, "\\dA", strlen("\\dA")) == 0) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_assertions, NULL); else if (strncmp(prev_wd, "\\db", strlen("\\db")) == 0) COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces); else if (strncmp(prev_wd, "\\dD", strlen("\\dD")) == 0) diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h index 4fadc74..0a1e325 100644 --- a/src/include/catalog/namespace.h +++ b/src/include/catalog/namespace.h @@ -132,6 +132,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 e1dba46..f27d7c4 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -59,8 +59,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 */ @@ -200,7 +199,7 @@ typedef enum ConstraintCategory { CONSTRAINT_RELATION, CONSTRAINT_DOMAIN, - CONSTRAINT_ASSERTION /* for future expansion */ + CONSTRAINT_ASSERTION } ConstraintCategory; /* diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index ca4fc62..35b37ca 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -1994,6 +1994,9 @@ DESCR("is a column updatable"); DATA(insert OID = 1250 ( unique_key_recheck PGNSP PGUID 12 1 0 0 0 f f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ unique_key_recheck _null_ _null_ _null_ )); DESCR("deferred UNIQUE constraint check"); +DATA(insert OID = 3789 ( assertion_check PGNSP PGUID 12 1 0 0 0 f f f f t f v 0 0 2279 "" _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 f t f v 0 0 2279 "" _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 0000000..e76f483 --- /dev/null +++ b/src/include/commands/constraint.h @@ -0,0 +1,10 @@ +#ifndef CONSTRAINT_H +#define CONSTRAINT_H + +#include "nodes/parsenodes.h" + +extern void CreateAssertion(CreateAssertionStmt *stmt); +extern void DropAssertion(DropStmt *drop); +extern Oid RenameAssertion(List *name, const char *newname); + +#endif diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index fc6b1d7..6cbcaa5 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -363,6 +363,7 @@ typedef enum NodeTag T_AlterEventTrigStmt, T_RefreshMatViewStmt, T_ReplicaIdentityStmt, + T_CreateAssertionStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 55524b4..67d2232 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1157,6 +1157,7 @@ typedef struct SetOperationStmt typedef enum ObjectType { OBJECT_AGGREGATE, + OBJECT_ASSERTION, OBJECT_ATTRIBUTE, /* type's attribute, when distinct from column */ OBJECT_CAST, OBJECT_COLUMN, @@ -2599,6 +2600,17 @@ typedef struct ReindexStmt } 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 bea3b07..4c8484b 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -57,6 +57,7 @@ typedef enum ParseExprKind EXPR_KIND_VALUES, /* VALUES */ EXPR_KIND_CHECK_CONSTRAINT, /* CHECK constraint for a table */ EXPR_KIND_DOMAIN_CHECK, /* CHECK constraint for a domain */ + EXPR_KIND_ASSERTION_CHECK, /* CHECK constraint in 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 2116259..3431029 100644 --- a/src/include/utils/acl.h +++ b/src/include/utils/acl.h @@ -189,6 +189,7 @@ typedef enum AclObjectKind ACL_KIND_OPCLASS, /* pg_opclass */ ACL_KIND_OPFAMILY, /* pg_opfamily */ ACL_KIND_COLLATION, /* pg_collation */ + ACL_KIND_CONSTRAINT, /* pg_constraint */ ACL_KIND_CONVERSION, /* pg_conversion */ ACL_KIND_TABLESPACE, /* pg_tablespace */ ACL_KIND_TSDICTIONARY, /* pg_ts_dict */ @@ -318,6 +319,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/builtins.h b/src/include/utils/builtins.h index ce3f00b..98c520f 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -1157,6 +1157,7 @@ extern Datum pg_identify_object(PG_FUNCTION_ARGS); /* commands/constraint.c */ extern Datum unique_key_recheck(PG_FUNCTION_ARGS); +extern Datum assertion_check(PG_FUNCTION_ARGS); /* commands/event_trigger.c */ extern Datum pg_event_trigger_dropped_objects(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/assertions.out b/src/test/regress/expected/assertions.out new file mode 100644 index 0000000..d09c6d0 --- /dev/null +++ b/src/test/regress/expected/assertions.out @@ -0,0 +1,58 @@ +CREATE TABLE test1 (a int, b text); +CREATE ASSERTION foo CHECK (1 < 2); +CREATE ASSERTION wrong CHECK (1 + 2); +ERROR: argument of CHECK must be type boolean, not type integer +CREATE ASSERTION wrong CHECK (a < 5); +ERROR: column "a" does not exist +CREATE ASSERTION a2 CHECK ((SELECT count(*) FROM test1) < 5); +CREATE ASSERTION wrong CHECK ((SELECT count(*) FROM wrong) < 5); +ERROR: relation "wrong" does not exist +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'); +INSERT INTO test1 VALUES (5, 'five'); +ERROR: assertion "a2" violated +SELECT constraint_schema, constraint_name FROM information_schema.assertions ORDER BY 1, 2; + constraint_schema | constraint_name +-------------------+----------------- + public | a2 + public | foo +(2 rows) + +\dA + 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; +ALTER ASSERTION foo RENAME TO a3; -- fails +ERROR: assertion "a3" already exists +ALTER ASSERTION wrong RENAME TO wrong2; -- fails +ERROR: assertion "wrong" does not exist +SELECT constraint_schema, constraint_name FROM information_schema.assertions ORDER BY 1, 2; + constraint_schema | constraint_name +-------------------+----------------- + public | a3 + public | foo +(2 rows) + +DROP ASSERTION foo; +DROP ASSERTION wrong; +ERROR: assertion "wrong" does not exist +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. +DROP TABLE test1 CASCADE; +NOTICE: drop cascades to constraint a3 +SELECT constraint_schema, constraint_name FROM information_schema.assertions ORDER BY 1, 2; + constraint_schema | constraint_name +-------------------+----------------- +(0 rows) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 5758b07..baeb57f 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -59,7 +59,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 +test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views assertions # ---------- # 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 78348f5..87c8708 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -52,6 +52,7 @@ test: create_function_1 test: create_type test: create_table test: create_function_2 +test: assertions test: copy test: copyselect test: create_misc diff --git a/src/test/regress/sql/assertions.sql b/src/test/regress/sql/assertions.sql new file mode 100644 index 0000000..247f74d --- /dev/null +++ b/src/test/regress/sql/assertions.sql @@ -0,0 +1,31 @@ +CREATE TABLE test1 (a int, b text); + +CREATE ASSERTION foo CHECK (1 < 2); +CREATE ASSERTION wrong CHECK (1 + 2); +CREATE ASSERTION wrong CHECK (a < 5); +CREATE ASSERTION a2 CHECK ((SELECT count(*) FROM test1) < 5); +CREATE ASSERTION wrong CHECK ((SELECT count(*) FROM wrong) < 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'); +INSERT INTO test1 VALUES (5, 'five'); + +SELECT constraint_schema, constraint_name FROM information_schema.assertions ORDER BY 1, 2; +\dA + +ALTER ASSERTION a2 RENAME TO a3; +ALTER ASSERTION foo RENAME TO a3; -- fails +ALTER ASSERTION wrong RENAME TO wrong2; -- fails + +SELECT constraint_schema, constraint_name FROM information_schema.assertions ORDER BY 1, 2; + +DROP ASSERTION foo; +DROP ASSERTION wrong; + +DROP TABLE test1; -- fails +DROP TABLE test1 CASCADE; + +SELECT constraint_schema, constraint_name FROM information_schema.assertions ORDER BY 1, 2; -- 1.8.4.2