dblink - custom datatypes don't work

[Mark Gibson]

Hello,
I've been experimenting with dblink recently, and have encountered some
limitations I'd like to discuss.

I've been trying to create views of remote tables, like so:

CREATE VIEW stuff AS
SELECT *
FROM dblink(' ... remote connection info ... ',
'SELECT id, title, title_idx FROM stuff')
AS t(
id integer,
title text,
title_idx txtidx
);

But, dblink seems to have a problem with the custom datatype 'txtidx'
(from contrib/tsearch).

I get an error like this (from PostgreSQL 7.4.1):

ERROR: cache lookup failed for type 123456

Where 123456 is the pg_type.oid of the 'txtidx' type in the remote database,
which differs from the oid of the same datatype within the local database.

Are there anyways around this (other than trying to initialise the
datatypes remotely and
locally with the same oid - which would be highly impractical).

Is this a limitation of PostgreSQL or dblink?
Could dblink use type names instead of oid's?
If not, could dblink be adapted to use some kind of
remote oid -> local oid mapping table for datatypes?

I would be willing to have a poke around in dblink.c,
if someone could confirm my findings and point me in the right direction.

Cheers

--
Mark Gibson <gi*****@cromwell.co.uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Joe Conway]

Mark Gibson wrote:

[custom datatype oid mismatch between local and remote side of dblink ] Is this a limitation of PostgreSQL or dblink?
Could dblink use type names instead of oid's?
If not, could dblink be adapted to use some kind of
remote oid -> local oid mapping table for datatypes?

I would be willing to have a poke around in dblink.c,
if someone could confirm my findings and point me in the right direction.

Without actually having tried it, I think you could hack
pgresultGetTupleDesc() in dblink.c. Replace the line:
atttypid = PQftype(res, i);
with an SPI based local lookup using attname.

But I'm not sure if it is really a good idea in general to assume that
the same name for a datatype in two databases means that they are
actually the same datatype. It would be nice if there was a way to
register a "signature" of some kind for custom datatypes that would be
unique enough to be sure same named types were actually the same.

Joe

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Mark Gibson]

Joe Conway wrote:

Mark Gibson wrote:

[custom datatype oid mismatch between local and remote side of dblink ]

Without actually having tried it, I think you could hack
pgresultGetTupleDesc() in dblink.c. Replace the line:
atttypid = PQftype(res, i);
with an SPI based local lookup using attname.

But I'm not sure if it is really a good idea in general to assume that
the same name for a datatype in two databases means that they are
actually the same datatype. It would be nice if there was a way to
register a "signature" of some kind for custom datatypes that would be
unique enough to be sure same named types were actually the same.

But matching datatypes by name would surely be safer than matching by oid -
it's not impossible that different datatypes on each side of the dblink
share the same oid.
Infact, would it not be more likely that two datatypes with the same
name are actually
the same datatype, than two types with the same oid, not counting
internal types.
But then again, are internal types guaranteed to share the same oid between
PostgreSQL versions?

I think a signature would be very difficult to maintain, it would have
to be consistent for
a datatype (across platforms, and versions of PostgreSQL) while it's
semantics remained
the same, and also be universally unique - central registry etc., urgghh :p

Could a config option be added somewhere to switch between oid and name
matching.
With name matching it could be a policy of dblink usage that datatype
names must remain
consistent between db's - but I think this would generally be a good
thing away :)

Anyway, I'm off to hack around in dblink.c, it's the first time I've
tried SPI,
should be fun! Thanks for your help.

Cheers

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org