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.
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
