The following bug has been logged online:
Bug reference: 4582
Logged by: Jan-Peter Seifert
Email address: jan-peter.seif...@gmx.de
PostgreSQL version: 8.3.5
Operating system: Windows xp
Description: Renaming sequences and default value
Details:
Hello PostgreSQL-Team,
there's a problem with renaming sequences in our databases. It seems to be
connected to the nextval 'syntax'.
We detected it during testing a script to correct 'broken connections'
between a serial column and its sequence.
When I try to rename 'old' sequences:
ALTER SEQUENCE t1_id_seq RENAME TO ___t1_id_seq;
everything is fine and both nextval and owned by are updated.
When I try the same on a different sequence neither owned by nor nextval are
updated.
ALTER SEQUENCE t2_id_seq RENAME TO ___t2_id_seq;
In pgAdmin (1.8.4 and server binaries) the create command for the first
table looks like this:
CREATE TABLE t1
(
dbef character varying(6),
ansc_id serial NOT NULL
.
.
.
)
The create command for the failing table/sequence looks like this:
CREATE TABLE t2
(
dbef character varying(6),
id integer NOT NULL DEFAULT nextval(('public.t2_id_seq'::text)::regclass)
.
.
.
)
The displayed create table command changed after inserting the column dbef
before the serial column into the already existing table. Before that the
create command looked the same ...
The Properties of the columns differ in the following way:
ALTER TABLE t1 ALTER COLUMN id SET DEFAULT nextval('t1_id_seq'::regclass);
ALTER TABLE t2 ALTER COLUMN id SET DEFAULT
nextval(('public.t2_id_seq'::text)::regclass);
When I change the table t2's nextval command to that from table t1 the
renaming of the sequence correctly causes an update of owned by and
nextval.
The first symptom was that the owned by connection between serial column and
sequence got lost - obviously due to inserting the new column. The
additional text cast to nextval does seem to cause a problem.
We had another problem with sequences as well that might be connected.
Unfortunately the logs weren't kept and it happened at random. After
renaming tables like: 'table' to '__table' and back to 'table' (without
quotes) there were sequences left with an underscore although our update
tool is not supposed to show this behaviour and PostgreSQL neither.
Thank you very much in advance,
Peter Seifert
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
