Re: [PATCHES] system catalog relation of a table and a serial sequence

[2001-12-17 09:48] Brent Verner said:
| [2001-12-16 23:23] Peter Eisentraut said:
| | Tom Lane writes:
| |
| | > I think it'd be a bit surprising if "pg_dump -t table" would dump
| | > sequences declared independently of the table. An example where you'd
| | > likely not be happy with that is if the same sequence is being used to
| | > feed multiple tables.
| | >
| | > I agree that dumping all such sequences will often be the desired
| | > behavior, but that doesn't leave me convinced that it's the right
| | > thing to do.
| | >
| | > Any comments out there?
| |
| | The more general question is: Should 'pg_dump -t table' dump all objects
| | that "table" depends on? Keep in mind that this could mean you have to
| | dump the entire database (think foreign keys). In my mind, dumping an
| | arbitrary subset of dependencies is not a proper solution, though.
|
| Do you care to share your ideas on what a proper solution /would/ be?
|
| I agree wholly with you that it is worse to dump the "arbitrary
| subset" of related objects along with a table.
|
| Assuming that 'pg_dump $ARGS db_1 > psql db_2' should never fail,
| we must either dump only table schema for ARGS="-t table" or dump
| /all/ dependencies for the same ARGS.
|
| Clearly, we are not in a position to dump all dependencies right now.
| Can we make the change that '-t table' is limited to dumping schema?

We need to have some new command line args to allow the user to
choose their desired behavior. I have a patch for pg_dump that adds:

-k, --serial-sequences when dumping schema for a single table, output
CREATE SEQUENCE statements and setval() function
calls for SERIAL columns in the table
-K, --all-sequences when dumping schema for a single table, output
CREATE SEQUENCE statements and setval() function
calls for ALL sequences referenced in any DEFAULT
column definition in the table

By default, no sequence statements are dumped when using the
'-t table' switch to address the real concern that we can't practically
dump /all/ dependencies on a single table (this late in beta). In
order to deal with the case where multiple tables are feeding from the
sequence, a safer setval() call will be made so the nextval will never
be set to a lower value. This is intended to setval such that
subsequent inserts into tables feeding off a(n already existing)
sequence will never fail due to duplicate values.

To determine if a sequence is a serial, I am testing if the seq
name ends with "_seq". When '-K' is used, I'm grabbing all sequences
referenced in any nextval(..) DEFAULT definitions on the table.

Sample output is below. If anyone is interested in trying this patch,
you may fetch it from
http://rcfile.org/posthack/pg_dump.diff.3

There is still a problem where using '-c' might drop a shared
sequence when dumping a table feeding from it. I also just thought
that it might be safer to dump all referenced sequences when using
'-s -t table'.

comments? advice?

thanks,
b

brent$ ./pg_dump -d -K -t t2 brent
-- [comments removed]

CREATE SEQUENCE "shared_sequence" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

CREATE SEQUENCE "t2_a_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

CREATE TABLE "t2" (
"a" integer DEFAULT nextval('"t2_a_seq"'::text) NOT NULL,
"b" integer DEFAULT nextval('shared_sequence'::text) NOT NULL
);

INSERT INTO "t2" VALUES (1,25);
INSERT INTO "t2" VALUES (2,26);
INSERT INTO "t2" VALUES (3,27);
INSERT INTO "t2" VALUES (4,28);
INSERT INTO "t2" VALUES (5,29);

CREATE UNIQUE INDEX t2_a_key ON t2 USING btree (a);

SELECT setval ('"shared_sequence"', (SELECT CASE
WHEN 29 > nextval('"shared_sequence"')
THEN 29
ELSE (currval('"shared_sequence"') - 1)
END),
true);

SELECT setval ('"t2_a_seq"', (SELECT CASE
WHEN 5 > nextval('"t2_a_seq"')
THEN 5
ELSE (currval('"t2_a_seq"') - 1)
END),
true);

--
"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