Re: ROLLBACK triggers?
| От | Jonah H. Harris |
|---|---|
| Тема | Re: ROLLBACK triggers? |
| Дата | |
| Msg-id | 36e682920601240448m1cda7e96n6a3c90cbbdef1998@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: ROLLBACK triggers? ("Jeroen T. Vermeulen" <jtv@xs4all.nl>) |
| Список | pgsql-hackers |
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.
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@xs4all.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
В списке pgsql-hackers по дате отправления: