Thread: 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.comPostgreSQL Development, 24x7 Support, Training and Services
2010/8/11 Marko Tiikkaja <marko.tiikkaja@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
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.tiikkaja@cs.helsinki.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.tiikkaja@cs.helsinki.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@gmx.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