From 868dc508088e734021099e5716f9e6da4de8337f Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Fri, 30 Aug 2024 10:27:31 -0400
Subject: [PATCH] New contrib module: alphabet_join.

This forces joins to be done alphabetically by alias name. It demonstrates
that join_path_setup_hook is sufficient to control the join order, and
is not intended for commit.
---
 contrib/Makefile                      |  1 +
 contrib/alphabet_join/Makefile        | 17 ++++++
 contrib/alphabet_join/alphabet_join.c | 74 +++++++++++++++++++++++++++
 contrib/alphabet_join/meson.build     | 12 +++++
 contrib/meson.build                   |  1 +
 5 files changed, 105 insertions(+)
 create mode 100644 contrib/alphabet_join/Makefile
 create mode 100644 contrib/alphabet_join/alphabet_join.c
 create mode 100644 contrib/alphabet_join/meson.build

diff --git a/contrib/Makefile b/contrib/Makefile
index abd780f277..b342261669 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -5,6 +5,7 @@ top_builddir = ..
 include $(top_builddir)/src/Makefile.global
 
 SUBDIRS = \
+		alphabet_join	\
 		amcheck		\
 		auth_delay	\
 		auto_explain	\
diff --git a/contrib/alphabet_join/Makefile b/contrib/alphabet_join/Makefile
new file mode 100644
index 0000000000..204bc35b3d
--- /dev/null
+++ b/contrib/alphabet_join/Makefile
@@ -0,0 +1,17 @@
+# contrib/alphabet_join/Makefile
+
+MODULE_big = alphabet_join
+OBJS = \
+	$(WIN32RES) \
+	alphabet_join.o
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/alphabet_join
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/alphabet_join/alphabet_join.c b/contrib/alphabet_join/alphabet_join.c
new file mode 100644
index 0000000000..6794bded04
--- /dev/null
+++ b/contrib/alphabet_join/alphabet_join.c
@@ -0,0 +1,74 @@
+/*-------------------------------------------------------------------------
+ *
+ * alphabet_join.c
+ *	  force tables to be joined in alphabetical order by alias name.
+ *    this is just a demonstration, so we don't worry about collation here.
+ *
+ * Copyright (c) 2016-2024, PostgreSQL Global Development Group
+ *
+ *	  contrib/alphabet_join/alphabet_join.c
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+#include "optimizer/paths.h"
+#include "parser/parsetree.h"
+
+static void aj_join_path_setup_hook(PlannerInfo *root,
+									RelOptInfo *joinrel,
+									RelOptInfo *outerrel,
+									RelOptInfo *innerrel,
+									JoinType jointype,
+									JoinPathExtraData *extra);
+
+static join_path_setup_hook_type prev_join_path_setup_hook = NULL;
+
+PG_MODULE_MAGIC;
+
+void
+_PG_init(void)
+{
+	prev_join_path_setup_hook = join_path_setup_hook;
+	join_path_setup_hook = aj_join_path_setup_hook;
+}
+
+static void
+aj_join_path_setup_hook(PlannerInfo *root, RelOptInfo *joinrel,
+						RelOptInfo *outerrel, RelOptInfo *innerrel,
+						JoinType jointype, JoinPathExtraData *extra)
+{
+	int		relid;
+	char   *outerrel_last = NULL;
+
+	/* Find the alphabetically last outerrel. */
+	relid = -1;
+	while ((relid = bms_next_member(outerrel->relids, relid)) >= 0)
+	{
+		RangeTblEntry *rte = planner_rt_fetch(relid, root);
+
+		Assert(rte->eref != NULL && rte->eref->aliasname != NULL);
+
+		if (outerrel_last == NULL ||
+			strcmp(outerrel_last, rte->eref->aliasname) < 0)
+			outerrel_last = rte->eref->aliasname;
+	}
+
+	/*
+	 * If any innerrel is alphabetically before the last outerrel, then
+	 * this join order is not alphabetical and should be rejected.
+	 */
+	relid = -1;
+	while ((relid = bms_next_member(innerrel->relids, relid)) >= 0)
+	{
+		RangeTblEntry *rte = planner_rt_fetch(relid, root);
+
+		Assert(rte->eref != NULL && rte->eref->aliasname != NULL);
+
+		if (strcmp(rte->eref->aliasname, outerrel_last) < 0)
+		{
+			extra->jsa_mask = 0;
+			return;
+		}
+	}
+}
diff --git a/contrib/alphabet_join/meson.build b/contrib/alphabet_join/meson.build
new file mode 100644
index 0000000000..437cb14af5
--- /dev/null
+++ b/contrib/alphabet_join/meson.build
@@ -0,0 +1,12 @@
+# Copyright (c) 2022-2024, PostgreSQL Global Development Group
+
+alphabet_join_sources = files(
+  'alphabet_join.c',
+)
+
+alphabet_join = shared_module('alphabet_join',
+  alphabet_join_sources,
+  kwargs: contrib_mod_args,
+)
+
+contrib_targets += alphabet_join
diff --git a/contrib/meson.build b/contrib/meson.build
index 14a8906865..4372242c8f 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -12,6 +12,7 @@ contrib_doc_args = {
   'install_dir': contrib_doc_dir,
 }
 
+subdir('alphabet_join')
 subdir('amcheck')
 subdir('auth_delay')
 subdir('auto_explain')
-- 
2.39.5