From: Nathan Bossart <nathandbossart@gmail.com>
To: Shawn McCoy <shawn.the.mccoy@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Vacuumlo improvements
Date: Wed, 13 May 2026 22:10:28 -0500
Message-ID: <agU9JPim3XNwRJ4b@nathan> (raw)
In-Reply-To: <agPpPyLs-61qFw88@nathan>
References: <CALsgZNAM=AYK-9ZLR7Z0YLx6Lyx5aSrjjse3T+FmsJ=jTvfhDQ@mail.gmail.com>
<agPpPyLs-61qFw88@nathan>
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
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.cindex 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)
Attachments:
[text/plain] v1-0001-teach-vacuumlo-to-handle-domains-over-oid.patch (1.7K, ../agU9JPim3XNwRJ4b@nathan/2-v1-0001-teach-vacuumlo-to-handle-domains-over-oid.patch)
download | inline diff:
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.cindex 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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, shawn.the.mccoy@gmail.com
Subject: Re: Vacuumlo improvements
In-Reply-To: <agU9JPim3XNwRJ4b@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox