The following bug has been logged online:
Bug reference: 2078
Logged by: Pierre Beyssac
Email address: postgres@fasterix.frmug.org
PostgreSQL version: 8.1.0
Operating system: FreeBSD 4.11
Description: lock freeing problem in transaction (?)
Details:
The following code causes the server to keep a RowShared lock, even after
the corresponding connection has been closed.
1) create the following tables
CREATE TABLE d ( id SERIAL PRIMARY KEY );
CREATE TABLE dc (
d_id INTEGER REFERENCES d(id),
c_id INTEGER
);
2) run the following Perl script twice (replace $base with appropriate db
name). Note: the "currval('xyz')" is wrong, this is on purpose to
demonstrate the problem.
#!/usr/bin/perl
use DBI;
use strict;
my $dbh;
my $base = "mabase";
my ($sel_d, $ins_dc);
print "A\n";
$dbh = DBI->connect("dbi:Pg:dbname=$base", "", "", {AutoCommit => 0});
$sel_d = $dbh->prepare("SELECT id FROM d WHERE id=?");
$ins_dc = $dbh->prepare("INSERT INTO dc (d_id,c_id) VALUES (?,(SELECT
currval('xyz')))");
$sel_d->execute(1);
$ins_dc->execute(1);
$sel_d->execute(1);
$dbh->disconnect;
print "B\n";
$dbh = DBI->connect("dbi:Pg:dbname=$base", "", "", {AutoCommit => 0});
$sel_d = $dbh->prepare("SELECT id FROM d WHERE id=?");
$sel_d->execute(1);
$dbh->commit;
exit 0;
3) first run yields the following. Note wrong output regarding
AccessShareLock after "B". Also note that the type of lock can change
depending on the underlying SQL code run during the transaction.
A
DBD::Pg::st execute failed: ERROR: relation "xyz" does not exist
DBD::Pg::st execute failed: ERROR: current transaction is aborted, commands
ignored until end of transaction block
DBI::db=HASH(0x81cd4c0)->disconnect invalidates 1 active statement handle
(either destroy statement handles or call finish on them before
disconnecting) at ./mc.pl line 16.
B
DBD::Pg::st execute failed: ERROR: lock AccessShareLock on object
27381/27551/0 is already held
4) just in case, the second run and all subsequent runs yield the following,
until the server is restarted.
A
DBD::Pg::st execute failed: ERROR: lock AccessShareLock on object
27381/27551/0 is already held
DBD::Pg::st execute failed: ERROR: current transaction is aborted, commands
ignored until end of transaction block
DBD::Pg::st execute failed: ERROR: prepared statement "dbdpg_1" does not
exist
B
DBD::Pg::st execute failed: ERROR: lock AccessShareLock on object
27381/27551/0 is already held