Thread: dblink - custom datatypes don't work

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 <gibsonm@cromwell.co.uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

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

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.