Re: [HACKERS] 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