Date: Fri, 24 Jul 2009 12:54:31 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Joshua Tolley <eggyknap@gmail.com>
Cc: Glenn Maynard <glenn@zewt.org>, Richard Huxton <dev@archonet.com>,
	pgsql-sql@postgresql.org
Subject: Re: Bit by "commands ignored until end of transaction block"
 again
Message-ID: <20090724165431.GB15591@alvh.no-ip.org>
References: <bd36f99e0907222158k6b4b3a0al65766c0d9c4906cf@mail.gmail.com>
	<4A67F5B6.4080409@archonet.com>
	<bd36f99e0907222304x38cfa50bkeba19f7c9b2694@mail.gmail.com>
	<20090723064656.GO25450@eddie>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20090723064656.GO25450@eddie>
User-Agent: Mutt/1.5.20 (2009-06-14)

Joshua Tolley escribió:

> Have you tried this? I expect if you give it a shot, you'll find you don't
> actually have this problem. Really, everything is always in a transaction. If
> you haven't explicitly opened one, PostgreSQL opens one for you before each
> statement, and issues a COMMIT afterwards (or a ROLLBACK, if your statement
> ran into an error). Statements within functions are always executed within the
> same transaction, so you can issue SAVEPOINT commands anywhere in PL/pgSQL
> functions without problems, because you're always in a transaction.

No, actually you can't call SAVEPOINT inside a PL/pgSQL function (or any
SPI user for that matter -- have you tried savepoints in LOLCODE?)
Inside PL/pgSQL the only way to use savepoints is with EXCEPTION blocks.

You are correct that you're always in a transaction, but in this context
not all transactions are equal :-(

(The problem, as we found out, is that the function must always have
control at the same level of transaction nestedness in SPI; you can't
just let the user define and release savepoints arbitrarily.)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.