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

Fri, 14 Dec 2001 22:53:27 -0500
Brent Verner <brent@rcfile.org>
system catalog relation of a table and a serial sequence
Brent Verner wrote:

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

Then some mechanism should be devised for disallowing other 
tables/functions/... from using these sequences.

---------------
Hannu

Sat, 15 Dec 2001 13:05:19 +0200
Hannu Krosing <hannu@tm.ee>
Re: system catalog relation of a table and a serial sequence
[2001-12-14 22:53] Brent Verner said:
| I propose adding a function to pg_dump.c for now.

Patch adding a getSerialSequenceName() function to pg_dump.[ch] is
attached.

I'm aware that this is not the /best/ solution to this problem, but
it is better than the current breakage in pg_dump.

feedback appreciated.

cheers.
  brent

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

Sat, 15 Dec 2001 20:32:56 -0500
Brent Verner <brent@rcfile.org>
Re: system catalog relation of a table and a serial sequence
[2001-12-15 20:32] Brent Verner said:
| [2001-12-14 22:53] Brent Verner said:
| | I propose adding a function to pg_dump.c for now.
| 
| Patch adding a getSerialSequenceName() function to pg_dump.[ch] is
| attached.

...too quick on the send.  Patch attached for real this time ;-)

  b

-- 
"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
Sat, 15 Dec 2001 20:33:50 -0500
Brent Verner <brent@rcfile.org>
Re: system catalog relation of a table and a serial sequence
Brent Verner <brent@rcfile.org> writes:
> I'm aware that this is not the /best/ solution to this problem, but
> it is better than the current breakage in pg_dump.

I'd dispute that, primarily because the patch blithely assumes that
there is no other kind of default value than a serial-created nextval().
It looks to me like it will either coredump or do the wrong thing
with other default-value strings.

			regards, tom lane

Sat, 15 Dec 2001 21:02:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: system catalog relation of a table and a serial sequence 
[2001-12-15 21:02] Tom Lane said:
| Brent Verner <brent@rcfile.org> writes:
| > I'm aware that this is not the /best/ solution to this problem, but
| > it is better than the current breakage in pg_dump.
| 
| I'd dispute that, primarily because the patch blithely assumes that
| there is no other kind of default value than a serial-created nextval().
| It looks to me like it will either coredump or do the wrong thing
| with other default-value strings.

monkey me!  Yes, quite a nasty oversight!  I'll clean this up.

thanks.
  brent

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

Sat, 15 Dec 2001 21:10:05 -0500
Brent Verner <brent@rcfile.org>
Re: system catalog relation of a table and a serial sequence
While you're at it, why not fix the code so that it can deal with
multiple SERIALs attached to a table?

			regards, tom lane

Sat, 15 Dec 2001 21:12:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: system catalog relation of a table and a serial sequence 
[2001-12-15 21:12] Tom Lane said:
| While you're at it, why not fix the code so that it can deal with
| multiple SERIALs attached to a table?

will do.  I'd appreciate a bit of advice on both of the issues to
be addressed.

1) Is a strcmp(firststrtok,"nextval('") == 0  sufficient to determine
   that the adsrc is indeed one that we're looking for?  If not, 
   suggestions are greatly appreciated :-)

2) Should this function now look like .. ?
     char** getSerialSequenceNames(const char* table)
   Or would you suggest it return a smarter struct?

thanks.
  brent

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

Sat, 15 Dec 2001 21:29:07 -0500
Brent Verner <brent@rcfile.org>
Re: system catalog relation of a table and a serial sequence
Brent Verner <brent@rcfile.org> writes:
> 1) Is a strcmp(firststrtok,"nextval('") == 0  sufficient to determine
>    that the adsrc is indeed one that we're looking for?  If not, 
>    suggestions are greatly appreciated :-)

I would not use strtok at all, but look for nextval('" at the start
of the string and "'::text) at the end.  If both match, and there's
at least one character between, then the intervening text can be
presumed to be a sequence name.  You might further check that the
apparent sequence name ends with _seq --- if not, it wasn't generated
by SERIAL.

> 2) Should this function now look like .. ?
>      char** getSerialSequenceNames(const char* table)
>    Or would you suggest it return a smarter struct?

char** (null-terminated vector) would probably work.

BTW, don't forget you have the OID of the table available from the table
list, so you can avoid the subselect, as well as the relname quoting
issues that you didn't take care of.  When a tablename argument is
provided, I'd be inclined to make a pre-pass over the table list to see
if it matches any non-sequence table names, and if so build a list of
their associated sequence name(s).  Keep in mind that we'll probably
generalize the tablename argument to support wildcarding someday soon,
so it'd be good if the code could cope with more than one matching
table.

			regards, tom lane

Sat, 15 Dec 2001 21:43:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: system catalog relation of a table and a serial sequence 
[2001-12-15 21:43] Tom Lane said:
| Brent Verner <brent@rcfile.org> writes:
| > 1) Is a strcmp(firststrtok,"nextval('") == 0  sufficient to determine
| >    that the adsrc is indeed one that we're looking for?  If not, 
| >    suggestions are greatly appreciated :-)
| 
| I would not use strtok at all, but look for nextval('" at the start
| of the string and "'::text) at the end.  If both match, and there's
| at least one character between, then the intervening text can be
| presumed to be a sequence name.  You might further check that the
| apparent sequence name ends with _seq --- if not, it wasn't generated
| by SERIAL.

Why not use strtok?  The following should be safe, no?

  t1 = strtok(adsrc,"\"");
  t2 = strtok(NULL,"\"");
  t3 = strtok(NULL,"\"");

  if( t0 && t2
      && strcmp(t0,"nextval('") == 0
      && strcmp(t2,"'::text)") == 0 ){
    /* this is a call to nextval, check for t1 =~ /_seq$/ */
    
  }

| > 2) Should this function now look like .. ?
| >      char** getSerialSequenceNames(const char* table)
| >    Or would you suggest it return a smarter struct?
| 
| char** (null-terminated vector) would probably work.
| 
| BTW, don't forget you have the OID of the table available from the table
| list, so you can avoid the subselect, as well as the relname quoting
| issues that you didn't take care of.  When a tablename argument is
| provided, I'd be inclined to make a pre-pass over the table list to see
| if it matches any non-sequence table names, and if so build a list of
| their associated sequence name(s).  Keep in mind that we'll probably
| generalize the tablename argument to support wildcarding someday soon,
| so it'd be good if the code could cope with more than one matching
| table.

gotcha.

thanks.
  brent

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

Sat, 15 Dec 2001 23:00:06 -0500
Brent Verner <brent@rcfile.org>
Re: system catalog relation of a table and a serial sequence
[2001-12-15 22:26] Rod Taylor said:
| 
| > Brent Verner <brent@rcfile.org> writes:
| > > 1) Is a strcmp(firststrtok,"nextval('") == 0  sufficient to
| determine
| > >    that the adsrc is indeed one that we're looking for?  If not,
| > >    suggestions are greatly appreciated :-)
| >
| > I would not use strtok at all, but look for nextval('" at the start
| > of the string and "'::text) at the end.  If both match, and there's
| > at least one character between, then the intervening text can be
| > presumed to be a sequence name.  You might further check that the
| > apparent sequence name ends with _seq --- if not, it wasn't
| generated
| > by SERIAL.
| 
| Wouldn't you want to include user sequences that are required for
| using the table?  If someone has used their own sequence as the
| default value for a column it would be nice to have it dumped as well.

This is my thought as well.  Hopefully Tom will concur.

cheers.
  brent

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

Sat, 15 Dec 2001 23:03:32 -0500
Brent Verner <brent@rcfile.org>
Re: [HACKERS] system catalog relation of a table and a serial
 sequence
> | > You might further check that the
> | > apparent sequence name ends with _seq --- if not, it wasn't
> | > generated by SERIAL.
> | 
> | Wouldn't you want to include user sequences that are required for
> | using the table?  If someone has used their own sequence as the
> | default value for a column it would be nice to have it dumped as well.

> This is my thought as well.  Hopefully Tom will concur.

Well, that's why I said "might".  I'm not sure what the correct behavior
is here.  If we had an actual SERIAL datatype --- that is, we could
unambiguously tell that a given column was SERIAL --- then a case could
be made that "pg_dump -t table" should dump only those sequences
associated with table's SERIAL columns.

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?

			regards, tom lane

Sat, 15 Dec 2001 23:17:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] system catalog relation of a table and a serial
	sequence
Brent Verner <brent@rcfile.org> writes:
> Why not use strtok?

Well, it's ugly (I don't like non-reentrant library routines), it's
not really buying anything, and I don't think you've got the corner
cases right anyway.  I'd go for something like

	if (strlen(adsrc) > 19 &&
	    strncmp(adsrc, "nextval('\"", 10) == 0 &&
	    strcmp(adsrc + strlen(adsrc) - 9, "\"'::text)") == 0)

			regards, tom lane

