[Dbix-class] find_or_create problem with Pg: proposed patch

[Alex Povolotsky]

Nigel Metheringham wrote:
>
> On 3 Apr 2008, at 16:30, Alex Povolotsky wrote:
>> Attempt to insert into postgres table with undef's for columns with 
>> default values yields an error. It is especially bad with 
>> find_or_create sub.
>>
>> I've made and tested a simple patch, it looks working ok.
>
>
> So the problem is that when you *explicitly* set column values to 
> undef (NULL), DBIC does not magically put default values in place?
Well, find_or_create initially tries to insert NULL as a primary key. 
Postgres fails on this. I do not know if it's a Postrges fault, or it is 
not specified in SQL standards, or something else; I just know that this 
patch fix problem with Postgres.
>
> Adding that (and the patch only touches Pg - thus making Pg behave 
> differently to all the other databases) appears like too much (or 
> inappropriate) magic to me.
>
> I'd be happier with something that generically puts default values in 
> when the key/value to insert was missed out altogether, but in general 
> I tend to let the DB do that.  At present it looks like default_value 
> is used only by deploy.
Well, it can be done somehow; trivially $to_insert->{$col}= 
$colinfo->{default_value}did not work. If someone more experienced in 
SQL will tell me that it is a Postgres fault and Postgres itself must be 
fixed, or that MySQL and SQLite behaves incorrectly (it's far too 
possible!) - I'd be glad.

Alex.