Re: [HACKERS] References to arbitrary database objects that are suitable for pg_dump
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
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
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
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
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