Author: turnstep
Date: Sun Jan 6 20:01:35 2008
New Revision: 10484
Modified:
DBD-Pg/trunk/Changes
DBD-Pg/trunk/Pg.pm
DBD-Pg/trunk/t/01setup.t
DBD-Pg/trunk/t/03dbmethod.t
Log:
Fix for bug #30924: change of sequence name causes last_insert_id to fail.
Use adbin instead of adsrc within the main function, not the use of pg_cache
in the docs, add specific tests for this bug, and add a few items to the
relation drops inside of 01setup.t
Modified: DBD-Pg/trunk/Changes
==============================================================================
--- DBD-Pg/trunk/Changes (original)
+++ DBD-Pg/trunk/Changes Sun Jan 6 20:01:35 2008
@@ -1,6 +1,11 @@
('GSM' is Greg Sabino Mullane, [EMAIL PROTECTED])
2.0.0
+ - Use adbin, not adsrc, when figuring out the sequence name for the
+ last_insert_id() method. This allows the function to work
properly
+ if the sequence name is changed. Not that {pg_cache=>0} should
be
+ passed to the function if you expect this might happen.
+ (CPAN bug #30924) [GSM]
- Use unsigned chars when parsing passed-in queries, preventing UTF-8
strings from ruining the prepare. UTF-16 may still cause
problems.
(CPAN bug #31577) [GSM]
Modified: DBD-Pg/trunk/Pg.pm
==============================================================================
--- DBD-Pg/trunk/Pg.pm (original)
+++ DBD-Pg/trunk/Pg.pm Sun Jan 6 20:01:35 2008
@@ -290,7 +290,7 @@
$oid =~ /(\d+)/ or die qq{OID was not numeric?!?\n};
$oid = $1;
## This table has a primary key. Is there a sequence
associated with it via a unique, indexed column?
- $SQL = "SELECT a.attname, i.indisprimary,
substring(d.adsrc for 128) AS def\n".
+ $SQL = "SELECT a.attname, i.indisprimary,
pg_catalog.pg_get_expr(adbin,adrelid)\n".
"FROM pg_catalog.pg_index i,
pg_catalog.pg_attribute a, pg_catalog.pg_attrdef d\n ".
"WHERE i.indrelid = $oid AND
d.adrelid=a.attrelid AND d.adnum=a.attnum\n".
" AND a.attrelid = $oid AND i.indisunique IS
TRUE\n".
@@ -2328,7 +2328,8 @@
meets these conditions, the primary key will be used. This involves some
looking up of things in the system table, so DBD::Pg will cache the sequence
name for susequent calls. If you need to disable this caching for some reason,
-you can control it via the C<pg_cache> attribute.
+(such as the sequence name changing), you can control it via the C<pg_cache>
+attribute.
Please keep in mind that this method is far from foolproof, so make your
script use it properly. Specifically, make sure that it is called
Modified: DBD-Pg/trunk/t/01setup.t
==============================================================================
--- DBD-Pg/trunk/t/01setup.t (original)
+++ DBD-Pg/trunk/t/01setup.t Sun Jan 6 20:01:35 2008
@@ -42,7 +42,27 @@
if (1==$count) {
$dbh->do(sprintf "DROP SEQUENCE %s%s", $schema ? "$schema." : '',
'dbd_pg_sequence');
}
+$SQL = "SELECT COUNT(*) FROM pg_class WHERE relname=?";
+$sth = $dbh->prepare($SQL);
+$sth->execute('dbd_pg_litest');
+$count = $sth->fetchall_arrayref()->[0][0];
+if (1==$count) {
+ $dbh->do("DROP TABLE dbd_pg_testli.dbd_pg_litest");
+}
+$sth->execute('dbd_pg_testseq');
+$count = $sth->fetchall_arrayref()->[0][0];
+if (1==$count) {
+ $dbh->do("DROP SEQUENCE dbd_pg_testli.dbd_pg_testseq");
+}
+# Remove test schemas
+$SQL = "SELECT COUNT(*) FROM pg_namespace WHERE nspname=?";
+$sth = $dbh->prepare($SQL);
+$sth->execute('dbd_pg_testli');
+$count = $sth->fetchall_arrayref()->[0][0];
+if (1==$count) {
+ $dbh->do("DROP SCHEMA dbd_pg_testli CASCADE");
+}
$dbh->do("CREATE SEQUENCE dbd_pg_sequence");
# If you add columns to this, please do not use reserved words!
@@ -69,6 +89,7 @@
$dbh->do("COMMENT ON COLUMN dbd_pg_test.id IS 'Bob is your uncle'");
# Double check that the file is there
+$sth = $dbh->prepare("SELECT 1 FROM pg_class WHERE relname = 'dbd_pg_test'");
$sth->execute();
$count = $sth->fetchall_arrayref()->[0][0];
is( $count, 1, 'Test table was successfully created')
Modified: DBD-Pg/trunk/t/03dbmethod.t
==============================================================================
--- DBD-Pg/trunk/t/03dbmethod.t (original)
+++ DBD-Pg/trunk/t/03dbmethod.t Sun Jan 6 20:01:35 2008
@@ -18,7 +18,7 @@
$|=1;
if (defined $ENV{DBI_DSN}) {
- plan tests => 200;
+ plan tests => 203;
}
else {
plan skip_all => 'Cannot run test unless DBI_DSN is defined. See the
README file';
@@ -35,7 +35,7 @@
$dbh->do("SET search_path TO " . $dbh->quote_identifier($schema));
}
-my ($SQL, $sth, $result, @result, $expected, $warning, $rows);
+my ($SQL, $sth, $result, @result, $expected, $warning, $rows, $t);
# Quick simple "tests"
@@ -100,18 +100,41 @@
};
ok( ! $@, 'DB handle method "last_insert_id" works when called twice (cached)
given a valid table');
+#$dbh->do("DROP SCHEMA IF EXISTS dbd_pg_testli CASCADE");
$dbh->do("CREATE SCHEMA dbd_pg_testli");
+$dbh->do("CREATE SEQUENCE dbd_pg_testli.dbd_pg_testseq");
$dbh->{Warn}=0;
-$dbh->do("CREATE TABLE dbd_pg_testli.litest(a serial primary key)");
+$dbh->do("CREATE TABLE dbd_pg_testli.dbd_pg_litest(a INTEGER PRIMARY KEY NOT
NULL DEFAULT nextval('dbd_pg_testli.dbd_pg_testseq'))");
$dbh->{Warn}=1;
-$dbh->do("INSERT INTO dbd_pg_testli.litest DEFAULT VALUES");
+$dbh->do("INSERT INTO dbd_pg_testli.dbd_pg_litest DEFAULT VALUES");
eval {
- $result = $dbh->last_insert_id(undef,'dbd_pg_testli','litest',undef);
+ $result =
$dbh->last_insert_id(undef,'dbd_pg_testli','dbd_pg_litest',undef);
};
is ($@, q{}, 'DB handle method "last_insert_id" works when called with a
schema not in the search path');
is ($result, 1, qq{Got 1});
+$dbh->commit();
+
+
+$t=qq{ DB handle method "last_insert_id" fails when the sequence name is
changed and cache is used};
+$dbh->do("ALTER SEQUENCE dbd_pg_testli.dbd_pg_testseq RENAME TO
dbd_pg_testseq2");
+$dbh->commit();
+eval {
+ $dbh->last_insert_id(undef,'dbd_pg_testli','dbd_pg_litest',undef);
+};
+like ($@, qr{last_insert_id}, $t);
+$dbh->rollback();
+
+$t=qq{ DB handle method "last_insert_id" fails when the sequence name is
changed and cache is turned off};
+$dbh->commit();
+eval {
+ $dbh->last_insert_id(undef,'dbd_pg_testli','dbd_pg_litest',undef,
{pg_cache=>0});
+};
+is ($@, q{}, $t);
+is ($result, 1, qq{Got 1});
+
-$dbh->do("DROP TABLE dbd_pg_testli.litest CASCADE");
+$dbh->do("DROP TABLE dbd_pg_testli.dbd_pg_litest CASCADE");
+$dbh->do("DROP SEQUENCE dbd_pg_testli.dbd_pg_testseq2");
$dbh->do("DROP SCHEMA dbd_pg_testli CASCADE");
#
