From: | Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com> |
---|---|
To: | Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> |
Cc: | Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: assertions and constraint triggers |
Date: | 2010-08-11 08:08:59 |
Message-ID: | AANLkTimAdvw9hexOsBB=11QHxWnHJaU2o__9=BxxYa47@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Boxuan Zhai | 2010-08-11 08:11:24 | Re: MERGE Specification |
Previous Message | Simon Riggs | 2010-08-11 08:02:17 | Re: assertions and constraint triggers |