Blagoj Petrushev wrote > Hi, > > I'm thinking of an extension to trigger functionality like this: > > CREATE TRIGGER trigger_name > AFTER event > ON table > CONCURRENTLY EXECUTE PROCEDURE trigger_fc > > This would call the trigger after the end of the transaction. > > The following is a use-case, please tell me if I'm doing it wrong. > > I have a big table with big text column article and a nullable > tsvector column fts_article. On each insert or update that changes the > article, I trigger-issue 'NOTIFY article_changed row_id', then, with a > daemon listener, I catch the notification and update fts_article > accordingly with my_fts_fc(article). The reason I don't do this > directly in my trigger is because my_fts_fc is slow for big articles, > fts_article has a gin index, and also, on heavy load, my listener can > do these updates concurrently. Now, with a concurrent execution of > triggers, I can just call my_fts_fc inside the trigger instead of the > notify roundtrip.
Conceptually, trigger actions run in-transaction and can cause it to ROLLBACK; so how would "after the end of the transaction" work? Since the easy way is to have COMMIT; block until all the AFTER event concurrent triggers fire I presume you would want something more like a task queue for background workers where, at commit, the function call is in place in a FIFO queue and the calling session is allowed to move onto other activity. It is not clear what you mean by "my listener can do these updates concurrently"? Concurrently with each other or concurrently with other DML action on table? I assume you have multiple listeners since the potential rate of insert of the documents is likely much greater than the rate of update/indexing. Also, it would seem you'd typically want the GIN index to be updated once the corresponding transaction committed and makes the rest of the data available. Or does your use case allow for some delay between the article being in the database physically and it being available in the index? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Trigger-concurrent-execution-tp5804158p5804164.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers