Re: [GENERAL] dblink - custom datatypes don't work

From: Mark Gibson <gibsonm(at)cromwell(dot)co(dot)uk>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: "Hackers (PostgreSQL)" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [GENERAL] dblink - custom datatypes don't work
Date: 2004-02-10 16:44:02
Message-ID: 40290A52.9000800@cromwell.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers pgsql-patches

Joe Conway wrote:

>>> 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.
>>

Hi,
Well I've been adventuring into the realms of SPI and dblink over
the last couple of days.
I've taken the approach of having a table that maps remote oids to local
oids:

CREATE TABLE public.dblink_oid_map (
db_id name, -- unique identifier for a remote database (see notes below)
remote_oid oid,
local_oid oid,
typname name, -- name of the datatype on remote database (aids error
reporting)
PRIMARY KEY (db_id,remote_oid)
)
WITHOUT OIDS;

I've modified dblink.c to look up the local oid from this table
(see attached patch, against 7.4.1).
By default all dblink functions will use this table.
The table can be built using dblink itself, but to avoid a chicken-egg
situation
I've created a new variation on the dblink function:

CREATE OR REPLACE FUNCTION public.dblink (text,text,bool)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE 'C' WITH (isstrict);

The last arg can be set to FALSE to disable the oid map.
This allows the following function to build the oid map for a remote db:
(The arg is a connection string passed to dblink)

CREATE OR REPLACE FUNCTION public.dblink_update_oid_map(text)
RETURNS void
AS '
DECLARE
v_connstr ALIAS FOR $1;
v_db_id name := ''test'';
BEGIN
DELETE FROM dblink_oid_map WHERE db_id = v_db_id;
INSERT INTO dblink_oid_map (db_id, remote_oid, local_oid, typname)
SELECT
v_db_id, r.oid, l.oid, r.typname
FROM
dblink(v_connstr,
''SELECT oid, typname FROM pg_type WHERE typtype IN
(''''b'''',''''d'''')'',
false
) AS r (oid oid, typname name)
LEFT JOIN pg_type l ON (l.typname = r.typname);
RETURN;
END;
' LANGUAGE 'plpgsql';

I wasn't sure what to do about datatypes in different schemas,
this approach allows an admin to customise the map.
I usually have all custom types in 'public' so it isn't a problem for me.

All datatypes from the remote database are included in the table,
so in some cases 'local_oid' may be NULL -
the patch handles this by reporting an error informing the user that the
datatype
doesn't exist locally, hence the 'typname' column.

We need to find a way to uniquely identify a remote database, and create a
consistent id algorythm for the 'db_id' column.
A hash of the connection string attributes: 'host'/'hostaddr' + 'dbname'
+ 'port' ???
For testing i've used an db_id of type 'name' with the value: 'test'.

Unfortunately, it doesn't work using the oid the map, whether custom types
are involved or not. All I get is the following message:

ERROR: unsupported byval length: nnnn

SPI is very new to me (like 2 days old ;).
Any suggestions where I've gone wrong?

Cheers

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

Attachment Content-Type Size
dblink_oid_map.patch text/plain 4.4 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Crawford 2004-02-10 16:47:16 Re: I want to use postresql for this app, but...
Previous Message Artemy 2004-02-10 16:40:41 PostgreSQL License

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2004-02-10 17:16:00 Re: [PATCHES] Current-stream read for psql's \copy
Previous Message Tom Lane 2004-02-10 15:55:41 Re: [PATCHES] Current-stream read for psql's \copy

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2004-02-10 17:16:00 Re: [PATCHES] Current-stream read for psql's \copy
Previous Message Scott Goodwin 2004-02-10 16:38:16 Re: Patch to psql to allow SEARCH_PATH to be set from env