Re: [GENERAL] dblink - custom datatypes don't work - Mailing list pgsql-hackers

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.

--- dblink.c-741    2003-11-28 05:03:48.000000000 +0000
+++ dblink.c    2004-02-10 16:31:11.305534520 +0000
@@ -82,7 +82,7 @@
 static int16 get_attnum_pk_pos(int16 *pkattnums, int16 pknumatts, int16 key);
 static HeapTuple get_tuple_of_interest(Oid relid, int16 *pkattnums, int16 pknumatts, char **src_pkattvals);
 static Oid    get_relid_from_relname(text *relname_text);
-static TupleDesc pgresultGetTupleDesc(PGresult *res);
+static TupleDesc pgresultGetTupleDesc(PGresult *res, const bool use_oid_map);
 static char *generate_relation_name(Oid relid);

 /* Global */
@@ -457,7 +457,7 @@
         if (functyptype == 'c')
             tupdesc = TypeGetTupleDesc(functypeid, NIL);
         else if (functyptype == 'p' && functypeid == RECORDOID)
-            tupdesc = pgresultGetTupleDesc(res);
+            tupdesc = pgresultGetTupleDesc(res, true);
         else
             /* shouldn't happen */
             elog(ERROR, "return type must be a row type");
@@ -550,6 +550,7 @@
         char       *sql = NULL;
         char       *conname = NULL;
         remoteConn *rcon = NULL;
+        bool        use_oid_map = true;

         /* create a function context for cross-call persistence */
         funcctx = SRF_FIRSTCALL_INIT();
@@ -559,20 +560,25 @@
          * calls
          */
         oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
-
-        if (PG_NARGS() == 2)
-        {
+
+        switch (PG_NARGS()) {
+        case 3:
+            if (!PG_ARGISNULL(2))
+                use_oid_map = PG_GETARG_BOOL(2);
+        case 2:
             DBLINK_GET_CONN;
             sql = GET_STR(PG_GETARG_TEXT_P(1));
-        }
-        else if (PG_NARGS() == 1)
-        {
+            break;
+
+        case 1:
             conn = persistent_conn;
             sql = GET_STR(PG_GETARG_TEXT_P(0));
-        }
-        else
+            break;
+
+        default:
             /* shouldn't happen */
             elog(ERROR, "wrong number of arguments");
+        }

         if (!conn)
             DBLINK_CONN_NOT_AVAIL;
@@ -620,7 +626,7 @@
             if (functyptype == 'c')
                 tupdesc = TypeGetTupleDesc(functypeid, NIL);
             else if (functyptype == 'p' && functypeid == RECORDOID)
-                tupdesc = pgresultGetTupleDesc(res);
+                tupdesc = pgresultGetTupleDesc(res, use_oid_map);
             else
                 /* shouldn't happen */
                 elog(ERROR, "return type must be a row type");
@@ -1802,7 +1808,7 @@
 }

 static TupleDesc
-pgresultGetTupleDesc(PGresult *res)
+pgresultGetTupleDesc(PGresult *res, const bool use_oid_map)
 {
     int            natts;
     AttrNumber    attnum;
@@ -1813,6 +1819,14 @@
     bool        attisset;
     Oid            atttypid;
     int            i;
+
+    void       *plan = NULL;
+    Oid            arg[2] = {NAMEOID,OIDOID};
+    int            ret = 0;
+    Datum        values[2];
+    Datum        local_oid;
+    char       *typname;
+    bool        isnull;

     /*
      * allocate a new tuple descriptor
@@ -1824,6 +1838,28 @@

     desc = CreateTemplateTupleDesc(natts, false);

+    if (use_oid_map) {
+        /*
+         * Connect to SPI manager
+         */
+        if ((ret = SPI_connect()) < 0)
+            /* internal error */
+            elog(ERROR, "SPI_connect failure - returned %d", ret);
+
+        /*
+         * Create query plan for mapping remote oid to local oid
+         */
+        plan = SPI_prepare("SELECT local_oid, typname FROM dblink_oid_map WHERE db_id = $1 AND remote_oid = $2", 2,
arg);
+        if (plan == NULL)
+            /* internal error */
+            elog(ERROR, "SPI_prepare returned %d", SPI_result);
+
+        /*
+         * TODO: Determine unique id of remote database (instead of "test")
+         */
+        values[0] = PointerGetDatum("test");
+    }
+
     attnum = 0;

     for (i = 0; i < natts; i++)
@@ -1839,6 +1875,33 @@
         atttypid = PQftype(res, i);
         atttypmod = PQfmod(res, i);

+        if (use_oid_map) {
+            values[1] = ObjectIdGetDatum(atttypid);
+
+            if ((ret = SPI_execp(plan, values, NULL, 1)) < 0)
+                elog(ERROR, "SPI_execp returned %d", ret);
+
+            if (SPI_processed < 1)
+                ereport(ERROR,
+                        (errcode(ERRCODE_MOST_SPECIFIC_TYPE_MISMATCH),
+                         errmsg("unknown remote datatype"),
+                         errdetail("Remote datatype oid \"%d\" is not in oid map",
+                                 atttypid)));
+
+            local_oid = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);
+
+            if (isnull) {
+                typname = SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 2);
+                ereport(ERROR,
+                        (errcode(ERRCODE_MOST_SPECIFIC_TYPE_MISMATCH),
+                         errmsg("unknown local datatype"),
+                         errdetail("Remote datatype \"%s\" is not mapped " \
+                                 "to a local datatype", (char *) DatumGetPointer(typname))));
+            }
+
+            atttypid = DatumGetObjectId(local_oid);
+        }
+
         if (PQfsize(res, i) != get_typlen(atttypid))
             ereport(ERROR,
                     (errcode(ERRCODE_MOST_SPECIFIC_TYPE_MISMATCH),
@@ -1855,6 +1918,10 @@
                            atttypmod, attdim, attisset);
     }

+    if (use_oid_map) {
+        SPI_finish();
+    }
+
     return desc;
 }