Postgresql – Using pgsql_fdw in a function result “ERROR: cache lookup failed for type 0”

postgresql

Now we want to use pgsql_fdw to select a table of remote postgresql database，When we
select the table in a session it is okey ,but when we use the foreign table in a funciton
it turns out "ERROR: cache lookup failed for type 0" ， anybody knows it ,thanks !

--1 base informaiton

skytf=> \d ft_test;
       Foreign table "skytf.ft_test"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(32) | 
Server: pgsql_srv


skytf=> \des+ pgsql_srv
                                                List of foreign servers
   Name    | Owner | Foreign-data wrapper | Access privileges | Type | Version |                Options                 
-----------+-------+----------------------+-------------------+------+---------+----------------------------------------
 pgsql_srv | skytf | pgsql_fdw            |                   |      |         | {host=127.0.0.1,port=1923,dbname=mydb}
(1 row)

--2 destination table 
mydb=> \d test
             Table "mydb.test"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(32) | 
Indexes:
    "idx_test_1" btree (id)

--3 function
 CREATE or replace FUNCTION  func_sync_bill() RETURNS INTEGER  AS $$
    BEGIN

     begin
      insert into test_tf (id,name) select id,name from ft_test;
      return 1;
     end; 
    END;
  $$ LANGUAGE 'plpgsql';




--4 it works in a session
skytf=> create table test_tf(id integer,name varchar(32));
CREATE TABLE

skytf=> insert into test_tf select * from ft_test;
INSERT 0 1990000


--5 function call error
skytf=> truncate table test_tf;
TRUNCATE TABLE

skytf=> select func_sync_bill();
ERROR:  cache lookup failed for type 0
CONTEXT:  SQL statement "insert into test_tf (id,name) select id,name from ft_test"
PL/pgSQL function "func_sync_bill" line 5 at SQL statement

when I call the function func_sync_bill() which will select a foreign table , it turns out the error.
Is this a bug of pgsql_fdw?

–verbose meeage

    skytf=> \set VERBOSITY verbose
    skytf=> select func_sync_bill();
    ERROR:  XX000: cache lookup failed for type 0
    CONTEXT:  SQL statement "insert into test_tf (id,name) select id,name from ft_test"
    PL/pgSQL function "func_sync_bill" line 5 at SQL statement

LOCATION:  getTypeOutputInfo, lsyscache.c:2441

Best Answer

Someone recently submitted a formal bug report that looks identical to your question. Without any follow-up so far.

If the problem is a mismatch between pgsql_fdw implementation and the way the plpgsql interpreter prepares its queries, using EXECUTE might help:

EXECUTE 'insert into test_tf (id,name) select id,name from ft_test';

This forces plpgsql to avoid pre-processing the query, and it's somehow the generic workaround against this kind of errors.

But anyway it looks like pgsql_fdw is quite experimental at the moment, it's not included in the PostgreSQL contrib directory, and its source code is suprisingly not available for download at: http://sourceforge.net/projects/interdbconnect/files/ where we would expect it. Currently, you may have more success with dblink, the "old way".

Related Question