BUG #7920: Sequence rename leave stale value for sequence_name

[m***@gmail.com]

The following bug has been logged on the website:

Bug reference: 7920
Logged by: Maksym Boguk
Email address: ***@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

[Andres Freund]

Post by m***@gmail.com
Bug reference: 7920
Logged by: Maksym Boguk
PostgreSQL version: 9.2.3
Operating system: Linux
[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

[Maxim Boguk]

Post by Andres Freund
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"
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/>

Phone RU: [phone]
Phone AU: [phone]

Skype: maxim.boguk
Jabber: ***@gmail.com
МПйКруг: 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."

[Tom Lane]

Post by Andres Freund
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.

Post by Andres Freund
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

[Andres Freund]

Post by Tom Lane

Post by Andres Freund
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.

Post by Tom Lane

Post by Andres Freund
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

[Tom Lane]

Post by Andres Freund

Post by Tom Lane
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.

Post by Andres Freund
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