Re: [HACKERS] References to arbitrary database objects that are suitable for pg_dump

[Alvaro Herrera]

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 Herrerahttps://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

Re: [HACKERS] References to arbitrary database objects that are suitable for pg_dump

[Jim Nasby]

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

Re: [HACKERS] References to arbitrary database objects that are suitable for pg_dump

[Jim Nasby]

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

Re: [HACKERS] References to arbitrary database objects that are suitable for pg_dump

[Tom Lane]

Jim Nasby  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

[HACKERS] References to arbitrary database objects that are suitable for pg_dump

[Jim Nasby]

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