Re: [HACKERS] triggers on prepare, commit, rollback... ?
Andrew Dunstan wrote: Fabien COELHO wrote: Dear Tom, trigger on prepare, commit, rollback, savepoint, Yup, and there won't be. That's a definite answer! This has been suggested and rejected before. See the archives. I'll check into that. This is a sufficiently frequently asked question that I wish someone would add an entry to the FAQ about it, or add it to the TODO list's Features we don't want section. This was already a TODO: * Add database and transaction-level triggers http://archives.postgresql.org/pgsql-hackers/2008-03/msg00451.php http://archives.postgresql.org/pgsql-hackers/2008-05/msg00620.php I have added a URL from the current discussion. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] triggers on prepare, commit, rollback... ?
On Tue, 20 May 2008 19:51:32 +0100 Sam Mason wrote: On Tue, May 20, 2008 at 01:20:48PM -0400, Greg Smith wrote: On Tue, 20 May 2008, Hannu Krosing wrote: Tell others that this trx failed, maybe log a failure ? OTOH, this can be implemented by a daemon that sits on tail -f logfile | grep ROLLBACK In order to follow the log files like that successfully in many environments, you need to stay in sync as the underlying log file changes (it might rotate every day for example). Unfortunately it's not as simple as just using tail. GNU tail provides the -F (or --follow=name) for just this reason. Not every system is GNU. Not every possible configuration uses the same logfile name for the next logfile. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group -- 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] triggers on prepare, commit, rollback... ?
On May 19, 2008, at 6:53 PM, Tom Lane wrote: Another response I've heard is but I don't want to make inside-the-database changes, I want to propagate the state to someplace external. Of course that's completely broken too, because there is *absolutely no way* you will ever make such changes atomic with the inside-the-database transaction commit. We discourage people from making triggers cause outside-the-database side effects already --- it's not going to be better to do it in an on commit trigger. Isn't this close to what NOTIFY is? An on-commit trigger that causes only outside-the-database effects. Cheers, Steve -- 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] triggers on prepare, commit, rollback... ?
On Mon, 2008-05-19 at 21:53 -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: trigger on prepare, commit, rollback, savepoint, This is a sufficiently frequently asked question that I wish someone would add an entry to the FAQ about it, or add it to the TODO list's Features we don't want section. OK, remind me why we don't want it again? I'm sure I've ranted on this several times before, but a quick archive search doesn't find anything. So, here are a few points to chew on: * Trigger on rollback: what's that supposed to do? Tell others that this trx failed, maybe log a failure ? OTOH, this can be implemented by a daemon that sits on tail -f logfile | grep ROLLBACK The current transaction is already aborted, so the trigger has no hope of making any database changes that will ever be visible to anyone. after reading your explanations trigger on rollback/failure is the only one what makes sense still, but it may be that after failure it does not have enough state usable to report anything useful. - Hannu -- 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] triggers on prepare, commit, rollback... ?
Dear Tom, trigger on prepare, commit, rollback, savepoint, I'm sure I've ranted on this several times before, but a quick archive search doesn't find anything. So, here are a few points to chew on: I agree that not all of them may make sense, but some may do, and I do not know which of them would definitely be of any use in any case for anyone. In fact, I just know that I do not know:-) * Trigger on rollback: what's that supposed to do? The current transaction is already aborted, so the trigger has no hope of making any database changes that will ever be visible to anyone. For some synchronous replication implementation, it may be to forward the ROLLBACK to the twin transaction in another database? * Trigger on commit: what do you do if the transaction fails after calling the trigger? The reductio ad absurdum for this is to consider having two on-commit triggers, where obviously the second one could fail. Obviously, an AFTER xxx cannot make the xxx to fail. I would use BEFORE COMMIT or BEFORE PREPARE to check global consistency constraints that would not make sense to be checked at the simple row or statement level in a complex transaction. The basic problem is that the transaction commit sequence is very Indeed. The BEFORE should be before anything else in the commit sequence, and may make it fail, but the AFTER is after anything else, and could not change the outcome. the user transaction commits or aborts. But that hardly seems like a usable basis for replication, since you're just plain out of luck if the secondary transaction fails. As for replication, I was naively thinking of using BEFORE PREPARE to forward a prepare and possibly fail in the master if the slave fails, and then an AFTER PREPARE or BEFORE COMMIT/ROLLBACK PREPARE would forward the final COMMIT/ROLLBACK depending on the success of the prepare operation in the master and slaves. The function could also wait for something to finish on the slave. If the something fails in between, then there are several state combinations to detect and handle, but this is just the point of 2PC. I'm not claiming this is easy, on the contrary, but ISTM that the 2PC semantics is all what is fundamentaly needed to achieve synchroneous replication, and once hooks are available there should be a way to prototype something at the user level (slony style). The efficiency of the stuff is another issue, but some applications don't need high throughput and low latency, but just to know that the data are not lost. Well, just my usual 0.02 EUR:-) -- Fabien. -- 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] triggers on prepare, commit, rollback... ?
On Tue, May 20, 2008 at 06:38:17AM +0100, James Mansion wrote: Tom Lane wrote: Another response I've heard is but I don't want to make inside-the-database changes, I want to propagate the state to someplace external. Of course that's completely broken too, because there is Some things are idempotent and are effectively hints - that they are not transacted can be well understood and accomodated. The idempotent comment makes sense, hints I'm not so sure about as they sound as though they've got very complicated semantics. One operation that fits the transaction BEGIN/END (either COMMIT or ROLLBACK) semantics well, that I can think of is that of the now() function. It can be modelled in at least a couple of ways using transaction begin/end. If you have a BEGIN trigger, then when a transaction is run it would save the time in a transaction level variable. If you have an END trigger, then this variable would be reset, the now() function would need to check on every call to see if it's missing and set it to the current time if it's missing, otherwise it should return its value. Could this be modelled without using transaction level triggers? Sam -- 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] triggers on prepare, commit, rollback... ?
On Tue, 2008-05-20 at 12:09 +0200, Fabien COELHO wrote: As for replication, I was naively thinking of using BEFORE PREPARE to forward a prepare and possibly fail in the master if the slave fails, and then an AFTER PREPARE or BEFORE COMMIT/ROLLBACK PREPARE would forward the final COMMIT/ROLLBACK depending on the success of the prepare operation in the master and slaves. The function could also wait for something to finish on the slave. If the something fails in between, then there are several state combinations to detect and handle, but this is just the point of 2PC. I'm not claiming this is easy, on the contrary, but ISTM that the 2PC semantics is all what is fundamentaly needed to achieve synchroneous replication, and once hooks are available there should be a way to prototype something at the user level (slony style). The efficiency of the stuff is another issue, but some applications don't need high throughput and low latency, but just to know that the data are not lost. There are probably easier solutions for achieving this, like using two connections at client level or doing async replication, or running data modifications through pl/proxy functions where partitioning function always returns two partitions --- Hannu -- 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] triggers on prepare, commit, rollback... ?
I'm not claiming this is easy, on the contrary, but ISTM that the 2PC semantics is all what is fundamentaly needed to achieve synchroneous replication There are probably easier solutions for achieving this, like using two connections at client level Sure, but that means doing the implementation in the client, although I think that a system solution is better and independent of the client code, and I may make the solution evolve in time without direct client-side effect. So I'm more interested in the generic implementation. or doing async replication, The problem of Async is the *A*, you cannot guarantee that the data will not be lost if the failure occurs between the commit and the later replication. For some application, this is not an option. or running data modifications through pl/proxy functions where partitioning function always returns two partitions I don't think that pl/proxy takes care of 2PC semantics in any useful way. Possibly something like pgpool could take care somehow of the replication by executing queries on two backends, but there are issues with such an approach (say, a SEQUENCE may not return the same result on both sides, some functions may have side effects...), and on commit it must use prepared statements on both sides, and I don't think this is the case for now with the current pgpool. Anyway, I do not think that there is a simple high availability / high throuput / low latency / guaranteed replication / easy to administrate / load balanced silver bullet... My point is more about exploration, and for that user-visible hooks would help. -- Fabien. -- 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] triggers on prepare, commit, rollback... ?
On Tue, 2008-05-20 at 13:36 +0200, Fabien COELHO wrote: or running data modifications through pl/proxy functions where partitioning function always returns two partitions I don't think that pl/proxy takes care of 2PC semantics in any useful way. Possibly something like pgpool could take care somehow of the replication by executing queries on two backends, but there are issues with such an approach (say, a SEQUENCE may not return the same result on both sides, some functions may have side effects...), and on commit it must use prepared statements on both sides, and I don't think this is the case for now with the current pgpool. Anyway, I do not think that there is a simple high availability / high throuput / low latency / guaranteed replication / easy to administrate / load balanced silver bullet... My point is more about exploration, and for that user-visible hooks would help. 2PC will never be any of ( high throuput / low latency / easy to administrate ) - Hannu -- 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] triggers on prepare, commit, rollback... ?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: trigger on prepare, commit, rollback, savepoint, This is a sufficiently frequently asked question that I wish someone would add an entry to the FAQ about it, or add it to the TODO list's Features we don't want section. OK, remind me why we don't want it again? I'm sure I've ranted on this several times before, but a quick archive search doesn't find anything. So, here are a few points to chew on: * Trigger on rollback: what's that supposed to do? The current transaction is already aborted, so the trigger has no hope of making any database changes that will ever be visible to anyone. * Trigger on commit: what do you do if the transaction fails after calling the trigger? The reductio ad absurdum for this is to consider having two on-commit triggers, where obviously the second one could fail. I full agree that having triggers on rollback and on commit of 2PC transactions is broken by design. Triggers on COMMIT (for non-2PC transactions) and PREPARE (for 2PC-Transactions) seem workable though - I'd expect such a trigger to be executed *before* any actual commit handling takes place. Essentially, doing BEGIN some work COMMIT in the presence of an on-commit trigger would be equivalent to doing BEGIN some work SELECT my_trigger_function COMMIT. A possible use-case for that is aggregating some statistics collected during a transaction. One could e.g. maintain a cache of table rowcounts by summing up the number of inserted and deleted records per table with some per-row ON INSERT and ON DELETE (presumably C-language) triggers, and than update a global cache at transaction end. regards, Florian Pflug -- 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] triggers on prepare, commit, rollback... ?
On Tue, 20 May 2008, Hannu Krosing wrote: Tell others that this trx failed, maybe log a failure ? OTOH, this can be implemented by a daemon that sits on tail -f logfile | grep ROLLBACK In order to follow the log files like that successfully in many environments, you need to stay in sync as the underlying log file changes (it might rotate every day for example). Unfortunately it's not as simple as just using tail. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] triggers on prepare, commit, rollback... ?
On Tue, May 20, 2008 at 01:20:48PM -0400, Greg Smith wrote: On Tue, 20 May 2008, Hannu Krosing wrote: Tell others that this trx failed, maybe log a failure ? OTOH, this can be implemented by a daemon that sits on tail -f logfile | grep ROLLBACK In order to follow the log files like that successfully in many environments, you need to stay in sync as the underlying log file changes (it might rotate every day for example). Unfortunately it's not as simple as just using tail. GNU tail provides the -F (or --follow=name) for just this reason. Sam -- 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] triggers on prepare, commit, rollback... ?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: trigger on prepare, commit, rollback, savepoint, This is a sufficiently frequently asked question that I wish someone would add an entry to the FAQ about it, or add it to the TODO list's Features we don't want section. OK, remind me why we don't want it again? I'm sure I've ranted on this several times before, but a quick archive search doesn't find anything. I know of this very short rant: http://archives.postgresql.org/pgsql-hackers/2008-04/msg01200.php Florian G. Pflug wrote: A possible use-case for that is aggregating some statistics collected during a transaction. One could e.g. maintain a cache of table rowcounts by summing up the number of inserted and deleted records per table with some per-row ON INSERT and ON DELETE (presumably C-language) triggers, and than update a global cache at transaction end. This is possible now using deferred constraint triggers (PL/pgSQL is sufficient), though better described IMHO as before prepare or before commit rather than on Any FAQ addition should mention deferred constraint triggers. I would expect problems with after commit and after rollback triggers. I think that the documentation of when the existing deferred constraint triggers run in 2PC/non 2PC could be clarified, and the effects on the transaction state that are possible within such trigger functions documented. http://www.postgresql.org/docs/current/interactive/sql-createconstraint.html Says They can be fired either at the end of the statement causing the triggering event, or at the end of the containing transaction; It refers to http://www.postgresql.org/docs/current/interactive/sql-createtable.html Which says Checking of constraints that are deferrable can be postponed until the end of the transaction and If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. I'd also contest this statement: Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. Regards, Stephen Denne. At the Datamail Group we value teamwork, respect, achievement, client focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way. The Datamail Group, through our GoGreen programme, is committed to environmental sustainability. Help us in our efforts by not printing this email. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] triggers on prepare, commit, rollback... ?
Dear pgdev, I've played with triggers a bit, and I have noticed that there seem to be no way to add a trigger on events such as prepare, commit, rollback, savepoint, if I'm not mistaken. Also, possible interesting events could be create, alter and so, but it may already be possible to catch these by having a trigger on pg_class or the like. It seems to me that such triggers would be useful to help implement a simple (hmmm...) synchroneous replication system, possibly by extending or modifying slony, or for advance logging. Is there any special semantical issue for providing them in pg, or is it just the matter of implementing the parser, bookkeeping, callbacks... but with no other special intrinsic difficulty? Thanks in advance, -- Fabien. -- 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] triggers on prepare, commit, rollback... ?
Fabien COELHO [EMAIL PROTECTED] writes: I've played with triggers a bit, and I have noticed that there seem to be no way to add a trigger on events such as prepare, commit, rollback, savepoint, Yup, and there won't be. This has been suggested and rejected before. See the archives. It seems to me that such triggers would be useful to help implement a simple (hmmm...) synchroneous replication system, That argument has no credibility whatever. We have not even been able to get the replication projects to agree on a common set of custom hooks; the chance that they could get by with triggers on SQL-visible events is nil. 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] triggers on prepare, commit, rollback... ?
Dear Tom, trigger on prepare, commit, rollback, savepoint, Yup, and there won't be. That's a definite answer! This has been suggested and rejected before. See the archives. I'll check into that. It seems to me that such triggers would be useful to help implement a simple (hmmm...) synchroneous replication system, That argument has no credibility whatever. If you say so. We have not even been able to get the replication projects to agree on a common set of custom hooks; the chance that they could get by with triggers on SQL-visible events is nil. That is indeed an issue. On the other hand, there are several possible strategies to implement replication, but ISTM that all should require a hook (whether SQL visible or not) at the prepare/commit levels to play around with the 2PC. Well, thanks for your answer anyway, -- Fabien. -- 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] triggers on prepare, commit, rollback... ?
Fabien COELHO wrote: Dear Tom, trigger on prepare, commit, rollback, savepoint, Yup, and there won't be. That's a definite answer! This has been suggested and rejected before. See the archives. I'll check into that. This is a sufficiently frequently asked question that I wish someone would add an entry to the FAQ about it, or add it to the TODO list's Features we don't want section. 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] triggers on prepare, commit, rollback... ?
Andrew Dunstan wrote: Fabien COELHO wrote: Dear Tom, trigger on prepare, commit, rollback, savepoint, Yup, and there won't be. That's a definite answer! This has been suggested and rejected before. See the archives. I'll check into that. This is a sufficiently frequently asked question that I wish someone would add an entry to the FAQ about it, or add it to the TODO list's Features we don't want section. OK, remind me why we don't want it again? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] triggers on prepare, commit, rollback... ?
Bruce Momjian [EMAIL PROTECTED] writes: trigger on prepare, commit, rollback, savepoint, This is a sufficiently frequently asked question that I wish someone would add an entry to the FAQ about it, or add it to the TODO list's Features we don't want section. OK, remind me why we don't want it again? I'm sure I've ranted on this several times before, but a quick archive search doesn't find anything. So, here are a few points to chew on: * Trigger on rollback: what's that supposed to do? The current transaction is already aborted, so the trigger has no hope of making any database changes that will ever be visible to anyone. * Trigger on commit: what do you do if the transaction fails after calling the trigger? The reductio ad absurdum for this is to consider having two on-commit triggers, where obviously the second one could fail. The basic problem is that the transaction commit sequence is very carefully designed to do things in a specific order, and there is a well-defined atomic point where the transaction is really committed, and we cannot go injecting random user-written code into that area and still expect to have a working system. These objections could be addressed to some extent by running the triggers in a separate transaction that's automatically executed after the user transaction commits or aborts. But that hardly seems like a usable basis for replication, since you're just plain out of luck if the secondary transaction fails. Another response I've heard is but I don't want to make inside-the-database changes, I want to propagate the state to someplace external. Of course that's completely broken too, because there is *absolutely no way* you will ever make such changes atomic with the inside-the-database transaction commit. We discourage people from making triggers cause outside-the-database side effects already --- it's not going to be better to do it in an on commit trigger. 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] triggers on prepare, commit, rollback... ?
Tom Lane wrote: * Trigger on rollback: what's that supposed to do? The current transaction is already aborted, so the trigger has no hope of making any database changes that will ever be visible to anyone. It can however affect state in the backend doing the rollback, which can be useful. * Trigger on commit: what do you do if the transaction fails after calling the trigger? The reductio ad absurdum for this is to consider having two on-commit triggers, where obviously the second one could fail. Ditto - this is effectively at the point where messaging for NOTIFY happens, and if it fails then that's tough. If you need to implement a custom NOTIFY, this is where to do it. Another response I've heard is but I don't want to make inside-the-database changes, I want to propagate the state to someplace external. Of course that's completely broken too, because there is You really are being absurdly judgemental here. _You_ may not have a use case, but that does not mean that no-one else does. Some things are idempotent and are effectively hints - that they are not transacted can be well understood and accomodated. Is 'Tom doesn't need it' an adequate reason to take such a hard line? James -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
