[rt.cpan.org #20696] last_insert_id() fails if schema is not in search_path

       Queue: DBD-Pg
 Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=20696 >

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
[email protected] 

On Wed Jul 26 10:55:23 2006, jrsupplee wrote:
> 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

_______________________________________________
Dbdpg-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/dbdpg-general

--- 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];