References to arbitrary database objects that are suitable for pg_dump

From: Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: References to arbitrary database objects that are suitable for pg_dump
Date: 2016-11-24 03:53:36
Message-ID: fb7be244-397a-3af3-2683-865aff92d00d@BlueTreble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Bapat 2016-11-24 04:17:39 Re: Calculation of param_source_rels in add_paths_to_joinrel
Previous Message Amit Kapila 2016-11-24 03:51:44 Re: Re: [bug fix] Cascading standby cannot catch up and get stuck emitting the same message repeatedly