Thread: ROLLBACK triggers?

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

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:

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 1/23/06, Daisuke Maki <daisuke@wafu.ne.jp> 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://wwwpostgresql.org/docs/faq

.

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