Обсуждение: BUG #7920: Sequence rename leave stale value for sequence_name
The following bug has been logged on the website: Bug reference: 7920 Logged by: Maksym Boguk Email address: maxim.boguk@gmail.com PostgreSQL version: 9.2.3 Operating system: Linux Description: = sequence_name left stale after sequence rename: Test case shows same problem on versions 9.0 9.1 9.2: [postgres]=3D# create sequence qqq; [postgres]=3D# SELECT sequence_name FROM qqq; sequence_name --------------- qqq [postgres]=3D# alter sequence qqq rename to lalala; --surprise [postgres]=3D# SELECT sequence_name FROM lalala; sequence_name --------------- qqq pg_dump -F p -s postgres | grep qqq --empty
On 2013-03-06 09:15:01 +0000, maxim.boguk@gmail.com wrote: > The following bug has been logged on the website: > > Bug reference: 7920 > Logged by: Maksym Boguk > Email address: maxim.boguk@gmail.com > PostgreSQL version: 9.2.3 > Operating system: Linux > Description: > > sequence_name left stale after sequence rename: > > Test case shows same problem on versions 9.0 9.1 9.2: > > [postgres]=# create sequence qqq; > > [postgres]=# SELECT sequence_name FROM qqq; > sequence_name > --------------- > qqq > > [postgres]=# alter sequence qqq rename to lalala; > > --surprise [postgres]=# SELECT sequence_name > FROM lalala; > sequence_name > --------------- > qqq > > > pg_dump -F p -s postgres | grep qqq > --empty I don't find this particularly suprising. Nothing looks at that field in sequences, there imo is no point on having the name inside at all. Do you need that for some usecase or did you just happen to notice it? SELECT tableoid::regclass AS sequence_name FROM lalala; should do the trick for now. I personally don't see any way to nicely fix that. We can add code to also change the contents, but currently thats generic code. Or we could just remove the column in the next release? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
> > I don't find this particularly suprising. Nothing looks at that field in > sequences, there imo is no point on having the name inside at all. > > Do you need that for some usecase or did you just happen to notice it? > > I personally don't see any way to nicely fix that. We can add code to > also change the contents, but currently thats generic code. Or we could > just remove the column in the next release? > Well, this story began with one very bad named sequence. After database structure audit the developers were asked to rename this sequence to something more appropriate. And when they performed alter ... rename they found that the name still same. After that they came to me with questions. Task is remove that very bad name from the production database altogether. It seems that the easiest way is drop sequence and create new sequece. +1 for "just remove the column in the next release" --=20 Maxim Boguk Senior Postgresql DBA http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/= > Phone RU: +7 910 405 4718 Phone AU: +61 45 218 5678 Skype: maxim.boguk Jabber: maxim.boguk@gmail.com =D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: http://mboguk.moikrug.ru/ "People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage."
Andres Freund <andres@2ndquadrant.com> writes:
> I don't find this particularly suprising. Nothing looks at that field in
> sequences, there imo is no point on having the name inside at all.
Yeah, and we really can't update the name there because there is no
provision for transactional updates of sequence tuples.
> I personally don't see any way to nicely fix that. We can add code to
> also change the contents, but currently thats generic code. Or we could
> just remove the column in the next release?
This has been discussed before, and the general opinion has been to
leave things alone until we get around to doing a wholesale refactoring
of sequence support. There has been talk for example of merging all
sequences into one catalog, instead of the current very wasteful
technique of having a whole relation to store (in essence) one counter.
That would probably break existing code that tries to select from a
sequence, but at least there would be objective benefits from it.
Removing the sequence_name column alone would also break existing code,
for ... um ... not much.
The correct answer is for applications to not rely on the sequence_name
column. It's been of dubious usefulness ever since we invented schemas,
anyhow.
regards, tom lane
On 2013-03-06 09:27:55 -0500, Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: > > I don't find this particularly suprising. Nothing looks at that field in > > sequences, there imo is no point on having the name inside at all. > > Yeah, and we really can't update the name there because there is no > provision for transactional updates of sequence tuples. True. > > I personally don't see any way to nicely fix that. We can add code to > > also change the contents, but currently thats generic code. Or we could > > just remove the column in the next release? > > This has been discussed before, and the general opinion has been to > leave things alone until we get around to doing a wholesale refactoring > of sequence support. There has been talk for example of merging all > sequences into one catalog, instead of the current very wasteful > technique of having a whole relation to store (in essence) one counter. > That would probably break existing code that tries to select from a > sequence, but at least there would be objective benefits from it. > Removing the sequence_name column alone would also break existing code, > for ... um ... not much. The only argument I see is reduced chance of people making errors. Code that actually uses sequence_name is broken. If we had something like columns that are computed on output, we could use that. What we could do is invent a new pseudo-column type like tableoid that renders as text.. In the end it doesn't seem worth bothering. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On 2013-03-06 09:27:55 -0500, Tom Lane wrote:
>> Removing the sequence_name column alone would also break existing code,
>> for ... um ... not much.
> The only argument I see is reduced chance of people making errors. Code
> that actually uses sequence_name is broken.
Well, only if you rename the sequence, which is something many people
would never do.
> If we had something like columns that are computed on output, we could
> use that. What we could do is invent a new pseudo-column type like
> tableoid that renders as text..
> In the end it doesn't seem worth bothering.
Yeah. If I recall the older discussions correctly, we talked about
somehow splitting a sequence's storage between transactionally-updatable
and non-transactionally-updatable parts, so that we could make altering
a sequence's parameters transactional. Preserving anything remotely
like "select * from sequence" would require a view or some such.
Whenever somebody gets around to attacking that whole problem, I'll be
for that; but in the meantime it seems like we should leave it alone
instead of making marginal changes.
regards, tom lane