Re: join pushdown and issue with foreign update

[Alexander Pyhalov]

Tom Lane писал 2021-06-01 21:19:
Alexander Pyhalov <a.pyha...@postgrespro.ru> writes:
What about the following patch?

ISTM that using a specific rowtype rather than RECORD would be
quite disastrous from the standpoint of bloating the number of
distinct resjunk columns we need for a partition tree with a
lot of children.  Maybe we'll have to go that way, but it seems
like an absolute last resort.

Why do you think they are distinct?
In suggested patch all of them will have type of the common ancestor 
(root of the partition tree).


I think a preferable fix involves making sure that the correct
record-type typmod is propagated to record_in in this context.
Alternatively, maybe we could insert the foreign table's rowtype
during execution of the input operation, without touching the
plan as such.

Could we start by creating a test case that doesn't involve
uncommittable hacks to the source code?

Yes, it seems the following works fine to reproduce the issue.

--
Best regards,
Alexander Pyhalov,
Postgres Professionaldiff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 78379bdea5b..4dbb80c3ee8 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3262,3 +3262,16 @@ DROP TABLE join_tbl;
 
 ALTER SERVER loopback OPTIONS (DROP async_capable);
 ALTER SERVER loopback2 OPTIONS (DROP async_capable);
+
+CREATE TABLE base_tbl (a int, b int);
+CREATE FOREIGN TABLE remote_tbl (a int, b int)
+  SERVER loopback OPTIONS (table_name 'base_tbl');
+
+insert into remote_tbl select generate_series(1,100), generate_series(1,100);
+
+ANALYZE base_tbl;
+ANALYZE remote_tbl;
+
+EXPLAIN (VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+UPDATE remote_tbl d SET a= CASE WHEN current_timestamp> '2012-02-02'::timestamp THEN 5 ELSE 6 END FROM remote_tbl AS t (a, b) WHERE d.a = (t.a);
+UPDATE remote_tbl d SET a= CASE WHEN current_timestamp> '2012-02-02'::timestamp THEN 5 ELSE 6 END FROM remote_tbl AS t (a, b) WHERE d.a = (t.a);