Not including the key in the SELECT results in weird behavior

[gkobluk]

I have this FOREIGN table:

CREATE FOREIGN TABLE global.t_shared (
    key text,
    value text
)
SERVER cypex_etcd
OPTIONS (rowid_column 'key');

If I SELECT:

SELECT *
FROM global.t_shared
WHERE key = '/gather/78';

Result:

-[ RECORD 1 ]------------------------------------------------------------------
key   | /gather/78
value | {"host_id": 75, "host_url": "postgres://pg-eu-01:5432/db",
          "gather_id": 78, "host_name": "pg-eu-01",
          "gather_name": "collect_stats", "gather_debug": true,
          "gather_purge": true, "gather_storage": "local",
          "gather_wanted_status": "planned"}

This works well too:

SELECT key, value
FROM global.t_shared
WHERE key = '/gather/78';

Result:

-[ RECORD 1 ]------------------------------------------------------------------
key   | /gather/78
value | {"host_id": 75, "host_url": "postgres://pg-eu-01:5432/db",
          "gather_id": 78, "host_name": "pg-eu-01",
          "gather_name": "collect_stats", "gather_debug": true,
          "gather_purge": true, "gather_storage": "local",
          "gather_wanted_status": "planned"}

But selecting only value (returns 0 rows)

SELECT value
FROM global.t_shared
WHERE key = '/gather/78';

Result:

(0 rows)

UPDATE presents the same behavior:

cypex=# update global.t_shared set key=key, value = value::jsonb || jsonb_build_object('gather_status', 'active') where key = '/gather/84';
UPDATE 1
cypex=# update global.t_shared set value = value::jsonb || jsonb_build_object('gather_status', 'active') where key = '/gather/84';
UPDATE 0

[gkobluk]

Additionally, joins between local and foreign tables should be reviewed too. This query return no results:

SELECT                                  
    g.*,
    sh.value::jsonb ->> 'gather_status'
FROM global.t_gather g
LEFT JOIN (select key, value from global.t_shared) sh
    ON sh.key = '/gather/' || g.id;

[Hunaid2000]

Initially, I thought this bug was related to recent commit where pushdown, but the issue can be reproduced on previous commits too like c225621.

While testing, I observed some interesting behaviors.

It seems that etcd sends the row to postgres but postgres filters it (Rows Removed by Filter: 1).
Also, though I selected only value column but plan shows both columns in tgts.
Wrappers: tgts = [Column { name: "value", num: 2, type_oid: 25 }, Column { name: "key", num: 1, type_oid: 25 }].

etcd_fdw=# select value from test where key = 'key2';
 value
-------
(0 rows)

etcd_fdw=# explain analyze select value from test where key = 'key2';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on test  (cost=0.00..1.00 rows=1 width=0) (actual time=0.032..0.034 rows=0 loops=1)
   Filter: (key = 'key2'::text)
   Rows Removed by Filter: 1
   Wrappers: quals = [Qual { field: "key", operator: "=", value: Cell(String("key2")), use_or: false, param: None }]
   Wrappers: tgts = [Column { name: "value", num: 2, type_oid: 25 }, Column { name: "key", num: 1, type_oid: 25 }]
   Wrappers: sorts = []
   Wrappers: limit = None
 Planning Time: 2.195 ms
 Execution Time: 3.665 ms
(9 rows)

When selecting key column, tgts in plan only shows key column.

etcd_fdw=# select key from test where key = 'key2';
 key
------
 key2
(1 row)

etcd_fdw=# explain analyze select key from test where key = 'key2';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on test  (cost=0.00..1.00 rows=1 width=0) (actual time=0.030..0.033 rows=1 loops=1)
   Filter: (key = 'key2'::text)
   Wrappers: quals = [Qual { field: "key", operator: "=", value: Cell(String("key2")), use_or: false, param: None }]
   Wrappers: tgts = [Column { name: "key", num: 1, type_oid: 25 }]
   Wrappers: sorts = []
   Wrappers: limit = None
 Planning Time: 2.014 ms
 Execution Time: 4.263 ms
(8 rows)

The issue seems related to how the FDW handles returned columns versus the query’s requested columns. This also appears to affect UPDATE and JOIN operations.

Let me explore more and if I can find something from supabase/wrappers repository.

[pashagolub]

thanks @Hunaid2000! Let me know if you will fix it faster than me! :) I'm trying to familiarize myself with the code base.

[Hunaid2000]

@pashagolub #29.