ROLLBACK triggers?

Daisuke,

A patch was done for replication hooks which implements global
database-level triggers for connection startup and shutdown, and transaction
begin, commit, and rollback; they may help you out in this situation.

http://gorda.di.uminho.pt/community/pgsqlhooks/

-Jonah

On Mon, Jan 23, 2006 at 06:35:18PM +0900, Daisuke Maki wrote:

A quick scan of the docs and the source code tree seems to indicate that
there is no such thing as a rollback trigger, short of hacking it.

Now, I'm wondering:
1. Is there a rollback/commit trigger? If not, is it planned to be
implemented at all?
2. Is there a way to undo changes to data external to postgres
when a rollback occurs, OR, only update that external data
when a commit occurs?

Actually, this is something that often comes up in terms of
LISTEN/NOTIFY and doing external non-transactional stuff like sending
emails. AFAIK there's no plans to add support for anything like a
rollback trigger.

Your best bet (short of hacking the backend) is to have your triggers
(or maybe this would mean you could just do this with rules, which could
be faster) record the relevant information in a staging table. After the
transaction commits, you can then pull the info out of the staging table
and use it to update your index. You could also use LISTEN/NOTIFY to
speed this process up.

Of course that means there will be a period of time where the index is
out-of-date, so perhaps there is some argument to be made for a ROLLBACK
trigger.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Hello Jonah,
Sorry for hi-jacking the thread (not my intent, I assure you)
however, is there any chance Jonah of expanding your work to include 'on
user login/logout' ?

As an aside, a trigger on rollback seems... unlikely (at least to my
mind). What is the functionality if your rollback trigger fails ? Ugh.
Down that road I can see madness looming, however, this -is- monday so
... :)

Regards
Stef

Jonah H. Harris wrote:

Hey Stef,

It's not my patch, it's Alfrânio Correia Júnior's. I don't think it would
be too difficult to add a hook for authentication, but that is off-topic.
If you would like to discuss it further, please create a new topic for it.

As for a rollback trigger, I don't really see too much of a use for it aside
from when you would need to notify an external application/database server
of commit/rollback such as what Daisuke would use it for or something which
could be extended by contrib modules (such as dblink, dblink_tds, ...). Of
course, people could always write bad transactional application code that
relies on this commit/rollback trigger, but that isn't the use case.

-Jonah

"Jim C. Nasby" <jnasby@pervasive.com> writes:

On Mon, Jan 23, 2006 at 06:35:18PM +0900, Daisuke Maki wrote:

A quick scan of the docs and the source code tree seems to indicate that
there is no such thing as a rollback trigger, short of hacking it.

Now, I'm wondering:
1. Is there a rollback/commit trigger? If not, is it planned to be
implemented at all?
2. Is there a way to undo changes to data external to postgres
when a rollback occurs, OR, only update that external data
when a commit occurs?

Actually, this is something that often comes up in terms of
LISTEN/NOTIFY and doing external non-transactional stuff like sending
emails. AFAIK there's no plans to add support for anything like a
rollback trigger.

Well, note that in the case of LISTEN/NOTIFY the receiving side doesn't see
the message until the sender commits. Precisely to maintain transactional
integrity.

If the external work can never fail then it seems like just postponing the
processing of it until transaction commit time like deferred constraints would
be better. I'm not sure you can really do that currently though.

--
greg

On Mon, January 23, 2006 16:35, Daisuke Maki wrote:

I'm currently trying to embed Senna full text search engine
(http://qwik.jp/senna/) into postgres. I'm trying to achieve this by
using triggers (implemented in C) to cause an update to senna's index at
various points.

This seemed to work fine until I realized that while postgres' SQL
commands could be rolled back, Senna's index remained already-changed.
There are other potential issues with regards to transaction safety, but
currently this seems to be a problem that I cannot fix by simply
patching Senna. So I thought that if there was a rollback trigger, I
could call whatever necessary to undo the changes that were made to the
index.

I may just be being stupid here (haven't had my coffee yet) but are you
sure that:

I. The triggers really do arrive even when the modifications are aborted?
AFAIK triggers that were, er, triggered during a transaction only really
get notified once the transaction commits. In psql:

=> LISTEN x;
LISTEN
=> BEGIN;
BEGIN
=> NOTIFY x;
NOTIFY
=> ABORT;
ROLLBACK
=> BEGIN;
BEGIN
=> NOTIFY x;
NOTIFY
=> COMMIT;
COMMIT
Asynchronous notification "x" received from server process with PID 42.

As you can see, the ABORT also rolled back the NOTIFY, so it never
arrived. This may be exactly what you want.

Well, actually it raises another question: is it alright for the ongoing
transaction not to see any changes it makes reflected in your index?

II. Is there any chance of wrapping your work in a function, so you can
then create an index on the result of that function? I've never tried
this but presumably the server would then do all the work to keep your
index updated, without any need for triggers and such.

This is no different from what you'd do if you wanted, say, an index on an
upper-cased version of a text field to speed up case-insensitive searches.
You create an index on "TOUPPER(name)" or whatever it is, and then when
you select on "WHERE TOUPPER(name)=TOUPPER(searchstring)" you get full use
of the index, which you wouldn't get from a regular index on "name".

Jeroen

My read is such that he is using Senna as follows (where external means
outside of PostgreSQL):

1. Create external FTI
2. BEGIN TRANS
3a. Insert record into PostgreSQL
3b. Insert record into Senna
4. Commit

His problem is that the PostgreSQL record (3a) *could* be rolled-back after
he adds them to the Senna index (it would depend what else he's doing in the
transaction). Although, I don't see why he wouldn't just be able to keep a
list of UNDO-like information (DELETEs) for the Senna INSERTs and do this
check at commit-time rather than needing some kind of success/failure
notification; it seems like he would have to do this somehow anyway.

Daisuke Maki wrote:

Hi,

First, apologies if my question is a bit off-course. Please feel free to
direct me to a different mailing list if not appropriate.

I'm currently trying to embed Senna full text search engine
(http://qwik.jp/senna/) into postgres. I'm trying to achieve this by
using triggers (implemented in C) to cause an update to senna's index at
various points.

This seemed to work fine until I realized that while postgres' SQL
commands could be rolled back, Senna's index remained already-changed.
There are other potential issues with regards to transaction safety, but
currently this seems to be a problem that I cannot fix by simply
patching Senna. So I thought that if there was a rollback trigger, I
could call whatever necessary to undo the changes that were made to the
index.

A quick scan of the docs and the source code tree seems to indicate that
there is no such thing as a rollback trigger, short of hacking it.

Now, I'm wondering:
1. Is there a rollback/commit trigger? If not, is it planned to be
implemented at all?
2. Is there a way to undo changes to data external to postgres
when a rollback occurs, OR, only update that external data
when a commit occurs?

Thanks in advance,
--d

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hello Daisuke,

would it work to change from "rollback triggers" and use two-phase commit?
You could try something like:

1. Insert/Update/Delete in postgres
2. Prepare your postgres commit
3. Update your senna index
4a. If the senna index update succeeded, commit in postgres
4b. If the senna index update failed, rollback in postgres

mark