Thread: Dump EXTENSION sequences too
Hi, 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. Thanks, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it
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'scalling 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 notcareful. 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 surrogatekey values. The solution we plan to implement to get around this is to add support for dumping config data via something other than justcopying raw table data. So our dump script would call a database function that would be responsible for spitting outraw 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