On Thursday, November 2, 2017 at 10:25:19 PM UTC+1, Rodrigo Rosenfeld Rosas
wrote:
>
> I think I'm pretty lost with these OID mapping thing. I performed the
> following query both on PG 9.6 and PG 10 and got the same result:
>
> select typname, oid, typarray from pg_type where typname = 'json';
> typname | oid | typarray
> ---------+-----+----------
> json | 114 | 199
>
Correct, the JSON OID type is statically assigned, at least since it was
officially added (in 9.2 I think).
> It seems like the JSON oid, assuming I'm looking at the right columns, for
> built-in types such as JSON should remain the same when I upgrade the
> database. However, if I remember correctly the application was interpreting
> a JSON column as if it was TEXT which means I'm missing something here
> because it doesn't seem to be a matter of simply ensuring those type OIDs
> are mapped...
>
Well, if you recall this specifically happening to a JSON column, I don't
think that could be caused by an OID issues, as the OIDs remain the same.
> I mean, I have just a handful of custom types and it would be probably
> easy to add those mappings to the initialization code and deploy a new
> version before switching to the new DB version. However I'm not sure if
> this would be enough.
>
> But I was also wondering, can't this approach yield to conflicts? I mean,
> maybe the same OID is present for both databases but requiring different
> conversion procs.
>
Obviously the possibility for that increases as the number of custom type
OIDs in use increases. However, it's probably fairly low, and you can at
least detect it if it does happen by looking for overlapping dynamic OIDs
on the servers.
> For example, if I understood correctly, in one of your proposed solutions,
> I'd add PG 10 as a read-only slave and keep 9.6 as the master and deploy
> the new version. I'd register the custom types in both databases using a
> with_server block. Then I'd do something like this:
>
> in with_server block I'd assign variables such as:
>
> pg96_conversion_procs = DB.conversion_procs # in PG 9.6 with_server block
> pg10_conversion_procs = DB.conversion_procs # in PG 10 with_server block
>
> (pg10_conversion_procs.keys - pg96_conversion_procs.keys).each {|k|
> pg96_conversion_procs[k] = pg10_conversion_procs[k] }
>
Well, not that exactly. If you use the server block extension to register
the the keys, it will update the conversion_procs directly, you won't need
to reassign it. pg96_conversion_procs and pg10_conversion_procs reference
the same object in your example code.
> Assuming there's no conflicting OIDs between both databases, it should be
> safe to switch the databases without restarting the application.
>
> Did I understand your proposal correctly this time? I just have this
> feeling that this wouldn't be enough because of errors related to JSON
> columns being interpreted as TEXT when I switched the database versions...
> After all, the JSON OID didn't change among those servers.
>
Correct, the problem is elsewhere if this happened to JSON, and I don't
have an idea where.
> I'll try to find some time to try those methods and will let you know in
> case I face any issues with that approach.
>
Great! Please let me know what you find out.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
