[CDBI] Inserts fail after upgrading PostgreSQL from 7.4 to 8.2

Lars Haugseth list+classdbi at larshaugseth.com
Tue Dec 11 10:14:43 GMT 2007


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



More information about the ClassDBI mailing list