Renaming sequence auto generated by SERIAL type don't update pg_attrdef

From: Dickson S. Guedes
Subject: Renaming sequence auto generated by SERIAL type don't update pg_attrdef
Date: ,
Msg-id: 1236650119.6410.73.camel@guedes-laptop
(view: Whole thread, Raw)
Responses: Re: Renaming sequence auto generated by SERIAL type don't update pg_attrdef  (Tom Lane)
List: pgsql-jdbc


Hi all,

Reproduced in 8.3.6.

postgres=# CREATE TABLE test (a serial);
NOTICE:  CREATE TABLE will create implicit sequence "test_a_seq" for
serial column "test.a"
CREATE TABLE
postgres=# \d test
                        Tabela "public.test"
 Coluna |  Tipo   |                  Modificadores
--------+---------+--------------------------------------------------
 a      | integer | not null default nextval('test_a_seq'::regclass)

postgres=# ALTER SEQUENCE test_a_seq RENAME TO seq_test_another;
ALTER SEQUENCE
postgres=# \d test
                           Tabela "public.test"
 Coluna |  Tipo   |
Modificadores
--------+---------+--------------------------------------------------------
 a      | integer | not null default
nextval('seq_test_another'::regclass)

postgres=# \x
postgres=#
postgres=# -- A sample select used by JDBC Driver for PostgreSQL
postgres=#
postgres=# SELECT n.nspname, c.relname, a.attname, a.atttypid,
 a.attnotnull, a.atttypmod, a.attlen, a.attnum,  def.adsrc,
 dsc.description,  t.typbasetype, t.typtype
postgres-# FROM     pg_catalog.pg_namespace n
postgres-# JOIN     pg_catalog.pg_class c ON (c.relnamespace = n.oid)
postgres-# JOIN     pg_catalog.pg_attribute a ON (a.attrelid=c.oid)
postgres-# JOIN     pg_catalog.pg_type t ON (a.atttypid = t.oid)
postgres-# LEFT JOIN pg_catalog.pg_attrdef def ON
(a.attrelid=def.adrelid AND a.attnum = def.adnum)
postgres-# LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid
AND a.attnum = dsc.objsubid)
postgres-# LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND
dc.relname='pg_class')
postgres-# LEFT JOIN pg_catalog.pg_namespace dn ON
(dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')
postgres-# WHERE
postgres-#     a.attnum > 0 AND
postgres-#     NOT a.attisdropped  AND
postgres-#     n.nspname LIKE 'public'  AND
postgres-#     c.relname LIKE 'test'  AND
postgres-#     a.attname LIKE '%'
postgres-# ORDER BY nspname,relname,attnum
postgres-# ;
-[ RECORD 1 ]--------------------------------
nspname     | public
relname     | test
attname     | a
atttypid    | 23
attnotnull  | t
atttypmod   | -1
attlen      | 4
attnum      | 1
adsrc       | nextval('test_a_seq'::regclass)
description |
typbasetype | 0
typtype     | b


Shouldn't the JDBC driver use another query instead that, since docs
alerts[1] to this problem?

[1]
http://www.postgresql.org/docs/current/static/catalog-pg-attrdef.html

Regards,
--
Dickson S. Guedes
mail/xmpp:  - skype: guediz
http://guedesoft.net - http://planeta.postgresql.org.br




pgsql-jdbc by date:

From: Tom Lane
Date:
Subject: Re: Renaming sequence auto generated by SERIAL type don't update pg_attrdef
From: Guillaume Cottenceau
Date:
Subject: Re: [PERFORM] Query much slower when run from postgres function