Dynamic type oid's

[David Welton]

Hi,

This is "interesting":

https://github.com/epgsql/epgsql/pull/6

It turns out that when you do CREATE EXTENSION, it assigns a dynamic
OID to the type, so that it's not possible to just write it down in
the pgsql_types.erl file.

Offhand, I can think of a few ways of trying to handle this:

* Look up any dynamic types we are interested in when we start up, and
if someone messes with them while things are running... too bad.

* Every time we get an unknown oid, we look it up in Postgres with a
query, cache the result, and use it.

The second seems more 'correct', but probably more of a pain in the
neck to get right.

Other thoughts?
--
David N. Welton

http://www.welton.it/davidw/

http://www.dedasys.com/

[Anton Lebedevich]

On 04/18/2014 12:34 PM, David Welton wrote:
> Hi,
>
> This is "interesting":
>
> https://github.com/epgsql/epgsql/pull/6
>
> It turns out that when you do CREATE EXTENSION, it assigns a dynamic
> OID to the type, so that it's not possible to just write it down in
> the pgsql_types.erl file.
>
> Offhand, I can think of a few ways of trying to handle this:
>
> * Look up any dynamic types we are interested in when we start up, and
> if someone messes with them while things are running... too bad.
>
> * Every time we get an unknown oid, we look it up in Postgres with a
> query, cache the result, and use it.
>
> The second seems more 'correct', but probably more of a pain in the
> neck to get right.
>
> Other thoughts?

I'm recalling that there were something done in that area in JDBC driver
for Postgresql. Maybe we could grab some ideas from it.

Regards,
Anton Lebedevich.

[Bach Le]

The JDBC driver has a per-connection type cache:

https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2Connection.java#L60

https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/TypeInfoCache.java

It is close to what I want to suggest since users can totally connect to two different databases or servers. The cache should be per-connection.

I think it can be handled this way:

When a connection is opened, user needs to supply a list({TypeName :: atom(), EncodeFun :: fun(), DecodeFun :: fun()}) or map(TypeName => {EncodeFun, DecodeFun}). The connection will perform some queries to figure out the type mapping, array type can be handled automatically by appending "[]" to the type name. This option can be passed along with host name, port ... during creation as part of the pool setting.

When extensions are created/dropped on the fly, users are responsible for restarting connections or calling update_type_map on all connection.

[David Welton]

> I think it can be handled this way:
>
> When a connection is opened, user needs to supply a list({TypeName ::
> atom(), EncodeFun :: fun(), DecodeFun :: fun()}) or map(TypeName =>
> {EncodeFun, DecodeFun}). The connection will perform some queries to figure
> out the type mapping, array type can be handled automatically by appending
> "[]" to the type name. This option can be passed along with host name, port
> ... during creation as part of the pool setting.
>
> When extensions are created/dropped on the fly, users are responsible for
> restarting connections or calling update_type_map on all connection.

Yes, I think this is the most reasonable strategy. I actually started
coding up something like it on Friday:

cache_dynamic_types(C) ->
{ok, _Cols, Rows} = equery(C, "select typname, typarray, oid from
pg_catalog.pg_type"),
Types = [hstore],
lists:map(fun(T) ->
{Name, BinArrOid, BinOid} =
lists:keyfind(atom_to_binary(T, latin1), 1, Rows),
ArrOid = binary_to_integer(BinArrOid),
Oid = binary_to_integer(BinOid),
%% Stash the results in the process dictionary,
%% for both regular types and array types.
put({oid2type, Oid}, T),
put({type2oid, T}, Oid),
put({oid2type, ArrOid}, {array, T}),
put({type2oid, {array, T}}, ArrOid),
[{T, Oid}, {{array, T}, ArrOid}]
end, Types).

I won't likely be able to finish it up (for instance moving the
process dictionary stuff into the gen_server!) until Tuesday, so if
anyone else wants to roll with this, have at it! I agree this should
happen at connection time. It should also be exposed as a command for
weird cases when people want to regenerate the cache.

[David Welton]

I have pushed a first stab at solving this here:

https://github.com/epgsql/epgsql/tree/dynamic_types

Let me know what you think. One thing I'm considering: adding a db
option to start without doing the lookup/cache, for faster startup
times for people who don't care about hstore. This would probably be
off by default so that people don't wonder why they don't have hstore.

[Bach Le]

I know the existing driver already uses process dictionary for datetime. However, I don't think it's a good idea, especially using process dictionary cross-module.

It may result in lots of rewrite though.

[David Welton]

For those following along on the mailing list, I ended up going with
Bach Le's patch, which was very intrusive, because of the need to
carry around some state in a lot of new places, but avoids the process
dictionary. A big thanks to him for doing that work.

> --
> You received this message because you are subscribed to the Google Groups
> "Erlang epgsql Postgres driver use and development" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to epgsql+un...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.