Thread: system catalog relation of a table and a serial sequence
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
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
[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
[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
Attachment
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
[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
[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
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
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
[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
[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
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
[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
> | > 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
[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
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
[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
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 |/
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
[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
Attachment
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
[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
[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
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
[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
statementsand setval() function calls for SERIAL columns in the table
-K, --all-sequences when dumping schema for a single table, output CREATE SEQUENCE
statementsand 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
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
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
[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
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, Pennsylvania19026