jsonb, collection & postgres_fdw

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 */ ;

Responses

Browse pgsql-hackers by date

  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