Hi,
I'm trying to clean up a bug in pg_dump where specifying a table
with the '-t tablename' argument fails to generate the necessary
CREATE SEQUENCE statements for columns of type SERIAL that are not
named "id" (example at bottom of email).
So... The gist of the problem is that there /appears/ to be no
direct way to determine the sequence(s) referenced in any nextval(...)
column defaults. Below is only relationship I've found between the
table "test2" and the SERIAL-created sequence "test2_i_seq".
brent=# select adsrc from pg_attrdef
brent-# where adrelid=(select oid from pg_class where relname='test2'); adsrc
--------------------------------nextval('"test2_i_seq"'::text)
(1 row)
Have I missed a more basic/straightforward relationship between these
two in the system catalogs?
I propose adding a function to pg_dump.c for now. I'll work on putting
this knowledge into the backend post-7.2, and toward solving the
DROP TABLE automatically dropping SERIAL-created sequences problem.
thanks. brent
======================================================================
sleepy:/usr/local/pg-7.2/bin
brent$ ./psql -c '\d test2' Table "test2"Column | Type |
Modifiers
--------+-----------------------+-------------------------------------------------n | character varying(32) | i
| integer | not null default nextval('"test2_i_seq"'::text)
Unique keys: test2_i_key
sleepy:/usr/local/pg-7.2/bin
brent$ ./pg_dump -t test2 brent
--
-- Selected TOC Entries:
--
\connect - brent
--
-- TOC Entry ID 2 (OID 16571)
--
-- Name: test2 Type: TABLE Owner: brent
--
CREATE TABLE "test2" ( "n" character varying(32), "i" integer DEFAULT nextval('"test2_i_seq"'::text) NOT NULL
);
--
-- Data for TOC Entry ID 4 (OID 16571)
--
-- Name: test2 Type: TABLE DATA Owner: brent
--
COPY "test2" FROM stdin;
\.
--
-- TOC Entry ID 3 (OID 16573)
--
-- Name: "test2_i_key" Type: INDEX Owner: brent
--
CREATE UNIQUE INDEX test2_i_key ON test2 USING btree (i);
--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman
