dblink - custom datatypes don't work

From: Mark Gibson <gibsonm(at)cromwell(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: dblink - custom datatypes don't work
Date: 2004-02-05 14:46:57
Message-ID: 40225761.8000706@cromwell.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers pgsql-patches

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(at)cromwell(dot)co(dot)uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Iker Arizmendi 2004-02-05 15:00:21 ERROR: column 'xxx' does not exist (under v. 7.4.1)
Previous Message Mike Charnoky 2004-02-05 14:23:23 pg_restore and large files

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2004-02-05 14:54:49 Re: [HACKERS] Sync vs. fsync during checkpoint
Previous Message Bruce Momjian 2004-02-05 14:34:44 Re: PITR Dead horse?

Browse pgsql-patches by date

  From Date Subject
Next Message Andrew Dunstan 2004-02-05 15:07:07 dollar quoting
Previous Message Andrew Dunstan 2004-02-05 14:34:33 Re: [PATCHES] log session end - again