On Wed, Jul 16, 2025 at 12:55:44AM +0300, Alexander Korotkov wrote:
> On Wed, Jun 4, 2025 at 11:52 PM Alexander Korotkov <[email protected]>
> wrote:
> > On Wed, Jun 4, 2025 at 6:15 PM Alexander Pyhalov
> > <[email protected]> wrote:
> > > Alexander Korotkov писал(а) 2025-06-04 14:29:
> > > > On Wed, Jan 29, 2025 at 11:59 AM Maxim Orlov <[email protected]> wrote:
> > > >> One important note here. This patch will change cast behaviour in case
> > > >> of local and foreign types are mismatched.
> > > >> The problem is if we cannot convert types locally, this does not mean
> > > >> that it is also true for a foreign wrapped data.
> > > >> In any case, it's up to the committer to decide whether this change is
> > > >> needed or not.
> > > >
> > > > I have two question regarding this aspect.
> > > > 1) Is it the same with regular type conversion?
> > >
> > > Yes, it's the same.
> > >
> > > CREATE TYPE enum_of_int_like AS enum('1', '2', '3', '4');
> > > CREATE TABLE conversions(id int, d enum_of_int_like);
> > > CREATE FOREIGN TABLE ft_conversions (id int, d char(1))
> > > SERVER loopback options (table_name 'conversions');
> > > SET plan_cache_mode = force_generic_plan;
> > > PREPARE s(varchar) AS SELECT count(*) FROM ft_conversions where d=$1;
> > > EXPLAIN (VERBOSE, COSTS OFF)
> > > EXECUTE s('1');
> > > QUERY PLAN
> > > -------------------------------------------------------------------------------------------
> > > Foreign Scan
> > > Output: (count(*))
> > > Relations: Aggregate on (public.ft_conversions)
> > > Remote SQL: SELECT count(*) FROM public.conversions WHERE ((d =
> > > $1::character varying))
> > > (4 rows)
> > >
> > > EXECUTE s('1');
> > > ERROR: operator does not exist: public.enum_of_int_like = character
> > > varying
> > > HINT: No operator matches the given name and argument types. You might
> > > need to add explicit type casts.
> > Got it, thank you for the explanation. I thin it's fair that array
> > coercion works the same way as a regular cast.
I agree with that principle. While the above example shows regular and array
casts aligned, Fable 5 found the attached test cases where that alignment is
absent, yielding array-specific wrong query result scenarios. This thread's
commit 62c3b4c introduced those. I think the test patch's "implicit-format
ArrayCoerceExpr" is not meaningfully a regression, because scalars have the
same problem. The other two, "pushed down although the element conversion
calls a cast" and "CoerceViaIO are pushed down", are array-specific
regressions. Scalars don't get corresponding trouble for those two. I'm also
attaching Fable 5's report.
> I've written a commit message for this patch. I'm going to push this
> if no objections.
commit cfbd131 (HEAD -> fdw-arraycoerce-tests)
Author: Noah Misch <[email protected]>
AuthorDate: Fri Jul 10 23:15:15 2026 +0000
Commit: Noah Misch <[email protected]>
CommitDate: Sat Jul 11 02:17:46 2026 +0000
Add tests exposing postgres_fdw ArrayCoerceExpr pushdown defects.
Commit 62c3b4c taught postgres_fdw to push down ArrayCoerceExpr, but
foreign_expr_walker() never examines elemexpr, the expression carrying
the per-element conversion semantics, and deparseArrayCoerceExpr()
ships the conversion as a bare ::type cast, omitting it entirely for
implicit-format casts. The remote server therefore re-resolves the
element conversion against its own catalogs and session state. Record
the current defective behavior:
* An array cast whose element conversion uses a user-defined cast
function ships without any shippability check, while the equivalent
scalar cast correctly stays local. A same-database loopback shares
the local catalogs, so these tests can pin only the pushdown
decision, not the wrong results and errors a real remote yields.
* An implicit-format ArrayCoerceExpr disappears from the remote query
entirely; against a stock remote server, the recorded query fails
with "operator does not exist: text = integer".
* Element conversions performed by CoerceViaIO reach I/O functions
that are marked immutable but depend on GUCs; scalar CoerceViaIO is
deliberately never shipped. Since postgres_fdw forces
extra_float_digits=3 on remote sessions, this one yields wrong query
results even in the loopback setup, with no user-defined objects
involved.
The expected output records today's wrong behavior; comments mark the
outputs that should change when the defects are fixed.
Co-Authored-By: Claude Fable 5 <[email protected]>
Claude-Session: https://claude.ai/code/session_014RSuqAwty57pReZcdArvqb
---
contrib/postgres_fdw/expected/postgres_fdw.out | 160 +++++++++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 86 +++++++++++++
2 files changed, 246 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 5ebae1c..79fb60d 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1219,6 +1219,166 @@ EXECUTE s(ARRAY['1','2']);
DEALLOCATE s;
RESET plan_cache_mode;
+-- ===================================================================
+-- test defects in ArrayCoerceExpr pushdown
+-- ===================================================================
+-- foreign_expr_walker() never examines elemexpr, which carries the
+-- per-element conversion semantics of an ArrayCoerceExpr, and
+-- deparseArrayCoerceExpr() ships the conversion as a bare ::type cast,
+-- omitting it entirely for implicit casts. The remote end therefore
+-- re-resolves the element conversion against its own catalogs and
+-- session state. The following record the current defective behavior;
+-- outputs marked BUG are wrong and should change when this is fixed.
+CREATE TABLE acx_tbl1 (id int, ta text[]);
+INSERT INTO acx_tbl1 VALUES (1, '{12345}'), (2, '{999}');
+CREATE FOREIGN TABLE acx_ft1 (id int, ta text[])
+ SERVER loopback OPTIONS (table_name 'acx_tbl1');
+CREATE FUNCTION acx_text2int(text) RETURNS int
+ LANGUAGE plpgsql IMMUTABLE STRICT AS 'BEGIN RETURN length($1); END';
+CREATE CAST (text AS integer) WITH FUNCTION acx_text2int(text);
+-- BUG: pushed down although the element conversion calls a cast
+-- function that was never vetted for shippability; a real remote
+-- server would apply its own text-to-integer coercion instead of
+-- acx_text2int (the loopback "remote" shares our catalogs, hiding
+-- the wrong results/errors this causes)
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft1 WHERE ta::int[] = ARRAY[5];
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Foreign Scan on public.acx_ft1
+ Output: id
+ Remote SQL: SELECT id FROM public.acx_tbl1 WHERE ((ta::integer[] =
'{5}'::integer[]))
+(3 rows)
+
+SELECT id FROM acx_ft1 WHERE ta::int[] = ARRAY[5];
+ id
+----
+ 1
+(1 row)
+
+-- the same cast in scalar form is correctly evaluated locally
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft1 WHERE ta[1]::int = 5;
+ QUERY PLAN
+--------------------------------------------------
+ Foreign Scan on public.acx_ft1
+ Output: id
+ Filter: ((acx_ft1.ta[1])::integer = 5)
+ Remote SQL: SELECT id, ta FROM public.acx_tbl1
+(4 rows)
+
+SELECT id FROM acx_ft1 WHERE ta[1]::int = 5;
+ id
+----
+ 1
+(1 row)
+
+DROP CAST (text AS integer);
+-- BUG: an implicit-format ArrayCoerceExpr vanishes from the remote
+-- query, making the remote re-run operator resolution that can pick a
+-- different operator or fail; here the loopback "remote" resolves via
+-- the same user-created implicit cast, but a stock remote server fails
+-- with "operator does not exist: text = integer"
+CREATE TABLE acx_tbl2 (id int, t text, ia int[]);
+INSERT INTO acx_tbl2 VALUES (1, '5', '{5}'), (2, '999', '{10}');
+CREATE FOREIGN TABLE acx_ft2 (id int, t text, ia int[])
+ SERVER loopback OPTIONS (table_name 'acx_tbl2');
+CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.to_hex(integer) AS
IMPLICIT;
+-- with no parameter involved, the omitted conversion leaves no trace
+-- in the remote query at all
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft2 WHERE t = ANY (ia);
+ QUERY PLAN
+---------------------------------------------------------------------
+ Foreign Scan on public.acx_ft2
+ Output: id
+ Remote SQL: SELECT id FROM public.acx_tbl2 WHERE ((t = ANY (ia)))
+(3 rows)
+
+SELECT id FROM acx_ft2 WHERE t = ANY (ia);
+ id
+----
+ 1
+(1 row)
+
+-- in the parameterized form, the $1::integer[] below is only
+-- deparseParam's type label for the parameter (its pre-coercion type);
+-- the omitted conversion should have appended ::text[] after it
+SET plan_cache_mode = force_generic_plan;
+PREPARE acx_p(int[]) AS SELECT id FROM acx_ft2 WHERE t = ANY ($1);
+EXPLAIN (VERBOSE, COSTS OFF)
+EXECUTE acx_p('{5}');
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Foreign Scan on public.acx_ft2
+ Output: id
+ Remote SQL: SELECT id FROM public.acx_tbl2 WHERE ((t = ANY ($1::integer[])))
+(3 rows)
+
+EXECUTE acx_p('{5}');
+ id
+----
+ 1
+(1 row)
+
+DEALLOCATE acx_p;
+RESET plan_cache_mode;
+DROP CAST (integer AS text);
+-- BUG: element conversions performed by CoerceViaIO are pushed down,
+-- reaching I/O functions that are marked immutable but depend on GUCs,
+-- while scalar CoerceViaIO is deliberately never shipped. postgres_fdw
+-- forces extra_float_digits = 3 on remote sessions, so the pushed-down
+-- qual computes a different answer than local evaluation: wrong results
+-- with no user-defined objects involved.
+CREATE TABLE acx_tbl3 (id int, f8 float8[], txt text[]);
+INSERT INTO acx_tbl3 VALUES (1, '{0.30000000000000004}', '{0.3}');
+CREATE FOREIGN TABLE acx_ft3 (id int, f8 float8[], txt text[])
+ SERVER loopback OPTIONS (table_name 'acx_tbl3');
+SET extra_float_digits = 0;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft3 WHERE f8::text[] = txt;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Foreign Scan on public.acx_ft3
+ Output: id
+ Remote SQL: SELECT id FROM public.acx_tbl3 WHERE ((f8::text[] = txt))
+(3 rows)
+
+-- BUG: finds no rows
+SELECT id FROM acx_ft3 WHERE f8::text[] = txt;
+ id
+----
+(0 rows)
+
+-- adding a volatile clause forces local evaluation, which finds the row
+SELECT id FROM acx_ft3 WHERE f8::text[] = txt OR random() < -1;
+ id
+----
+ 1
+(1 row)
+
+-- the same conversion in scalar form is correctly evaluated locally
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft3 WHERE f8[1]::text = txt[1];
+ QUERY PLAN
+-------------------------------------------------------
+ Foreign Scan on public.acx_ft3
+ Output: id
+ Filter: ((acx_ft3.f8[1])::text = acx_ft3.txt[1])
+ Remote SQL: SELECT id, f8, txt FROM public.acx_tbl3
+(4 rows)
+
+SELECT id FROM acx_ft3 WHERE f8[1]::text = txt[1];
+ id
+----
+ 1
+(1 row)
+
+RESET extra_float_digits;
+-- clean up
+DROP FOREIGN TABLE acx_ft1, acx_ft2, acx_ft3;
+DROP TABLE acx_tbl1, acx_tbl2, acx_tbl3;
+DROP FUNCTION acx_text2int(text);
-- a regconfig constant referring to this text search configuration
-- is initially unshippable
CREATE TEXT SEARCH CONFIGURATION public.custom_search
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql
b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e868da0..a06ec70 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -485,6 +485,92 @@ EXECUTE s(ARRAY['1','2']);
DEALLOCATE s;
RESET plan_cache_mode;
+-- ===================================================================
+-- test defects in ArrayCoerceExpr pushdown
+-- ===================================================================
+-- foreign_expr_walker() never examines elemexpr, which carries the
+-- per-element conversion semantics of an ArrayCoerceExpr, and
+-- deparseArrayCoerceExpr() ships the conversion as a bare ::type cast,
+-- omitting it entirely for implicit casts. The remote end therefore
+-- re-resolves the element conversion against its own catalogs and
+-- session state. The following record the current defective behavior;
+-- outputs marked BUG are wrong and should change when this is fixed.
+CREATE TABLE acx_tbl1 (id int, ta text[]);
+INSERT INTO acx_tbl1 VALUES (1, '{12345}'), (2, '{999}');
+CREATE FOREIGN TABLE acx_ft1 (id int, ta text[])
+ SERVER loopback OPTIONS (table_name 'acx_tbl1');
+CREATE FUNCTION acx_text2int(text) RETURNS int
+ LANGUAGE plpgsql IMMUTABLE STRICT AS 'BEGIN RETURN length($1); END';
+CREATE CAST (text AS integer) WITH FUNCTION acx_text2int(text);
+-- BUG: pushed down although the element conversion calls a cast
+-- function that was never vetted for shippability; a real remote
+-- server would apply its own text-to-integer coercion instead of
+-- acx_text2int (the loopback "remote" shares our catalogs, hiding
+-- the wrong results/errors this causes)
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft1 WHERE ta::int[] = ARRAY[5];
+SELECT id FROM acx_ft1 WHERE ta::int[] = ARRAY[5];
+-- the same cast in scalar form is correctly evaluated locally
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft1 WHERE ta[1]::int = 5;
+SELECT id FROM acx_ft1 WHERE ta[1]::int = 5;
+DROP CAST (text AS integer);
+
+-- BUG: an implicit-format ArrayCoerceExpr vanishes from the remote
+-- query, making the remote re-run operator resolution that can pick a
+-- different operator or fail; here the loopback "remote" resolves via
+-- the same user-created implicit cast, but a stock remote server fails
+-- with "operator does not exist: text = integer"
+CREATE TABLE acx_tbl2 (id int, t text, ia int[]);
+INSERT INTO acx_tbl2 VALUES (1, '5', '{5}'), (2, '999', '{10}');
+CREATE FOREIGN TABLE acx_ft2 (id int, t text, ia int[])
+ SERVER loopback OPTIONS (table_name 'acx_tbl2');
+CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.to_hex(integer) AS
IMPLICIT;
+-- with no parameter involved, the omitted conversion leaves no trace
+-- in the remote query at all
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft2 WHERE t = ANY (ia);
+SELECT id FROM acx_ft2 WHERE t = ANY (ia);
+-- in the parameterized form, the $1::integer[] below is only
+-- deparseParam's type label for the parameter (its pre-coercion type);
+-- the omitted conversion should have appended ::text[] after it
+SET plan_cache_mode = force_generic_plan;
+PREPARE acx_p(int[]) AS SELECT id FROM acx_ft2 WHERE t = ANY ($1);
+EXPLAIN (VERBOSE, COSTS OFF)
+EXECUTE acx_p('{5}');
+EXECUTE acx_p('{5}');
+DEALLOCATE acx_p;
+RESET plan_cache_mode;
+DROP CAST (integer AS text);
+
+-- BUG: element conversions performed by CoerceViaIO are pushed down,
+-- reaching I/O functions that are marked immutable but depend on GUCs,
+-- while scalar CoerceViaIO is deliberately never shipped. postgres_fdw
+-- forces extra_float_digits = 3 on remote sessions, so the pushed-down
+-- qual computes a different answer than local evaluation: wrong results
+-- with no user-defined objects involved.
+CREATE TABLE acx_tbl3 (id int, f8 float8[], txt text[]);
+INSERT INTO acx_tbl3 VALUES (1, '{0.30000000000000004}', '{0.3}');
+CREATE FOREIGN TABLE acx_ft3 (id int, f8 float8[], txt text[])
+ SERVER loopback OPTIONS (table_name 'acx_tbl3');
+SET extra_float_digits = 0;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft3 WHERE f8::text[] = txt;
+-- BUG: finds no rows
+SELECT id FROM acx_ft3 WHERE f8::text[] = txt;
+-- adding a volatile clause forces local evaluation, which finds the row
+SELECT id FROM acx_ft3 WHERE f8::text[] = txt OR random() < -1;
+-- the same conversion in scalar form is correctly evaluated locally
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT id FROM acx_ft3 WHERE f8[1]::text = txt[1];
+SELECT id FROM acx_ft3 WHERE f8[1]::text = txt[1];
+RESET extra_float_digits;
+
+-- clean up
+DROP FOREIGN TABLE acx_ft1, acx_ft2, acx_ft3;
+DROP TABLE acx_tbl1, acx_tbl2, acx_tbl3;
+DROP FUNCTION acx_text2int(text);
+
-- a regconfig constant referring to this text search configuration
-- is initially unshippable
CREATE TEXT SEARCH CONFIGURATION public.custom_search
# User-visible defects in commit 62c3b4c (postgres_fdw ArrayCoerceExpr
pushdown), still present in master
Audit of 62c3b4c "Support for deparsing of ArrayCoerceExpr node in
contrib/postgres_fdw"
(Alexander Korotkov, 2025-07-18). Code is functionally unchanged in master
(only comment-typo
fix 4c5159a since) and is in REL_19_STABLE, so these ship in PostgreSQL 19
unless fixed.
Method: 23-agent workflow — 12 independent finder lenses (23 raw findings) →
root-cause
dedup (3 clusters) → per-cluster adversarial refuter + independent end-to-end
tracer + live
reproduction on a server built from this tree (master @ a8c2547, PG 19beta1;
loopback FDW
with a second database as the "remote", so local-only catalog objects genuinely
don't exist
remotely; ground truth = forced-local evaluation of the identical predicate) →
completeness
critic. **All three defects: refuter confirmed, tracer confirmed,
live-reproduced.**
---
## 1. (high, wrong results) `foreign_expr_walker` never vets the
element-coercion pathway (`elemexpr`)
**Anchor:** `contrib/postgres_fdw/deparse.c:706-733` (walker case recurses only
into `e->arg`, line 713)
The per-element conversion — a `FuncExpr` of the `pg_cast` function, a
`CoerceViaIO`, or a
`RelabelType` — lives in `e->elemexpr` and is never examined. Unlike
`T_FuncExpr` (:566),
`T_OpExpr` (:614), and `T_ScalarArrayOpExpr` (:654), no `is_shippable()` check
touches the
function that gives the cast its semantics; the `:1050` exit vets only the
result *array type*.
The would-be backstop `contain_mutable_functions` (`is_foreign_expr`, :287)
reaches `elemexpr`
via `expression_tree_walker` but checks only volatility, and IMMUTABLE is the
conventional
declaration for cast functions. `deparseArrayCoerceExpr` (:3544-3556) then
ships bare
`arg::resulttype` (or nothing for implicit format), so the remote re-resolves
the element
coercion against *its* `pg_cast` catalog. Pre-commit, all of this was evaluated
locally
(walker default case rejected the node).
Violates the documented contract (postgres-fdw.sgml ~1168-1173: only built-in /
listed-extension types, operators, functions are shipped) and is inconsistent
with the
scalar form of the identical cast, which the `T_FuncExpr` shippability check
still keeps local.
Live-reproduced (all observed on PG 19beta1):
```sql
-- Remote database "remotedb" (stock, only tables):
CREATE TABLE public.t_text (id int, c text[]);
INSERT INTO t_text VALUES (1,'{12345}'), (2,'{999}');
CREATE TABLE public.t_s (id int, c text);
INSERT INTO t_s VALUES (1,'00005'), (2,'5');
-- Local database:
CREATE EXTENSION postgres_fdw;
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname
'remotedb');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE FOREIGN TABLE ft_text (id int, c text[]) SERVER loopback OPTIONS
(table_name 't_text');
CREATE FOREIGN TABLE ft_s (id int, c text) SERVER loopback OPTIONS
(table_name 't_s');
-- (A) Explicit-cast form: silent wrong results
CREATE FUNCTION text2int(text) RETURNS int LANGUAGE plpgsql IMMUTABLE STRICT
AS 'BEGIN RETURN length($1); END';
CREATE CAST (text AS integer) WITH FUNCTION text2int(text);
EXPLAIN (VERBOSE, COSTS OFF) SELECT id, c FROM ft_text WHERE c::int[] =
ARRAY[5];
-- Remote SQL: SELECT id, c FROM public.t_text WHERE ((c::integer[] =
'{5}'::integer[]))
SELECT id, c FROM ft_text WHERE c::int[] = ARRAY[5];
-- Observed: 0 rows. Correct (pre-commit/local semantics, cf.
-- SELECT * FROM (SELECT * FROM ft_text OFFSET 0) s WHERE c::int[] =
ARRAY[5]):
-- (1,'{12345}') because length('12345') = 5.
SELECT id, c FROM ft_text WHERE c::int[] = ARRAY[12345];
-- Observed: spurious row (1,'{12345}'); locally: 0 rows.
-- (B) New hard error on data the local cast handles:
-- on remote: INSERT INTO t_text VALUES (3,'{abc}');
SELECT id FROM ft_text WHERE c::int[] = ARRAY[5];
-- Observed: ERROR: invalid input syntax for type integer: "abc"
-- CONTEXT: remote SQL command: SELECT id FROM public.t_text WHERE
((c::integer[] = '{5}'::integer[]))
-- Locally-evaluated control returns id=1.
-- (C) Implicit-format branch (cast omitted entirely; see also defect 3):
CREATE FUNCTION int2padded(int) RETURNS text LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT lpad(CAST($1 AS varchar), 5, '0') $$;
CREATE CAST (integer AS text) WITH FUNCTION int2padded(int) AS IMPLICIT;
SET plan_cache_mode = force_generic_plan;
PREPARE s(int[]) AS SELECT id FROM ft_s WHERE c = ANY($1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE s(ARRAY[5]);
-- Remote SQL: SELECT id FROM public.t_s WHERE ((c = ANY ($1::integer[])))
EXECUTE s(ARRAY[5]);
-- Observed: ERROR: operator does not exist: text = integer (remote SQL
command)
-- Correct/pre-commit: id=1 (verified via OFFSET 0 control).
-- Control demonstrating the inconsistency: the identical cast in scalar form
stays local
EXPLAIN (VERBOSE, COSTS OFF) SELECT id FROM ft_text WHERE c[1]::int = 5;
-- Filter: ((ft_text.c[1])::integer = 5) (local; correct results)
```
Because pushed-down quals also gate direct UPDATE/DELETE, the wrong-row
selection extends to
wrong rows *modified*.
---
## 2. (high, wrong results) Builtin GUC-sensitive I/O coercions shipped via
`elemexpr`'s `CoerceViaIO` — zero user-defined objects needed
**Anchor:** `contrib/postgres_fdw/deparse.c:706` (same unvetted-`elemexpr`
hole, distinct fix implication)
postgres_fdw deliberately never ships scalar `T_CoerceViaIO` (absent from
walker and
`deparseExpr` dispatch), so `f8col::text` stays a local Filter. But
`f8arr::text[]` is an
ArrayCoerceExpr whose `elemexpr` is a `CoerceViaIO` (string-category I/O
fallback,
parse_coerce.c:3275-3283) — and it's now judged shippable:
`float8out`/`byteaout`/`textin`
are marked immutable in pg_proc.dat despite depending on `extra_float_digits` /
`bytea_output`. postgres_fdw itself forces `SET extra_float_digits = 3` on
every remote
session (connection.c:840) and never syncs `bytea_output`, so local-vs-remote
rendering
diverges deterministically.
Key fix implication: **an `is_shippable()` check on the `elemexpr` function OID
would NOT
catch this** — the I/O functions are builtin. The walker must refuse
non-RelabelType element
coercions (or the deparser must stop delegating them).
Live-reproduced:
```sql
-- Remote (stock): CREATE TABLE public.t1 (id int, f8 float8[], txt text[]);
-- INSERT INTO t1 VALUES (1, ARRAY[(0.1::float8 +
0.2::float8)], ARRAY['0.3']);
-- Local: CREATE FOREIGN TABLE ft1 (id int, f8 float8[], txt text[]) SERVER
loopback OPTIONS (table_name 't1');
SET extra_float_digits = 0;
SELECT id FROM ft1 WHERE f8::text[] = txt;
-- Observed on master: 0 rows. Remote SQL: SELECT id FROM public.t1 WHERE
((f8::text[] = txt))
-- (remote session renders '{0.30000000000000004}' under forced
extra_float_digits=3)
-- Correct (pre-commit local semantics, cf. WHERE (f8::text[] = txt OR
random() < -1)): id=1.
-- bytea variant: remote ALTER DATABASE ... SET bytea_output='escape', local
default 'hex':
-- WHERE barr::text[] = txt → 0 rows pushed down vs id=1 locally.
-- Control: scalar WHERE txt[1] = f8[1]::text stays a local Filter and is
correct.
```
---
## 3. (medium, remote errors) `deparseArrayCoerceExpr` omits
`COERCE_IMPLICIT_CAST` coercions entirely
**Anchor:** `contrib/postgres_fdw/deparse.c:3552`
Emitting no text for implicit-format coercions bets that the remote parser
re-derives the
same coercion during operator resolution. That bet fails independently of any
`elemexpr`
vetting fix:
- **(a)** local implicit pg_cast entry backed by a *builtin* function — every
involved OID is
shippable, yet the remote errors:
```sql
-- Remote (stock): CREATE TABLE public.t (c2 text, c3 int[]); INSERT INTO t
VALUES ('3','{3}');
-- Local:
CREATE FOREIGN TABLE ft (c2 text, c3 int[]) SERVER loopback OPTIONS (table_name
't');
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.to_hex(integer) AS
IMPLICIT;
SELECT count(*) FROM ft WHERE c2 = ANY (c3);
-- Remote SQL: SELECT count(*) FROM public.t WHERE ((c2 = ANY (c3))) --
coercion vanished
-- Observed: ERROR: operator does not exist: text = integer
-- Correct/pre-commit (to_hex(3)='3'): count = 1.
-- Same via generic plan: PREPARE p(int[]) AS ... WHERE c2 = ANY($1)
-- → Remote SQL ((c2 = ANY ($1::integer[]))), identical error.
```
- **(b)** declared-type mismatch, *no user objects at all*: remote column is an
enum, local
foreign table declares `char(1)`; `PREPARE s(varchar[]) ... WHERE d =
ANY($1)` ships
`d = ANY ($1::character varying[])` and fails
`operator does not exist: public.enum_of_int_like = character varying`, where
pre-commit
it returned 2 rows via a local Filter. This mode was demonstrated in-thread
by the patch
author and flagged by a reviewer pre-commit, then consciously accepted.
Rated medium: loud error rather than silent corruption, and the scalar
implicit-cast FuncExpr
path has long had the same omission policy (this commit extends the exposure to
array
coercions). Unlike `RelabelType`, an ArrayCoerceExpr is not pure relabeling, so
the omission
drops real semantics; a remote can also bind a semantically different operator
(e.g. texteq
vs bpchareq trailing-space handling) rather than erroring.
---
## Follow-up lead from the completeness critic (traced in code, not live-run)
**Array-over-domain casts push a hidden `CoerceToDomain` to the remote**
(medium confidence).
When the target element type is a domain, `elemexpr` contains `CoerceToDomain`
— the one
shape that evades even the volatility backstop: `check_functions_in_node` has no
`T_CoerceToDomain` case and domain CHECK expressions live in the catalog, not
the node tree.
With the domain in a server-listed extension, `is_shippable(resulttype)` passes
(implicit
array types are extension members, pg_type.c:628-635), so `carr::dom[]` ships
while scalar
`c::dom` never does, and the *remote's* domain constraints (remote catalog
state, remote
session GUCs) replace local enforcement — contradicting clauses.c:3428-3440's
rationale for
not const-folding through CoerceToDomain. Traced failure modes: `unrecognized
configuration
parameter` remote errors for GUC-reading constraints; silently weaker
constraint enforcement
under local ALTER DOMAIN / extension version skew. Mitigating: requires the
`extensions`
option, whose docs shift identical-behavior responsibility to the user.
## Checked and found clean / derivative
- **Collation logic** (deparse.c:719-731): exact parity with `T_RelabelType`;
`elemexpr`
cannot introduce a Var-derived collation (`CaseTestExpr` is built with
InvalidOid collation,
parse_coerce.c:947). Affirmatively clean.
- **ORDER BY / GROUP BY / aggregate pushdown, direct UPDATE/DELETE, EPQ
rechecks**: traced;
only derivative manifestations of defects 1-2 (wrong sort/grouping keys,
wrong rows
modified, nondeterministic row drops when a diverging pushed qual is
re-evaluated locally
in an EPQ recheck), not new defect classes.
- **Fix shape implied by the evidence**: restrict pushdown to element coercions
that are pure
relabelings (elemexpr is RelabelType/bare CaseTestExpr) — and if kept, always
emit the cast
rather than omitting implicit format; vetting only the elemexpr function OID
is insufficient
(defect 2), and omission is unsound even when everything is shippable (defect
3).
## Provenance
- Workflow `wf_16d96442-0cf`: 23 agents (cap 30), 12/12 finder lenses returned,
23 raw
findings → 3 root causes, 0 dropped, 0 refuted. Each root cause: adversarial
refuter
**confirmed** + independent end-to-end tracer **confirmed** + empirical
reproducer
**reproduced=yes**. Archives lens independently matched defect 3(b) to the
pre-commit
review thread.
- Empirical setup: reused `tmp_install` of this tree (PG 19beta1, master
a8c2547); fresh
cluster in session scratchpad, unix-socket only; "remote" = second database
over a loopback
postgres_fdw server. Servers stopped afterward; socket dirs removed; data
dirs left in
scratchpad; git tree untouched (`tmp_install/` is gitignored).
- Full machine-readable results:
`/tmp/claude-1000/-home-nm-src-pg-postgresql/afe6d0ac-a2cb-4fa6-9526-676825c85665/tasks/wlbjtr33p.output`
