Sequences/defaults and pg_dump

From: Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>
To: PostgreSQL-general <pgsql-general(at)postgresql(dot)org>
Subject: Sequences/defaults and pg_dump
Date: 2006-02-07 11:33:56
Message-ID: e431ff4c0602070333n300d0d94t9e845706228559df@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Maybe it was discussed already, but I think it's very strange behavior
and things should be changed (please correct me if I'm wrong)

Suppose we have database containing only one simple table:

***
template1=# CREATE DATABASE testseq;
template1=# \c testseq
testseq=# CREATE TABLE test(id SERIAL, data TEXT);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
CREATE TABLE
***

Look at the table 'test':
***
testseq=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+----------------------------------------------------------
id | integer | not null default (nextval('test_id_seq'::regclass))
data | text |
***

So, if we don't know the history we cannot understand that id is of
type SERIAL. We think as it's INTEGER with DEFAULT
'nextval('test_id_seq'::regclass)' [as expression]

This is the question #1 - how I can distinguish pure SERIAL and
INTEGER with corresponding DEFAULT setting?

Then... Imagine that we should use sequence in some other manner. For example:
***
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10;
***
All is OK:
***
testseq=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+----------------------------------------------------------
id | integer | not null default (nextval('test_id_seq'::regclass) * 10)
data | text |
***
... and it works as is supposed to do.

But after simple dump&restore procedure (I use 'pg_dump -U ns testseq
> ~/testseq.dump' and then in psql - '\i /home/ns/testseq.dump') we
have:
***
testseq=# \d test;
Table "public.test"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
data | text |
***
In dump file I see SERIAL as the type for test.id ...
So, the question #2 (the main Q): why pg_dump didn't dump my expression?

For me as end-user this is very-very strange and I consider it as
'gotcha' of PosgreSQL.

--
Best regards,
Nikolay

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-02-07 11:56:49 Re: Sequences/defaults and pg_dump
Previous Message Richard Huxton 2006-02-07 10:39:05 Re: strange query runtime

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2006-02-07 11:40:53 Re: Need Help In arrays
Previous Message Simon Riggs 2006-02-07 09:40:37 Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and