Skip site navigation (1) Skip section navigation (2)

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

From: Brent Verner <brent(at)rcfile(dot)org>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Rod Taylor <rbt(at)zort(dot)ca>, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCHES] system catalog relation of a table and a serial sequence
Date: 2002-01-01 23:01:52
Message-ID: 20020101230152.GA3069@rcfile.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
[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

In response to

Responses

pgsql-hackers by date

Next:From: Barry LindDate: 2002-01-02 00:31:38
Subject: problems with new vacuum (??)
Previous:From: Adam HaberlachDate: 2002-01-01 22:26:47
Subject: SET DATESTYLE to time_t style for client libraries?

pgsql-patches by date

Next:From: Karel ZakDate: 2002-01-02 13:04:07
Subject: Re: [SQL] Unpredictable text -> date implicit casting behaviour/to_date bug
Previous:From: Tom LaneDate: 2001-12-31 16:02:47
Subject: Re: Unpredictable text -> date implicit casting behaviour/to_date bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group