Date: Wed, 13 May 2026 22:10:28 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Shawn McCoy <shawn.the.mccoy@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Vacuumlo improvements
Message-ID: <agU9JPim3XNwRJ4b@nathan>
References: 
 <CALsgZNAM=AYK-9ZLR7Z0YLx6Lyx5aSrjjse3T+FmsJ=jTvfhDQ@mail.gmail.com>
 <agPpPyLs-61qFw88@nathan>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="K++Jp4URl2Oj0VAw"
Content-Disposition: inline
In-Reply-To: <agPpPyLs-61qFw88@nathan>
Archived-At: <https://www.postgresql.org/message-id/agU9JPim3XNwRJ4b%40nathan>
Precedence: bulk


--K++Jp4URl2Oj0VAw
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Tue, May 12, 2026 at 10:00:15PM -0500, Nathan Bossart wrote:
> Commit 64c604898e added the note about domains to the docs.  Unfortunately,
> neither that nor the corresponding thread [0] offer any clues as to why
> vacuumlo doesn't resolve domains.  The commit history for vacuumlo has been
> pretty quiet for a long time, so maybe it's just been overlooked.

It seems to be relatively easy to teach vacuumlo to handle domains over
oid.  Note that you need a recursive query because you can have domains
over domains.  Please test it out.  I noticed that vacuumlo's tests are
pretty sad, so this might be a good opportunity to change that.

-- 
nathan

--K++Jp4URl2Oj0VAw
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v1-0001-teach-vacuumlo-to-handle-domains-over-oid.patch

From f27e9a4606777c41cb41a9e71ede56384b79be84 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 13 May 2026 22:05:02 -0500
Subject: [PATCH v1 1/1] teach vacuumlo to handle domains over oid

---
 contrib/vacuumlo/vacuumlo.c | 14 +++++++++++++-
 1 file changed, 13 insertions(+), 1 deletion(-)

diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
index 8102569466b..230f6958fc1 100644
--- a/contrib/vacuumlo/vacuumlo.c
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -191,13 +191,25 @@ vacuumlo(const char *database, const struct _param *param)
 	 * delete...
 	 */
 	buf[0] = '\0';
+	if (PQserverVersion(conn) >= 140000)
+		strcat(buf, "WITH RECURSIVE cte AS "
+			   "(SELECT oid AS oid2, oid, typname, typbasetype FROM pg_type "
+			   "UNION ALL "
+			   "SELECT t2.oid2, t.oid, t.typname, t.typbasetype FROM pg_type t "
+			   "JOIN cte t2 ON t.oid = t2.typbasetype) ");
 	strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
 	strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
+	if (PQserverVersion(conn) >= 140000)
+		strcat(buf, ", cte ");
 	strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
 	strcat(buf, "      AND a.attrelid = c.oid ");
 	strcat(buf, "      AND a.atttypid = t.oid ");
 	strcat(buf, "      AND c.relnamespace = s.oid ");
-	strcat(buf, "      AND t.typname in ('oid', 'lo') ");
+	if (PQserverVersion(conn) >= 140000)
+		strcat(buf, "  AND t.oid = cte.oid2 "
+			   "       AND cte.typname = 'oid' ");
+	else
+		strcat(buf, "  AND t.typname in ('oid', 'lo') ");
 	strcat(buf, "      AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
 	strcat(buf, "      AND s.nspname !~ '^pg_'");
 	res = PQexec(conn, buf);
-- 
2.50.1 (Apple Git-155)


--K++Jp4URl2Oj0VAw--