Thread: Changing a trigger function
I have found a nasty bug in one of the trigger function I did put into production a couple of days ago. I have a fix for it but wonder if I can put it into the production database with users connected on it. What would happen if someone sends an update firing that trigger whilst I am in the middle of updating it via a create or update ? Do I need to wait for this evening or can I safely runs the update of the function ? I know that several other databases would reject the update (Sybase, Firebird for example) but I am too new to PostgresSQL to know what would happen in such a situation Didier
am Thu, dem 24.04.2008, um 11:08:12 +0200 mailte Didier Gasser-Morlay folgendes: > I have found a nasty bug in one of the trigger function I did put into > production a couple of days ago. I have a fix for it but wonder if I > can put it into the production database with users connected on it. > > What would happen if someone sends an update firing that trigger > whilst I am in the middle of updating it via a create or update ? Do > I need to wait for this evening or can I safely runs the update of the > function ? All works within a TRANSACTION. If you change the function while the old version are running for an other transaction, the other transaction has the old version until the end. In other words: you can change the function without problems. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thursday 24 April 2008 05:08, Didier Gasser-Morlay wrote: > I have found a nasty bug in one of the trigger function I did put into > production a couple of days ago. I have a fix for it but wonder if I > can put it into the production database with users connected on it. > > What would happen if someone sends an update firing that trigger > whilst I am in the middle of updating it via a create or update ? Do > I need to wait for this evening or can I safely runs the update of the > function ? > > I know that several other databases would reject the update (Sybase, > Firebird for example) but I am too new to PostgresSQL to know what > would happen in such a situation > > > > Didier You do not have to wait to load the trigger. If some process accesses the trigger in the midst of your loading it, it will be handled in the same way as someone reading a set of records from a table while another is inserting or updating records in the same table; that is, the data as it existed before the transaction started would be used. Loading the trigger will behave the same way. We've done this many times. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 terry@turbocorp.com www.turbocorp.com
"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> am Thu, dem 24.04.2008, um 11:08:12 +0200 mailte Didier Gasser-Morlay folgendes:
>> What would happen if someone sends an update firing that trigger
>> whilst I am in the middle of updating it via a create or update ? Do
>> I need to wait for this evening or can I safely runs the update of the
>> function ?
> All works within a TRANSACTION. If you change the function while the old
> version are running for an other transaction, the other transaction has
> the old version until the end.
Actually I believe that stuff works on SnapshotNow, meaning that the new
definition will be absorbed at the next call of the function after you
commit the CREATE OR REPLACE FUNCTION command. Existing active calls
will continue to use the old function definition till they finish.
For plpgsql functions, there were some bugs in this in 8.1.0-8.1.6
and 8.2.0-8.2.1, so there's some risk of a problem if you're running one
of those versions.
regards, tom lane
Andreas, Tom, Terry, the more I discover about PostgresSQL the more I like it ! Thanks for the help, Didier