Re: jsonb and nested hstore

On Wed, Mar  5, 2014 at 10:39:56AM -0500, Andrew Dunstan wrote:
> 
> On 03/05/2014 10:30 AM, Tom Lane wrote:
> >Merlin Moncure <mmoncure@gmail.com> writes:
> >>On Wed, Mar 5, 2014 at 9:24 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>>Also, there might be other cases besides arrays where we've embedded
> >>>type OIDs in on-disk data; anyone remember?
> >>composite types.
> >But that's only the composite type's own OID, no?  So it's not really
> >a problem unless the type we wanted to move into (or out of) core was
> >itself composite.
> >
> >            
> 
> 
> Sure, although that's not entirely impossible to imagine. I admit it
> seems less likely, and I could accept it as a restriction if we
> conquered the general case.

OK, so let's look at the general case.  Here is what pg_upgrade
preserves:
*  We control all assignments of pg_class.oid (and relfilenode) so toast*  oids are the same between old and new
clusters. This is important*  because toast oids are stored as toast pointers in user tables.**  While pg_class.oid and
pg_class.relfilenodeare initially the same*  in a cluster, they can diverge due to CLUSTER, REINDEX, or VACUUM*  FULL.
Inthe new cluster, pg_class.oid and pg_class.relfilenode will*  be the same and will match the old pg_class.oid value.
Becauseof*  this, old/new pg_class.relfilenode values will not match if CLUSTER,*  REINDEX, or VACUUM FULL have been
performedin the old cluster.**  We control all assignments of pg_type.oid because these oids are stored*  in user
compositetype values.**  We control all assignments of pg_enum.oid because these oids are stored*  in user tables as
enumvalues.**  We control all assignments of pg_authid.oid because these oids are stored*  in pg_largeobject_metadata.
 

It seems only pg_type.oid is an issue for hstore.  We can easily modify
pg_dump --binary-upgrade mode to suppress the creation of the hstore
extension.  That should allow user hstore columns to automatically map
to the new constant hstore oid.  We can also modify pg_upgrade to scan
all the user tables for any use of hstore arrays and perhaps composite
types and tell the user they have to drop and upgrade those table
separately.

Again, I am not asking what can be done for 9.4 but what is our final
goal, though the pg_upgrade change are minimal as we have done such
adjustments in the past.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +