[Dbix-class] bug with find_or_new and update_or_insert, for postgres DB

[Patrick Weemeeuw]

On Monday 07 January 2008 10:43:49 Jess Robinson wrote:
> On Sun, 6 Jan 2008, Patrick Weemeeuw wrote:
> > | create table article (
> > |   artId           integer        NOT NULL DEFAULT
> > | nextval('article_artid_seq'), day             char(8)        NOT NULL,
> > |   seqnr           integer        NOT NULL,
> > |   visibility      char(1)        NOT NULL CHECK ( visibility = 'P' OR
> > | visibility = 'R' OR visibility = '' ), title           varchar(100)  
> > | NOT NULL, -- ftext
> > |   body            text           NOT NULL, -- ftext
> > |   UNIQUE (day,seqnr),
> > |   UNIQUE (artid)
> > | );
> > |
> > | my $art = $schema->resultset('Article')->find_or_new( { artid => undef
> > | } );
> > |
> > | $ perl -I ../lib testme3.pl
> > | 0.08008
> > | DBIx::Class::Row::update_or_insert(): DBI Exception: DBD::Pg::st
> > | execute failed: ERROR:  null value in column "artid" violates not-null
> > | constraint [for Statement "INSERT INTO article (artid, body, day,
> > | seqnr, title, visibility) VALUES (?, ?, ?, ?, ?, ?)" with ParamValues:
> > | 6='P', 4='3', 1=undef, 3='20022222', 2='bbb', 5='ttt'] at testme3.pl
> > | line 30
>
> Why are you explicitly setting artid => undef, which translates to artid =
> NULL ? Just dont supply that column at all, when you want the database to
> supply the default value.
>
> Jess
>

The larger context is a web application, where the same web form and code is 
used both for creating a new row and updating an existing one (the example is 
similar to one from the catalyst book [Jonathan Rockway], and apparently 
works for some DB type other than postgres). When creating a new entry, we 
enter the procedure with the id undefined. After filling in / updating the 
row, we commit the changes with update_or_insert. It's actually nice and 
clean code, until one starts wondering about the exact semantics.

So, I don't know whether the find_or_new method should create an embryonic row 
that satisfies the conditions of the where clause, or not. In the first case 
the where-clause values are copied to the row, in the other case they are 
not. And I am certainly not confident enough in SQL semantics to have much of 
an opinion what should happen with an undef/NULL value in the where clause.

-- Patrick