Re: ROLLBACK triggers?

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