jsonb, collection & postgres_fdw
От | Konstantin Knizhnik |
---|---|
Тема | jsonb, collection & postgres_fdw |
Дата | |
Msg-id | a90c64e5-e144-8d69-4de3-68c06d10b0f3@postgrespro.ru обсуждение исходный текст |
Ответы |
Re: jsonb, collection & postgres_fdw
Re: jsonb, collection & postgres_fdw |
Список | 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 */ ;
В списке pgsql-hackers по дате отправления: