[Dbix-class] schema load/deploy round-trip problem with PostgreSQL

[Frank Schwach]

ok, it seems to have been a bug but it seems to have been fixed in the 
latest version of
DBIx::Class::Schema::Loader.
I just updated to version 0.07014 and the generated scheme for table 
"names" now looks like this:

__PACKAGE__->add_columns(
   "id",
   {
     data_type         => "integer",
     is_auto_increment => 1,
     is_nullable       => 0,
     sequence          => "names_id_seq",
   },
);

which re-recreates the original table correctly when deployed.



On 22/11/11 18:13, Frank Schwach wrote:
> I have a DDL file that creates a database on a PostgreSQL (8.3) 
> instance, which has something like the following statement to create a 
> table:
>
> CREATE TABLE names (
>   id   SERIAL NOT NULL,
>   PRIMARY KEY (id)
> );
>
> When this is executed, I get a table and a sequence, shown here in a 
> psql shell:
> # \d
>               List of relations
>  Schema |     Name     |   Type   |   Owner
> --------+--------------+----------+-----------
>  public | names        | table    | pbr_owner
>  public | names_id_seq | sequence | pbr_owner
>
> and the table "names" looks like this:
>
> # \d names;
>                          Table "public.names"
>  Column |  Type   |                     Modifiers
> --------+---------+----------------------------------------------------
>  id     | integer | not null default nextval('names_id_seq'::regclass)
> Indexes:
>     "names_pkey" PRIMARY KEY, btree (id)
>
>
> using the Schema::Loader via dbicdump gives me a Result class for this 
> table that contains this:
>
> ###### lib/My/Schema/Result/Name.pm ########
> package My::Schema::Result::Name;
> use base 'DBIx::Class::Core';
> __PACKAGE__->add_columns(
>   "id",
>   {
>     data_type         => "integer",
>     default_value     => \"nextval('names_id_seq'::regclass)",
>     is_auto_increment => 1,
>     is_nullable       => 0,
>   },
> );
> __PACKAGE__->set_primary_key("id");
> ###########################################
>
> So far so good, but when I now try to deploy that generated schema to 
> another PostgreSQL database, I get an error:
>
> $ perl -MMy::Schema -e 'My::Schema->connect(## SOME CONNECT STRING 
> ###)->deploy ; '
>
> NOTICE:  CREATE TABLE will create implicit sequence "names_id_seq1" 
> for serial column "names.id"
> DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI 
> Exception: DBD::Pg::db do failed: ERROR:  multiple default values 
> specified for column "id" of table "names" at -e line 1
>  (running "CREATE TABLE "names" (
>   "id" serial DEFAULT nextval('names_id_seq'::regclass) NOT NULL,
>   PRIMARY KEY ("id")
> )") at -e line 1
>
> Looking at the generated SQL statement, the error is in the "id" 
> column which is defined as type "serial" AND has an explicit default 
> "nextval('names_id_seq'::regclass)", but it should have been simply:
>
>   "id" serial NOT NULL
>
> or
>
>   "id" integer DEFAULT nextval('names_id_seq'::regclass)
>
> but not a combination of both.
>
> The culprit is the line
>
>    default_value     => \"nextval('names_id_seq'::regclass)",
>
> in the "id" column definition of the My::Schema Result class. Indeed, 
> removing this line fixes the problem and generates a "serial NOT NULL" 
> definition for the id column, as it should be.
>
> Is this a bug or is there something I am doing wrong here (is there an 
> option that I have overlooked that controls this behaviour)? Or do I 
> simply have to delete all the "default" definitions manually at the 
> cost of loosing the ability to auto-update the dumped schema classes 
> with dbicdump, having changed code above the "do not modify" line?
>
> Thanks for your help!
>
> Frank
>
>
>
>
>
>
>
>
>


-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.