Thread: 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
caughtthem 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
creationof x is not going to be easy (don't anyone mention regexes here - this is not a good case for their use IMNSHO,
muchas 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
OIDSwith the right name at run time. So if we want to restore something with a different name, we'd just change the
storedname 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
allcatalog references happen by OID not by name. The only case where renaming the object after restoring it doesn't
workis if the object's original name collides with the name of an already existing object. But solving that seems much
simplerthan renaming objects while restoring them. We could, for example, simply rename the pre-existing object prior
torestoring, and rename it back aftwards. 

best regards,
Florian Pflug