Re: [PATCH] SQL assertions prototype

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