Re: [PATCHES] system catalog relation of a table and a serial - Mailing list pgsql-hackers

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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026