BUG #3619: Renaming sequence does not update its 'sequence_name' field

The following bug has been logged online:

Bug reference: 3619
Logged by: Guillaume 'ioguix' de Rorthais
Email address: ioguix(at)free(dot)fr
PostgreSQL version: all - cvs
Operating system: Linux
Description: Renaming sequence does not update its 'sequence_name'
field
Details:

When renaming a sequence, using ALTER SEQUENCE in pg8.3 or ALTER TABLE for
pg < 8.3, its sequence_name field is not updated.

Here is how to produce this bug (output from psql 8.3devel):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
pagila=# select sequence_name from actor_actor_id_seq_renamed;
sequence_name
--------------------
actor_actor_id_seq
(1 row)
pagila=# ALTER SEQUENCE actor_actor_id_seq RENAME TO
actor_actor_id_seq_renamed;
ALTER SEQUENCE
pagila=# select sequence_name from actor_actor_id_seq_renamed;
sequence_name
--------------------
actor_actor_id_seq
(1 row)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I guess the latest request should output actor_actor_id_seq_renamed,
shouldn't it ?

--
ioguix

I am confused by this bug report. Please show us the CREATE TABLE that
goes with it.

---------------------------------------------------------------------------

Guillaume 'ioguix' de Rorthais wrote:
>
> The following bug has been logged online:
>
> Bug reference: 3619
> Logged by: Guillaume 'ioguix' de Rorthais
> Email address: ioguix(at)free(dot)fr
> PostgreSQL version: all - cvs
> Operating system: Linux
> Description: Renaming sequence does not update its 'sequence_name'
> field
> Details:
>
> When renaming a sequence, using ALTER SEQUENCE in pg8.3 or ALTER TABLE for
> pg < 8.3, its sequence_name field is not updated.
>
> Here is how to produce this bug (output from psql 8.3devel):
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> pagila=# select sequence_name from actor_actor_id_seq_renamed;
> sequence_name
> --------------------
> actor_actor_id_seq
> (1 row)
> pagila=# ALTER SEQUENCE actor_actor_id_seq RENAME TO
> actor_actor_id_seq_renamed;
> ALTER SEQUENCE
> pagila=# select sequence_name from actor_actor_id_seq_renamed;
> sequence_name
> --------------------
> actor_actor_id_seq
> (1 row)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> I guess the latest request should output actor_actor_id_seq_renamed,
> shouldn't it ?
>
> --
> ioguix
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

