Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
sorry for the ugly formate, anyone can tell me how to format it ? –  francs Jun 26 '12 at 5:58
    
Above the text field there is that {} button, it formats selected text as code (actually prepends 4 spaces to each row). –  dezso Jun 26 '12 at 6:37
    
thanks you ,dezso :) –  francs Jun 26 '12 at 6:46
    
Do \set VERBOSITY verbose before running the stored proc and check for further messages. –  dezso Jun 26 '12 at 10:40
    
I paste the verbose meeage above. –  francs Jun 28 '12 at 1:17

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Thank you very much , Using "Excute sql " works now ! –  francs Jun 30 '12 at 4:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.