Sat, 15 Dec 2001 23:25:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: system catalog relation of a table and a serial sequence 
[2001-12-15 23:17] Tom Lane said:
| > | > You might further check that the
| > | > apparent sequence name ends with _seq --- if not, it wasn't
| > | > generated by SERIAL.
| > | 
| > | Wouldn't you want to include user sequences that are required for
| > | using the table?  If someone has used their own sequence as the
| > | default value for a column it would be nice to have it dumped as well.
| 
| > This is my thought as well.  Hopefully Tom will concur.
| 
| Well, that's why I said "might".  I'm not sure what the correct behavior
| is here.  If we had an actual SERIAL datatype --- that is, we could
| unambiguously tell that a given column was SERIAL --- then a case could
| be made that "pg_dump -t table" should dump only those sequences
| associated with table's SERIAL columns.
| 
| 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?

sure :-)  What we can do is determine /any/ sequence referenced 
by a nextval(..) attribute default with the following SELECT query.

create sequence non_serial_sequence;
create table aaa ( 
  id serial, 
  nonid int default nextval('non_serial_sequence') 
);
SELECT adsrc FROM pg_attrdef WHERE adrelid=(
  SELECT oid FROM pg_class WHERE relname='aaa'
);

                adsrc                 
--------------------------------------
 nextval('"aaa_id_seq"'::text)
 nextval('non_serial_sequence'::text)

We get the nextval(..) calls to both of the referenced sequences,
and the strtok code I'm using extracts the proper sequence names.
Am I overlooking something here?  Is there any other way a nextval(..)
adsrc would appear not containing a sequence related to this relation?

cheers.
  brent

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

Sat, 15 Dec 2001 23:38:19 -0500
Brent Verner <brent@rcfile.org>
Re: [HACKERS] system catalog relation of a table and a serial
 sequence
[2001-12-15 23:25] Tom Lane said:
| Brent Verner <brent@rcfile.org> writes:
| > Why not use strtok?
| 
| Well, it's ugly (I don't like non-reentrant library routines), it's
| not really buying anything, and I don't think you've got the corner
| cases right anyway.  I'd go for something like

How about strtok_r?  I /really/ like the fact that strtok will
eat either of the tokens ['"] that might be around the sequence
name... just call me lazy :-)

thanks.
  brent

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

Sat, 15 Dec 2001 23:52:30 -0500
Brent Verner <brent@rcfile.org>
Re: system catalog relation of a table and a serial sequence
Brent Verner <brent@rcfile.org> writes:
> How about strtok_r?  I /really/ like the fact that strtok will
> eat either of the tokens ['"] that might be around the sequence
> name... just call me lazy :-)

That behavior creates one of the "corner cases" I was alluding to.
Shall I leave the difficulty as an exercise for the student?

			regards, tom lane

Sun, 16 Dec 2001 00:42:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: system catalog relation of a table and a serial sequence 
[2001-12-16 00:42] Tom Lane said:
| Brent Verner <brent@rcfile.org> writes:
| > How about strtok_r?  I /really/ like the fact that strtok will
| > eat either of the tokens ['"] that might be around the sequence
| > name... just call me lazy :-)
| 
| That behavior creates one of the "corner cases" I was alluding to.
| Shall I leave the difficulty as an exercise for the student?

sure.  I'm assuming the strtok_r is not acceptable :-)  I'll work on
this a bit more tonight.  Expect a better patch sometiime tomorrow.

thanks.
  brent

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

Sun, 16 Dec 2001 01:20:18 -0500
Brent Verner <brent@rcfile.org>
Re: system catalog relation of a table and a serial sequence
At 01:20 16/12/01 -0500, Brent Verner wrote:
>
> Expect a better patch sometiime tomorrow.
>

The hard part in this might be getting pg_restore to know that the sequence
is part of the table definition; perhaps it is adequate to use the 'deps'
field. 

