search

bucardo / dbdpg

Perl Postgres driver DBD::Pg aka dbdpg
48 stars 36 forks source link

Add the data types in `contrib` to pg_types #39

Closed davidfetter closed 6 years ago

davidfetter commented 6 years ago

Things like hstore come back as unknown. I can see not including stuff like this if PostgreSQL didn't officially ship it, but types created in contrib seems like a relatively small lift.

machack666 commented 6 years ago

If they have a consistent/fixed OID, then it shouldn't be a problem, I would expect. Without looking at it, I'm not sure that's definitively the case. (ISTR it has the type/oid mapping hard-coded in the module, but it's been a while since I've looked.)

davidfetter commented 6 years ago

If it's not fixed, is it reasonable to do catalog lookups based on EXTENSIONs found?

machack666 commented 6 years ago

At what point? DBD::Pg connect? Cached per-handle? I would expect that this could fall apart unless you were caching/storing this info per DBH, as the same extension could have different OIDs depending on where you connected, so something simple like:

my $dbh1 = DBI->connect('dbi:Pg:dbname=database1', $user1, $pass1);
my $dbh2 = DBI->connect('dbi:Pg:dbname=database2', $user2, $pass2);

could introduce overhead both in connection time (if that's when we query/cache the data), or different/non-matching OIDs depending on the database/host queried (if we try to shared the data once per module initialization).

This is not to say that it'd be impossible, just there are real obstacles here.

machack666 commented 6 years ago

Yeah, looking at the code, there's not really any way to do this dynamically; you could conceivably use pg_types without connecting to the database, and it is indeed a static struct in types.c.

davidfetter commented 6 years ago

pg_types_dynamic with a warning about the overhead? Caching per DBH seems reasonable. With slightly more effort, probably as a separate feature, pg_types_dyname->{when} could be specified.

machack666 commented 6 years ago

What are you looking for specifically about that interface?

machack666 commented 6 years ago

It would need to be created as a DBH-level method, not a global function.

davidfetter commented 6 years ago

Oh, good point.

davidfetter commented 6 years ago

Come to think of it, everything we've discussed here seems like a DBH-level matter. If people want common attributes for all DBH's in their space, they know how.

machack666 commented 6 years ago

They are indeed free to query the system tables. :-) Some specific helper methods though might be nice, similar to ->table_info() and the like.