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

Brent, is this the final version?

---------------------------------------------------------------------------

Brent Verner wrote:
> [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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026