Hello.
I was looking at enabling partition-wise join with whole row vars. My
main motivation
was to enable push down DML with partition-wise join in postgres_fdw.
The work is
based on the earlier patches of Ashutosh Bapat [1].
Partition-wise joins are disabled when whole row vars from relations,
participating in join, are required to be selected.
Using whole row vars is not frequent by itself, but happens when FDW is
used in DML or SELECT
FOR UPDATE/ FOR SHARE queries. I'd like to remove this restriction,
because this will make it possible
to do direct modify operations DML joins in postgres_fdw.
Currently there's an issue in PostgreSQL that when we want to get whole
row var of child table, we should
translate the record type of child table to the record type of the
parent table. So, there appear additional
ConvertRowtyeExpressions to cast types and different places in planner
(and in postgres_fdw) now should know about this.
Here's the series of patches, originally proposed by Ashutosh Bapat,
rebased and updated to work with current master.
What was done:
1) Reverting changes, that forbid PWJ when whole row vars are required.
This also restores logic in setrefs.c, necessary to
deal with ConvertRowtyeExpressions.
2) Applying modified patch from the original series to handle
ConvertRowtypeExprs in pull_vars_clause(). Unlike original patch,
default pull_var_clause_walker() behavior is unchanged. However, when
PVC_INCLUDE_CONVERTROWTYPES flag is specified, converted
whole row references are returned as is and are not recursed to. This is
done in such a way to avoid modifying all function consumers.
3) Modified one of the original patches to handle ConvertRowtypeExpr in
find_computable_ec_member().
4) The next patch modifies logic to search for ConvertRowtypeExpr in
search_indexed_tlist_for_non_var() - at least for rowid vars
varnullingrels
can be omitted, so we avoid looking at them when comparing different
ConvertRowtypeExprs. It seems to be working as expected, but I'm not
deadly sure about this. Perhaps, we also should consider
context->nrm_match?
5) The next patch is the original one - pulling ConvertRowtypeExpr in
build_tlist_to_deparse() and deparsing ConvertRowtypeExpr().
6) The rest is fix for postgres_fdw to correctly deparse DML.
The first tricky part here is that in UPDATE SET clause arbitrary
relations can appear, and currently there's no API to handle this.
So I've modified get_translated_update_targetlist() to do proper
adjustments.
The second is work with returning lists - we should set tableoids for
the returned values. This is done by modifying returning filters
to save this information and later to set it in returned tuples.
What this gives us - is the posibility of partition-wise joins for
foreign DML, like following:
EXPLAIN (COSTS OFF, VERBOSE)
DELETE FROM fprt1 USING fprt2 WHERE fprt1.a = fprt2.b AND fprt2.a % 30 =
29;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Delete on public.fprt1
Foreign Delete on public.ftprt1_p1 fprt1_1
Foreign Delete on public.ftprt1_p2 fprt1_2
-> Append
-> Foreign Delete
Remote SQL: DELETE FROM public.fprt1_p1 r3 USING
public.fprt2_p1 r5 WHERE ((r3.a = r5.b)) AND (((r5.a % 30) = 29))
-> Foreign Delete
Remote SQL: DELETE FROM public.fprt1_p2 r4 USING
public.fprt2_p2 r6 WHERE ((r4.a = r6.b)) AND (((r6.a % 30) = 29))
[1]
https://www.postgresql.org/message-id/CAFjFpRc8ZoDm0%2Bzhx%2BMckwGyEqkOzWcpVqbvjaxwdGarZSNrmA%40mail.gmail.com
--
Best regards,
Alexander Pyhalov,
Postgres Professional
