Re: ROLLBACK triggers?

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.

On 1/23/06, Jeroen T. Vermeulen <jtv(at)xs4all(dot)nl> wrote:
>
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>