Fun with nested transactions in PL/pgSQL

From: "Jonathan M(dot) Gardner" <jgardner(at)jonathangardner(dot)net>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Fun with nested transactions in PL/pgSQL
Date: 2004-07-17 19:20:27
Message-ID: 200407171220.29776.jgardner@jonathangardner.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

-----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 while processing 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(at)jonathangardner(dot)net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2004-07-17 20:15:21 Re: [HACKERS] Point in Time Recovery
Previous Message Tom Lane 2004-07-17 19:18:26 Re: [HACKERS] Point in Time Recovery