[HACKERS] Re: COMMIT TRIGGERs, take n+1
The attached file demonstrates how to create COMMIT, BEGIN, and even session CONNECT TRIGGERs for PostgreSQL using PlPgSQL only, via normal and CONSTRAINT TRIGGERs. There have been many threads on the PG mailing lists about commit triggers, with much skepticism shown about the possible semantics of such a thing. Below we list use cases, and demonstrate reasonable, useful, and desirable semantics. The previous version of this could be defeated by using SET CONSTRAINTS .. IMMEDIATE. This version detects this when it would cause commit triggers to run too soon, and causes an exception to be raised. The technique used to detect this could be used by anyone whose business logic breaks when SET CONSTRAINTS .. IMMEDIATE is used. There is one shortcoming of this implementation: it is inefficient because it has to use FOR EACH ROW triggers under the hood, so if you do 1,000 inserts, then 999 of the resulting internal trigger procedure invocations will be unnecessary. These are FOR EACH ROW triggers because that is the only level permitted for CONSTRAINT triggers, which are used under the hood to trigger running at commit time. (It would be nice if CONSTRAINT triggers could be FOR EACH STATEMENT too...) Use-cases: - create TEMP schema before it's needed by regular triggers This can be useful if CREATE TEMP TABLE IF EXISTS and such in regular triggers could slow them down. - cleanup internal, temporary state left by triggers from earlier transactions - perform global consistency checks (assertions, if you like) Note that these can be made to scale by checking only the changes made by the current transaction. Transition tables, temporal tables, audit tables -- these can all help for the purpose of checking only deltas as opposed to the entire database. Related: there was a thread about a patch to add assertions: https://www.postgresql.org/message-id/flat/20131218113911.GC5224%40alap2.anarazel.de#20131218113911.gc5...@alap2.anarazel.de - update materializations of views when all the relevant deltas can be considered together I use an alternatively view materialization system that allows direct updates of the materialization table, and records updates in a related history table. Being about to update such materializations via triggers is very convenient; being able to defer such updates as long as possible is a nice optimization. - call C functions that have external side-effects when you know the transaction will succeed (or previous ones that have succeeded but not had those functions called) Semantics: - connect/begin/commit trigger procedures called exactly once per- transaction that had any writes (even if they changed nothing in the end), with one exception: - exceptions thrown by triggers may be caught, and the triggering statement retried, in which case triggers will run again - connect/begin trigger procedures called in order of trigger name (ascending) before any rows are inserted/updated/deleted by any DML statements on non-TEMP tables in the current transaction - commit trigger procedures called in order of commit trigger name (ascending) at commit time, after the last statement sent by the client/user for the current transaction - begin and commit trigger procedures may perform additional write operations, and if so that will NOT cause additional invocations of begin/commit trigger procedures - commit trigger procedures may RAISE EXCEPTION, triggering a rollback of the transaction Nico -- commit_trigger.sql Description: application/sql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pre-commit triggers
On 11/24/2013 06:42 AM, Simon Riggs wrote: I think we should be thinking harder about how to implement ASSERTIONs, possibly calling them ASSERTION TRIGGERs not pre-commit write event triggers. I don't know that anyone is working on this, though, or even plans to. -- 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
Re: [HACKERS] pre-commit triggers
On 11/24/2013 09:42 AM, Simon Riggs wrote: It looks to me that this idea is horribly physical and seems likely to be badly misused. I don't see any way to use these that won't be quite ugly. There is no trigger descriptor, so no way of writing a constraint sensibly, since you'll need to make a constraint check for every commit by every user, not just ones that touch the data you care about. And security goes straight out the window, so these can't be used in normal application development. Plus we can already do this with RegisterXactCallback() as Alvaro points out - so if its a hack we're after, then we already have it, no patch required. Write a hack is not normally advice I like to give or receive. We're after a feature that at least one other RDBMS that we know of suports. But leaving that aside, what are the restrictions, if any, in what can be done in such a callback? Are we allowed to alter the database? If so, what happens to FK constraints? Can we raise an ERROR exception? 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
Re: [HACKERS] pre-commit triggers
Josh Berkus j...@agliodbs.com writes: On 11/24/2013 06:42 AM, Simon Riggs wrote: I think we should be thinking harder about how to implement ASSERTIONs, possibly calling them ASSERTION TRIGGERs not pre-commit write event triggers. I don't know that anyone is working on this, though, or even plans to. Huh? http://www.postgresql.org/message-id/1384486216.5008.17.ca...@vanquo.pezone.net It's far from committable, of course, but there is somebody working on it. I tend to agree with the complaints that pre-commit triggers in the proposed form would be pretty useless. You'd have to code in C to get any useful information about what the transaction had done (and you'd still not have much), but if you're coding in C there's already a hook you can use for this. 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
Re: [HACKERS] pre-commit triggers
Andrew Dunstan and...@dunslane.net writes: Write a hack is not normally advice I like to give or receive. We're after a feature that at least one other RDBMS that we know of suports. But leaving that aside, what are the restrictions, if any, in what can be done in such a callback? Are we allowed to alter the database? If so, what happens to FK constraints? Can we raise an ERROR exception? An XACT_EVENT_PRE_COMMIT action is fairly unconstrained, though if you're planning to do something that might break FKs, you should do AfterTriggerFireDeferred() afterwards. Actually it might be smart to repeat the whole loop that's just before CallXactCallbacks(XACT_EVENT_PRE_COMMIT); in CommitTransaction. Of course, there's a certain chicken and egg question here. If you're planning to modify the database in a way that would cause FK triggers to fire, then this is not exactly the last thing that happens before commit, is it? So I think this sounds more like fuzzy thinking than a valid requirement. 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
Re: [HACKERS] pre-commit triggers
On 11/26/2013 09:45 AM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: On 11/24/2013 06:42 AM, Simon Riggs wrote: I think we should be thinking harder about how to implement ASSERTIONs, possibly calling them ASSERTION TRIGGERs not pre-commit write event triggers. I don't know that anyone is working on this, though, or even plans to. Huh? http://www.postgresql.org/message-id/1384486216.5008.17.ca...@vanquo.pezone.net Damn, I missed that. I'll have to check that out. -- 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
Re: [HACKERS] pre-commit triggers
On 11/26/2013 01:04 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Write a hack is not normally advice I like to give or receive. We're after a feature that at least one other RDBMS that we know of suports. But leaving that aside, what are the restrictions, if any, in what can be done in such a callback? Are we allowed to alter the database? If so, what happens to FK constraints? Can we raise an ERROR exception? An XACT_EVENT_PRE_COMMIT action is fairly unconstrained, though if you're planning to do something that might break FKs, you should do AfterTriggerFireDeferred() afterwards. Actually it might be smart to repeat the whole loop that's just before CallXactCallbacks(XACT_EVENT_PRE_COMMIT); in CommitTransaction. Of course, there's a certain chicken and egg question here. If you're planning to modify the database in a way that would cause FK triggers to fire, then this is not exactly the last thing that happens before commit, is it? So I think this sounds more like fuzzy thinking than a valid requirement. As far as I know the client isn't proposing to alter the database at all. I'm just trying to get a clear understanding of the limitations of this approach. 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
Re: [HACKERS] pre-commit triggers
On 19 November 2013 16:46, Robert Haas robertmh...@gmail.com wrote: On Tue, Nov 19, 2013 at 12:45 AM, Noah Misch n...@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. It looks to me that this idea is horribly physical and seems likely to be badly misused. I don't see any way to use these that won't be quite ugly. There is no trigger descriptor, so no way of writing a constraint sensibly, since you'll need to make a constraint check for every commit by every user, not just ones that touch the data you care about. And security goes straight out the window, so these can't be used in normal application development. Plus we can already do this with RegisterXactCallback() as Alvaro points out - so if its a hack we're after, then we already have it, no patch required. So this patch doesn't give us anything genuinely useful for application developers, nor does it give us the thing that Josh is looking for.. The main reason is to enforce arbitrary assertions which need enforcement at the end of a transaction and not before. I like the goal, but this is not the solution. Josh also points out... This is the sort of thing the SQL committee covered in ASSERTIONS, but of course they never specified any method for implementation. I think we should be thinking harder about how to implement ASSERTIONs, possibly calling them ASSERTION TRIGGERs not pre-commit write event triggers. -- 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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
On Tue, Nov 19, 2013 at 12:45 AM, Noah Misch n...@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
Re: [HACKERS] pre-commit triggers
Andrew Dunstan and...@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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
On 2013-11-19 16:04:12 -0500, Andrew Dunstan wrote: 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... Don't get me wrong, I am not -1'ing the feature, just wondering whether there might be better alternatives. 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. Well, a) that code is battle tested b) it properly handles new events being created during the invocation of a trigger c) it allows only triggering when specific tables have been modified. That'd allow major efficiency improvements in the usecase cited upthread. I think the major advantage is that it doesn't depend on the relatively obscure definition of an xid has been assigned. Putting the onus on the user to detect multiple invocations of the trigger would make for MORE fragility, not less. Yea, that's the major reason against it. Without that I'd say that's the clear route. But maybe adding a AFTER STATEMENT ONCE (or better using an existing keyword) is the way to go for that? 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
Re: [HACKERS] pre-commit triggers
On 11/19/2013 04:23 PM, Andres Freund wrote: On 2013-11-19 16:04:12 -0500, Andrew Dunstan wrote: 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... Don't get me wrong, I am not -1'ing the feature, just wondering whether there might be better alternatives. 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. Well, a) that code is battle tested b) it properly handles new events being created during the invocation of a trigger c) it allows only triggering when specific tables have been modified. That'd allow major efficiency improvements in the usecase cited upthread. I think the major advantage is that it doesn't depend on the relatively obscure definition of an xid has been assigned. That part or really orthogonal to the issue at hand I think. i.e. I could, by removing about 30 characters, remove that restriction and leave the rest intact. Putting the onus on the user to detect multiple invocations of the trigger would make for MORE fragility, not less. Yea, that's the major reason against it. Without that I'd say that's the clear route. But maybe adding a AFTER STATEMENT ONCE (or better using an existing keyword) is the way to go for that? What if you need to have such a trigger on multiple tables? How many times does it fire? This feature would be nicely and easily defined - it will run each such trigger once per transaction (modulo the xid issue). Now maybe we could use an event trigger WHEN clause instead of always applying the xid must be real rule. I'm not sure what that would look like - i.e. what would be the filter variable or its possible values, but it's possibly worth exploring. 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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
On 11/17/2013 01:42 AM, Tom Lane wrote: Hannu Krosing ha...@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
Re: [HACKERS] pre-commit triggers
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 Herrerahttp://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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
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Ü
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
Hannu Krosing ha...@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
[HACKERS] 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 diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml index ac31332..3bbf1a4 100644 --- a/doc/src/sgml/event-trigger.sgml +++ b/doc/src/sgml/event-trigger.sgml @@ -12,7 +12,7 @@ productnamePostgreSQL/ also provides event triggers. Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of - capturing DDL events. + capturing DDL events or transaction commits. /para para @@ -29,8 +29,9 @@ occurs in the database in which it is defined. Currently, the only supported events are literalddl_command_start/, - literalddl_command_end/ - and literalsql_drop/. + literalddl_command_end/, + literalsql_drop/, and + literaltransaction_commit/. Support for additional events may be added in future releases. /para @@ -65,6 +66,15 @@ /para para +A literaltransaction_commit/ trigger is called at the end of a +transaction, just before any deferred triggers are fired, unless +no data changes have been made by the transaction, or +productnamePostgreSQL/ is running in Single-User mode. This is so +that you can recover from a badly specified literaltransaction_commit/ +trigger. + /para + + para Event triggers (like other functions) cannot be executed in an aborted transaction. Thus, if a DDL command fails with an error, any associated literalddl_command_end/ triggers will not be executed. Conversely, @@ -77,8 +87,13 @@ /para para - For a complete list of commands supported by the event trigger mechanism, - see xref linkend=event-trigger-matrix. +A literaltransaction_commit/ trigger is also not called in an +aborted transaction. + /para + + para + For a complete list of commands supported by the event trigger + mechanism, see xref linkend=event-trigger-matrix. /para para @@ -101,6 +116,11 @@ to intercept. A common use of such triggers is to restrict the range of DDL operations which users may perform. /para + + para +literaltransaction_commit/ triggers do not currently support +literalWHEN/literal clauses. + /para /sect1 sect1 id=event-trigger-matrix diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index 0591f3f..74fc04c 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -1825,6 +1825,16 @@ CommitTransaction(void) Assert(s-parent == NULL); /* + * First fire any pre-commit triggers, so if they in turn cause any + * deferred triggers etc to fire this will be picked up below. + * Only fire them, though, if we have a real transaction ID and + * we're not running standalone. Not firing when standalone provides + * a way to recover from setting up a bad transaction trigger. + */ + if (s-transactionId != InvalidTransactionId IsUnderPostmaster) + PreCommitTriggersFire(); + + /* * Do pre-commit processing that involves calling user-defined code, such * as triggers. Since closing cursors could queue trigger actions, * triggers could open cursors, etc, we have to keep looping until there's @@ -2030,6 +2040,16 @@ PrepareTransaction(void) Assert(s-parent == NULL); /* + * First fire any pre-commit triggers, so if they in turn cause any + * deferred triggers etc to fire this will be picked up below. + * Only fire them, though, if we have a real transaction ID and + * we're not running standalone. Not firing when standalone provides + * a way to recover from setting up a bad transaction trigger. + */ + if (s-transactionId != InvalidTransactionId IsUnderPostmaster) + PreCommitTriggersFire(); + + /* * Do pre-commit processing that involves calling user-defined code, such * as triggers. Since closing cursors could queue trigger actions, * triggers could open cursors, etc, we have to keep looping until there's diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index 328e2a8..f93441f 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -153,7 +153,8 @@
Re: [HACKERS] pre-commit triggers
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
Re: [HACKERS] pre-commit triggers
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 diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml index ac31332..3bbf1a4 100644 --- a/doc/src/sgml/event-trigger.sgml +++ b/doc/src/sgml/event-trigger.sgml @@ -12,7 +12,7 @@ productnamePostgreSQL/ also provides event triggers. Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of - capturing DDL events. + capturing DDL events or transaction commits. /para para @@ -29,8 +29,9 @@ occurs in the database in which it is defined. Currently, the only supported events are literalddl_command_start/, - literalddl_command_end/ - and literalsql_drop/. + literalddl_command_end/, + literalsql_drop/, and + literaltransaction_commit/. Support for additional events may be added in future releases. /para @@ -65,6 +66,15 @@ /para para +A literaltransaction_commit/ trigger is called at the end of a +transaction, just before any deferred triggers are fired, unless +no data changes have been made by the transaction, or +productnamePostgreSQL/ is running in Single-User mode. This is so +that you can recover from a badly specified literaltransaction_commit/ +trigger. + /para + + para Event triggers (like other functions) cannot be executed in an aborted transaction. Thus, if a DDL command fails with an error, any associated literalddl_command_end/ triggers will not be executed. Conversely, @@ -77,8 +87,13 @@ /para para - For a complete list of commands supported by the event trigger mechanism, - see xref linkend=event-trigger-matrix. +A literaltransaction_commit/ trigger is also not called in an +aborted transaction. + /para + + para + For a complete list of commands supported by the event trigger + mechanism, see xref linkend=event-trigger-matrix. /para para @@ -101,6 +116,11 @@ to intercept. A common use of such triggers is to restrict the range of DDL operations which users may perform. /para + + para +literaltransaction_commit/ triggers do not currently support +literalWHEN/literal clauses. + /para /sect1 sect1 id=event-trigger-matrix diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index 0591f3f..e7f5095 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -30,6 +30,7 @@ #include catalog/namespace.h #include catalog/storage.h #include commands/async.h +#include commands/event_trigger.h #include commands/tablecmds.h #include commands/trigger.h #include executor/spi.h @@ -1825,6 +1826,16 @@ CommitTransaction(void) Assert(s-parent == NULL); /* + * First fire any pre-commit triggers, so if they in turn cause any + * deferred triggers etc to fire this will be picked up below. + * Only fire them, though, if we have a real transaction ID and + * we're not running standalone. Not firing when standalone provides + * a way to recover from setting up a bad transaction trigger. + */ + if (s-transactionId != InvalidTransactionId IsUnderPostmaster) + PreCommitTriggersFire(); + + /* * Do pre-commit processing that involves calling user-defined code, such * as triggers. Since closing cursors could queue trigger actions, * triggers could open cursors, etc, we have to keep looping until there's @@ -2030,6 +2041,16 @@ PrepareTransaction(void) Assert(s-parent == NULL); /* + * First fire any pre-commit triggers, so if they in turn cause any + * deferred triggers etc to fire this will be picked up below. + * Only fire them, though, if we have a real transaction ID and + * we're not running standalone. Not firing when standalone provides + * a way to recover from setting up a bad transaction trigger. + */ + if (s-transactionId != InvalidTransactionId IsUnderPostmaster) + PreCommitTriggersFire(); + + /* * Do pre-commit processing that involves calling user-defined code, such * as triggers. Since closing cursors could queue trigger actions, * triggers could open cursors, etc, we have to keep looping until there's diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index 328e2a8..f93441f 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -153,7 +153,8 @@ CreateEventTrigger(CreateEventTrigStmt *stmt) /* Validate event name. */ if (strcmp(stmt-eventname, ddl_command_start) != 0
[HACKERS] On Commit Triggers?
While waiting an application using the PostgreSQL database, I've come in the need of an On Commit Trigger. As you know such trigger doesn't not exist currently in the postgresql but I was wondering if it would be possible to add, maybe in a future version, or if I can obtain something similar with the actual code. Regards, Antonio Scotti ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] On Commit Triggers
I am using PostgreSQL for some application and I've come in the need of a Before Commit and an After Commit trigger. PostgreSQL currently doesn't support them and I am wondering if it will ever be possible for developers to add them. Antonio Scotti ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
