Hello,
It looks like it is impossible to use the “PK shorthand” GROUP BY form (introduced in 9.1) via postgres_fdw foreign data wrapper.
A simple test case:
(Local DB)
testn=> \d franks.t1
Table "franks.t1"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
c1 | integer | | not null |
c2 | integer | | |
c3 | integer | | |
c4 | character varying(255) | | |
Indexes:
"t1_pkey" PRIMARY KEY, btree (c1)
testn=> select c1, c2, c3, c4 from franks.t1 group by c1;
c1 | c2 | c3 | c4
----+----+----+------
3 | 1 | 1 | aaaa
2 | 1 | 1 | aaaa
1 | 1 | 1 | aaaa
(3 rows)
(Remote DB)
testn=> \d pekkas.t1
Foreign table "pekkas.t1"
Column | Type | Collation | Nullable | Default | FDW options
--------+------------------------+-----------+----------+---------+--------------------
c1 | integer | | not null | | (column_name 'c1')
c2 | integer | | | | (column_name 'c2')
c3 | integer | | | | (column_name 'c3')
c4 | character varying(255) | | | | (column_name 'c4')
Server: testn
FDW options: (schema_name 'franks', table_name 't1', use_remote_estimate 'true')
testn=> select c1, c2, c3, c4 from pekkas.t1 group by c1;
ERROR: column "t1.c2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select c1, c2, c3, c4 from pekkas.t1 group by c1;
Since the remote end has no idea that “c1” is the PK for “t1”, it flat out rejects SQLs that are perfectly legit in the local end.
This seem like a pretty fundamental issue. It affects all versions of postgres_fdw.
--antti