PostgreSQL, serial data types and last_insert_id

[jfraire]

Hello Laurent!

First off, congratulations for your fantastic ORM. I found it very
usable and easy to understand. A big thank you. I use it with
PostgreSQL and I love it.

For some reason I started getting this error in my application log:
ERROR REPORTED: FAILED TRANSACTION: DBD::Pg::db last_insert_id failed:
last_insert_id needs at least a sequence or table name at /usr/local/
share/perl/5.8.8/DBIx/DataModel/Table.pm line 245

That line corresponds to the _get_last_insert_id method which is used
in connection with SERIAL fields. My problem was that, because I did
not declare the database schema when I fed the $dbh to my
DBIx::DataModel::Schema, I was falling into the third case for getting
the last_insert_id:

# or plain call to last_insert_id() with all undefs
: $dbh->last_insert_id(undef, undef, undef, undef);

The last_insert_id method of $dbh is implemented by DBD::Pg. It
queries the database looking for any SERIAL cols in a given table.
Because all the params to $dbh->last_insert_id are undefined, DBD::Pg
does not know what we are talking about and reports that it does not
know what table or sequence to query.

So, would you please change the third and fourth undefs for $table,
$col? You are already using them in the third option. I guess $col is
the primary key of the table:

# or plain call to last_insert_id() with all undefs
: $dbh->last_insert_id(undef, undef, $table, $col);

I fixed my problem by doing this:
My::Schema->dbh( $dbh, schema => 'public');
by which I got the second option to look like this:
$dbh->last_insert_id(undef, 'public', $table, $col);

Thank you again and best regards,

Julio Fraire

[Dami Laurent (PJ)]

>-----Message d'origine-----
>De : dbix-da...@googlegroups.com [mailto:dbix-
>data...@googlegroups.com] De la part de jfraire
>Envoyé : jeudi, 23. septembre 2010 10:09
>À : dbix-datamodel
>Objet : [dbix-datamodel] PostgreSQL, serial data types and
>last_insert_id

>
>Hello Laurent!
>
>First off, congratulations for your fantastic ORM. I found it very
>usable and easy to understand. A big thank you. I use it with
>PostgreSQL and I love it.

Thanks, I'm always happy to hear from users. If you could find some time to repeat your appreciation as a report in cpanratings.perl.org, that would help DBIx::DataModel to become more well-known. Or write a blog entry explaining why you chose DBIxDM instead of DBIx::Class. DBIxDM is in great need of some publicity !

>
>So, would you please change the third and fourth undefs for $table,
>$col? You are already using them in the third option. I guess $col is
>the primary key of the table:
>
> # or plain call to last_insert_id() with all undefs
> : $dbh->last_insert_id(undef, undef, $table, $col);

Actually, I had something like this in a previous version ... but then some drivers didn't like getting $table and $col arguments without a schema or catalog! So this is why I reverted to passing all undefs, which is somehow the "standard default" defined by DBI. If this is not convenient, there are some options to tune it, as you already discovered.

>
>I fixed my problem by doing this:
>My::Schema->dbh( $dbh, schema => 'public');
>by which I got the second option to look like this:
> $dbh->last_insert_id(undef, 'public', $table, $col);

Good, the "schema" or "catalog" options were especially meant for that kind of usage, so I'm happy if it works. Another way would be to write your own last_id handler, like this :

My::Schema->dbh($dbh, last_insert_id => sub {
my ($dbh, $table, $col) = @_;
$dbh->last_insert_id(undef, 'public', $table, $col); # or whatever code you like
});

Best regards,

Laurent Dami