2010/8/11 Marko Tiikkaja <marko.tiikk...@cs.helsinki.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
