Sim Zacks <sim@xxxxxxxxxxxxxx> writes: > Is there any way to write a statement trigger that runs in the > background? In my testing, when I have an After Insert For Each > Statement trigger, the function call does not end until the trigger is > finished processing. > > What I would like to do, and please let me know if there is a better > way to do this, is to have an after event statement trigger run in a > separate process so the function call ends when it ends and the > statement trigger gets executed on its own time. > > The use that I have for this at the moment, and I can think of many > other uses, is that I want to populate a statistics table each time > that a table is updated. But the code to populate the table takes 10 > seconds to run. I don't want the user to have to wait 10 seconds to > add a record. This seems a case for using NOTIFY/LISTEN. - You have a process connected to the database that runs LISTEN, causing it to listen for a particular message. LISTEN regen_statistics; - Your trigger submits a notification: NOTIFY regen_statistics; The notification doesn't get committed to internal table pg_catalog.pg_listener until the transaction doing the writing completes its COMMIT, so the listener won't "hear" anything until then... Per the docs: The method a frontend application must use to detect notify events depends on which Postgres application programming interface it uses. With the basic libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the routine PQnotifies to find out whether any notify events have been received. Other interfaces such as libpgtcl provide higher-level methods for handling notify events; indeed, with libpgtcl the application programmer should not even issue LISTEN or UNLISTEN directly. See the documentation for the library you are using for more details. -- select 'cbbrowne' || '@' || 'ntlug.org'; http://cbbrowne.com/info/multiplexor.html "I am a bomb technician. If you see me running, try to keep up..."
