[CDBI] Inserts fail after upgrading PostgreSQL from 7.4 to 8.2
Lars Haugseth <[email protected]> Tue, 11 Dec 2007 11:14:43 +0100
| Newsgroups | gmane.comp.lang.perl.modules.class-dbi |
|---|---|
| Message-ID | <[email protected]> |
I'm looking into a problem with an application using Class::DBI version 0.96. Until now, the application has been running on a PostgreSQL 7.4 database. For several reasons, we wish to upgrade the database to run on PostgreSQL 8.2. However, upgrading the database breaks all inserts being made. Here's a sample error message: Can't insert new AdGen::Properties: DBD::Pg::st execute failed: ERROR: null value in column "propid" violates not-null constraint [for Statement "INSERT INTO properties (proptype, proptnr, proppostcode, area, propid, propowner, propname, propcustomer) VALUES (?, ?, ?, ?, ?, ?, ?, ?) " with ParamValues: 6='295', 3='0266', 7='test', 2='T1562-test', 8='1464', 1='1', 4='2', 5=undef] at /usr/local/share/perl/5.8.8/DBIx/ContextualFetch.pm line 52. Stack: [/usr/local/share/perl/5.8.8/DBIx/ContextualFetch.pm:52] [/usr/local/share/perl/5.8.8/DBIx/ContextualFetch.pm:32] [/usr/local/share/perl/5.8.8/Class/DBI.pm:636] [/usr/local/share/perl/5.8.8/Class/DBI.pm:590] [/usr/local/share/perl/5.8.8/Class/DBI.pm:470] [/storage/adportal/templates/adgen/new/index.html:101] The primary key is 'propid', which is of type serial (an integer with a corresponding autoincrementing sequence). The problem is that when doing insert statements, Class::DBI tries to set the column 'propid' to NULL/undef. This happened to work in PostgreSQL 7.4, but this will fail in 8.2. The proper way would be to not assign to the primary key column at all in the insert statement, or alternatively use the nextval()-function to set the value. Is there a known fix for this problem, or any hints of how to go about fixing it? Regards, -- Lars Haugseth