Finding the sequence used for a primary key, after renaming it

  Hi.

I just ran into a problem when (/after) renaming a sequence.

It seems that psql does something different than DBD::Pg when finding
the default value of a column.

DBD::Pg seems to get it directly from pg_catalog.pg_attrdef(adsrc), but
apparantly 'ALTER SEQUENCE some_seq RENAME TO other_seq' doesn't update
that, and psql seems to look somewhere else.

Here is an example that hopefully illustrates the problem:

First I create a simple table and rename the sequence:

  core@[local] test=# CREATE TABLE test (id serial, name text, value integer);
  NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
  CREATE TABLE
  core@[local] test=# \d test
                           Table "public.test"
   Column |  Type   |                     Modifiers                     
  --------+---------+---------------------------------------------------
   id     | integer | not null default nextval('test_id_seq'::regclass)
   name   | text    | 
   value  | integer | 

  core@[local] test=# ALTER SEQUENCE test_id_seq RENAME TO other_id_seq;
  ALTER SEQUENCE
  core@[local] test=# \d test
                            Table "public.test"
   Column |  Type   |                     Modifiers                      
  --------+---------+----------------------------------------------------
   id     | integer | not null default nextval('other_id_seq'::regclass)
   name   | text    | 
   value  | integer | 

  core@[local] test=# 

This looks good, the default of the id column is automatically updated
with the new sequence name as expected.

For some reason pg_catalog.pg_attrdef(adsrc) isn't updated, though:

  core@[local] test=# SELECT adrelid, adsrc FROM pg_catalog.pg_attrdef;
   adrelid |              adsrc               
  ---------+----------------------------------
   1466741 | nextval('test_id_seq'::regclass)
  (1 row)

  core@[local] test=# 

When DBIx::Class asks DBD::Pg for the column_info, the COLUMN_DEF field
is filled in from the pg_attrdef:

  $ perl -MDBI -e '$dbh=DBI->connect("dbi:Pg:dbname=test", "core", "core"); $info=$dbh->column_info(undef, undef, "test", "id")->fetchrow_hashref; print $info->{COLUMN_DEF} . "\n"'
  nextval('test_id_seq'::regclass)
  $ 

... and SELECT currval on 'test_id_seq' fails, as that isn't the name of
the sequence (any more).

My question is basically where to try and get it fixed:

 * Should PostgreSQL update pg_attrdef as part of executing "ALTER
   SEQUENCE .. RENAME TO .."?

 * Should DBD::Pg get COLUMN_DEF from the same place that psql does? - or:

 * Should DBIx::Class get the default value of a column from somewhere
   else than COLUMN_DEF, if so, where?

Thanks :-)


Version information:

  PostgreSQL 8.3.6
  DBD::Pg 2.8.2
  DBI 1.605
  DBIx::Class 0.08010

  Ubuntu 8.10 amd64


  Best regards,

    Adam

-- 
                                                          Adam Sjøgren
                                                    [email protected]