Thread: BUG #4582: Renaming sequences and default value

The following bug has been logged online:

Bug reference:      4582
Logged by:          Jan-Peter Seifert
Email address:      jan-peter.seifert@gmx.de
PostgreSQL version: 8.3.5
Operating system:   Windows xp
Description:        Renaming sequences and default value
Details:

Hello PostgreSQL-Team,

there's a problem with renaming sequences in our databases. It seems to be
connected to the nextval 'syntax'.
We detected it during testing a script to correct 'broken connections'
between a serial column and its sequence.

When I try to rename 'old' sequences:

ALTER SEQUENCE t1_id_seq RENAME TO ___t1_id_seq;
everything is fine and both nextval and owned by are updated.

When I try the same on a different sequence neither owned by nor nextval are
updated.
ALTER SEQUENCE t2_id_seq RENAME TO ___t2_id_seq;

In pgAdmin (1.8.4 and server binaries) the create command for the first
table looks like this:

CREATE TABLE t1
(
  dbef character varying(6),
  ansc_id serial NOT NULL
    .
    .
    .
)

The create command for the failing table/sequence looks like this:

CREATE TABLE t2
(
  dbef character varying(6),
  id integer NOT NULL DEFAULT nextval(('public.t2_id_seq'::text)::regclass)
    .
    .
    .
)

The displayed create table command changed after inserting the column dbef
before the serial column into the already existing table. Before that the
create command looked the same ...

The Properties of the columns differ in the following way:

ALTER TABLE t1 ALTER COLUMN id SET DEFAULT nextval('t1_id_seq'::regclass);

ALTER TABLE t2 ALTER COLUMN id SET DEFAULT
nextval(('public.t2_id_seq'::text)::regclass);

When I change the table t2's nextval command to that from table t1 the
renaming of the sequence correctly causes an update of owned by and
nextval.

The first symptom was that the owned by connection between serial column and
sequence got lost - obviously due to inserting the new column. The
additional text cast to nextval does seem to cause a problem.

We had another problem with sequences as well that might be connected.
Unfortunately the logs weren't kept and it happened at random. After
renaming tables like: 'table' to  '__table' and back to 'table' (without
quotes) there were sequences left with an underscore although our update
tool is not supposed to show this behaviour and PostgreSQL neither.

Thank you very much in advance,

Peter Seifert

"Jan-Peter Seifert" <jan-peter.seifert@gmx.de> writes:
> there's a problem with renaming sequences in our databases.

I don't think there's really a problem here.  You've apparently got some
obsolete syntax in your CREATE commands:

>   id integer NOT NULL DEFAULT nextval(('public.t2_id_seq'::text)::regclass)

This specifically says that 'public.t2_id_seq' is a string (text) constant,
which is not going to change in response to anything.  If you would
like it to track renamings of the sequence then it needs to be a
regclass constant:

    id integer NOT NULL DEFAULT nextval('public.t2_id_seq'::regclass)

Also, neither of these forms will by itself establish an OWNED BY
relationship --- you'll need a separate ALTER SEQUENCE OWNED BY
command if you want that.

If that doesn't help, you'll need to be a lot more specific about the
actions you took.

            regards, tom lane

Tom Lane wrote:

Thank you very much for your quick reply. I wanted to do some testing
before reporting back.

> "Jan-Peter Seifert" <jan-peter.seifert@gmx.de> writes:
>> there's a problem with renaming sequences in our databases.
>
> I don't think there's really a problem here.  You've apparently got some
> obsolete syntax in your CREATE commands:
>
>>   id integer NOT NULL DEFAULT nextval(('public.t2_id_seq'::text)::regclass)
>
> This specifically says that 'public.t2_id_seq' is a string (text) constant,
> which is not going to change in response to anything.  If you would
> like it to track renamings of the sequence then it needs to be a
> regclass constant:
>
>     id integer NOT NULL DEFAULT nextval('public.t2_id_seq'::regclass)

At first I thought the reason why a renamed sequence couldn't be found
via nextval anymore - even with 'owned by' set - was evaluating the
'historical' adsrc and not the adbin column in pg_attrdef within the
maintenance script. But then I saw the difference in nextval syntax
etc.. The update tool in question uses the obsolete syntax. Has this
text constant thing been official syntax for some time? I wonder why the
person implementing this syntax into the tool did it in the first place.
 Now after changing the nextval syntax with an ALTER TABLE as well all
seems to be alright again. Even pgAdmin III now shows 'serial' as data
type for the column again.

> Also, neither of these forms will by itself establish an OWNED BY
> relationship --- you'll need a separate ALTER SEQUENCE OWNED BY
> command if you want that.

Thank you very much for pointing this out. We only discovered it after
looking through a dump some time ago.

Peter Seifert

Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de> writes:
> Tom Lane wrote:
>> I don't think there's really a problem here.  You've apparently got some
>> obsolete syntax in your CREATE commands:
>>
>>> id integer NOT NULL DEFAULT nextval(('public.t2_id_seq'::text)::regclass)

> ... But then I saw the difference in nextval syntax
> etc.. The update tool in question uses the obsolete syntax. Has this
> text constant thing been official syntax for some time? I wonder why the
> person implementing this syntax into the tool did it in the first place.

It might not be entirely the tool's fault.  If you had something like
DEFAULT nextval('public.t2_id_seq') in an old version of PG (pre 8.1
I think) then the business with text is actually the most precise, if
not the most useful, translation of that --- pre 8.1 didn't have any way
to track renamings of a serial sequence and so this structure mimics its
semantics.  So you might have got to this state via a dump and reload.
Or maybe whoever wrote the tool copied what he saw in an old dump,
without realizing that it wasn't very desirable.

            regards, tom lane