Re: [HACKERS] ROLLBACK triggers?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ROLLBACK triggers?
My read is such that he is using Senna as follows (where external means outside of PostgreSQL):1. Create external FTI2. BEGIN TRANS3a. Insert record into PostgreSQL3b. Insert record into Senna4. 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 [EMAIL PROTECTED] 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 yousure that:I. The triggers really do arrive even when the modifications are aborted? AFAIK triggers that were, er, triggered during a transaction only reallyget notified once the transaction commits.In psql:= LISTEN x;LISTEN= BEGIN;BEGIN= NOTIFY x;NOTIFY = ABORT;ROLLBACK= BEGIN;BEGIN= NOTIFY x;NOTIFY= COMMIT;COMMITAsynchronous 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 ongoingtransaction 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 triedthis but presumably the server would then do all the work to keep yourindex 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 whenyou 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
[HACKERS] 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 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
Re: [HACKERS] 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 1/23/06, Daisuke Maki [EMAIL PROTECTED] wrote: Hi,First, apologies if my question is a bit off-course. Please feel free todirect 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 byusing triggers (implemented in C) to cause an update to senna's index atvarious 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, butcurrently this seems to be a problem that I cannot fix by simply patching Senna. So I thought that if there was a rollback trigger, Icould call whatever necessary to undo the changes that were made to theindex.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
Re: [HACKERS] ROLLBACK triggers?
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ROLLBACK triggers?
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 [EMAIL PROTECTED] 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 .
Re: [HACKERS] ROLLBACK triggers?
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. -JonahOn 1/23/06, Stef T [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] 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 .
Re: [HACKERS] ROLLBACK triggers?
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
