| Subject: | last_insert_id fails on non-schema-local sequences |
| Date: | Fri, 30 Mar 2007 11:59:55 +0200 |
| To: | bug-dbd-pg [...] rt.cpan.org |
| From: | Jens-Wolfhard Schicke <j.schicke [...] asco.de> |
Hi,
we have a table defined as
db=# set search_path = entwurf;
SET
db=# \d users;
Table "entwurf.users"
Column | Type |
Modifiers
------------------------+-----------------------------+----------------------------------------------------------
user_id | integer | not null default
nextval('public.user_id_seq'::regclass)
...
Indexes:
"pk_users" PRIMARY KEY, btree (user_id)
If the DBD::Pg connection has also set search_path = entwurf, and is
therefore accessing this table, last_insert_id fails, because the
catalog-reading query selects adsrc and not pg_get_expr(adbin, adrelid) and
thereby misses the schema qualifier on the sequence name.
Patch (works for us):
--- /var/autofs/net/pizza3/usr/local/lib/perl/5.8.8/DBD/Pg.pm 2006-05-05
17:20:15.000000000 +0200
+++ Pg.pm 2007-03-30 11:27:56.000000000 +0200
@@ -268,7 +268,7 @@
}
my $oid = $sth->fetchall_arrayref()->[0][0];
## 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,
substring(pg_get_expr(d.adbin, d.adrelid) for 128) AS def\n".
"FROM ${DBD::Pg::dr::CATALOG}pg_index i,
${DBD::Pg::dr::CATALOG}pg_attribute a, ${DBD::Pg::dr::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".
Best regards,
Jens Schicke
--
Jens Schicke j.schicke@asco.de
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400