assertions and constraint triggers

Thinking about SQL assertions (check constraints that are independent of
one particular table), do you think it would be reasonable to implement
those on top of constraint triggers? On creation you'd hook up a
trigger to each of the affected tables. And the trigger function runs
the respective check expression. Conceptually, this doesn't seem to be
very far away from foreign key constraints after all.

On 8/11/10 8:31 AM +0300, Peter Eisentraut wrote:
> Thinking about SQL assertions (check constraints that are independent of
> one particular table), do you think it would be reasonable to implement
> those on top of constraint triggers? On creation you'd hook up a
> trigger to each of the affected tables. And the trigger function runs
> the respective check expression. Conceptually, this doesn't seem to be
> very far away from foreign key constraints after all.

I thought the point of ASSERTIONs was that you could write a thing such as:

CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4);

Enforcing that kind of constraints without true serializability seems
impractical.

Regards,
Marko Tiikkaja

On Wed, 2010-08-11 at 08:31 +0300, Peter Eisentraut wrote:

> Thinking about SQL assertions (check constraints that are independent of
> one particular table), do you think it would be reasonable to implement
> those on top of constraint triggers? On creation you'd hook up a
> trigger to each of the affected tables. And the trigger function runs
> the respective check expression. Conceptually, this doesn't seem to be
> very far away from foreign key constraints after all.

I would be interested in virtual assertions, i.e. allowing the user to
say it is true without it being enforced.

The cost of executing enforced assertions is likely to be prohibitive.

Most common use case if you do have them is the equivalent of
minoccurs/maxoccurs constraints in an XML Schema definition (XSD).

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services

2010/8/11 Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>:

> On 8/11/10 8:31 AM +0300, Peter Eisentraut wrote:
>
>> Thinking about SQL assertions (check constraints that are independent of
>> one particular table), do you think it would be reasonable to implement
>> those on top of constraint triggers?  On creation you'd hook up a
>> trigger to each of the affected tables.  And the trigger function runs
>> the respective check expression.  Conceptually, this doesn't seem to be
>> very far away from foreign key constraints after all.
>
> I thought the point of ASSERTIONs was that you could write a thing such as:
>
> CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4);
>
> Enforcing that kind of constraints without true serializability seems
> impractical.

Exactly what I thought when I read this. Without true serializability,
the view of the database at any moment during a transaction doesn't
have to be the same as the view that a newly started transaction gets.
Therefore, checking that the assertion holds after changing something
doesn't necessarily guarantee that it will hold for any other
transactions.

To elaborate on a variant of Marko's example, where the "=" is
replaced with "<=". Assume "non-true SERIALIZABLE" transactions:

* The table has 3 rows.
* T1 inserts a row, and concurrently, T2 also inserts a row; after
each statement, the assertion is not violated for the corresponding
transaction's snapshot.
* The assertion is now violated for a subsequent transaction T3
(because it sees 5 rows).

Nicolas

On ons, 2010-08-11 at 10:54 +0300, Marko Tiikkaja wrote:
> Enforcing that kind of constraints without true serializability seems
> impractical.

Yes, but that is being worked on, I understand.

On 8/11/10 1:18 PM +0300, Peter Eisentraut wrote:
> On ons, 2010-08-11 at 10:54 +0300, Marko Tiikkaja wrote:
>> Enforcing that kind of constraints without true serializability seems
>> impractical.
>
> Yes, but that is being worked on, I understand.

Correct. But you'd have to somehow make the constraints to be checked
with true serializability, and that part of the original suggestion
seemed to be completely missing. Not sure how hard that would be though.

Regards,
Marko Tiikkaja

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> On 8/11/10 1:18 PM +0300, Peter Eisentraut wrote:
>> On ons, 2010-08-11 at 10:54 +0300, Marko Tiikkaja wrote:
>>> Enforcing that kind of constraints without true serializability
>>> seems impractical.
>>
>> Yes, but that is being worked on, I understand.
>
> Correct. But you'd have to somehow make the constraints to be
> checked with true serializability, and that part of the original
> suggestion seemed to be completely missing. Not sure how hard
> that would be though.

I keep bumping into use cases where cool things could be done if you
could be sure that *all* transactions were being run at the fully
serializable transaction isolation level. Perhaps we could look at
a GUC (or initdb option, if people fear the consequences of changes
in an existing database) which not only defaults to serializable,
but silently ignores requests for other levels. If we only allowed
these constraints to be used in a database which was configured this
way, they would work fine.

Enforcing *part* of a transaction under full serializable isolation
seems totally infeasible, unless someone has a clever idea I'm
missing.

-Kevin

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> On 8/11/10 8:31 AM +0300, Peter Eisentraut wrote:
>> Thinking about SQL assertions (check constraints that are independent of
>> one particular table), do you think it would be reasonable to implement
>> those on top of constraint triggers? On creation you'd hook up a
>> trigger to each of the affected tables. And the trigger function runs
>> the respective check expression. Conceptually, this doesn't seem to be
>> very far away from foreign key constraints after all.

> I thought the point of ASSERTIONs was that you could write a thing such as:
> CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4);
> Enforcing that kind of constraints without true serializability seems
> impractical.

Enforcing that kind of constraint seems impractical with or without
serializability. You need some optimization method that avoids the need
to do full-table scans after every update, or it's not going to be
useful for any real-world situation. Without a scheme that can do
incremental checking for some useful class of assertion expressions,
this isn't going to go far.

regards, tom lane

On ons, 2010-08-11 at 13:23 +0300, Marko Tiikkaja wrote:
> But you'd have to somehow make the constraints to be checked
> with true serializability, and that part of the original suggestion
> seemed to be completely missing. Not sure how hard that would be
> though.

I don't think somehow running the constraint checks at a different
transaction isolation level than the rest of the transaction is
sensible. I imagine the solution would look similar to how foreign keys
do it: take a lock on the rows that are required for constraint
satisfaction. For general assertions, this would require predicate
locking. But also notice that for the (SELECT count(*) FROM tbl) = N
case, this is the same as a table lock. I don't think there is any
magic around it.

On ons, 2010-08-11 at 10:47 -0400, Tom Lane wrote:
> > I thought the point of ASSERTIONs was that you could write a thing
> such as:
> > CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4);
> > Enforcing that kind of constraints without true serializability
> seems
> > impractical.
>
> Enforcing that kind of constraint seems impractical with or without
> serializability. You need some optimization method that avoids the
> need
> to do full-table scans after every update, or it's not going to be
> useful for any real-world situation. Without a scheme that can do
> incremental checking for some useful class of assertion expressions,
> this isn't going to go far.

I'm not sure how great a use case there is for an assertion of the kind
"this table must contain at least 30 million rows". But I think there
are many uses cases for checks like that on small and rarely changing
tables.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Thinking about SQL assertions (check constraints that are independent of
> one particular table), do you think it would be reasonable to implement
> those on top of constraint triggers? On creation you'd hook up a
> trigger to each of the affected tables. And the trigger function runs
> the respective check expression. Conceptually, this doesn't seem to be
> very far away from foreign key constraints after all.

On further reflection it seems like the major implementation problem
would be to identify "the affected tables". What if the assertion
references views, or user-defined functions that contain queries?
Even an assertion on a table with inheritance children would be a
bit problematic. In principle you could handle the latter case by
propagating copies of the trigger to all the children, but we have
no mechanism for that now.

regards, tom lane