[Dbix-class] Creating a sequence in DBIx::Class (was Re: Problems deploying a Postgres database that uses sequences)

[Robert Rothenberg]

On a related note, how can I create a sequence using DBIx::Class?

It seems deploying a schema that uses sequences from the Perl modules only
works if the sequence was already defined in the database.


On 11/08/12 13:50 Robert Rothenberg wrote:
> I am working on a project that uses DBIx::Class but deploys the database
> using an SQL script.  We'd like to use DBix::Class::DeploymentHandler or
> (preferably) DBIx::Class::Migration for deploying the databases, and
> handling upgrades.
> 
> The database in question uses sequences for primary keys.
> 
> If we use DBIx::Class's deploy method, the new database will have a similar
> schema. Importantly, it will use sequences, e.g. an "id" field will be
> defined "integer not null default nextval('table_id_seq'::regclass).
> 
> This is all well and good, but we want a better way to handle upgrades (or
> possibly downgrades) and load data.
> 
> If we use DBIx::Class::Migration, it sets the the primary keys to "serial"
> instead.
> 
> I imagine substituting the auto-generated
> share/migrations/PostgreSQL/deploy/1/001-auto.sql with our original SQL
> script might fix that, but, will it be overwritten later by upgrades?
> 
> Even worse though: when we populate the database with data, the sequences
> (well, "serial" counters) do not seem to be incremented properly, so adding
> new records fails.  So is there a way to update sequences when using
> DBIx::Class::Migration?