I think it is currently unused for SEQUENCE (and SEQUENCE SET) entries, so
we could assume that if it is set, the sequence is logically part of a
table. You could set the deps to the table OID, then the restore operation
(_tocEntryRequired) could scan the TOC for a matching table, and see if the
matching table is being restored (ie. _tocEntryRequired would, in the case
of 'SEQUENCE' and 'SEQUENCE SET' entries, scan the entire TOC for the
matching table then call itself recursively on the table entry.




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sun, 16 Dec 2001 18:12:56 +1100
Philip Warner <pjw@rhyme.com.au>
Re: system catalog relation of a table and a serial
On Sun, 2001-12-16 at 17:17, Tom Lane wrote:
> > | > You might further check that the
> > | > apparent sequence name ends with _seq --- if not, it wasn't
> > | > generated by SERIAL.
> > | 
> > | Wouldn't you want to include user sequences that are required for
> > | using the table?  If someone has used their own sequence as the
> > | default value for a column it would be nice to have it dumped as well.
> 
> > This is my thought as well.  Hopefully Tom will concur.
> 
> Well, that's why I said "might".  I'm not sure what the correct behavior
> is here.  If we had an actual SERIAL datatype --- that is, we could
> unambiguously tell that a given column was SERIAL --- then a case could
> be made that "pg_dump -t table" should dump only those sequences
> associated with table's SERIAL columns.
> 
> 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?

Along with "DROP COLUMN" this is probably one of the biggest "I can't
believe it doesn't" things out there.

I would tend to say that Brent's patch, in dumping all of the sequences
used by a table, is erring on the _correct_ side of caution.

Remember that someone who this is a problem for can easily post-process
the sequence out of the dump with sed or something, but someone for whom
the opposite is true doesn't have anything like as trivial a job to put
it back in there.

Cheers,
					Andrew.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267


16 Dec 2001 20:25:00 +1300
Andrew McMillan <andrew@catalyst.net.nz>
Re: [HACKERS] system catalog relation of a table and a
[2001-12-16 00:42] Tom Lane said:
| Brent Verner <brent@rcfile.org> writes:
| > How about strtok_r?  I /really/ like the fact that strtok will
| > eat either of the tokens ['"] that might be around the sequence
| > name... just call me lazy :-)
| 
| That behavior creates one of the "corner cases" I was alluding to.
| Shall I leave the difficulty as an exercise for the student?

Ok... I ended up working longer than I'd thought :-)

* no strtok were used in this patch. ;-)
* Handles both serial-sequences and user-sequences referenced in 
  nextval(...) default column defs.
* Loop over tables so we can check wildcard table name in the future
  per your suggestion.  I've only noted a TODO: regarding the wildcard
  matching.
* Instead of using a NULL terminated char** array to hold the collected
  sequence names, I put in a simple strarray ADT -- mostly so I could
  have the strarrayContains() test to call from the conditional around
  dumpSequence().  If this is just dumb, I'll replace it with a simple
  char** implementation.  Did I overlook some utility funcs in the
  PG source that already does this?  If so, I'll gladly use those.
* Patch is really attached :-P

comments?

tired.
  b

-- 
"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
Sun, 16 Dec 2001 06:30:21 -0500
Brent Verner <brent@rcfile.org>
Re: system catalog relation of a table and a serial sequence
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.

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 16 Dec 2001 23:23:56 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [PATCHES] system catalog relation of a table and a
[2001-12-16 06:30] Brent Verner said:
| [2001-12-16 00:42] Tom Lane said:
| | Brent Verner <brent@rcfile.org> writes:
| | > How about strtok_r?  I /really/ like the fact that strtok will
| | > eat either of the tokens ['"] that might be around the sequence
| | > name... just call me lazy :-)
| | 
| | That behavior creates one of the "corner cases" I was alluding to.
| | Shall I leave the difficulty as an exercise for the student?
| 
| Ok... I ended up working longer than I'd thought :-)
| 
| * no strtok were used in this patch. ;-)
| * Handles both serial-sequences and user-sequences referenced in 
|   nextval(...) default column defs.
| * Loop over tables so we can check wildcard table name in the future
|   per your suggestion.  I've only noted a TODO: regarding the wildcard
|   matching.
| * Instead of using a NULL terminated char** array to hold the collected
|   sequence names, I put in a simple strarray ADT -- mostly so I could
|   have the strarrayContains() test to call from the conditional around
|   dumpSequence().  If this is just dumb, I'll replace it with a simple
|   char** implementation.  Did I overlook some utility funcs in the
|   PG source that already does this?  If so, I'll gladly use those.
| * Patch is really attached :-P

This patch needs a fix already...  I just realized (while playing with
this code in a different context) that I forgot to change the malloc 
line in strarrayInit() after typedef'ing strarray as pointer to struct,
instead of just the struct.

-  strarray _ary = (strarray)malloc(sizeof(strarray));
+  strarray _ary = (strarray)malloc(sizeof(struct strarray));

cheers.
  brent

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

