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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

"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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice