Thread: PL/pgSQL equivalent to PQtransactionStatus?
My apologies if this is in the docs and I missed it, but is there a PL/pgSQL function equivalent for the pglib function PQtransactionStatus (i.e., a way to find out if we're in an open transaction block, and if that transaction is in an error status)?
Christophe wrote: > My apologies if this is in the docs and I missed it, but is there a > PL/pgSQL function equivalent for the pglib function PQtransactionStatus > (i.e., a way to find out if we're in an open transaction block, and if > that transaction is in an error status)? A pl/pgsql function *always* executes within a transaction. If an error occurs while that function is executing you can catch the exception (see the "Trapping Errors" section of the pl/pgsql docs). If an error occurs before the function executes then no other statements will be executed. HTH -- Richard Huxton Archonet Ltd
On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote: > A pl/pgsql function *always* executes within a transaction. Indeed so. What I'm looking for is a way of detecting if a transaction block has been opened (i.e., we're within a BEGIN).
Christophe wrote: > > On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote: >> A pl/pgsql function *always* executes within a transaction. > > Indeed so. What I'm looking for is a way of detecting if a transaction > block has been opened (i.e., we're within a BEGIN). Why does it matter? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: > Why does it matter? I'm attempting to "clean out" a connection that is in an unknown state (along the lines of what pgpool does when reusing an open connection). Of course, I could just fire an ABORT down, but it seems nicer to avoid doing so if no transaction block is open.
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: > Why does it matter? Ah, I see, deep confusing on my part regarding PL/pgSQL and tranasctions! Ignore question. :)
Christophe wrote: > > On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote: >> A pl/pgsql function *always* executes within a transaction. > > Indeed so. What I'm looking for is a way of detecting if a transaction > block has been opened (i.e., we're within a BEGIN). There is no difference between a transaction explicitly started with BEGIN...COMMIT and one wrapping a single statement. What were you planning to do differently if a BEGIN was issued? -- Richard Huxton Archonet Ltd
Christophe wrote: > > On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: >> Why does it matter? > > I'm attempting to "clean out" a connection that is in an unknown state > (along the lines of what pgpool does when reusing an open connection). > Of course, I could just fire an ABORT down, but it seems nicer to avoid > doing so if no transaction block is open. Maybe DISCARD ALL does what you want? http://www.postgresql.org/docs/8.3/static/sql-discard.html -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.