From: | Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | jsonb, collection & postgres_fdw |
Date: | 2020-08-13 15:24:37 |
Message-ID: | a90c64e5-e144-8d69-4de3-68c06d10b0f3@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 */ ;
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2020-08-13 15:45:52 | Re: Switch to multi-inserts for pg_depend |
Previous Message | Amit Kapila | 2020-08-13 13:17:20 | Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions |