[Tom Lane <tgl@sss.pgh.pa.us>]

Whilst fooling with my outer-join-aware-Vars patch, I tripped
across a multi-way join query that failed with
  ERROR:  could not devise a query plan for the given query
when enable_partitionwise_join is on.

I traced that to the fact that reparameterize_path_by_child()
omits support for MaterialPath, so that if the only surviving
path(s) for a child join include materialization steps, we'll
fail outright to produce a plan for the parent join.

Unfortunately, I don't have an example that produces such a
failure against HEAD.  It seems certain to me that such cases
exist, though, so I'd like to apply and back-patch the attached.

I'm suspicious now that reparameterize_path() should be
extended likewise, but I don't really have any hard
evidence for that.

			regards, tom lane

Attachments:

fix-reparameterize_path_by_child.patch (text/x-diff, 765B) download
commit f74ca5d8611af3306eb96719d5d1910c9b6a8db5
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Thu Dec 1 21:04:49 2022 -0500

    Add missing MaterialPath case in reparameterize_path_by_child.
    
    Surprised we hadn't noticed this already.

diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index c77399ca92..224ba84107 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -4209,6 +4209,16 @@ do { \
 			}
 			break;
 
+		case T_MaterialPath:
+			{
+				MaterialPath *mpath;
+
+				FLAT_COPY_PATH(mpath, path, MaterialPath);
+				REPARAMETERIZE_CHILD_PATH(mpath->subpath);
+				new_path = (Path *) mpath;
+			}
+			break;
+
 		case T_MemoizePath:
 			{
 				MemoizePath *mpath;