I created this bug report using the pagila db sample (
http://pgfoundry.org/projects/dbsamples/ ). Here is how the sequence and
table are created :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE SEQUENCE actor_actor_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

CREATE TABLE actor (
actor_id integer DEFAULT nextval('actor_actor_id_seq'::regclass) NOT
NULL,
first_name character varying(45) NOT NULL,
last_name character varying(45) NOT NULL,
last_update timestamp without time zone DEFAULT now() NOT NULL
);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

But I made another full trivial test to show this bug from pg8.1 so
using ALTER TABLE request (I don't have access on a pg8.3 presently) :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
postgres=# create DATABASE br3619;
CREATE DATABASE
postgres=# \c br3619
Vous êtes maintenant connecté à la base de données «br3619».
br3619=# CREATE SEQUENCE sample_seq_to_rename;
CREATE SEQUENCE
br3619=# select sequence_name from sample_seq_to_rename;
sequence_name
----------------------
sample_seq_to_rename
(1 ligne)

br3619=# ALTER TABLE sample_seq_to_rename RENAME TO sample_seq;
ALTER TABLE
br3619=# select sequence_name from sample_seq;
sequence_name
----------------------
sample_seq_to_rename
(1 ligne)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--
Guillaume 'ioguix' de Rorthais

Bruce Momjian a écrit :
> I am confused by this bug report. Please show us the CREATE TABLE that
> goes with it.
>
> ---------------------------------------------------------------------------
>
> Guillaume 'ioguix' de Rorthais wrote:
>> The following bug has been logged online:
>>
>> Bug reference: 3619
>> Logged by: Guillaume 'ioguix' de Rorthais
>> Email address: ioguix(at)free(dot)fr
>> PostgreSQL version: all - cvs
>> Operating system: Linux
>> Description: Renaming sequence does not update its 'sequence_name'
>> field
>> Details:
>>
>> When renaming a sequence, using ALTER SEQUENCE in pg8.3 or ALTER TABLE for
>> pg < 8.3, its sequence_name field is not updated.
>>
>> Here is how to produce this bug (output from psql 8.3devel):
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> pagila=# select sequence_name from actor_actor_id_seq_renamed;
>> sequence_name
>> --------------------
>> actor_actor_id_seq
>> (1 row)
>> pagila=# ALTER SEQUENCE actor_actor_id_seq RENAME TO
>> actor_actor_id_seq_renamed;
>> ALTER SEQUENCE
>> pagila=# select sequence_name from actor_actor_id_seq_renamed;
>> sequence_name
>> --------------------
>> actor_actor_id_seq
>> (1 row)
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>> I guess the latest request should output actor_actor_id_seq_renamed,
>> shouldn't it ?
>>
>> --
>> ioguix
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>

ioguix <ioguix(at)free(dot)fr> writes:
> br3619=# CREATE SEQUENCE sample_seq_to_rename;
> CREATE SEQUENCE
> br3619=# select sequence_name from sample_seq_to_rename;
> sequence_name
> ----------------------
> sample_seq_to_rename
> (1 ligne)

> br3619=# ALTER TABLE sample_seq_to_rename RENAME TO sample_seq;
> ALTER TABLE
> br3619=# select sequence_name from sample_seq;
> sequence_name
> ----------------------
> sample_seq_to_rename
> (1 ligne)

This is something we are unlikely to change, because it would have to be
a nontransactional update, which means it'd be out of sync if the ALTER
rolls back after making it. That cure seems hardly better than the
disease.

I seem to recall some prior discussions about rearranging the
representation of sequences to allow separation of transactional and
nontransactional updates, but I don't remember if there were any
non-cosmetic reasons to do it. This one seems pretty cosmetic ...

regards, tom lane

Tom Lane wrote:
> ioguix <ioguix(at)free(dot)fr> writes:
> > br3619=# CREATE SEQUENCE sample_seq_to_rename;
> > CREATE SEQUENCE
> > br3619=# select sequence_name from sample_seq_to_rename;
> > sequence_name
> > ----------------------
> > sample_seq_to_rename
> > (1 ligne)
>
> > br3619=# ALTER TABLE sample_seq_to_rename RENAME TO sample_seq;
> > ALTER TABLE
> > br3619=# select sequence_name from sample_seq;
> > sequence_name
> > ----------------------
> > sample_seq_to_rename
> > (1 ligne)
>
> This is something we are unlikely to change, because it would have to be
> a nontransactional update, which means it'd be out of sync if the ALTER
> rolls back after making it. That cure seems hardly better than the
> disease.
>
> I seem to recall some prior discussions about rearranging the
> representation of sequences to allow separation of transactional and
> nontransactional updates, but I don't remember if there were any
> non-cosmetic reasons to do it. This one seems pretty cosmetic ...

The reason we were exploring that at all was because somebody wanted to
be able to get all the current values from sequences in a single query.
So we wanted to group them all in a single catalog, but that required
non-transactional updates to it.

I am amused by the fact that we store the sequence name in the sequence
itself though.

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
www.google.com: interfaz de línea de órdenes para la web.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> I am amused by the fact that we store the sequence name in the sequence
> itself though.

Yeah, it's a bit pointless. One possible response to this gripe would
be to take the name out of the sequence itself. However, that would
likely break client-side code for no very good reason.

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> I am amused by the fact that we store the sequence name in the sequence
>> itself though.
>
> Yeah, it's a bit pointless. One possible response to this gripe would
> be to take the name out of the sequence itself. However, that would
> likely break client-side code for no very good reason.

Would it be possible to create a SELECT rule on the sequence that
returns the sequence name from the catalog instead?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas a écrit :
> Tom Lane wrote:
>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>>> I am amused by the fact that we store the sequence name in the sequence
>>> itself though.
>> Yeah, it's a bit pointless. One possible response to this gripe would
>> be to take the name out of the sequence itself. However, that would
>> likely break client-side code for no very good reason.
Actually, there's at least one reason : client side code using this value is buggy when a sequence is renamed.
That's exactly how I found this issue: when coding "alter sequence" stuff in ppa which was using it...
Presently, I will not use this value anymore, but I think other dev / projects which are using it should be inform about
this issue.
However, I don't know if breaking client side code is the solution neither.

> Would it be possible to create a SELECT rule on the sequence that
> returns the sequence name from the catalog instead?
>
Well it looks a pretty good idea to me, but I'm not really aware about internal constraints to judge :S

Guillaume 'ioguix' de Rorthais wrote:
> Heikki Linnakangas a écrit :
>> Tom Lane wrote:
>>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>>>> I am amused by the fact that we store the sequence name in the sequence
>>>> itself though.
>>> Yeah, it's a bit pointless. One possible response to this gripe would
>>> be to take the name out of the sequence itself. However, that would
>>> likely break client-side code for no very good reason.
> Actually, there's at least one reason : client side code using this value is buggy when a sequence is renamed.
> That's exactly how I found this issue: when coding "alter sequence" stuff in ppa which was using it...
> Presently, I will not use this value anymore, but I think other dev / projects which are using it should be inform about
> this issue.
> However, I don't know if breaking client side code is the solution neither.

Well, the way it is now is just broken, so IMO we have to either fix it
or remove it altogether.

Since having sequence_name in there doesn't let you do anything you
can't do without it, and there's no easy way to fix it, I'd say let's
just remove it in 8.3 and do nothing in backbranches.

>> Would it be possible to create a SELECT rule on the sequence that
>> returns the sequence name from the catalog instead?
>>
> Well it looks a pretty good idea to me, but I'm not really aware about internal constraints to judge :S

I looked at it briefly. Unfortunately you can't just use CREATE RULE to
create one, that throws an error. I believe we could change the code to
allow it, and create such a rule automatically in DefineSequence, but
frankly it doesn't seem worth the trouble to me. Doesn't seem like a
candidate for backporting anyway.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> Well, the way it is now is just broken, so IMO we have to either fix it
> or remove it altogether.

> Since having sequence_name in there doesn't let you do anything you
> can't do without it, and there's no easy way to fix it, I'd say let's
> just remove it in 8.3 and do nothing in backbranches.

I don't think this is a good idea. It's removing functionality that
works fine as long as you don't rename sequences. Also, there's still
the notion of someday providing a system catalog or view that shows
parameters of all sequences, and in that view the current column set of
an individual sequence would be what we'd want.

My inclination is to leave it alone for now until we have an approach to
providing that view, and at that time decide what to do about individual
sequences. If we have to change the API, then so be it, but lets do it
just once not twice.

regards, tom lane

Tom Lane wrote:
> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> > Well, the way it is now is just broken, so IMO we have to either fix it
> > or remove it altogether.
>
> > Since having sequence_name in there doesn't let you do anything you
> > can't do without it, and there's no easy way to fix it, I'd say let's
> > just remove it in 8.3 and do nothing in backbranches.
>
> I don't think this is a good idea. It's removing functionality that
> works fine as long as you don't rename sequences. Also, there's still
> the notion of someday providing a system catalog or view that shows
> parameters of all sequences, and in that view the current column set of
> an individual sequence would be what we'd want.
>
> My inclination is to leave it alone for now until we have an approach to
> providing that view, and at that time decide what to do about individual
> sequences. If we have to change the API, then so be it, but lets do it
> just once not twice.

Added to TODO:

o Have ALTER SEQUENCE RENAME rename the sequence name stored
in the sequence table

http://archives.postgresql.org/pgsql-bugs/2007-09/msg00092.php
http://archives.postgresql.org/pgsql-bugs/2007-10/msg00007.php

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +