Skip Menu |

This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id: 20696
Status: resolved
Priority: 0/
Queue: DBD-Pg

People
Owner: Nobody in particular
Requestors: john [...] supplee.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 1.49
Fixed in: 2.0.0



Subject: last_insert_id() fails if schema is not in search_path
The last_insert_id function fails if the table's schema name is not in search_path. I have patched my version of Pg.pm (ver 1.49) which used to look like this: 308: $sth = $dbh->prepare("SELECT currval(?)"); 309: $sth->execute($sequence); to look like this: 308: $sth = $dbh->prepare("SELECT currval(?)"); 309: $sequence = "$schema.$sequence" if $schema; 310: $sth->execute($sequence); Everything is fine now. I think this bug should be fixed because not only is last_insert_id() broken tables whose schema is not in search_path, but it is possible that last_insert_id() could return the value for the wrong table if two schemas have tables with the same name. John Supplee
From: ivan.wills [...] gmail.com
I just tried this patch and it does not cure my problem with DBD::Pg. I had to modify it further to get it to work as sometimes DBD::Pg puts the schema name in (or is it some times it does not remove the schema name?). My version of the patch looks like: Orig: 308: $sth = $dbh->prepare("SELECT currval(?)"); 309: $sth->execute($sequence); New: 308: $sth = $dbh->prepare("SELECT currval(?)"); 309: $sequence = "$schema.$sequence" if $schema && $sequence !~ /[.]/; 310: $sth->execute($sequence); this probably means that there is some deeper problem in the way the schema is being determined (or not). Ivan PS I think that this bug is in responce to my email to dbdpg-general@gborg.postgresql.org On Wed Jul 26 10:55:23 2006, jrsupplee wrote: Show quoted text
> The last_insert_id function fails if the table's schema name is not in > search_path. I have patched my version of Pg.pm (ver 1.49) which used > to look like this: > > 308: $sth = $dbh->prepare("SELECT currval(?)"); > 309: $sth->execute($sequence); > > to look like this: > > 308: $sth = $dbh->prepare("SELECT currval(?)"); > 309: $sequence = "$schema.$sequence" if $schema; > 310: $sth->execute($sequence); > > Everything is fine now. I think this bug should be fixed because not > only is last_insert_id() broken tables whose schema is not in > search_path, but it is possible that last_insert_id() could return the > value for the wrong table if two schemas have tables with the same > name. > > John Supplee
--- Pg.pm 2006-08-01 17:12:12.000000000 +1000 +++ Pg.pm.new 2006-08-01 17:11:56.000000000 +1000 @@ -306,6 +306,9 @@ } $sth = $dbh->prepare("SELECT currval(?)"); + $sequence = "$schema.$sequence" if $schema && $sequence !~ /[.]/; $sth->execute($sequence); return $sth->fetchall_arrayref()->[0][0];