[PATCH] SQL assertions prototype
Various places in the constraint checking code say something like, if we
ever implement assertions, here is where it should go. I've been
fiddling with filling in those gaps for some time now, and the other day
I noticed, hey, this actually kind of works, so here it is. Let's see
whether this architecture is sound.
A constraint trigger performs the actual checking. For the
implementation of the trigger, I've used some SPI hacking for now; that
could probably be refined. The attached patch has documentation, tests,
psql support. Missing pieces are pg_dump support, dependency
management, and permission checking (the latter marked in the code).
This is not a performance feature. It's for things like, this table
should have at most 10 rows, or all the values in this table must be
bigger than all the values in that other table. It's a bit esoteric,
but it comes up again and again.
Let me know what you think.
Attachments:
0001-SQL-assertions-prototype.patchtext/x-patch; charset=UTF-8; name=0001-SQL-assertions-prototype.patchDownload
>From 615f43ae291de545b350369c7b4f3397aff4d75e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
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 @@ <title>Exclusion Constraints</title>
of the type specified in the constraint declaration.
</para>
</sect2>
+
+ <sect2 id="ddl-constraints-assertions">
+ <title>Assertions</title>
+
+ <indexterm zone="ddl-constraints-assertions">
+ <primary>assertion</primary>
+ </indexterm>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ An assertion is a separate schema object and is created with the
+ command <command>CREATE ASSERTION</command>. The constraint
+ expression is written as a <literal>CHECK</literal> clause like in
+ check constraints. For instance, to ensure that there is always
+ at least one entry in the product table:
+<programlisting>
+CREATE ASSERTION products_not_empty CHECK ((SELECT count(*) FROM products) > 0);
+</programlisting>
+ 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.
+ </para>
+ </sect2>
</sect1>
<sect1 id="ddl-system-columns">
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 @@
<!-- SQL commands -->
<!ENTITY abort SYSTEM "abort.sgml">
<!ENTITY alterAggregate SYSTEM "alter_aggregate.sgml">
+<!ENTITY alterAssertion SYSTEM "alter_assertion.sgml">
<!ENTITY alterCollation SYSTEM "alter_collation.sgml">
<!ENTITY alterConversion SYSTEM "alter_conversion.sgml">
<!ENTITY alterDatabase SYSTEM "alter_database.sgml">
@@ -51,6 +52,7 @@
<!ENTITY commitPrepared SYSTEM "commit_prepared.sgml">
<!ENTITY copyTable SYSTEM "copy.sgml">
<!ENTITY createAggregate SYSTEM "create_aggregate.sgml">
+<!ENTITY createAssertion SYSTEM "create_assertion.sgml">
<!ENTITY createCast SYSTEM "create_cast.sgml">
<!ENTITY createCollation SYSTEM "create_collation.sgml">
<!ENTITY createConversion SYSTEM "create_conversion.sgml">
@@ -91,6 +93,7 @@
<!ENTITY discard SYSTEM "discard.sgml">
<!ENTITY do SYSTEM "do.sgml">
<!ENTITY dropAggregate SYSTEM "drop_aggregate.sgml">
+<!ENTITY dropAssertion SYSTEM "drop_assertion.sgml">
<!ENTITY dropCast SYSTEM "drop_cast.sgml">
<!ENTITY dropCollation SYSTEM "drop_collation.sgml">
<!ENTITY dropConversion SYSTEM "drop_conversion.sgml">
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 @@
+<!--
+doc/src/sgml/ref/alter_assertion.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-ALTERASSERTION">
+ <refmeta>
+ <refentrytitle>ALTER ASSERTION</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>ALTER ASSERTION</refname>
+ <refpurpose>change the definition of an assertion</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-alterassertion">
+ <primary>ALTER ASSERTION</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+ALTER ASSERTION <replaceable>name</replaceable> RENAME TO <replaceable>new_name</replaceable>
+ALTER ASSERTION <replaceable>name</replaceable> OWNER TO <replaceable>new_owner</replaceable>
+ALTER ASSERTION <replaceable>name</replaceable> SET SCHEMA <replaceable>new_schema</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>ALTER ASSERTION</command> changes the definition of an
+ assertion.
+ </para>
+
+ <para>
+ You must own the assertion to use <command>ALTER ASSERTION</>. To
+ change the schema of an assertion, you must also have
+ <literal>CREATE</literal> 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 <literal>CREATE</literal>
+ 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.)
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of an existing assertion.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_name</replaceable></term>
+ <listitem>
+ <para>
+ The new name of the assertion.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_owner</replaceable></term>
+ <listitem>
+ <para>
+ The new owner of the assertion.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_schema</replaceable></term>
+ <listitem>
+ <para>
+ The new schema for the assertion.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To rename the assertuib <literal>check_size</literal>
+ to <literal>check_count</literal>:
+<programlisting>
+ALTER ASSERTION check_size RENAME TO check_count;
+</programlisting>
+ </para>
+
+ <para>
+ To change the owner of the assertion <literal>check_size</literal>
+ to <literal>joe</literal>:
+<programlisting>
+ALTER ASSERTION check_size OWNER TO joe;
+</programlisting>
+ </para>
+
+ <para>
+ To move the assertion <literal>check_size</literal> into
+ schema <literal>myschema</literal>:
+<programlisting>
+ALTER ASSERTION check_size SET SCHEMA myschema;
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ There is no <command>ALTER ASSERTION</command> statement in the SQL
+ standard.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createassertion"></member>
+ <member><xref linkend="sql-dropassertion"></member>
+ </simplelist>
+ </refsect1>
+</refentry>
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 @@
+<!--
+doc/src/sgml/ref/create_assertion.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-CREATEASSERTION">
+ <refmeta>
+ <refentrytitle>CREATE ASSERTION</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CREATE ASSERTION</refname>
+ <refpurpose>define a new assertion</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-createassertion">
+ <primary>CREATE ASSERTION</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+CREATE ASSERTION <replaceable class="PARAMETER">name</replaceable> CHECK ( <replaceable class="PARAMETER">name</replaceable> ) [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>CREATE ASSERTION</command> 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.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="PARAMETER">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the assertion to
+ create. Assertions use the same namespace as constraints on
+ tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> )</literal></term>
+ <listitem>
+ <para>
+ The <literal>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.
+ </para>
+
+ <para>
+ The check expression typically involves subselects in order to
+ read data from tables. See the examples below.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DEFERRABLE</literal></term>
+ <term><literal>NOT DEFERRABLE</literal></term>
+ <term><literal>INITIALLY IMMEDIATE</literal></term>
+ <term><literal>INITIALLY DEFERRED</literal></term>
+ <listitem>
+ <para>
+ These clauses control the deferrability of the constraint. See
+ <xref linkend="sql-createtable"> for an explanation.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Check that the table <literal>table1</literal> has at most 30 rows:
+<programlisting>
+CREATE ASSERTION table1_max30 CHECK ((SELECT count(*) FROM table1) <= 30);
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <command>CREATE ASSERTION</command> conforms to the SQL standard.
+ The PostgreSQL implementation has certain restrictions on what
+ check expressions are allowed in assertions.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-alterassertion"></member>
+ <member><xref linkend="sql-dropassertion"></member>
+ </simplelist>
+ </refsect1>
+</refentry>
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 @@
+<!--
+doc/src/sgml/ref/drop_assertion.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-DROPASSERTION">
+ <refmeta>
+ <refentrytitle>DROP ASSERTION</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>DROP ASSERTION</refname>
+ <refpurpose>remove an assertion</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-dropassertion">
+ <primary>DROP ASSERTION</primary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+DROP ASSERTION [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>DROP ASSERTION</command> removes an existing assertion. To
+ execute this command the current user must be the owner of the
+ assertion.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+
+ <varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the assertion does not exist. A notice
+ is issued in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of an existing assertion.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>CASCADE</literal></term>
+ <listitem>
+ <para>
+ Automatically drop objects that depend on the assertion.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RESTRICT</literal></term>
+ <listitem>
+ <para>
+ Refuse to drop the assertion if any objects depend on it. This
+ is the default.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To remove the assertion <literal>check_size</literal>:
+<programlisting>
+DROP ASSERTION check_size;
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ This command conforms to the SQL standard, except that the standard
+ only allows one assertion to be dropped per command, and apart from
+ the <literal>IF EXISTS</> option, which is
+ a <productname>PostgreSQL</> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-alterassertion"></member>
+ <member><xref linkend="sql-createassertion"></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
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 @@ <title>SQL Commands</title>
&abort;
&alterAggregate;
+ &alterAssertion;
&alterCollation;
&alterConversion;
&alterDatabase;
@@ -79,6 +80,7 @@ <title>SQL Commands</title>
&commitPrepared;
©Table;
&createAggregate;
+ &createAssertion;
&createCast;
&createCollation;
&createConversion;
@@ -119,6 +121,7 @@ <title>SQL Commands</title>
&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 <name> */
+ 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 <name> */
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
+1
interesting feature
Pavel
2013/11/15 Peter Eisentraut <peter_e@gmx.net>
Show quoted text
Various places in the constraint checking code say something like, if we
ever implement assertions, here is where it should go. I've been
fiddling with filling in those gaps for some time now, and the other day
I noticed, hey, this actually kind of works, so here it is. Let's see
whether this architecture is sound.A constraint trigger performs the actual checking. For the
implementation of the trigger, I've used some SPI hacking for now; that
could probably be refined. The attached patch has documentation, tests,
psql support. Missing pieces are pg_dump support, dependency
management, and permission checking (the latter marked in the code).This is not a performance feature. It's for things like, this table
should have at most 10 rows, or all the values in this table must be
bigger than all the values in that other table. It's a bit esoteric,
but it comes up again and again.Let me know what you think.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15.11.2013 05:30, Peter Eisentraut wrote:
Various places in the constraint checking code say something like, if we
ever implement assertions, here is where it should go. I've been
fiddling with filling in those gaps for some time now, and the other day
I noticed, hey, this actually kind of works, so here it is. Let's see
whether this architecture is sound.
Cool!
A constraint trigger performs the actual checking. For the
implementation of the trigger, I've used some SPI hacking for now; that
could probably be refined. The attached patch has documentation, tests,
psql support. Missing pieces are pg_dump support, dependency
management, and permission checking (the latter marked in the code).
A fundamental problem with this is that it needs to handle isolation
reliable, so that the assertion cannot be violated when two concurrent
backends do things. Consider the example from the manual, which checks
that a table has at least one row. Now, if the table has two rows to
begin with, and in one backend you delete one row, and concurrently in
another backend you delete the other row, and then commit both
transactions, the assertion is violated.
In other words, the assertions need to be checked in serializable mode.
Now that we have a real serializable mode, I think that's actually feasible.
PS. The patch doesn't check that the assertion holds when it's created:
postgres=# create table foo (i int4);
CREATE TABLE
postgres=# create assertion myassert check ((select count(*) from foo)
0);
CREATE ASSERTION
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 November 2013 03:30, Peter Eisentraut <peter_e@gmx.net> wrote:
A constraint trigger performs the actual checking.
Good, that is consistent with other constraints.
This is not a performance feature. It's for things like, this table
should have at most 10 rows, or all the values in this table must be
bigger than all the values in that other table. It's a bit esoteric,
but it comes up again and again.
While I accept it may never perform well, it needs to perform reasonably well.
The key use cases for this are
* enforcing "one and only one" relationships
* enforcing quantified relationships like we do in XML: minoccurs and maxoccurs
* enforcing only one sub-type across multiple sub-type tables
etc
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the table.
That last point will make it unusable for sensible amounts of data.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Nov 24, 2013 at 11:03 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the table.
That last point will make it unusable for sensible amounts of data.
That sounds very similar to handling incremental maintenance of
materialized views, which Kevin is working on.
Let's assume that the "huge SQL command that re-checks every row of
the table" is actually a materialized view. In that case, the CREATE
ASSERTION trigger would merely need to scan the matview and raise an
error if any rows were present. That should be a very quick
operation. No need to invent some sort of "get access to the changed
rows" mechanism especially for CREATE ASSERTION.
Kind regards,
Andrew Tipton
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andrew Tipton <andrew@kiwidrew.com> wrote:
Simon Riggs <simon@2ndquadrant.com> wrote:
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the
table. That last point will make it unusable for sensible
amounts of data.That sounds very similar to handling incremental maintenance of
materialized views, which Kevin is working on.
It does.
Let's assume that the "huge SQL command that re-checks every row
of the table" is actually a materialized view. In that case, the
CREATE ASSERTION trigger would merely need to scan the matview
and raise an error if any rows were present. That should be a
very quick operation.
That would certainly be a viable way to implement this once we have
incremental maintenance for materialized views, although I make no
claims to having evaluated it versus the alternatives to be able to
assert what the *best* way is.
No need to invent some sort of "get access to the changed
rows" mechanism especially for CREATE ASSERTION.
As soon as we are out of this CF, I am planning to write code to
capture deltas and fire functions to process them "eagerly" (within
the creating transaction). There has been suggestions that the
changeset mechanism should be used for that, which I will look
into; but my gut feel is that it will be better to build a
tuplestore of tids flagged with "old" or "new" around the point
that "after triggers" fire. How close does that sound to what
CREATE ASSERTION (as currently envisioned) would need? How viable
does it sound to turn an assertion expression into a matview which
is empty if there are no violations?
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Nov 25, 2013 at 11:04:23AM -0800, Kevin Grittner wrote:
Andrew Tipton <andrew@kiwidrew.com> wrote:
Simon Riggs <simon@2ndquadrant.com> wrote:
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the
table.� That last point will make it unusable for sensible
amounts of data.That sounds very similar to handling incremental maintenance of
materialized views, which Kevin is working on.It does.
Let's assume that the "huge SQL command that re-checks every row
of the table" is actually a materialized view.� In that case, the
CREATE ASSERTION trigger would merely need to scan the matview
and raise an error if any rows were present.� That should be a
very quick operation.That would certainly be a viable way to implement this once we have
incremental maintenance for materialized views, although I make no
claims to having evaluated it versus the alternatives to be able to
assert what the *best* way is.No need to invent some sort of "get access to the changed
rows" mechanism especially for CREATE ASSERTION.As soon as we are out of this CF, I am planning to write code to
capture deltas and fire functions to process them "eagerly" (within
the creating transaction).� There has been suggestions that the
changeset mechanism should be used for that, which I will look
into; but my gut feel is that it will be better to build a
tuplestore of tids flagged with "old" or "new" around the point
that "after triggers" fire.� How close does that sound to what
CREATE ASSERTION (as currently envisioned) would need?
It sounds *extremely* close to what we'd need for row access in
per-statement triggers, as in probably identical. The SQL syntax of
this sub-feature is described in Foundation section 11.49 and called
REFERENCING in CREATE TRIGGER.
Do you have any prototypes I could use for that purpose?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
David Fetter <david@fetter.org> wrote:
On Mon, Nov 25, 2013 at 11:04:23AM -0800, Kevin Grittner wrote:
As soon as we are out of this CF, I am planning to write code to
capture deltas and fire functions to process them "eagerly"
(within the creating transaction). There has been suggestions
that the changeset mechanism should be used for that, which I
will look into; but my gut feel is that it will be better to
build a tuplestore of tids flagged with "old" or "new" around
the point that "after triggers" fire. How close does that sound
to what CREATE ASSERTION (as currently envisioned) would need?It sounds *extremely* close to what we'd need for row access in
per-statement triggers, as in probably identical. The SQL syntax
of this sub-feature is described in Foundation section 11.49 and
called REFERENCING in CREATE TRIGGER.Do you have any prototypes I could use for that purpose?
No, but it is at the top of my list after the CF. I will also need
an execution node type or two to produce the referenced rows for
the appropriate contexts, which is probably also very close to what
you need for per-statement triggers. I will be happy to coordinate
work with you.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/24/13, 10:03 AM, Simon Riggs wrote:
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the table.
That last point will make it unusable for sensible amounts of data.
SQL assertions work with arbitrary expressions. So even if you had the
changed rows, you couldn't do anything with them in general. For cases
where it makes sense to consider the changed rows, you probably want a
per-table trigger or an exclusion constraint or perhaps an auto-updated
materialized view. Then again, we have lots of ways to make queries
fast even for large tables.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
A fundamental problem with this is that it needs to handle isolation
reliable, so that the assertion cannot be violated when two concurrent
backends do things. Consider the example from the manual, which checks
that a table has at least one row. Now, if the table has two rows to
begin with, and in one backend you delete one row, and concurrently in
another backend you delete the other row, and then commit both
transactions, the assertion is violated.In other words, the assertions need to be checked in serializable mode.
Now that we have a real serializable mode, I think that's actually
feasible.
Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.
Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMb7c0cf79448024412fa00bbb8b5bbd90112f0ebe39306dc19eb845f4ccd83b005a9dfeb8ce9f58a083cd8f6f65b9419a@asav-3.01.com
Josh Berkus <josh@agliodbs.com> wrote:
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
A fundamental problem with this is that it needs to handle isolation
reliable, so that the assertion cannot be violated when two concurrent
backends do things. Consider the example from the manual, which checks
that a table has at least one row. Now, if the table has two rows to
begin with, and in one backend you delete one row, and concurrently in
another backend you delete the other row, and then commit both
transactions, the assertion is violated.In other words, the assertions need to be checked in serializable mode.
Now that we have a real serializable mode, I think that's actually
feasible.Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.
It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.
Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/17/2013 04:42 PM, Kevin Grittner wrote:
Josh Berkus <josh@agliodbs.com> wrote:
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
A fundamental problem with this is that it needs to handle isolation
reliable, so that the assertion cannot be violated when two concurrent
backends do things. Consider the example from the manual, which checks
that a table has at least one row. Now, if the table has two rows to
begin with, and in one backend you delete one row, and concurrently in
another backend you delete the other row, and then commit both
transactions, the assertion is violated.In other words, the assertions need to be checked in serializable mode.
Now that we have a real serializable mode, I think that's actually
feasible.Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.
Maybe the presence of an assertion should be enough to force
serializable, i.e. turn it on and not allow it to be turned off.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 18/12/13 10:48, Andrew Dunstan wrote:
On 12/17/2013 04:42 PM, Kevin Grittner wrote:
Josh Berkus <josh@agliodbs.com> wrote:
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
A fundamental problem with this is that it needs to handle isolation
reliable, so that the assertion cannot be violated when two concurrent
backends do things. Consider the example from the manual, which checks
that a table has at least one row. Now, if the table has two rows to
begin with, and in one backend you delete one row, and concurrently in
another backend you delete the other row, and then commit both
transactions, the assertion is violated.In other words, the assertions need to be checked in serializable
mode.
Now that we have a real serializable mode, I think that's actually
feasible.Going back over this patch, I haven't seen any further discussion of
the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.Maybe the presence of an assertion should be enough to force
serializable, i.e. turn it on and not allow it to be turned off.cheers
andrew
Perhaps then it should be called an 'assurance', rather than an 'assertion?'
(Not being entirely facetious!)
Cheers,
Gavin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/17/2013 01:42 PM, Kevin Grittner wrote:
Josh Berkus <josh@agliodbs.com> wrote:
Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.
Serializable or not, *what* do we lock for assertions? It's not rows.
Tables? Which tables? What if the assertion is an interpreted language
function? Does the SSI reference counter really take care of all of this?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMec25e898f96af35050e6491f9956cd01fcb3c7ae5bfbe310723074e2de34ad7d629c9c867f126526930d62576145a615@asav-3.01.com
On 12/18/2013 02:59 AM, Josh Berkus wrote:
On 12/17/2013 01:42 PM, Kevin Grittner wrote:
Josh Berkus <josh@agliodbs.com> wrote:
Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.Serializable or not, *what* do we lock for assertions? It's not rows.
Tables? Which tables? What if the assertion is an interpreted language
function? Does the SSI reference counter really take care of all of this?
SSI does make everything rosy, as long as all the transactions
participate in it. The open questions revolve around what happens if a
transaction is not running in SSI mode.
If you force everyone to run in SSI as soon as you create even a single
assertion in your database, that's kind of crappy for performance. Also,
if one user creates an assertion, it becomes a funny kind of a partial
denial of service attack to other users, if you force other user's to
also run in SSI mode.
If you don't force everything to run in SSI mode, then you have to
somehow figure out what parts do need to run in SSI mode to enforce the
assertion. For example, if the assertion refers tables A and B, perhaps
you can get away without predicate locks on table C?
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 02:59 AM, Josh Berkus wrote:
On 12/17/2013 01:42 PM, Kevin Grittner wrote:
It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.Serializable or not, *what* do we lock for assertions? It's not rows.
Tables? Which tables? What if the assertion is an interpreted language
function? Does the SSI reference counter really take care of all of this?
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot. Now, there's a race condition, if another
transaction is committing at the same time, and performs the same check
concurrently. That race condition can be eliminated by holding an
exclusive lock while running the assertion, until commit, effectively
allowing the assertion to be checked by only one transaction at a time.
I think that would work, and would be simple, although it wouldn't scale
too well.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-12-18 13:07:51 +0200, Heikki Linnakangas wrote:
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot. Now, there's a race condition, if another transaction is
committing at the same time, and performs the same check concurrently. That
race condition can be eliminated by holding an exclusive lock while running
the assertion, until commit, effectively allowing the assertion to be
checked by only one transaction at a time.I think that would work, and would be simple, although it wouldn't scale too
well.
It probably would also be very prone to deadlocks.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 01:39 PM, Andres Freund wrote:
On 2013-12-18 13:07:51 +0200, Heikki Linnakangas wrote:
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot. Now, there's a race condition, if another transaction is
committing at the same time, and performs the same check concurrently. That
race condition can be eliminated by holding an exclusive lock while running
the assertion, until commit, effectively allowing the assertion to be
checked by only one transaction at a time.I think that would work, and would be simple, although it wouldn't scale too
well.It probably would also be very prone to deadlocks.
Hmm, since this would happen at commit, you would know all the
assertions that need to be checked at that point. You could check them
e.g in Oid order to avoid deadlocks.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-12-18 13:46:59 +0200, Heikki Linnakangas wrote:
On 12/18/2013 01:39 PM, Andres Freund wrote:
On 2013-12-18 13:07:51 +0200, Heikki Linnakangas wrote:
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot.
I think that would work, and would be simple, although it wouldn't scale too
well.It probably would also be very prone to deadlocks.
Hmm, since this would happen at commit, you would know all the assertions
that need to be checked at that point. You could check them e.g in Oid order
to avoid deadlocks.
I think real problem are the lock upgrades, because eventual DML will
have acquired less heavy locks.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 01:50 PM, Andres Freund wrote:
On 2013-12-18 13:46:59 +0200, Heikki Linnakangas wrote:
On 12/18/2013 01:39 PM, Andres Freund wrote:
On 2013-12-18 13:07:51 +0200, Heikki Linnakangas wrote:
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot.
I think that would work, and would be simple, although it wouldn't scale too
well.It probably would also be very prone to deadlocks.
Hmm, since this would happen at commit, you would know all the assertions
that need to be checked at that point. You could check them e.g in Oid order
to avoid deadlocks.I think real problem are the lock upgrades, because eventual DML will
have acquired less heavy locks.
Ah, I see. You don't need to block anyone else from modifying the table,
you just need to block anyone else from committing a transaction that
had modified the table. So the locks shouldn't interfere with regular
table locks. A ShareUpdateExclusiveLock on the assertion should do it.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 06:00 AM, Heikki Linnakangas wrote:
If you don't force everything to run in SSI mode, then you have to
somehow figure out what parts do need to run in SSI mode to enforce
the assertion. For example, if the assertion refers tables A and B,
perhaps you can get away without predicate locks on table C?
But the assertion might simply run a function. For non-trivial cases
that's what I would expect people to do.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
On 12/18/2013 01:39 PM, Andres Freund wrote:
On 2013-12-18 13:07:51 +0200, Heikki Linnakangas wrote:
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot. Now, there's a race condition, if another transaction is
committing at the same time, and performs the same check concurrently. That
race condition can be eliminated by holding an exclusive lock while running
the assertion, until commit, effectively allowing the assertion to be
checked by only one transaction at a time.I think that would work, and would be simple, although it wouldn't scale too
well.It probably would also be very prone to deadlocks.
Hmm, since this would happen at commit, you would know all the
assertions that need to be checked at that point. You could check them
e.g in Oid order to avoid deadlocks.
So you would actually serialize all COMMITs, or at least all which
had done anything which could affect the truth of an assertion?
That seems like it would work, but I suspect that it would be worse
for performance than SSI in many workloads, and better than SSI in
other workloads. Maybe a GUC to determine which strategy is used?
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Josh Berkus <josh@agliodbs.com> wrote:
Serializable or not, *what* do we lock for assertions? It's not
rows. Tables? Which tables? What if the assertion is an
interpreted language function? Does the SSI reference counter
really take care of all of this?
The simple answer is that, without adding any additional blocking,
SSI guarantees that the behavior of running any set of concurrent
serializable transactions is consistent with some serial
(one-at-a-time) execution of those transactions. If the assertion
is run as part of the transaction, it is automatically handled,
regardless of the issues you are asking about.
The longer answer is in the README and the papers it references:
For practical examples of how it works, this Wiki page includes
examples of maintaining a multi-table invariant using triggers:
http://wiki.postgresql.org/wiki/SSI
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Heikki Linnakangas wrote:
Ah, I see. You don't need to block anyone else from modifying the
table, you just need to block anyone else from committing a
transaction that had modified the table. So the locks shouldn't
interfere with regular table locks. A ShareUpdateExclusiveLock on
the assertion should do it.
Causing serialization of transaction commit just because a single
assertion exists in the database seems too much of a hit, so looking for
optimization opportunities seems appropriate. Here are some ideas for
brainstorming.
It might prove useful to note that any given assertion involves tables
A, B, C. If a transaction doesn't modify any of those tables then
there's no need to run the assertion when the transaction commits,
skipping acquisition of the assertion lock.
Probably this can only be implemented for SQL-language assertions, but
even so it might be worth considering. (Assertions in any other
language would be checked by every transaction.)
Another thought: at the initial run of the assertion, note which tables
it locked, and record this as an OID array in the catalog row for the
assertion; consider running the assertion only when those tables are
touched. This doesn't work if the assertion code locks some tables when
run under certain conditions and other tables under different
conditions. But then this can be checked too: if an assertion lists in
its catalog row that it involves tables A, B, C and then, under
different conditions, it tries to acquire lock on table D, have the
whole thing fail indicating that the assertion is misdeclared.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 08:44 AM, Alvaro Herrera wrote:
Another thought: at the initial run of the assertion, note which tables
it locked, and record this as an OID array in the catalog row for the
assertion; consider running the assertion only when those tables are
touched. This doesn't work if the assertion code locks some tables when
run under certain conditions and other tables under different
conditions. But then this can be checked too: if an assertion lists in
its catalog row that it involves tables A, B, C and then, under
different conditions, it tries to acquire lock on table D, have the
whole thing fail indicating that the assertion is misdeclared.
This sounds like you're re-inventing SSI.
SERIALIZABLE mode *exists* in order to be able to enforce constraints
which potentially involve more than one transaction. "Balance can never
go below 0", for example. The whole reason we have this really cool and
unique SSI mode is so that we can do such things without killing
performance. These sorts of requirements are ideally suited to
Assertions, so it's logically consistent to require Serializable mode in
order to use Assertions.
I'm leaning towards the alternative that Assertions require SERIALIZABLE
mode, and throw a WARNING at the user and the log every time we create,
modify, or trigger an assertion while not in SERIALIZABLE mode. And
beyond, that, we don't guarantee the integrity of Assertions if people
choose to run in READ COMMITTED anyway.
This is consistent with how we treat the interaction of constraints and
triggers; under some circumstances, we allow triggers to violate CHECK
and FK constraints.
Alternately, we add a GUC assertion_serializable_mode, which can be
"off", "warn" or "error". If it's set to "error", and the user triggers
an assertion while in READ COMMITTED mode, an exception occurs. If it's
set to "off", then assertions are disabled, in order to deal with buggy
assertions.
Now, it would be even better if we could prevent users from switching
transaction mode, but that's a MUCH bigger and more complicated patch.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM5f1a368931b3d10738968467b86bcf0ad93811e83c6df6c214524dacbce3eb67526326650b0a9a4d6f419bd374a42169@asav-3.01.comReference msg id not found: WMb7c0cf79448024412fa00bbb8b5bbd90112f0ebe39306dc19eb845f4ccd83b005a9dfeb8ce9f58a083cd8f6f65b9419a@asav-3.01.com
On 2013-12-18 13:44:15 -0300, Alvaro Herrera wrote:
Heikki Linnakangas wrote:
Ah, I see. You don't need to block anyone else from modifying the
table, you just need to block anyone else from committing a
transaction that had modified the table. So the locks shouldn't
interfere with regular table locks. A ShareUpdateExclusiveLock on
the assertion should do it.Causing serialization of transaction commit just because a single
assertion exists in the database seems too much of a hit, so looking for
optimization opportunities seems appropriate.
It would only force serialization for transactions that modify tables
covered by the assert, that doesn't seem to bad. Anything covered by an
assert shoulnd't be modified frequently, otherwise you'll run into major
performance problems.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/13, 11:57 AM, Josh Berkus wrote:
On 12/18/2013 08:44 AM, Alvaro Herrera wrote:
Another thought: at the initial run of the assertion, note which tables
it locked, and record this as an OID array in the catalog row for the
assertion; consider running the assertion only when those tables are
touched. This doesn't work if the assertion code locks some tables when
run under certain conditions and other tables under different
conditions. But then this can be checked too: if an assertion lists in
its catalog row that it involves tables A, B, C and then, under
different conditions, it tries to acquire lock on table D, have the
whole thing fail indicating that the assertion is misdeclared.This sounds like you're re-inventing SSI.
SERIALIZABLE mode *exists* in order to be able to enforce constraints
which potentially involve more than one transaction. "Balance can never
go below 0", for example. The whole reason we have this really cool and
unique SSI mode is so that we can do such things without killing
performance. These sorts of requirements are ideally suited to
Assertions, so it's logically consistent to require Serializable mode in
order to use Assertions.
The flip-side is that now you can get serialization failures, and I think there's a ton of software that has no clue how to deal with that. So now you don't get to use assertions at all unless you re-engineer your application (but see below).
Now, if Postgres could re-attempt serialization failures, maybe that would become a non-issue... (though, there's probably a lot of dangers in doing that...)
This is consistent with how we treat the interaction of constraints and
triggers; under some circumstances, we allow triggers to violate CHECK
and FK constraints.
We do? Under what circumstances?
Alternately, we add a GUC assertion_serializable_mode, which can be
"off", "warn" or "error". If it's set to "error", and the user triggers
an assertion while in READ COMMITTED mode, an exception occurs. If it's
set to "off", then assertions are disabled, in order to deal with buggy
assertions.Now, it would be even better if we could prevent users from switching
transaction mode, but that's a MUCH bigger and more complicated patch.
Another possibility is to allow for two different types of assertions, one based on SSI and one based on locking.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/13, 10:44 AM, Alvaro Herrera wrote:
It might prove useful to note that any given assertion involves tables
A, B, C. If a transaction doesn't modify any of those tables then
there's no need to run the assertion when the transaction commits,
skipping acquisition of the assertion lock.Probably this can only be implemented for SQL-language assertions, but
even so it might be worth considering. (Assertions in any other
language would be checked by every transaction.)
This is another case where it would be very useful to restrict what relations a transaction (or in this case, a substransaction) can access. If we had the ability to make that restriction then we could force assertions that aren't plain SQL to explicitly specify what tables the assert is going to hit, and if the assert tries to do something different then we throw an error.
The ability to restrict object access within a transaction would also benefit VACUUM and possibly the Changeset stuff. From /messages/by-id/52ACAAFD.6060509@nasby.net:
"This would be useful when you have some tables that see very frequent updates/deletes in a database that also has to support long-running transactions that don't hit those tables. You'd explicitly limit the tables your long-running transaction will touch and that way vacuum can ignore the long-running XID when calculating minimum tuple age for the heavy-hit tables."
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres Freund wrote:
On 2013-12-18 13:44:15 -0300, Alvaro Herrera wrote:
Heikki Linnakangas wrote:
Ah, I see. You don't need to block anyone else from modifying the
table, you just need to block anyone else from committing a
transaction that had modified the table. So the locks shouldn't
interfere with regular table locks. A ShareUpdateExclusiveLock on
the assertion should do it.Causing serialization of transaction commit just because a single
assertion exists in the database seems too much of a hit, so looking for
optimization opportunities seems appropriate.It would only force serialization for transactions that modify tables
covered by the assert, that doesn't seem to bad. Anything covered by an
assert shoulnd't be modified frequently, otherwise you'll run into major
performance problems.
Well, as presented there is no way (for the system) to tell which tables
are covered by an assertion, is there? That's my point.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby <jim@nasby.net> wrote:
This is another case where it would be very useful to restrict
what relations a transaction (or in this case, a substransaction)
can access. If we had the ability to make that restriction then
we could force assertions that aren't plain SQL to explicitly
specify what tables the assert is going to hit, and if the assert
tries to do something different then we throw an error.The ability to restrict object access within a transaction would
also benefit VACUUM and possibly the Changeset stuff.
I'm pretty sure that SSI could also optimize based on that,
although there are probably about 10 other optimizations that would
be bigger gains before getting to that.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-12-18 16:39:58 -0300, Alvaro Herrera wrote:
Andres Freund wrote:
It would only force serialization for transactions that modify tables
covered by the assert, that doesn't seem to bad. Anything covered by an
assert shoulnd't be modified frequently, otherwise you'll run into major
performance problems.Well, as presented there is no way (for the system) to tell which tables
are covered by an assertion, is there? That's my point.
Well, the patch's syntax seems to only allow to directly specify a SQL
query to check - we could iterate over the querytree to gather all
related tables and reject any function we do not understand.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/13, 1:42 PM, Kevin Grittner wrote:
Jim Nasby <jim@nasby.net> wrote:
This is another case where it would be very useful to restrict
what relations a transaction (or in this case, a substransaction)
can access. If we had the ability to make that restriction then
we could force assertions that aren't plain SQL to explicitly
specify what tables the assert is going to hit, and if the assert
tries to do something different then we throw an error.The ability to restrict object access within a transaction would
also benefit VACUUM and possibly the Changeset stuff.I'm pretty sure that SSI could also optimize based on that,
although there are probably about 10 other optimizations that would
be bigger gains before getting to that.
Any ideas how hard this would be? My thought is that we might be able to perform this check in the functions that do catalog lookups, but I'm worried that that wouldn't allow us to support subtransaction checks (which we'd need for assertions), and it runs the risk of long-lasting object references spanning the transaction (or subtransaction) and thereby thwarting the check.
Another option would be in heap accessor functions, but that means we could only restrict access to tables. For assertions, it would be nice to also disallow access to functions that could have unintended consequences that could violate the assertion (like dblink).
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby <jim@nasby.net> wrote:
On 12/18/13, 1:42 PM, Kevin Grittner wrote:
Jim Nasby <jim@nasby.net> wrote:
This is another case where it would be very useful to restrict
what relations a transaction (or in this case, a substransaction)
can access. If we had the ability to make that restriction then
we could force assertions that aren't plain SQL to explicitly
specify what tables the assert is going to hit, and if the assert
tries to do something different then we throw an error.The ability to restrict object access within a transaction would
also benefit VACUUM and possibly the Changeset stuff.I'm pretty sure that SSI could also optimize based on that,
although there are probably about 10 other optimizations that would
be bigger gains before getting to that.Any ideas how hard this would be?
If we had a list to check against, I think it would be possible to
do this during parse analysis and AcquireRewriteLocks(). (One or
the other happens before query rewrite.) The hard part seems to me
to be defining a sane way to get the list.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 02:45 PM, Andres Freund wrote:
On 2013-12-18 16:39:58 -0300, Alvaro Herrera wrote:
Andres Freund wrote:
It would only force serialization for transactions that modify tables
covered by the assert, that doesn't seem to bad. Anything covered by an
assert shoulnd't be modified frequently, otherwise you'll run into major
performance problems.Well, as presented there is no way (for the system) to tell which tables
are covered by an assertion, is there? That's my point.Well, the patch's syntax seems to only allow to directly specify a SQL
query to check - we could iterate over the querytree to gather all
related tables and reject any function we do not understand.
Umm, that's really a major limitation in utility. We need to come up
with a better answer than this, which would essentially hobble the facility.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 11:04 PM, Andrew Dunstan wrote:
On 12/18/2013 02:45 PM, Andres Freund wrote:
On 2013-12-18 16:39:58 -0300, Alvaro Herrera wrote:
Andres Freund wrote:
It would only force serialization for transactions that modify tables
covered by the assert, that doesn't seem to bad. Anything covered by an
assert shoulnd't be modified frequently, otherwise you'll run into
major
performance problems.Well, as presented there is no way (for the system) to tell which tables
are covered by an assertion, is there? That's my point.Well, the patch's syntax seems to only allow to directly specify a SQL
query to check - we could iterate over the querytree to gather all
related tables and reject any function we do not understand.Umm, that's really a major limitation in utility.
The query can be "SELECT is_my_assertion_true()", and the function can
do anything.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 04:09 PM, Heikki Linnakangas wrote:
On 12/18/2013 11:04 PM, Andrew Dunstan wrote:
On 12/18/2013 02:45 PM, Andres Freund wrote:
On 2013-12-18 16:39:58 -0300, Alvaro Herrera wrote:
Andres Freund wrote:
It would only force serialization for transactions that modify tables
covered by the assert, that doesn't seem to bad. Anything covered
by an
assert shoulnd't be modified frequently, otherwise you'll run into
major
performance problems.Well, as presented there is no way (for the system) to tell which
tables
are covered by an assertion, is there? That's my point.Well, the patch's syntax seems to only allow to directly specify a SQL
query to check - we could iterate over the querytree to gather all
related tables and reject any function we do not understand.Umm, that's really a major limitation in utility.
The query can be "SELECT is_my_assertion_true()", and the function can
do anything.
OK, but isn't that what Andres is suggesting we reject?
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 11:26 AM, Jim Nasby wrote:
The flip-side is that now you can get serialization failures, and I
think there's a ton of software that has no clue how to deal with that.
So now you don't get to use assertions at all unless you re-engineer
your application (but see below).
Well, the software will need to deal with an Assertion failure, which I
doubt it's prepared to do right now either.
This is consistent with how we treat the interaction of constraints and
triggers; under some circumstances, we allow triggers to violate CHECK
and FK constraints.We do? Under what circumstances?
AFTER triggers are allowed to ignore constraints sometimes. For
example, if you have a tree table with an FK to other rows in the same
table, and you have an AFTER trigger on it, the AFTER trigger is allowed
to violate the self-FK. That's the one I ran across, but I vaguely
remember other cases, and there's some documentation on this in the
order of application of triggers in the main docs.
Another possibility is to allow for two different types of assertions,
one based on SSI and one based on locking.
The locking version would have to pretty much lock on a table basis (or
even a whole-database basis) every time an assertion executed, no?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM0e0c94bb11c2324987aff6af97d85eb4221f7ed49cbcda6ddeca6ee8ff149cfdee356470753a1919d4b7b847609d79a5@asav-3.01.comReference msg id not found: WMb7c0cf79448024412fa00bbb8b5bbd90112f0ebe39306dc19eb845f4ccd83b005a9dfeb8ce9f58a083cd8f6f65b9419a@asav-3.01.com
Josh Berkus <josh@agliodbs.com> wrote:
On 12/18/2013 11:26 AM, Jim Nasby wrote:
Another possibility is to allow for two different types of
assertions, one based on SSI and one based on locking.The locking version would have to pretty much lock on a table
basis (or even a whole-database basis) every time an assertion
executed, no?
As far as I can see, if SSI is *not* used, there needs to be a
mutually exclusive lock taken from somewhere inside the COMMIT code
until the transaction is complete -- effectively serializing
assertion processing for transactions which could affect a given
assertion. Locking on tables would, as previously suggested, be
very prone to deadlocks on the heavyweight locks. Locking on the
assertions in a predictable order seems more promising, especially
if there could be some way to only do that if the transaction
really might have done something which could affect the truth of
the assertion.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Dec18, 2013, at 20:39 , Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Andres Freund wrote:
On 2013-12-18 13:44:15 -0300, Alvaro Herrera wrote:
Heikki Linnakangas wrote:
Ah, I see. You don't need to block anyone else from modifying the
table, you just need to block anyone else from committing a
transaction that had modified the table. So the locks shouldn't
interfere with regular table locks. A ShareUpdateExclusiveLock on
the assertion should do it.Causing serialization of transaction commit just because a single
assertion exists in the database seems too much of a hit, so looking for
optimization opportunities seems appropriate.It would only force serialization for transactions that modify tables
covered by the assert, that doesn't seem to bad. Anything covered by an
assert shoulnd't be modified frequently, otherwise you'll run into major
performance problems.Well, as presented there is no way (for the system) to tell which tables
are covered by an assertion, is there? That's my point.
Well, we *do* know that after executing the assertion, since we know (or
at least can track) which tables the assertion touches. I wonder if we
couldn't lazily enable SERIALIZED semantics for those tables only, and do
so while we evaluate the assertion.
So, before evaluating the assertion, we would change the isolation level to
SERIALIZABLE. We'd then have to make sure that we detect any conflicts which
we would have detected had the isolation level been SERIALIZABLE all along
*and* which somehow involve the assertion. Simply changing the isolation
level should suffice to detect cases where we read data modified by
concurrent transactions. To also detect cases where we write data read by
concurrent transactions, we'd have to watch for tuples which were modified
by our own transaction. For these tuples, we'd have to verify do what we would
have done had we already been in SERIALIZABLE mode when the modification
occurred. That means checking for SIREAD locks taken by other transactions,
on the tuple and all relevant index pages (plus all corresponding
coarser-grained entities like the tuples's page, the table, …).
best regards,
Florian Pflug
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Nov 25, 2013 at 12:10:22PM -0800, Kevin Grittner wrote:
David Fetter <david@fetter.org> wrote:
On Mon, Nov 25, 2013 at 11:04:23AM -0800, Kevin Grittner wrote:
As soon as we are out of this CF, I am planning to write code to
capture deltas and fire functions to process them "eagerly"
(within the creating transaction).� There has been suggestions
that the changeset mechanism should be used for that, which I
will look into; but my gut feel is that it will be better to
build a tuplestore of tids flagged with "old" or "new" around
the point that "after triggers" fire.� How close does that sound
to what CREATE ASSERTION (as currently envisioned) would need?It sounds *extremely* close to what we'd need for row access in
per-statement triggers, as in probably identical.� The SQL syntax
of this sub-feature is described in Foundation section 11.49 and
called REFERENCING in CREATE TRIGGER.Do you have any prototypes I could use for that purpose?
No, but it is at the top of my list after the CF.� I will also need
an execution node type or two to produce the referenced rows for
the appropriate contexts, which is probably also very close to what
you need for per-statement triggers.� I will be happy to coordinate
work with you.
Now that the CF in question is over, how can I help?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/13, 2:22 PM, Andres Freund wrote:
It would only force serialization for transactions that modify tables
covered by the assert, that doesn't seem to bad. Anything covered by an
assert shoulnd't be modified frequently, otherwise you'll run into major
performance problems.
I think that makes sense. If you want to use assertions, you need to
run in serializable mode, otherwise you get an error if you modify
anything covered by an assertion.
In the future, someone could enhance this for other isolation levels,
but as Josh has pointed out, that would likely just be reimplementing
SSI with big locks.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/13, 2:45 PM, Andres Freund wrote:
Well, the patch's syntax seems to only allow to directly specify a SQL
query to check - we could iterate over the querytree to gather all
related tables and reject any function we do not understand.
Creating an assertion scans the query tree and attaches constraint
triggers to all tables covered by the assertion condition. So it does
essentially work the way you say.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/13, 4:04 PM, Andrew Dunstan wrote:
Umm, that's really a major limitation in utility. We need to come up
with a better answer than this, which would essentially hobble the
facility.
We don't have any facility to run a trigger on just any command, it
needs to be triggered by a table. Of course, that's where your on
commit even triggers come in, presumably. But for example, constraint
triggers support deferrability, which an on commit trigger wouldn't.
We'd just need DML triggers on any/no tables.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Peter Eisentraut <peter_e@gmx.net> schrieb:
On 12/18/13, 2:22 PM, Andres Freund wrote:
It would only force serialization for transactions that modify tables
covered by the assert, that doesn't seem to bad. Anything covered byan
assert shoulnd't be modified frequently, otherwise you'll run into
major
performance problems.
I think that makes sense. If you want to use assertions, you need to
run in serializable mode, otherwise you get an error if you modify
anything covered by an assertion.In the future, someone could enhance this for other isolation levels,
but as Josh has pointed out, that would likely just be reimplementing
SSI with big locks.
SSI only actually works correctly if all transactions use SSI... I am not sure if we can guarantee that the subset we'd require'd be safe without the read sie using SSI.
Andres
--
Please excuse brevity and formatting - I am writing this on my mobile phone.
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres Freund <andres@2ndquadrant.com> wrote:
Peter Eisentraut <peter_e@gmx.net> schrieb:
On 12/18/13, 2:22 PM, Andres Freund wrote:
It would only force serialization for transactions that modify
tables covered by the assert, that doesn't seem to bad.
Anything covered by an assert shoulnd't be modified frequently,
otherwise you'll run into major performance problems.I think that makes sense. If you want to use assertions, you
need to run in serializable mode, otherwise you get an error if
you modify anything covered by an assertion.In the future, someone could enhance this for other isolation
levels, but as Josh has pointed out, that would likely just be
reimplementing SSI with big locks.SSI only actually works correctly if all transactions use SSI...
I am not sure if we can guarantee that the subset we'd require'd
be safe without the read sie using SSI.
You could definitely see a state which would not be consistent with
getting to some later state under procedural business rules;
however, I don't think any connection could ever see a state which
violated the constraint as of the moment it was viewed.
For examples of essentially enforcing multi-table constraints using
triggers and SSI see this section:
http://wiki.postgresql.org/wiki/SSI#Enforcing_Business_Rules_in_Triggers
For an example of how things can look OK in terms of enforced
constraints as of different moments in time, yet those moments in
time could be inconsistent, see this section:
http://wiki.postgresql.org/wiki/SSI#Enforcing_Business_Rules_in_Triggers
SSI gives you a guarantee that with any set of concurrently running
transactions, the effect is the same as some serial (one-at-a-time)
execution of those transactions; but it says little about the mix
of serializable and non-serializable transactions. Non-serializable
transactions will, after the last of those serializable
transactions has committed or rolled back, see a state which is
consistent with some serial execution of those serializable
transactions which committed, but it will not necessarily be
consistent with them having run in any *particular* order. NOTE:
the state might be consistent with some order other than commit
order. This means that a non-serializable transaction running in
the midst of those serializable transaction commits might see the
work of some transaction which will appear to all serializable
transactions as having been run *later* while not yet seeing the
work of a transaction which will appear to all serializable
transactions to have run *earlier*.
I'm pretty sure that this means that an invariant, if it is an
expression which must always hold for any view of the database, can
be enforced by requiring modifying transactions to be serializable.
What it doesn't guarantee is that business rules about
*transitions* can be enforced without requiring all *transactions*
to be serializable. In the Deposit Report example, note that a
non-serializable transaction would never be able to see a receipt
with a deposit number that was not open; but it *would* be able to
see a closed batch header with a set of receipts which was not yet
complete.
So I think the answer is that the suggested approach is sufficient
for enforcing assertions about "static" database state. If you
want to make sure that nobody sees a state for which a given
expression is false, it is sufficient. Just don't overestimate
what that means. You can't ensure that a non-serializable
transaction won't see a state which is inconsistent with a later
database state according to *procedural* business rules.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Kevin Grittner <kgrittn@ymail.com> wrote:
For an example of how things can look OK in terms of enforced
constraints as of different moments in time, yet those moments in
time could be inconsistent, see this section:http://wiki.postgresql.org/wiki/SSI#Enforcing_Business_Rules_in_Triggers
Copy/paste error. I meant this link:
http://wiki.postgresql.org/wiki/SSI#Read_Only_Transactions
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers