[PATCH] SQL assertions prototype
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.
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).
This is not a performance feature. It's for things like, this table
should have at most 10 rows, or all the values in this table must be
bigger than all the values in that other table. It's a bit esoteric,
but it comes up again and again.
Let me know what you think.
Attachments:
0001-SQL-assertions-prototype.patchtext/x-patch; charset=UTF-8; name=0001-SQL-assertions-prototype.patchDownload+1303-42
+1
interesting feature
Pavel
2013/11/15 Peter Eisentraut <peter_e@gmx.net>
Show quoted text
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.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).This is not a performance feature. It's for things like, this table
should have at most 10 rows, or all the values in this table must be
bigger than all the values in that other table. It's a bit esoteric,
but it comes up again and again.Let me know what you think.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 15 November 2013 03:30, Peter Eisentraut <peter_e@gmx.net> wrote:
A constraint trigger performs the actual checking.
Good, that is consistent with other constraints.
This is not a performance feature. It's for things like, this table
should have at most 10 rows, or all the values in this table must be
bigger than all the values in that other table. It's a bit esoteric,
but it comes up again and again.
While I accept it may never perform well, it needs to perform reasonably well.
The key use cases for this are
* enforcing "one and only one" relationships
* enforcing quantified relationships like we do in XML: minoccurs and maxoccurs
* enforcing only one sub-type across multiple sub-type tables
etc
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the table.
That last point will make it unusable for sensible amounts of data.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Nov 24, 2013 at 11:03 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the table.
That last point will make it unusable for sensible amounts of data.
That sounds very similar to handling incremental maintenance of
materialized views, which Kevin is working on.
Let's assume that the "huge SQL command that re-checks every row of
the table" is actually a materialized view. In that case, the CREATE
ASSERTION trigger would merely need to scan the matview and raise an
error if any rows were present. That should be a very quick
operation. No need to invent some sort of "get access to the changed
rows" mechanism especially for CREATE ASSERTION.
Kind regards,
Andrew Tipton
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andrew Tipton <andrew@kiwidrew.com> wrote:
Simon Riggs <simon@2ndquadrant.com> wrote:
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the
table. That last point will make it unusable for sensible
amounts of data.That sounds very similar to handling incremental maintenance of
materialized views, which Kevin is working on.
It does.
Let's assume that the "huge SQL command that re-checks every row
of the table" is actually a materialized view. In that case, the
CREATE ASSERTION trigger would merely need to scan the matview
and raise an error if any rows were present. That should be a
very quick operation.
That would certainly be a viable way to implement this once we have
incremental maintenance for materialized views, although I make no
claims to having evaluated it versus the alternatives to be able to
assert what the *best* way is.
No need to invent some sort of "get access to the changed
rows" mechanism especially for CREATE ASSERTION.
As soon as we are out of this CF, I am planning to write code to
capture deltas and fire functions to process them "eagerly" (within
the creating transaction). There has been suggestions that the
changeset mechanism should be used for that, which I will look
into; but my gut feel is that it will be better to build a
tuplestore of tids flagged with "old" or "new" around the point
that "after triggers" fire. How close does that sound to what
CREATE ASSERTION (as currently envisioned) would need? How viable
does it sound to turn an assertion expression into a matview which
is empty if there are no violations?
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Nov 25, 2013 at 11:04:23AM -0800, Kevin Grittner wrote:
Andrew Tipton <andrew@kiwidrew.com> wrote:
Simon Riggs <simon@2ndquadrant.com> wrote:
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the
table.� That last point will make it unusable for sensible
amounts of data.That sounds very similar to handling incremental maintenance of
materialized views, which Kevin is working on.It does.
Let's assume that the "huge SQL command that re-checks every row
of the table" is actually a materialized view.� In that case, the
CREATE ASSERTION trigger would merely need to scan the matview
and raise an error if any rows were present.� That should be a
very quick operation.That would certainly be a viable way to implement this once we have
incremental maintenance for materialized views, although I make no
claims to having evaluated it versus the alternatives to be able to
assert what the *best* way is.No need to invent some sort of "get access to the changed
rows" mechanism especially for CREATE ASSERTION.As soon as we are out of this CF, I am planning to write code to
capture deltas and fire functions to process them "eagerly" (within
the creating transaction).� There has been suggestions that the
changeset mechanism should be used for that, which I will look
into; but my gut feel is that it will be better to build a
tuplestore of tids flagged with "old" or "new" around the point
that "after triggers" fire.� How close does that sound to what
CREATE ASSERTION (as currently envisioned) would need?
It sounds *extremely* close to what we'd need for row access in
per-statement triggers, as in probably identical. The SQL syntax of
this sub-feature is described in Foundation section 11.49 and called
REFERENCING in CREATE TRIGGER.
Do you have any prototypes I could use for that purpose?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
David Fetter <david@fetter.org> wrote:
On Mon, Nov 25, 2013 at 11:04:23AM -0800, Kevin Grittner wrote:
As soon as we are out of this CF, I am planning to write code to
capture deltas and fire functions to process them "eagerly"
(within the creating transaction). There has been suggestions
that the changeset mechanism should be used for that, which I
will look into; but my gut feel is that it will be better to
build a tuplestore of tids flagged with "old" or "new" around
the point that "after triggers" fire. How close does that sound
to what CREATE ASSERTION (as currently envisioned) would need?It sounds *extremely* close to what we'd need for row access in
per-statement triggers, as in probably identical. The SQL syntax
of this sub-feature is described in Foundation section 11.49 and
called REFERENCING in CREATE TRIGGER.Do you have any prototypes I could use for that purpose?
No, but it is at the top of my list after the CF. I will also need
an execution node type or two to produce the referenced rows for
the appropriate contexts, which is probably also very close to what
you need for per-statement triggers. I will be happy to coordinate
work with you.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/24/13, 10:03 AM, Simon Riggs wrote:
So we'd need to get access to the changed rows, rather than
re-executing a huge SQL command that re-checks every row of the table.
That last point will make it unusable for sensible amounts of data.
SQL assertions work with arbitrary expressions. So even if you had the
changed rows, you couldn't do anything with them in general. For cases
where it makes sense to consider the changed rows, you probably want a
per-table trigger or an exclusion constraint or perhaps an auto-updated
materialized view. Then again, we have lots of ways to make queries
fast even for large tables.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
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.
Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.
Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMb7c0cf79448024412fa00bbb8b5bbd90112f0ebe39306dc19eb845f4ccd83b005a9dfeb8ce9f58a083cd8f6f65b9419a@asav-3.01.com
Josh Berkus <josh@agliodbs.com> wrote:
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
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.Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.
It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.
Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/17/2013 04:42 PM, Kevin Grittner wrote:
Josh Berkus <josh@agliodbs.com> wrote:
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
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.Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.
Maybe the presence of an assertion should be enough to force
serializable, i.e. turn it on and not allow it to be turned off.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 18/12/13 10:48, Andrew Dunstan wrote:
On 12/17/2013 04:42 PM, Kevin Grittner wrote:
Josh Berkus <josh@agliodbs.com> wrote:
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
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.Going back over this patch, I haven't seen any further discussion of
the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.Maybe the presence of an assertion should be enough to force
serializable, i.e. turn it on and not allow it to be turned off.cheers
andrew
Perhaps then it should be called an 'assurance', rather than an 'assertion?'
(Not being entirely facetious!)
Cheers,
Gavin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/17/2013 01:42 PM, Kevin Grittner wrote:
Josh Berkus <josh@agliodbs.com> wrote:
Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.
Serializable or not, *what* do we lock for assertions? It's not rows.
Tables? Which tables? What if the assertion is an interpreted language
function? Does the SSI reference counter really take care of all of this?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMec25e898f96af35050e6491f9956cd01fcb3c7ae5bfbe310723074e2de34ad7d629c9c867f126526930d62576145a615@asav-3.01.com
On 12/18/2013 02:59 AM, Josh Berkus wrote:
On 12/17/2013 01:42 PM, Kevin Grittner wrote:
Josh Berkus <josh@agliodbs.com> wrote:
Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.Serializable or not, *what* do we lock for assertions? It's not rows.
Tables? Which tables? What if the assertion is an interpreted language
function? Does the SSI reference counter really take care of all of this?
SSI does make everything rosy, as long as all the transactions
participate in it. The open questions revolve around what happens if a
transaction is not running in SSI mode.
If you force everyone to run in SSI as soon as you create even a single
assertion in your database, that's kind of crappy for performance. Also,
if one user creates an assertion, it becomes a funny kind of a partial
denial of service attack to other users, if you force other user's to
also run in SSI mode.
If you don't force everything to run in SSI mode, then you have to
somehow figure out what parts do need to run in SSI mode to enforce the
assertion. For example, if the assertion refers tables A and B, perhaps
you can get away without predicate locks on table C?
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 02:59 AM, Josh Berkus wrote:
On 12/17/2013 01:42 PM, Kevin Grittner wrote:
It works fine as long as you set default_transaction_isolation =
'serializable' and never override that. :-) Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.Serializable or not, *what* do we lock for assertions? It's not rows.
Tables? Which tables? What if the assertion is an interpreted language
function? Does the SSI reference counter really take care of all of this?
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot. Now, there's a race condition, if another
transaction is committing at the same time, and performs the same check
concurrently. That race condition can be eliminated by holding an
exclusive lock while running the assertion, until commit, effectively
allowing the assertion to be checked by only one transaction at a time.
I think that would work, and would be simple, although it wouldn't scale
too well.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-12-18 13:07:51 +0200, Heikki Linnakangas wrote:
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot. Now, there's a race condition, if another transaction is
committing at the same time, and performs the same check concurrently. That
race condition can be eliminated by holding an exclusive lock while running
the assertion, until commit, effectively allowing the assertion to be
checked by only one transaction at a time.I think that would work, and would be simple, although it wouldn't scale too
well.
It probably would also be very prone to deadlocks.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 01:39 PM, Andres Freund wrote:
On 2013-12-18 13:07:51 +0200, Heikki Linnakangas wrote:
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot. Now, there's a race condition, if another transaction is
committing at the same time, and performs the same check concurrently. That
race condition can be eliminated by holding an exclusive lock while running
the assertion, until commit, effectively allowing the assertion to be
checked by only one transaction at a time.I think that would work, and would be simple, although it wouldn't scale too
well.It probably would also be very prone to deadlocks.
Hmm, since this would happen at commit, you would know all the
assertions that need to be checked at that point. You could check them
e.g in Oid order to avoid deadlocks.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-12-18 13:46:59 +0200, Heikki Linnakangas wrote:
On 12/18/2013 01:39 PM, Andres Freund wrote:
On 2013-12-18 13:07:51 +0200, Heikki Linnakangas wrote:
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot.
I think that would work, and would be simple, although it wouldn't scale too
well.It probably would also be very prone to deadlocks.
Hmm, since this would happen at commit, you would know all the assertions
that need to be checked at that point. You could check them e.g in Oid order
to avoid deadlocks.
I think real problem are the lock upgrades, because eventual DML will
have acquired less heavy locks.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 12/18/2013 01:50 PM, Andres Freund wrote:
On 2013-12-18 13:46:59 +0200, Heikki Linnakangas wrote:
On 12/18/2013 01:39 PM, Andres Freund wrote:
On 2013-12-18 13:07:51 +0200, Heikki Linnakangas wrote:
Here's another idea that doesn't involve SSI:
At COMMIT, take a new snapshot and check that the assertion still passes
with that snapshot.
I think that would work, and would be simple, although it wouldn't scale too
well.It probably would also be very prone to deadlocks.
Hmm, since this would happen at commit, you would know all the assertions
that need to be checked at that point. You could check them e.g in Oid order
to avoid deadlocks.I think real problem are the lock upgrades, because eventual DML will
have acquired less heavy locks.
Ah, I see. You don't need to block anyone else from modifying the table,
you just need to block anyone else from committing a transaction that
had modified the table. So the locks shouldn't interfere with regular
table locks. A ShareUpdateExclusiveLock on the assertion should do it.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
