[Dbix-class] Weird INSERT RETURNING error after updating to DBIx::Class .08123

[Peter Rabbitson]

On Sun, Jul 11, 2010 at 06:11:22PM -0700, Steve Kleiman wrote:
> Searched the archives on this and didn't see anyone else raise the issue....
> 
> Just upgraded to DBIx::Class .08123 from 08120.
> 
> I see in the Changelog:
> 
> > DBIx::Class
> > 0.08121 2010-04-11 18:43:00 (UTC)
> >        - Add core support for INSERT RETURNING (for storages that supports this syntax, currently PostgreSQL and Firebird)
> 
> I'm using Postgres. Before the update, a simple create query was dispatched as:
> > INSERT INTO "project"."vendor_view" ( "name", "project", "classification_xref") VALUES ( 1277144170, 3819016, 10 );
> 
> 
> That worked great and returned the last_insert_id as expected.
> 
> After upgrading to DBIx::Class . 08123, the SQL generated is now:
> > INSERT INTO "project"."vendor_view" ( "name", "project", "classification_xref") VALUES ( 1277144170, 3819016, 10 ) RETURNING id
> 
> 
> And that throws the error:
> > ERROR:  cannot perform INSERT RETURNING on relation "vendor_view"
> > HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.
> 
> I see in DBIX::Class::Storage::DBI::Pg the comment:
> > # only used when INSERT ... RETURNING is disabled
> > sub last_insert_id {
> > ...
> > }
> 
> Seems to me I want to disable the RETURNING functionality to make the behavior like it was, but I can't figure out how to do that.
> 

Sorry for the late reply. This is an unfortunate oversight, and to make matters
worse the code to disable a specific capability has not been thought out
very well either. Please check out the following proof-of-concept branch [1]
and try to do: $schema->storage->_use_insert_returning(0) to selectively
disable the behavior where you need it. When I have more time eor OSS
stuff (mid Aug) I will look into solving the general problem of writable
views in pg.

Cheers

[1] git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git
    branch: people/riba/dbh_capability_refactor