Hi hackers,
Right now jsonb functions are treated as non-shippable by postgres_fdw
and so predicates with them are not pushed down to foreign server:
create table jt(content jsonb);
create extension postgres_fdw;
create server pg_fdw FOREIGN DATA WRAPPER postgres_fdw options(host
'127.0.0.1', dbname 'postgres');
create user mapping for current_user server pg_fdw options (user
'postgres');
create foreign table fjt(content jsonb) server pg_fdw options
(table_name 'jt');
postgres=# explain select * from fjt where jsonb_exists(content, 'some');
QUERY PLAN
--------------------------------------------------------------
Foreign Scan on fjt (cost=100.00..157.50 rows=487 width=32)
Filter: jsonb_exists(content, 'some'::text)
It is because of the following check in postgres_fdw:
/*
* If function's input collation is not derived from a
foreign
* Var, it can't be sent to remote.
*/
if (fe->inputcollid == InvalidOid)
/* OK, inputs are all noncollatable */ ;
else if (inner_cxt.state != FDW_COLLATE_SAFE ||
fe->inputcollid != inner_cxt.collation)
return false;
In my case
(gdb) p fe->inputcollid
$1 = 100
(gdb) p inner_cxt.collation
$3 = 0
(gdb) p inner_cxt.state
$4 = FDW_COLLATE_NONE
I wonder if there is some way of making postgres_fdw to push this this
function to foreign server?
May be this check should be changed to:
if (fe->inputcollid == InvalidOid || inner_cxt.state ==
FDW_COLLATE_NONE)
/* OK, inputs are all noncollatable */ ;
