BUG #1316: Alter Name of a Serial Field won't change the corresponding SEQUENCE name
PostgreSQL Bugs List
The following bug has been logged online:
Bug reference: 1316
Logged by: Hongyi Gao
Email address: ad...@listfusion.net
PostgreSQL version: 7.4.5
Operating system: SuSe Linux 9
Description: Alter Name of a Serial Field won't change the
corresponding SEQUENCE name
Details:
Alter Name of a Serial Field won't change the corresponding SEQUENCE name
--------------------------------------------------------
say we had a table:
atable (field1 Serial)
it implies to create a sequence: atable_field1_seq
if I alter table atable alter field1 rename to field2
it becomes atable(field2 Serial)
however, the sequence stays: atable_field1_seq
it's not tablename_fieldname_seq any more
----------------------------------------------------
This will cause problem when we restore a backup:
....
NOTICE: create table atable implies create a sequence atable_field2_seq ...
(here it defaults to tablename_fieldname_seq again)
...
SET SEQUENCE atable_field1_seq ... (here it still tries to restore the
actually sequence we used)
since it's not created, it will fail. And the value of atable_field2_seq is
NOT set !)
this will ruin the whole field.
The same thing may happen if you change the name of a table that has serial
field(s).
Regards,
Hongyi
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Tom Lane
> if I alter table atable alter field1 rename to field2
> it becomes atable(field2 Serial)
> however, the sequence stays: atable_field1_seq
> it's not tablename_fieldname_seq any more
It's intended to do that.
> This will cause problem when we restore a backup:
pg_dump in 8.0 has a workaround for this.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?