References to arbitrary database objects that are suitable for pg_dump - Mailing list pgsql-hackers

From Jim Nasby
Subject References to arbitrary database objects that are suitable for pg_dump
Date
Msg-id fb7be244-397a-3af3-2683-865aff92d00d@BlueTreble.com
Whole thread Raw
Responses Re: References to arbitrary database objects that are suitable for pg_dump
List pgsql-hackers
I'm working on an extension that is meant to enable metaprogramming 
within Postgres, namely around the creation of objects. For example, a 
"lookup table" class might create a table, grant specific permissions on 
that table, and create several functions to support getting data from 
the table. You could think of this as similar to creating an extension, 
except you get to control aspects of the objects being created (notably 
their names), and you can create as many as you want (baring name 
collisions).

As part of doing this, I need a way to identify each of these classes. 
Since this is geared towards creating database objects, a natural choice 
is to pick a particular object as the "unique object" for the class. In 
this example, the table would serve that purpose.

The challenge I'm running into is finding a way to store a reference to 
an arbitrary object that will survive a rename as well as working with 
pg_dump.

One option would be to store the output of pg_get_object_address(); 
AFAICT that's guaranteed never to change unless the object is dropped. 
But, that would become useless after restoring from a pg_dump; the oid's 
could now be pointing at any random object.

Another option would be to store what you would pass into 
pg_get_object_address(). That would work great with pg_dump, but a 
rename, a move to another schema, or possibly other operations would 
render the stored names incorrect.

The last option I see is to have a table that contains a number of reg* 
fields. Those would remain accurate through any renames or other 
operations (other than a DROP, which would be easy enough to handle), 
and because they would dump as text a reload would work as well. The 
downside is not every object has a reg* pseudotype, but I can live with 
that for now.

Have I missed any other possibilities?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)



pgsql-hackers by date:

Previous
From: Fabien COELHO
Date:
Subject: Re: confusing checkpoint_flush_after / bgwriter_flush_after
Next
From: Tom Lane
Date:
Subject: Re: confusing checkpoint_flush_after / bgwriter_flush_after