Is this known? If you create a table with a SERIAL and then rename the table
you can't dump/restore the database. The create table creates a sequence based
on the new table name but the setval() call still refers to the old sequence
name.
I'm not really sure which sequence name ought to be used. Personally I'm
pretty attached to the idea that pg_dump recreates the same database, not just
a functionally equivalent one. You could imagine someone calling currval() on
the sequence by name for example. But you could also look at SERIAL as more
than just syntactic sugar and the sequence it creates as an internal thing.
$ psql -d template1 -U postgres
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
template1=# create database test2 with template = template0;
CREATE DATABASE
template1=# \q
$ psql -d test2 -U postgres
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
test2=# create table test (foo serial);
NOTICE: CREATE TABLE will create implicit sequence "test_foo_seq" for "serial" column "test.foo"
CREATE TABLE
test2=# alter table test rename to test2;
ALTER TABLE
test2=# \d test2
Table "public.test2"
Column | Type | Modifiers
--------+---------+-------------------------------------------------------
foo | integer | not null default nextval('public.test_foo_seq'::text)
test2=# \q
$ pg_dump -U postgres -Fc -f test.dmp test2
template1=# drop database test2;
DROP DATABASE
template1=# create database test2 with template=template0;
CREATE DATABASE
template1=# \q
$ pg_restore -d test2 -f test2.dmp
...
pg_restore: NOTICE: CREATE TABLE will create implicit sequence "test2_t_seq" for "serial" column "test2.t"
pg_restore: [archiver (db)] could not execute query: ERROR: relation "test_t_seq" does not exist
Repeating the process without -Fc shows:
$ pg_dump -U postgres -f test2.dmp test2
$ cat test2.dmp
...
CREATE TABLE test2 (
foo serial NOT NULL
);
...
SELECT pg_catalog.setval('test_foo_seq', 1, false);
$ psql -d test2 -U postgres -f test2.dmp
SET
SET
SET
REVOKE
GRANT
SET
SET
psql:test2.dmp:30: NOTICE: CREATE TABLE will create implicit sequence "test2_foo_seq" for "serial" column "test2.foo"
CREATE TABLE
psql:test2.dmp:47: ERROR: relation "test_foo_seq" does not exist
COMMENT
--
greg
