problem with pg_dump and restoring sequences

[Martin Hart]

Hi,

postgresql 7.4beta4 on linux
and postgresql 7.3.4 on linux

We have a database that we routinely backup using "pg_dump -a"
We have to do this (dump the data only) because of various characteristics of
the database (e.g. the dump file tries to create fks to tables that it hasn't
created yet if we export schema + data).

When we come to do a restore, the schema is created by running through our
*.sql files, and then we use psql to import the backed up data. However,
there is a problem with only 2 of the many sequences that psql tries to
restore:

here is a snippet of the lines that fail in the dump file:

-- start quote--
SET search_path = signal, pg_catalog;

--
-- TOC entry 2 (OID 169712)
-- Name: type_id_seq; Type: SEQUENCE SET; Schema: signal; Owner: martin
--
SELECT pg_catalog.setv al('type_id_seq ', 1, false);

--
-- TOC entry 3 (OID 169728)
-- Name: auto_id_seq; Type: SEQUENCE SET; Schema: signal; Owner: martin
--

SELECT pg_catalog.setv al('auto_id_seq ', 19, true); <------ FAILS
--
-- TOC entry 4 (OID 169745)
-- Name: instance_at_seq ; Type: SEQUENCE SET; Schema: signal; Owner: martin
--

SELECT pg_catalog.setv al('instance_at _seq', 41, true); <----- FAILS
-- end quote--

the failure messages are:
psql:db.backup: 42041: ERROR: relation "auto_id_se q" does not exist
psql:db.backup: 42049: ERROR: relation "instance_at_se q" does not exist

Now - to make it work all I have to do is edit the dump file and prepend the
schema name to the setval call for these 2 failing cases:

select pg_catalog.setv al('signal.auto _id_seq', 19, true); <----- WORKS

I initially thought that the schema name might have been the cause of the
problem, but changing it has no effect - so maybe it's the search path (but
notice that the first sequence [type_id_seq] works). I do create all tables
"without oids", maybe that's related?

It is most likely that the problem is with my code - but I can't see it. Here
is a \d of the two tables taken immediately before trying to import the data:

db=# \d signal.instance
Table "signal.instanc e"
Column | Type | Modifiers
---------+--------+----------------------------------------------------------
at | bigint | not null default nextval('signal .instance_at_se q'::text)
type_id | bigint | not null
f0 | text |
f1 | text |
f2 | text |
f3 | text |
f4 | text |
Indexes:
"instance_p key" primary key, btree ("at")
"instance_f 0" btree (f0)
"instance_type_ id" btree (type_id)
Foreign-key constraints:
"$1" FOREIGN KEY (type_id) REFERENCES signal."type"(i d)

db=# \d signal.auto
Table "signal.aut o"
Column | Type | Modifiers
-----------+---------+------------------------------------------------------
id | bigint | not null default nextval('signal .auto_id_seq':: text)
type_id | bigint | not null
schema | text | not null
relation | text | not null
fields | text |
criteria | text |
is_insert | boolean | not null default false
is_update | boolean | not null default false
is_delete | boolean | not null default false
Indexes:
"auto_pkey" primary key, btree (id)
Foreign-key constraints:
"$1" FOREIGN KEY (type_id) REFERENCES signal."type"(i d) ON DELETE CASCADE

also, both sequences do actually exist at this point :-)

What I do not understand is why it is only these 2 cases out of lots of
similar sequence setting that fail. I understand that I am probably not
providing enough information here for a diagnostic - this email is just to
test the water and see if anybody else has experienced similar problems.

I will start work on a test case to see if I can reproduce the behaviour with
a smaller input set. If so I'll post it - but I would be interested to hear
from anybody with similar experiences. At the moment this problem is
preventing us offering an "automatic backup and restore" :-(

TIA
Martin Hart
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)