References to arbitrary database objects that are suitable for pg_dump
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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
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.
Can't you do it like pg_depend does, that is store the class and object
OIDs? Then use pg_describe_object() to turn that into text when dumping.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/25/16 6:00 PM, Tom Lane wrote:
OIDs? Then use pg_describe_object() to turn that into text when dumping.
How would I get pg_dump to do that?
I could certainly make this work if there was some way to customize how
pg_dump dumps things, but AFAIK there's no way to do that, even with
extensions.
--
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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/26/16 12:30 AM, Jim Nasby wrote:
On 11/25/16 6:00 PM, Tom Lane wrote:
OIDs? Then use pg_describe_object() to turn that into text when dumping.
How would I get pg_dump to do that?
I could certainly make this work if there was some way to customize how
pg_dump dumps things, but AFAIK there's no way to do that, even with
extensions.
Is there anything that will translate an object identity (as returned by
pg_event_trigger_ddl_commands) into class/object/subobject OIDs? If I
had that, I could use it to track objects by OID (like pg_depend does),
as well as storing the text representation. That way I can have an event
trigger that updates the stored pg_describe_object() text any time an
object was renamed. Without that, I don't see any good way to find the
original name of a renamed object (so that I can update my reference to it).
ISTM it would be useful to add address_names and address_args to
pg_event_trigger_ddl_commands(), as well as adding some way to get the
original information for an object when an ALTER is being done. In
particular, if something (such as a column) is being renamed.
BTW, why were pg_get_object_address and pg_identify_object_as_address
added instead of creating a function that accepted the output of
pg_identify_object()? Is there a reason that wouldn't work?
--
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)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim Nasby wrote:
Is there anything that will translate an object identity (as returned by
pg_event_trigger_ddl_commands) into class/object/subobject OIDs? If I had
that, I could use it to track objects by OID (like pg_depend does), as well
as storing the text representation. That way I can have an event trigger
that updates the stored pg_describe_object() text any time an object was
renamed. Without that, I don't see any good way to find the original name of
a renamed object (so that I can update my reference to it).
BTW, why were pg_get_object_address and pg_identify_object_as_address added
instead of creating a function that accepted the output of
pg_identify_object()? Is there a reason that wouldn't work?
I suppose it's because your use case is different than the one I was
trying to satisfy, so we have different ideas on what is necessary.
ISTM it would be useful to add address_names and address_args to
pg_event_trigger_ddl_commands(), as well as adding some way to get the
original information for an object when an ALTER is being done. In
particular, if something (such as a column) is being renamed.
Yeah, this info is available in the pg_ddl_command object, but you need
to write some C code to get to it, as the structs are specific to each
possible command. I suppose we could have written another function such
as the one with have for drops (pg_event_trigger_dropped_objects). As
far as I can tell we don't have any commands that rename more than one
object at a time, so it would be simpler, but I don't know how likely
that is to change.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers