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

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: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://planeta.postgresql.org.br

"Dickson S. Guedes" <listas@guedesoft.net> writes:
> Shouldn't the JDBC driver use another query instead that, since docs
> alerts[1] to this problem?

What's the default value being used for anyway?  If it's to determine
which sequence is associated with the column, I think you shouldn't be
looking at pg_attrdef at all; you should look for a dependent sequence
via pg_depend.  But maybe it's only being used to check if the column
has a default, in which case the possible staleness of the string
value isn't important.

            regards, tom lane

Tom Lane wrote:
> "Dickson S. Guedes" <listas@guedesoft.net> writes:
>> Shouldn't the JDBC driver use another query instead that, since docs
>> alerts[1] to this problem?
>
> What's the default value being used for anyway?  If it's to determine
> which sequence is associated with the column, I think you shouldn't be
> looking at pg_attrdef at all; you should look for a dependent sequence
> via pg_depend.  But maybe it's only being used to check if the column
> has a default, in which case the possible staleness of the string
> value isn't important.

I'm guessing that particular query is from
DatabaseMetaData.getColumns(), which returns amongst other things:

13. COLUMN_DEF String => default value (may be null)

(yes, that's the sum total of the JDBC documentation about that piece of
metadata)

-O

Oliver Jowett <oliver@opencloud.com> writes:
> Tom Lane wrote:
>> What's the default value being used for anyway?

> I'm guessing that particular query is from
> DatabaseMetaData.getColumns(), which returns amongst other things:
> 13. COLUMN_DEF String => default value (may be null)
> (yes, that's the sum total of the JDBC documentation about that piece of
> metadata)

Hmm.  The correct, pg_dump-tested way to get the default expression is
    pg_catalog.pg_get_expr(adbin, adrelid)
but that's fairly expensive.  I'm having a hard time recommending
that you put it into DatabaseMetaData.getColumns, when probably 99.99%
of applications won't even look at the value let alone care if it is
stale.  OTOH the joins involved are not-cheap already, so maybe this
worry is just premature micro-optimization.

BTW, is there a reason for the query to be using LIKE 'name' instead of
= 'name'?  And if so, is whatever generates it smart about underscores
and % and \ in the name?

            regards, tom lane

Tom Lane wrote:

> Hmm.  The correct, pg_dump-tested way to get the default expression is
>     pg_catalog.pg_get_expr(adbin, adrelid)
> but that's fairly expensive.  I'm having a hard time recommending
> that you put it into DatabaseMetaData.getColumns, when probably 99.99%
> of applications won't even look at the value let alone care if it is
> stale.  OTOH the joins involved are not-cheap already, so maybe this
> worry is just premature micro-optimization.

I don't think DBMD.getColumns() is expected to be called all that much,
certainly not in performance-critical paths, so perhaps the more complex
expression is the way to go. How far back, version-wise, will that
expression work?

> BTW, is there a reason for the query to be using LIKE 'name' instead of
> = 'name'?  And if so, is whatever generates it smart about underscores
> and % and \ in the name?

The API lets the application specify a pattern to search on, using the
same syntax that LIKE expects, so the driver just passes it straight
through into the query (modulo normal string escaping)

-O

Oliver Jowett <oliver@opencloud.com> writes:
> Tom Lane wrote:
>> pg_catalog.pg_get_expr(adbin, adrelid)

> I don't think DBMD.getColumns() is expected to be called all that much,
> certainly not in performance-critical paths, so perhaps the more complex
> expression is the way to go. How far back, version-wise, will that
> expression work?

According to the pg_dump sources, that's good back to 7.3 ... do you
care about earlier?

>> BTW, is there a reason for the query to be using LIKE 'name' instead of
>> = 'name'?  And if so, is whatever generates it smart about underscores
>> and % and \ in the name?

> The API lets the application specify a pattern to search on, using the
> same syntax that LIKE expects, so the driver just passes it straight
> through into the query (modulo normal string escaping)

Fair enough.  I was just trying to remember whether the planner was
smart enough to consider LIKE 'constant-pattern' to be equivalent to
= 'constant-pattern' even when the locale prevents doing anything
smarter for non-constant patterns.  But that'd be my problem to fix not
yours ...

            regards, tom lane

Em Seg, 2009-03-09 às 23:28 -0400, Tom Lane escreveu:
> But maybe it's only being used to check if the column
> has a default, in which case the possible staleness of the string
> value isn't important.

Some Java programs (as DBVis and Aqua for example), are using it to get
column default value, but are showing an old information after we rename
the sequence.

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

On Tue, 10 Mar 2009, Tom Lane wrote:

> Oliver Jowett <oliver@opencloud.com> writes:
>> Tom Lane wrote:
>>> pg_catalog.pg_get_expr(adbin, adrelid)
>
>> I don't think DBMD.getColumns() is expected to be called all that much,
>> certainly not in performance-critical paths, so perhaps the more complex
>> expression is the way to go. How far back, version-wise, will that
>> expression work?
>
> According to the pg_dump sources, that's good back to 7.3 ... do you
> care about earlier?

It works for 7.2 as well.  Prior to that we'll continue using adsrc.
Committed to CVS and backpatched to 8.0.

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> On Tue, 10 Mar 2009, Tom Lane wrote:
>>> pg_catalog.pg_get_expr(adbin, adrelid)
>>
>> According to the pg_dump sources, that's good back to 7.3 ... do you
>> care about earlier?

> It works for 7.2 as well.

Er, not with the pg_catalog qualifier ...

            regards, tom lane

Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
>> It works for 7.2 as well.
>
> Er, not with the pg_catalog qualifier ...
>

I was just referring to pg_get_expr, naturally the 7.2 codepath doesn't
use the schema.

Kris Jurka