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

On Thu, Apr 19, 2012 at 6:42 AM, Gianni Ciolli
<gianni.ciolli@2ndquadrant.it> 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.

Seems like a good idea to me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company