Mon, 17 Dec 2001 09:19:54 -0500
Brent Verner <brent@rcfile.org>
Re: system catalog relation of a table and a serial sequence
[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?

  brent

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

Mon, 17 Dec 2001 09:48:58 -0500
Brent Verner <brent@rcfile.org>
Re: [PATCHES] system catalog relation of a table and a serial
 sequence
Brent Verner writes:

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

Either all dependencies or no dependencies would be a "proper" solution,
in my mind.  Which one we should use is not obvious, that's why I stated
that question.

When you think about it, dumping the dependencies turns out to be less
useful than it seems at first.  Since any object can be a dependency for
more than one object it would not work in general to do

pg_dump -t table1 > out1
pg_dump -t table2 > out2

psql -f out1
psql -f out2

unless you mess with CREATE OR REPLACE, which we don't have for all
objects and which would probably not be possible to execute in all
situations.

So the only real use for "dump object X and all dependencies" would be to
extract a functional subset of one database into another.  But that seems
to be a lot less common operation.

Therefore I think that we should go with "no dependencies" (which is also
a lot easier, no doubt).

(Whether we should consider serial columns to be a dependency or an
integral part is a different question.)

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 17 Dec 2001 23:02:40 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [PATCHES] system catalog relation of a table and a
[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

Tue, 1 Jan 2002 18:01:52 -0500
Brent Verner <brent@rcfile.org>
Re: [PATCHES] system catalog relation of a table and a serial
 sequence

Brent, do you have a new, final patch that you want to submit for this?


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

Brent Verner wrote:
> [2001-12-16 06:30] Brent Verner said:
> | [2001-12-16 00:42] Tom Lane said:
> | | Brent Verner <brent@rcfile.org> writes:
> | | > How about strtok_r?  I /really/ like the fact that strtok will
> | | > eat either of the tokens ['"] that might be around the sequence
> | | > name... just call me lazy :-)
> | | 
> | | That behavior creates one of the "corner cases" I was alluding to.
> | | Shall I leave the difficulty as an exercise for the student?
> | 
> | Ok... I ended up working longer than I'd thought :-)
> | 
> | * no strtok were used in this patch. ;-)
> | * Handles both serial-sequences and user-sequences referenced in 
> |   nextval(...) default column defs.
> | * Loop over tables so we can check wildcard table name in the future
> |   per your suggestion.  I've only noted a TODO: regarding the wildcard
> |   matching.
> | * Instead of using a NULL terminated char** array to hold the collected
> |   sequence names, I put in a simple strarray ADT -- mostly so I could
> |   have the strarrayContains() test to call from the conditional around
> |   dumpSequence().  If this is just dumb, I'll replace it with a simple
> |   char** implementation.  Did I overlook some utility funcs in the
> |   PG source that already does this?  If so, I'll gladly use those.
> | * Patch is really attached :-P
> 
> This patch needs a fix already...  I just realized (while playing with
> this code in a different context) that I forgot to change the malloc 
> line in strarrayInit() after typedef'ing strarray as pointer to struct,
> instead of just the struct.
> 
> -  strarray _ary = (strarray)malloc(sizeof(strarray));
> +  strarray _ary = (strarray)malloc(sizeof(struct strarray));
> 
> cheers.
>   brent
> 
> -- 
> "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 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  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, Pennsylvania 19026

Thu, 7 Mar 2002 19:55:42 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: system catalog relation of a table and a serial sequence

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, Pennsylvania 19026

Thu, 7 Mar 2002 20:00:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] system catalog relation of a table and a serial
[2002-03-07 20:00] Bruce Momjian said:
| 
| Brent, is this the final version?

Can you hold 'til the weekend?  I'd like to look this over again,
but I'm swamped til tomorrow evening.  I'll send a final patch
Friday night unless someone objects to the approach.

thanks.
  brent

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

Thu, 7 Mar 2002 20:25:36 -0500
Brent Verner <brent@rcfile.org>
Re: [PATCHES] system catalog relation of a table and a serial
 sequence
Brent Verner wrote:
> [2002-03-07 20:00] Bruce Momjian said:
> | 
> | Brent, is this the final version?
> 
> Can you hold 'til the weekend?  I'd like to look this over again,
> but I'm swamped til tomorrow evening.  I'll send a final patch
> Friday night unless someone objects to the approach.

We are in no rush.  Just send it over when you are ready.  Thanks.

-- 
  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, Pennsylvania 19026

Thu, 7 Mar 2002 20:27:04 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] system catalog relation of a table and a serial