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

[Frank Schwach]

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.