Re: Dump EXTENSION sequences too

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 jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net