create table foo (foo serial not null, bar text);
create sequence foo_seq;
alter table foo alter column foo set default nextval('foo_seq');
This is flat out pilot error: you do not get to mess with the default
expression of a SERIAL column, because it's part of the internal
implementation of the SERIAL pseudo-type. If I were going to do
anything about it, I'd patch ALTER TABLE to refuse the above command.
It is not pilot error if PostgreSQL allows it. There is nothing "illegal" about the above commands in their execution. The pg_dump application should recognize that the object has changed and react accordingly.
Let me elaborate. Look at the following table (I didn't design it):
rp_nuke_old=# \d nuke_bbtopics
Table "public.nuke_bbtopics"
Column | Type | Modifiers
---------------------+----------------+---------------------------------------------------------------
topic_id | integer | not null default nextval('public.nuke_bbtopics_id_seq'::text)
forum_id | smallint | not null default '0'
topic_title | character(255) | not null default ''
topic_poster | integer | not null default '0'
topic_time | integer | not null default '0'
topic_views | integer | not null default '0'
topic_replies | integer | not null default '0'
topic_status | smallint | not null default '0'
topic_vote | smallint | not null default '0'
topic_type | smallint | not null default '0'
topic_last_post_id | integer | not null default '0'
topic_first_post_id | integer | not null default '0'
topic_moved_id | integer | not null default '0'
news_id | integer | not null default '0'
Indexes: nuke_bbtopics_pkey primary key btree (topic_id),
forum_id_nuke_bbtopics btree (forum_id),
nuke_bbtopics_news_id btree (news_id),
topic_last_post_id_nuke_bbtopics btree (topic_last_post_id),
topic_type_nuke_bbtopics btree (topic_type),
topic_vote_nuke_bbtopics btree (topic_vote)
Check constraints: "$1" (forum_id >= 0)
"$2" (topic_views >= 0)
"$3" (topic_replies >= 0)
"$4" (topic_last_post_id >= 0)
"$5" (topic_first_post_id >= 0)
"$6" (topic_moved_id >= 0)
Notice that topic_id is an integer with a default value of: nextval('public.nuke_bbtopics_id_seq'::text) .
Now lets look at what pg_dump does to this table:
CREATE TABLE nuke_bbtopics ( topic_id serial NOT NULL, forum_id smallint DEFAULT '0' NOT NULL, topic_title character(255) DEFAULT '' NOT NULL, topic_poster integer DEFAULT '0' NOT NULL, topic_time integer DEFAULT '0' NOT NULL, topic_views integer DEFAULT '0' NOT NULL, topic_replies integer DEFAULT '0' NOT NULL, topic_status smallint DEFAULT '0' NOT NULL, topic_vote smallint DEFAULT '0' NOT NULL, topic_type smallint DEFAULT '0' NOT NULL, topic_last_post_id integer DEFAULT '0' NOT NULL, topic_first_post_id integer DEFAULT '0' NOT NULL, topic_moved_id integer DEFAULT '0' NOT NULL, news_id integer DEFAULT '0' NOT NULL, CONSTRAINT "$1" CHECK ((forum_id >= 0)), CONSTRAINT "$2" CHECK ((topic_views >= 0)), CONSTRAINT "$3" CHECK ((topic_replies >= 0)), CONSTRAINT "$4" CHECK ((topic_last_post_id >= 0)), CONSTRAINT "$5" CHECK ((topic_first_post_id >= 0)), CONSTRAINT "$6" CHECK ((topic_moved_id >= 0)) );
Notice that pg_dump has changed the topic_id integer to the serial psuedotype. Which when restored will create:
Table "public.nuke_bbtopics"
Column | Type | Modifiers
---------------------+----------------+---------------------------------------------------------------------
topic_id | integer | not null default nextval('public.nuke_bbtopics_topic_id_seq'::text)
forum_id | smallint | not null default '0'
topic_title | character(255) | not null default ''
topic_poster | integer | not null default '0'
topic_time | integer | not null default '0'
topic_views | integer | not null default '0'
topic_replies | integer | not null default '0'
topic_status | smallint | not null default '0'
topic_vote | smallint | not null default '0'
topic_type | smallint | not null default '0'
topic_last_post_id | integer | not null default '0'
topic_first_post_id | integer | not null default '0'
topic_moved_id | integer | not null default '0'
news_id | integer | not null default '0'
Check constraints: "$1" (forum_id >= 0)
"$2" (topic_views >= 0)
"$3" (topic_replies >= 0)
"$4" (topic_last_post_id >= 0)
"$5" (topic_first_post_id >= 0)
"$6" (topic_moved_id >= 0)
So in the end I have a table with a column topic_id that is an integer that points to the WRONG sequence.
Sincerely,
Joshua D. Drake
regards, tom lane
-- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
