Thread: Fun with nested transactions in PL/pgSQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Check out this gem.

=> CREATE TABLE t (i int);

=> CREATE OR REPLACE FUNCTION test() RETURNS VOID
LANGUAGE 'plpgsql' AS '
BEGIN INSERT INTO t VALUES (1); EXECUTE ''BEGIN''; DELETE FROM t; EXECUTE ''ROLLBACK''; RETURN;
END
';

=> SELECT test();
server closed the connection unexpectedly       This probably means the server terminated abnormally       before or
whileprocessing the request. 
The connection to the server was lost. Attempting reset: Failed.

In the log:
LOG:  server process (PID 23748) was terminated by signal 11
LOG:  terminating any other active server processes
LOG:  background writer process (PID 23740) exited with exit code 1
FATAL:  the database system is in recovery mode
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-07-17 12:18:09 PDT
LOG:  checkpoint record is at 0/B116FC
LOG:  redo record is at 0/B116FC; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 565; next OID: 25419
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/B1173C
LOG:  redo is not required
LOG:  database system is ready


- --
Jonathan Gardner
jgardner@jonathangardner.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFA+Xv7qp6r/MVGlwwRAm4ZAKCJe11K5vYNtSAbS/VbqBMM6G+YcACguref
8T4f1oQ7gjaNak5s6WhIdwU=
=eEU+
-----END PGP SIGNATURE-----

On Sat, Jul 17, 2004 at 12:20:27PM -0700, Jonathan M. Gardner wrote:

> => CREATE TABLE t (i int);
> 
> => CREATE OR REPLACE FUNCTION test() RETURNS VOID
> LANGUAGE 'plpgsql' AS '
> BEGIN
>   INSERT INTO t VALUES (1);
>   EXECUTE ''BEGIN'';
>   DELETE FROM t;
>   EXECUTE ''ROLLBACK'';
>   RETURN;
> END
> ';
> 
> => SELECT test();
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.

I described this behavior not less than two weeks ago, including why it
happens.  Try "begin; select test(); commit".

The submitted savepoint patch prevents this from happening, and some
other misbehaviors as well.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I personally became interested in Linux while I was dating an English major
who wouldn't know an operating system if it walked up and bit him."
(Val Henson)