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.

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> 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

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.

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

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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> 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

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

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

On Mon, 2008-05-19 at 21:53 -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> 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

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.

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

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

>> 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.

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

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> 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

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 gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

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

> Tom Lane wrote:
> > Bruce Momjian <bruce(at)momjian(dot)us> 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.
__________________________________________________________________

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

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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +