On 4/19/12 5:42 AM, Gianni Ciolli wrote:
currently an EXTENSION can mark some of its tables as "configuration
tables" using pg_catalog.pg_extension_config_dump(), so that pg_dump
"does the right thing".
I think it would be useful to mark sequences too, but unfortunately it
is not possible; hence, each time a dump is reloaded, all the
sequences in the extension are reset to 1, causing all the related
problems.
Moreover, the error message that we get if we try to mark a sequence
does not mention the requirement that the relation is a table. The
"OID %u does not refer to a table" error message seems to be wrongly
restricted to the case when get_rel_name can't find a relation.
Is there any objection to the above proposal? I did a little search of
the archives, but I couldn't find any related discussions; I apologise
if I missed something.
I'll toss in something related to this...
At work we use the concept of "seed tables" that have their data dumped along with their
structure (using a script that's calling pg_dump). These are similar to the concept of
"configuration tables".
The problem that we've discovered with this is that surrogate keys based on
sequences can really screw you if you're not careful. The issue comes about if
you're using the dump in more than one database (ie: a dump of a common set of
tools) and the different databases have also added configuration. In that
scenario it's easy to end up with duplicated surrogate key values.
The solution we plan to implement to get around this is to add support for
dumping config data via something other than just copying raw table data. So
our dump script would call a database function that would be responsible for
spitting out raw SQL that gets injected directly into the dump. That SQL would
then be able to remove all references to surrogate keys (doing stuff like
INSERT WHERE NOT EXISTS and then JOINing to avoid outputting raw surrogate
keys).
--
Jim C. Nasby, Database Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
