restoring an object to a different name
This is a subject that has come up recently, and I can think of a number
of use cases for it.
However, there are lots of wrinkles. For example, the names of objects
appear in LOTS of places, and making sure we caught them all might be
quite tricky. Say you have a table x that inherits a,b, and c, and you
decide to restore with b renamed. Now x will have a dependency on b
recorded, but finding b in the opaque sql string that is stored for the
creation of x is not going to be easy (don't anyone mention regexes here
- this is not a good case for their use IMNSHO, much as I love them).
One idea I came up with was to set up the SQL using OIDS instead of
names as placeholders, and then replacing the OIDS with the right name
at run time. So if we want to restore something with a different name,
we'd just change the stored name in the node where it's defined and the
new name would then be picked up everywhere it's used (might need a
<catalog_oid, object_oid> pair, but the idea would be the same).
Does anyone else have anything better? I don't think this is something
that can be achieved cleanly with a small patch.
cheers
andrew
PS, if you want to see what info pg_restore actually has available in a
dump file, you might like to use my little utility at
<https://gist.github.com/1258232>.
On Oct4, 2011, at 00:59 , Andrew Dunstan wrote:
However, there are lots of wrinkles. For example, the names of objects appear in LOTS of places, and making sure we caught them all might be quite tricky. Say you have a table x that inherits a,b, and c, and you decide to restore with b renamed. Now x will have a dependency on b recorded, but finding b in the opaque sql string that is stored for the creation of x is not going to be easy (don't anyone mention regexes here - this is not a good case for their use IMNSHO, much as I love them).
One idea I came up with was to set up the SQL using OIDS instead of names as placeholders, and then replacing the OIDS with the right name at run time. So if we want to restore something with a different name, we'd just change the stored name in the node where it's defined and the new name would then be picked up everywhere it's used (might need a <catalog_oid, object_oid> pair, but the idea would be the same).
Hm, that is pretty much what happens if you rename the object after restoring it (using ALTER ... RENAME ...), since all catalog references happen by OID not by name. The only case where renaming the object after restoring it doesn't work is if the object's original name collides with the name of an already existing object. But solving that seems much simpler than renaming objects while restoring them. We could, for example, simply rename the pre-existing object prior to restoring, and rename it back aftwards.
best regards,
Florian Pflug