#11 pgsql_fdw's foreign table cann't used in plpgsql function

closed-fixed
pgsql_fdw (6)
5
2014-08-22
2012-07-04
Shigeru Hanada
No

Original report was submitted by digoal.zhou via pgsql-bugs.

----

Bug reference: 6708
Logged by: digoal.zhou
Email address: digoal@126.com
PostgreSQL version: 9.1.3
Operating system: CentOS 5.x bit64
Description:

pgsql_fdw's foreign table "cache lookup failed for type 0" bug when used in
function
1.INSTALL
https://github.com/dvarrazzo/interdbconnect/downloads
mv pgsql_fdw $PG_SRC/contrib/
cd $PG_SRC/contrib/pgsql_fdw
. /home/postgres/.bash_profile
USE_PGXS=1 make
USE_PGXS=1 make install
2.TEST
digoal=# create extension pgsql_fdw;
CREATE EXTENSION

CREATE SERVER srv_digoal FOREIGN DATA WRAPPER pgsql_fdw
OPTIONS (host '172.16.3.33', port '5432', dbname 'digoal');

CREATE USER MAPPING FOR digoal SERVER srv_digoal
OPTIONS (user 'rmt_digoal', password 'DIGOAL123');

grant usage on foreign server srv_digoal to digoal;

\c digoal digoal
CREATE FOREIGN TABLE test
(
id int
) server srv_digoal options (nspname 'digoal', relname 'table_digoal');

select * from test;
It can do success, and return currect rows;

but , if test table in plpgsql function , then error occur:
create or replace function f_test () returns int as $$
declare
begin
perform 1 from test limit 1;
return 0;
end;
$$ language plpgsql;

digoal=# \set VERBOSITY verbose
digoal=# select * from f_test();
ERROR: XX000: cache lookup failed for type 0
CONTEXT: SQL statement "SELECT 1 from test limit 1"
PL/pgSQL function "f_test" line 3 at PERFORM
LOCATION: getTypeOutputInfo, lsyscache.c:2440

3. DETAIL information please visit my blog :
http://blog.163.com/digoal@126/blog/static/16387704020125218171919/

thanks very much;

Discussion

  • Shigeru Hanada
    Shigeru Hanada
    2012-07-04

    The problem could be reproduced.

    The cause is extra parameter entries in ParamListInfo which are added by PL/pgSQL for variables, such as FOUND, and function parameters even if they are not used in the remote query. They might not be instanciated when pgsql_fdw query is invoked and require fetchParam invocation. Even after that, unused entries have InvalidOid for type oid so libpq emits error though they are not used in the remote query.

    The solution I chose is to use TEXTOID (oid of text data type) for such unused parameters. This is not most desirable way, but seems a acceptable workaround.

     
  • Shigeru Hanada
    Shigeru Hanada
    2012-07-04

    • status: open --> closed-fixed