Обсуждение: pgsql & transaction doubt
Hi all, a little doubt that came into my mind reading the official manual (chapter 37, page 677): "functions and trigger procedures are always executed within a transaction established by an outer query". What does "outer" means? If I'd like a function to be execute within a transaction should I manually begin and end the transaction or is the procedure code always executed in a kind of subtransaction? I mean, supposing a function raises an exception, what happens to all the sql statement issued by the function before the exception? Are they automatically rollbacked? Thanks, Luca
am Fri, dem 26.01.2007, um 8:39:16 +0100 mailte Luca Ferrari folgendes: > Hi all, > a little doubt that came into my mind reading the official manual (chapter 37, > page 677): "functions and trigger procedures are always executed within a > transaction established by an outer query". What does "outer" means? If I'd > like a function to be execute within a transaction should I manually begin > and end the transaction or is the procedure code always executed in a kind of > subtransaction? I mean, supposing a function raises an exception, what > happens to all the sql statement issued by the function before the exception? > Are they automatically rollbacked? If you call a function "select my_function(param1, param2);", then is this select the 'outer' query and starts a transaction. An exception within the function rollback the whole function. If you have a code like this: begin; insert into foo ... select my_function(param1, param2); commit; and the function fails, then fails also the insert before your function. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Assuming we have well over 1000 users accessing the database through a web application. We have a row-level trigger set up on table "alpha" that gets executed after updates. I was wondering if there is a way to temporarily disable a trigger on table "alpha" for a single user, even though there may be 999 other users sending transactions to the database that need the trigger to remain in place? For example, I only want the trigger disabled for one single transaction (using commands inside that transaction) while the trigger still gets fired properly for the other 999 transactions that hit the database simultaneously. Is this possible at present? Thank you for your insights, Derrick
I don't know if there's a clever way to do it, but the user can be identified via the pseudo-column user. e.g. SELECT DISTINCT user FROM team; current_user -------------- dev (1 row) ( user is not a column in team. ) You could incorporate that into the trigger logic. Regards Duncan -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Derrick Betts Sent: 27 January 2007 16:49 To: pgsql-novice@postgresql.org Subject: [NOVICE] Enable/Disable Trigger per single transaction Assuming we have well over 1000 users accessing the database through a web application. We have a row-level trigger set up on table "alpha" that gets executed after updates. I was wondering if there is a way to temporarily disable a trigger on table "alpha" for a single user, even though there may be 999 other users sending transactions to the database that need the trigger to remain in place? For example, I only want the trigger disabled for one single transaction (using commands inside that transaction) while the trigger still gets fired properly for the other 999 transactions that hit the database simultaneously. Is this possible at present? Thank you for your insights, Derrick ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend