Обсуждение: BUG #3619: Renaming sequence does not update its 'sequence_name' field
BUG #3619: Renaming sequence does not update its 'sequence_name' field
От
"Guillaume 'ioguix' de Rorthais"
Дата:
The following bug has been logged online: Bug reference: 3619 Logged by: Guillaume 'ioguix' de Rorthais Email address: ioguix@free.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@free.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@momjian.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@free.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@free.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@free.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@commandprompt.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
Re: BUG #3619: Renaming sequence does not update its 'sequence_name' field
От
Heikki Linnakangas
Дата:
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.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
Re: BUG #3619: Renaming sequence does not update its 'sequence_name' field
От
Guillaume 'ioguix' de Rorthais
Дата:
Heikki Linnakangas a écrit : > Tom Lane wrote: >> Alvaro Herrera <alvherre@commandprompt.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
Re: BUG #3619: Renaming sequence does not update its 'sequence_name' field
От
Heikki Linnakangas
Дата:
Guillaume 'ioguix' de Rorthais wrote: > Heikki Linnakangas a écrit : >> Tom Lane wrote: >>> Alvaro Herrera <alvherre@commandprompt.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@enterprisedb.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@enterprisedb.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@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +