pre-commit triggers
Attached is a patch to provide a new event trigger that will fire on
transaction commit. I have tried to make certain that it fires at a
sufficiently early stage in the commit process that some of the evils
mentioned in previous discussions on this topic aren't relevant.
The triggers don't fire if there is no real XID, so only actual data
changes should cause the trigger to fire. They also don't fire in single
user mode, so that if you do something stupid like create a trigger that
unconditionally raises an error you have a way to recover.
This is intended to be somewhat similar to the same feature in the
Firebird database, and the initial demand came from a client migrating
from that system to Postgres.
cheers
andrew
Attachments:
txn-commit-triggers-1.patchtext/x-patch; name=txn-commit-triggers-1.patchDownload+92-7
On Fri, 2013-11-15 at 13:01 -0500, Andrew Dunstan wrote:
Attached is a patch to provide a new event trigger that will fire on
transaction commit.
xact.c: In function ‘CommitTransaction’:
xact.c:1835:3: warning: implicit declaration of function ‘PreCommitTriggersFire’ [-Wimplicit-function-declaration]
--
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 09:07 PM, Peter Eisentraut wrote:
On Fri, 2013-11-15 at 13:01 -0500, Andrew Dunstan wrote:
Attached is a patch to provide a new event trigger that will fire on
transaction commit.xact.c: In function ‘CommitTransaction’:
xact.c:1835:3: warning: implicit declaration of function ‘PreCommitTriggersFire’ [-Wimplicit-function-declaration]
Oops. missed a #include. Revised patch attached.
cheers
andrew
Attachments:
txn-commit-triggers-2.patchtext/x-patch; name=txn-commit-triggers-2.patchDownload+93-7
On 11/15/2013 07:01 PM, Andrew Dunstan wrote:
Attached is a patch to provide a new event trigger that will fire on
transaction commit. I have tried to make certain that it fires at a
sufficiently early stage in the commit process that some of the evils
mentioned in previous discussions on this topic aren't relevant.The triggers don't fire if there is no real XID, so only actual data
changes should cause the trigger to fire.
I have not looked at the patch, but does it also run pre-rollback ?
If not, how hard would it be to make it so ?
They also don't fire in single user mode, so that if you do something
stupid like create a trigger that unconditionally raises an error you
have a way to recover.This is intended to be somewhat similar to the same feature in the
Firebird database, and the initial demand came from a client migrating
from that system to Postgres.cheers
andrew
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic O�
On 11/16/2013 03:00 PM, Hannu Krosing wrote:
On 11/15/2013 07:01 PM, Andrew Dunstan wrote:
Attached is a patch to provide a new event trigger that will fire on
transaction commit. I have tried to make certain that it fires at a
sufficiently early stage in the commit process that some of the evils
mentioned in previous discussions on this topic aren't relevant.The triggers don't fire if there is no real XID, so only actual data
changes should cause the trigger to fire.I have not looked at the patch, but does it also run pre-rollback ?
If not, how hard would it be to make it so ?
No it doesn't.
The things you can do once a rollback has been initiated are extremely
limited, so I'm not sure value there would be in such a thing.
The requirements I was given specifically excluded this, so I haven't
looked at it, but I suspect the answer to your second question is "quite
hard". But feel free to prove me wrong :-)
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
Hannu Krosing <hannu@2ndQuadrant.com> writes:
I have not looked at the patch, but does it also run pre-rollback ?
error in trigger -> instant infinite loop.
Besides, exactly what would you do in such a trigger? Not modify
the database, for certain, because we're about to roll back.
regards, tom lane
--
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/17/2013 01:42 AM, Tom Lane wrote:
Hannu Krosing <hannu@2ndQuadrant.com> writes:
I have not looked at the patch, but does it also run pre-rollback ?
error in trigger -> instant infinite loop.
Means this needs to have some kind of recursion depth limit, like python
def x():
... return x()
...
x()
... (a few thousand messages like the following) ...
File "<stdin>", line 2, in x
RuntimeError: maximum recursion depth exceeded
Besides, exactly what would you do in such a trigger?
The use case would be telling another system about the rollback.
Basically sending a "ignore what I told you to do" message
So it would send a network message, a signal or writing something to
external file.
Not modify
the database, for certain, because we're about to roll back.regards, tom lane
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic O�
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hannu Krosing wrote:
So it would send a network message, a signal or writing something to
external file.
If you're OK with a C function, you could try registering a callback,
see RegisterXactCallback().
--
�lvaro Herrera 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 11/17/2013 04:20 PM, Alvaro Herrera wrote:
Hannu Krosing wrote:
So it would send a network message, a signal or writing something to
external file.If you're OK with a C function, you could try registering a callback,
see RegisterXactCallback().
I already have an implementation doing just that, thoughg having a
trigger would be perhaps clearer :)
And I suspect that calling a pl/* function after the ROLLBACK has
actually happened due to
error is a no-go anyway, so it has to be C.
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic O�
--
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-11-17 09:39:26 +0100, Hannu Krosing wrote:
Besides, exactly what would you do in such a trigger?
The use case would be telling another system about the rollback.
Basically sending a "ignore what I told you to do" message
But you can't rely on it - if e.g. the server restarted/crashed, there
won't be any messages about it. In that light, I really don't see what
you could do with it.
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 11/17/2013 07:31 PM, Andres Freund wrote:
On 2013-11-17 09:39:26 +0100, Hannu Krosing wrote:
Besides, exactly what would you do in such a trigger?
The use case would be telling another system about the rollback.
Basically sending a "ignore what I told you to do" message
But you can't rely on it - if e.g. the server restarted/crashed, there
won't be any messages about it. In that light, I really don't see what
you could do with it.
I can get the info about non-commit earlier :)
At some point I can call back into the database and see if the
transaction is still running.
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic O�
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Nov 15, 2013 at 01:01:48PM -0500, Andrew Dunstan wrote:
The triggers don't fire if there is no real XID, so only actual data
changes should cause the trigger to fire.
What's the advantage of this provision? Without it, an individual trigger
could make the same check and drop out quickly. A trigger not wanting it
can't so easily work around its presence, though. Heretofore, skipping XID
assignment has been an implementation detail that improves performance without
otherwise calling user attention to itself. This provision would make the
decision to acquire an XID (where optional) affect application behavior.
Thanks,
nm
--
Noah Misch
EnterpriseDB http://www.enterprisedb.com
--
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/19/2013 12:45 AM, Noah Misch wrote:
On Fri, Nov 15, 2013 at 01:01:48PM -0500, Andrew Dunstan wrote:
The triggers don't fire if there is no real XID, so only actual data
changes should cause the trigger to fire.What's the advantage of this provision? Without it, an individual trigger
could make the same check and drop out quickly. A trigger not wanting it
can't so easily work around its presence, though. Heretofore, skipping XID
assignment has been an implementation detail that improves performance without
otherwise calling user attention to itself. This provision would make the
decision to acquire an XID (where optional) affect application behavior.
Mainly speed. How is the trigger (especially if not written in C) going
to check the same thing?
Conventional triggers don't fire except on data changing events, so this
seemed consistent with that.
Perhaps my understanding of when XIDs are acquired is insufficient. When
exactly is it optional?
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 Tue, Nov 19, 2013 at 08:54:49AM -0500, Andrew Dunstan wrote:
On 11/19/2013 12:45 AM, Noah Misch wrote:
On Fri, Nov 15, 2013 at 01:01:48PM -0500, Andrew Dunstan wrote:
The triggers don't fire if there is no real XID, so only actual data
changes should cause the trigger to fire.What's the advantage of this provision? Without it, an individual trigger
could make the same check and drop out quickly. A trigger not wanting it
can't so easily work around its presence, though. Heretofore, skipping XID
assignment has been an implementation detail that improves performance without
otherwise calling user attention to itself. This provision would make the
decision to acquire an XID (where optional) affect application behavior.Mainly speed. How is the trigger (especially if not written in C)
going to check the same thing?
Probably through a thin C function calling GetCurrentTransactionIdIfAny(). If
using C is not an option, one could query pg_locks.
Conventional triggers don't fire except on data changing events, so
this seemed consistent with that.
The definitions of "data changing event" differ, though. An UPDATE that finds
no rows to change will fire statement-level triggers, but this commit trigger
would not fire.
Perhaps my understanding of when XIDs are acquired is insufficient.
When exactly is it optional?
The following commands force XID assignment, but I think that's an
implementation detail rather than a consequence of their identity as
data-changing events:
SELECT ... FOR <lockmode>
NOTIFY
PREPARE TRANSACTION (gets an XID even if nothing else had done so)
Also, parents of XID-bearing subtransactions always have XIDs, even if all
subtransactions that modified data have aborted. This, too, is an
implementation artifact.
--
Noah Misch
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi,
On 2013-11-15 13:01:48 -0500, Andrew Dunstan wrote:
Attached is a patch to provide a new event trigger that will fire on
transaction commit. I have tried to make certain that it fires at a
sufficiently early stage in the commit process that some of the evils
mentioned in previous discussions on this topic aren't relevant.The triggers don't fire if there is no real XID, so only actual data changes
should cause the trigger to fire. They also don't fire in single user mode,
so that if you do something stupid like create a trigger that
unconditionally raises an error you have a way to recover.This is intended to be somewhat similar to the same feature in the Firebird
database, and the initial demand came from a client migrating from that
system to Postgres.
Could you explain a bit what the use case of this is and why it's not
sufficient to allow constraint triggers to work on a statement level?
"Just" that there would be multiple ones fired?
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 Tue, Nov 19, 2013 at 12:45 AM, Noah Misch <noah@leadboat.com> wrote:
On Fri, Nov 15, 2013 at 01:01:48PM -0500, Andrew Dunstan wrote:
The triggers don't fire if there is no real XID, so only actual data
changes should cause the trigger to fire.What's the advantage of this provision? Without it, an individual trigger
could make the same check and drop out quickly. A trigger not wanting it
can't so easily work around its presence, though. Heretofore, skipping XID
assignment has been an implementation detail that improves performance without
otherwise calling user attention to itself. This provision would make the
decision to acquire an XID (where optional) affect application behavior.
Yeah, I agree that that's an ugly wart. If we want a pre-commit
trigger that's only called for transactions that write data, we at
least need to name it appropriately.
--
Robert Haas
EnterpriseDB: 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
Andrew Dunstan <andrew@dunslane.net> writes:
Perhaps my understanding of when XIDs are acquired is insufficient. When
exactly is it optional?
My understanding of Noah's comment is that we would be exposing what
used to be an optimisation only implementation detail to the user, and
so we would need to properly document the current situation and would
probably be forbidden to change it in the future.
Then I guess it's back to the use cases: do we have use cases where it
would be interesting for the pre-commit trigger to only get fired when
an XID has been consumed?
I don't think so, because IIRC CREATE TEMP TABLE will consume an XID
even in an otherwise read-only transaction, and maybe the TEMP TABLE
writes will not be considered "actual writes" by the confused user.
What about specifying what notion of "data modifying" transactions
you're interested into and providing an SQL callable C function that the
trigger user might then use, or even a new WHEN clause?
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
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/19/2013 08:42 AM, Andres Freund wrote:
Could you explain a bit what the use case of this is and why it's not
sufficient to allow constraint triggers to work on a statement level?
"Just" that there would be multiple ones fired?
The main reason is to enforce arbitrary assertions which need
enforcement at the end of a transaction and not before. For example:
"each person record needs at least one record in the phone_numbers table"
or:
"no person may have more than one work and one home address which are
currently active"
You can't enforce this at the statement level because the
update/insert/deletes can happen in any order on the various tables.
The proposed patch is certainly an inefficient way to implement them
(since your checks get run regardless of which tables were touched), but
any other method would require a large and complex accounting
infrastructure to track which tables were modified and how.
This is the sort of thing the SQL committee covered in ASSERTIONS, but
of course they never specified any method for implementation.
--
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: WM3c75299e2f91fd58cdd3460e9750ffc2bbd73e16e69f2ac7db9b6241f8d052ebaea3db3dc97f2eead4f58b20226a536a@asav-1.01.com
On 2013-11-19 12:45:27 -0800, Josh Berkus wrote:
On 11/19/2013 08:42 AM, Andres Freund wrote:
Could you explain a bit what the use case of this is and why it's not
sufficient to allow constraint triggers to work on a statement level?
"Just" that there would be multiple ones fired?The main reason is to enforce arbitrary assertions which need
enforcement at the end of a transaction and not before. For example:
[...]
You can't enforce this at the statement level because the
update/insert/deletes can happen in any order on the various tables.
That's why I suggested adding statement level constraint triggers
(should be a farily small patch), which can be deferred till commit. The
problem there is that they can be triggered several times, but that can
relatively easily accounted for in user code.
I can't really say why, but commit time even triggers make me nervous...
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 11/19/2013 03:54 PM, Andres Freund wrote:
On 2013-11-19 12:45:27 -0800, Josh Berkus wrote:
On 11/19/2013 08:42 AM, Andres Freund wrote:
Could you explain a bit what the use case of this is and why it's not
sufficient to allow constraint triggers to work on a statement level?
"Just" that there would be multiple ones fired?The main reason is to enforce arbitrary assertions which need
enforcement at the end of a transaction and not before. For example:
[...]
You can't enforce this at the statement level because the
update/insert/deletes can happen in any order on the various tables.That's why I suggested adding statement level constraint triggers
(should be a farily small patch), which can be deferred till commit. The
problem there is that they can be triggered several times, but that can
relatively easily accounted for in user code.I can't really say why, but commit time even triggers make me nervous...
This feature is really extremely close to being a deferred constraint
trigger that is called once. The code that calls these event triggers
runs right before the code that runs the deferred triggers. That spot in
the code was chosen with some care, to try to reduce any risk from the
feature.
Putting the onus on the user to detect multiple invocations of the
trigger would make for MORE fragility, not less.
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
