pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuumlo improvements
7+ messages / 3 participants
[nested] [flat]
* Vacuumlo improvements
@ 2026-05-12 17:34 Shawn McCoy <shawn.the.mccoy@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Shawn McCoy @ 2026-05-12 17:34 UTC (permalink / raw)
To: pgsql-hackers
I'd like to discuss a behavior in the vacuumlo utility that can lead
to silent data loss when large object references are stored in columns
whose type is a domain over `oid` or `lo`. While fully stated in the
docs, we have observed users getting some surprises when they are
trying to do routine maintenance. I'll attach a very simple repro
that displays the behavior using
a fairly routine use of a domain. [1]
DESCRIPTION
---------------------
The vacuumlo documentation [2] states:
"Only types with these names are considered; in particular, domains
over them are not considered."
While the behavior is documented, in the field, the consequence is
severe: if a user creates a domain over `oid` (e.g., for semantic
clarity or to add constraints) and uses that domain-typed column to
store large object OIDs, vacuumlo will treat those LOs as orphaned and
delete them. The referenced data is silently destroyed.
This is particularly dangerous because:
- Domains over base types are a popular PostgreSQL practice
- There is no warning or diagnostic output from vacuumlo when it skips
domain-typed columns
- The --dry-run / -n flag will show these LOs as "would be removed"
but nothing indicates *why* they appear orphaned
- The data loss is irreversible
SUGGESTION
---------------------
Ideally, vacuumlo could be improved to:
- Resolve domain types back to their base types when scanning columns
(using pg_type.typbasetype), or
- At least emit a WARNING when it encounters columns with domains over
oid/lo that it is skipping, so the user is aware.
I don't currently have a patch attached, but wanted to shine light on
the issue given the silent data-loss risk. I know LO's are a sensitive
topic with discussions that wander towards deprecation, but they seem
to be here to stay and are very commonly used in the field.
At minimum, I can submit a documentation improvement to make the
data-loss risk more prominent. The current parenthetical note is easy
to miss.
[1] attached vacuumlo_repro.txt
[2] https://www.postgresql.org/docs/current/vacuumlo.html
Thanks,
Shawn
vacuumlo removing user domain type
----------------------------------
-- 1. Setup: create a domain over oid and a table using it
CREATE DOMAIN my_lo AS oid;
CREATE TABLE documents (
id serial PRIMARY KEY,
name text,
content my_lo -- stores large object references
);
-- 2. Create a large object and store its OID
-- create a dummy file
echo "test" > test.txt
SELECT lo_create(0); -- returns an OID, e.g. 12345
-- (use the returned OID below)
INSERT INTO documents (name, content) VALUES ('test.txt', 12345);
-- Verify the LO exists
SELECT oid FROM pg_largeobject_metadata;
-- 3. Run vacuumlo in dry-run mode to see the problem:
-- $ vacuumlo -n -v <dbname>
--
-- Expected: LO 12345 should NOT appear in the removal list
-- (it is referenced by documents.content)
-- Actual: LO 12345 DOES appear as "would remove" because
-- vacuumlo does not scan the `my_lo` (domain) column
-- 4. If run without -n, the large object is deleted:
-- $ vacuumlo <dbname>
--
-- After this, the OID in documents.content is now a dangling reference:
SELECT lo_get(content) FROM documents WHERE name = 'test.txt';
-- ERROR: large object 12345 does not exist
Attachments:
[text/plain] vacuumlo_repro.txt (1.2K, ../../CALsgZNAM=AYK-9ZLR7Z0YLx6Lyx5aSrjjse3T+FmsJ=jTvfhDQ@mail.gmail.com/2-vacuumlo_repro.txt)
download | inline:
vacuumlo removing user domain type
----------------------------------
-- 1. Setup: create a domain over oid and a table using it
CREATE DOMAIN my_lo AS oid;
CREATE TABLE documents (
id serial PRIMARY KEY,
name text,
content my_lo -- stores large object references
);
-- 2. Create a large object and store its OID
-- create a dummy file
echo "test" > test.txt
SELECT lo_create(0); -- returns an OID, e.g. 12345
-- (use the returned OID below)
INSERT INTO documents (name, content) VALUES ('test.txt', 12345);
-- Verify the LO exists
SELECT oid FROM pg_largeobject_metadata;
-- 3. Run vacuumlo in dry-run mode to see the problem:
-- $ vacuumlo -n -v <dbname>
--
-- Expected: LO 12345 should NOT appear in the removal list
-- (it is referenced by documents.content)
-- Actual: LO 12345 DOES appear as "would remove" because
-- vacuumlo does not scan the `my_lo` (domain) column
-- 4. If run without -n, the large object is deleted:
-- $ vacuumlo <dbname>
--
-- After this, the OID in documents.content is now a dangling reference:
SELECT lo_get(content) FROM documents WHERE name = 'test.txt';
-- ERROR: large object 12345 does not exist
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Vacuumlo improvements
@ 2026-05-13 03:00 Nathan Bossart <nathandbossart@gmail.com>
parent: Shawn McCoy <shawn.the.mccoy@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Nathan Bossart @ 2026-05-13 03:00 UTC (permalink / raw)
To: Shawn McCoy <shawn.the.mccoy@gmail.com>; +Cc: pgsql-hackers
On Tue, May 12, 2026 at 11:34:10AM -0600, Shawn McCoy wrote:
> Ideally, vacuumlo could be improved to:
> - Resolve domain types back to their base types when scanning columns
> (using pg_type.typbasetype), or
> - At least emit a WARNING when it encounters columns with domains over
> oid/lo that it is skipping, so the user is aware.
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.
> At minimum, I can submit a documentation improvement to make the
> data-loss risk more prominent. The current parenthetical note is easy
> to miss.
Improving the documentation seems reasonable, too. Another thing we could
explore is allowing users to specify which tables/columns refer to LOs,
perhaps with a user-provided query. One wrinkle is that dblink allows
specifying multiple databases, and presumably each database will be a
little different.
Separately, do you know whether users are using lo_manage() at all? And if
not, why?
[0] https://postgr.es/m/BAY164-W265A089BD32F8901A686C9FF430%40phx.gbl
--
nathan
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Vacuumlo improvements
@ 2026-05-13 15:28 Sami Imseih <samimseih@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Sami Imseih @ 2026-05-13 15:28 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Shawn McCoy <shawn.the.mccoy@gmail.com>; pgsql-hackers
> > Ideally, vacuumlo could be improved to:
> > - Resolve domain types back to their base types when scanning columns
> > (using pg_type.typbasetype), or
> > - At least emit a WARNING when it encounters columns with domains over
> > oid/lo that it is skipping, so the user is aware.
>
> 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.
>
> > At minimum, I can submit a documentation improvement to make the
> > data-loss risk more prominent. The current parenthetical note is easy
> > to miss.
>
> Improving the documentation seems reasonable, too.
+1 to documentation that calls out the risk of data-loss.
> Another thing we could explore is allowing users to specify which
> tables/columns refer to LOs,
> perhaps with a user-provided query. One wrinkle is that dblink allows
> specifying multiple databases, and presumably each database will be a
> little different.
> Separately, do you know whether users are using lo_manage() at all? And if
> not, why?
I think recommending the use of the LO extension [1] in the core large object
documentation is a good start. Ideally, a user should not have to run vacuumlo.
Using the LO extension, a user can use lo_manage for simple types ( or domain
over simple types ) or if they have a more complex situation, like a composite
type holding an LO, they can use a custom trigger.
In the case of TRUNCATE, since per-row triggers don't fire. But even
that can be handled with a statement level BEFORE TRUNCATE trigger that scans
and unlinks. vacuumlo then becomes a cleanup tool for legacy schemas, not a
routine requirement.
All to say, we should be steering the users towards this extension with more
recommendations, perhaps.
[1] https://www.postgresql.org/docs/current/lo.html
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Vacuumlo improvements
@ 2026-05-14 03:10 Nathan Bossart <nathandbossart@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Nathan Bossart @ 2026-05-14 03:10 UTC (permalink / raw)
To: Shawn McCoy <shawn.the.mccoy@gmail.com>; +Cc: pgsql-hackers
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.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)
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.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)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Vacuumlo improvements
@ 2026-05-15 19:36 Sami Imseih <samimseih@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Sami Imseih @ 2026-05-15 19:36 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Shawn McCoy <shawn.the.mccoy@gmail.com>; pgsql-hackers
> > 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 think there is value in expanding the vacuumlo search capability for
LOs and OIDs.
We can also detect LOs and OIDs stored in composite types, or OID[] and LO[].
All these are detectable from the catalog.
The one complexity will be we will need vacuumlo to generate more complex
expressions for deleting the data.
DELETE FROM t WHERE lo IN (SELECT ("data")."lo_ref" FROM t_lo);
But, this will be more comprehensive and can cover all potential ways
an OID or LO can be used.
What do you think?
> Please test it out. I noticed that vacuumlo's tests are
> pretty sad, so this might be a good opportunity to change that.
More tests will be needed for sure.
But with all this done, I am not sure how much this moves the needle. It may
somewhat, but it's hard to tell how much.
I know I have seen users store LO references in text or other types,
so I think we still need the documentation enhancement to call out
the "data loss" potential.
I also think it will be good for the LO documentation [1] to nudge the users
to think about using the LO extension, as is done with the vacuumlo [2]
documentation.
[1] https://www.postgresql.org/docs/current/lo.html
[2] https://www.postgresql.org/docs/current/vacuumlo.html
--
Sami
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Vacuumlo improvements
@ 2026-05-15 20:29 Nathan Bossart <nathandbossart@gmail.com>
parent: Sami Imseih <samimseih@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Nathan Bossart @ 2026-05-15 20:29 UTC (permalink / raw)
To: Sami Imseih <samimseih@gmail.com>; +Cc: Shawn McCoy <shawn.the.mccoy@gmail.com>; pgsql-hackers
On Fri, May 15, 2026 at 02:36:22PM -0500, Sami Imseih wrote:
> I think there is value in expanding the vacuumlo search capability for
> LOs and OIDs. We can also detect LOs and OIDs stored in composite types,
> or OID[] and LO[]. All these are detectable from the catalog.
>
> The one complexity will be we will need vacuumlo to generate more complex
> expressions for deleting the data.
>
> DELETE FROM t WHERE lo IN (SELECT ("data")."lo_ref" FROM t_lo);
>
> But, this will be more comprehensive and can cover all potential ways
> an OID or LO can be used.
>
> What do you think?
It seems worth exploring.
> But with all this done, I am not sure how much this moves the needle. It may
> somewhat, but it's hard to tell how much. I know I have seen users store
> LO references in text or other types, so I think we still need the
> documentation enhancement to call out the "data loss" potential.
>
> I also think it will be good for the LO documentation [1] to nudge the users
> to think about using the LO extension, as is done with the vacuumlo [2]
> documentation.
Yeah, I think we'll have to do some combination of 1) improving vacuumlo,
2) improving the documentation to warn users about things vacuumlo doesn't
catch, and 3) improving the documentation to nudge users toward the lo
extension..
--
nathan
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Vacuumlo improvements
@ 2026-06-02 21:05 Sami Imseih <samimseih@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Sami Imseih @ 2026-06-02 21:05 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Shawn McCoy <shawn.the.mccoy@gmail.com>; pgsql-hackers
Hi,
sorry for the late reply.
> > DELETE FROM t WHERE lo IN (SELECT ("data")."lo_ref" FROM t_lo);
> >
> > But, this will be more comprehensive and can cover all potential ways
> > an OID or LO can be used.
> >
> > What do you think?
>
> It seems worth exploring.
>
> > But with all this done, I am not sure how much this moves the needle. It may
> > somewhat, but it's hard to tell how much. I know I have seen users store
> > LO references in text or other types, so I think we still need the
> > documentation enhancement to call out the "data loss" potential.
> >
> > I also think it will be good for the LO documentation [1] to nudge the users
> > to think about using the LO extension, as is done with the vacuumlo [2]
> > documentation.
>
> Yeah, I think we'll have to do some combination of 1) improving vacuumlo,
> 2) improving the documentation to warn users about things vacuumlo doesn't
> catch, and 3) improving the documentation to nudge users toward the lo
> extension..
See the attached patches.
0001 - vacuumlo now can recursively search for OID or LO types inside
more complex
data types. It does so by having the query return the search
expression depending on the type
to the delete statement.
0002 - adds documentation mentioning the "data loss" and to nudge
users to use vacuumlo.
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v2-0001-vacuumlo-Find-OID-references-inside-domains-composit.patch (12.0K, ../../CAA5RZ0ufaaZmJP-JLHBz07QXhyfAGPY+MOsbk0-QQ3Mn8kH7Qw@mail.gmail.com/2-v2-0001-vacuumlo-Find-OID-references-inside-domains-composit.patch)
download | inline diff:
From dc675031bd6132d60ee3dfe3860bfdc2def901b4 Mon Sep 17 00:00:00 2001
From: Sami Imseih <samimseih@gmail.com>
Date: Tue, 2 Jun 2026 20:29:31 +0000
Subject: [PATCH 1/2] vacuumlo: Find OID references inside domains, composites,
and arrays.
Previously, vacuumlo only recognized columns whose type was directly
named 'oid' or 'lo'. Columns using domains over oid, composite types
containing oid fields, or arrays of these were silently ignored,
causing referenced large objects to be incorrectly removed.
Use a recursive CTE to discover all paths to oid through domains,
composites, and arrays. For servers older than v14, retain the
original logic since those are EOL.
While at it, add a TAP test covering orphan removal, which was
previously untested.
Discussion: https://postgr.es/m/CALsgZNAM=AYK-9ZLR7Z0YLx6Lyx5aSrjjse3T+FmsJ=jTvfhDQ@mail.gmail.com
---
contrib/vacuumlo/meson.build | 1 +
contrib/vacuumlo/t/002_orphan_remove.pl | 109 ++++++++++++++++++++++++
contrib/vacuumlo/vacuumlo.c | 104 +++++++++++++---------
doc/src/sgml/vacuumlo.sgml | 7 +-
4 files changed, 179 insertions(+), 42 deletions(-)
create mode 100644 contrib/vacuumlo/t/002_orphan_remove.pl
diff --git a/contrib/vacuumlo/meson.build b/contrib/vacuumlo/meson.build
index 4ee5b048575..dac0b49272f 100644
--- a/contrib/vacuumlo/meson.build
+++ b/contrib/vacuumlo/meson.build
@@ -24,6 +24,7 @@ tests += {
'tap': {
'tests': [
't/001_basic.pl',
+ 't/002_orphan_remove.pl',
],
},
}
diff --git a/contrib/vacuumlo/t/002_orphan_remove.pl b/contrib/vacuumlo/t/002_orphan_remove.pl
new file mode 100644
index 00000000000..def3d407a23
--- /dev/null
+++ b/contrib/vacuumlo/t/002_orphan_remove.pl
@@ -0,0 +1,109 @@
+# Copyright (c) 2021-2026, PostgreSQL Global Development Group
+
+# This tests that vacuumlo correctly removes orphaned large objects while
+# preserving references stored in oid columns, lo columns, and complex types
+# built over either.
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+my $dbname = 'vacuumlo_test';
+$node->safe_psql('postgres', "CREATE DATABASE $dbname");
+
+# Install the lo extension (creates the lo type, recognized by name).
+$node->safe_psql($dbname, "CREATE EXTENSION lo");
+
+# Create composite types containing lo fields.
+$node->safe_psql($dbname, q{
+ CREATE TYPE comp_with_lo AS (label text, loid lo);
+ CREATE TYPE nested_comp AS (label text, inner_comp comp_with_lo);
+});
+
+# Create tables exercising various patterns.
+$node->safe_psql($dbname, q{
+ CREATE TABLE t_plain_oid (id serial, loid oid);
+ CREATE TABLE t_lo (id serial, loid lo);
+ CREATE TABLE t_comp (id serial, val comp_with_lo);
+ CREATE TABLE t_nested_comp (id serial, val nested_comp);
+ CREATE TABLE t_array_oid (id serial, loids oid[]);
+ CREATE TABLE t_array_lo (id serial, loids lo[]);
+ CREATE TABLE t_array_comp (id serial, vals comp_with_lo[]);
+});
+
+# Create large objects: lo1..lo9 will be referenced, lo10..lo14 are orphans.
+my @lo_oids;
+for my $i (1 .. 14)
+{
+ my $oid = $node->safe_psql($dbname, "SELECT lo_create(0)");
+ push @lo_oids, $oid;
+}
+
+# lo1: plain oid column
+$node->safe_psql($dbname,
+ "INSERT INTO t_plain_oid (loid) VALUES ('$lo_oids[0]')");
+
+# lo2: lo extension type
+$node->safe_psql($dbname,
+ "INSERT INTO t_lo (loid) VALUES ('$lo_oids[1]')");
+
+# lo3: composite type with lo field
+$node->safe_psql($dbname,
+ "INSERT INTO t_comp (val) VALUES (ROW('a', '$lo_oids[2]')::comp_with_lo)");
+
+# lo4: nested composite
+$node->safe_psql($dbname,
+ "INSERT INTO t_nested_comp (val) VALUES (ROW('b', ROW('c', '$lo_oids[3]')::comp_with_lo)::nested_comp)");
+
+# lo5, lo6: array of oid
+$node->safe_psql($dbname,
+ "INSERT INTO t_array_oid (loids) VALUES (ARRAY['$lo_oids[4]', '$lo_oids[5]']::oid[])");
+
+# lo7: array of lo
+$node->safe_psql($dbname,
+ "INSERT INTO t_array_lo (loids) VALUES (ARRAY['$lo_oids[6]']::lo[])");
+
+# lo8, lo9: array of composite with lo
+$node->safe_psql($dbname,
+ "INSERT INTO t_array_comp (vals) VALUES (ARRAY[ROW('d', '$lo_oids[7]'), ROW('e', '$lo_oids[8]')]::comp_with_lo[])");
+
+# Verify all 14 large objects exist before vacuumlo.
+my $count_before = $node->safe_psql($dbname,
+ "SELECT count(*) FROM pg_largeobject_metadata");
+is($count_before, '14', 'all 14 large objects exist before vacuumlo');
+
+# Run vacuumlo — assert success and check removal message.
+command_like(
+ ['vacuumlo', '-v', $node->connstr($dbname)],
+ qr/Successfully removed 5 large objects/,
+ 'vacuumlo removes orphan large objects');
+
+# lo10..lo14 (indices 9..13) should have been removed.
+my $count_after = $node->safe_psql($dbname,
+ "SELECT count(*) FROM pg_largeobject_metadata");
+is($count_after, '9', 'only 9 referenced large objects remain after vacuumlo');
+
+# Verify each referenced LO still exists.
+for my $i (0 .. 8)
+{
+ my $exists = $node->safe_psql($dbname,
+ "SELECT count(*) FROM pg_largeobject_metadata WHERE oid = $lo_oids[$i]");
+ is($exists, '1', "referenced lo (index $i, oid $lo_oids[$i]) still exists");
+}
+
+# Verify each orphan LO was removed.
+for my $i (9 .. 13)
+{
+ my $exists = $node->safe_psql($dbname,
+ "SELECT count(*) FROM pg_largeobject_metadata WHERE oid = $lo_oids[$i]");
+ is($exists, '0', "orphan lo (index $i, oid $lo_oids[$i]) was removed");
+}
+
+$node->stop;
+done_testing();
diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
index 8102569466b..f5131bf2bed 100644
--- a/contrib/vacuumlo/vacuumlo.c
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -29,7 +29,7 @@
#include "libpq-fe.h"
#include "pg_getopt.h"
-#define BUFSIZE 1024
+#define BUFSIZE 4096
enum trivalue
{
@@ -182,24 +182,72 @@ vacuumlo(const char *database, const struct _param *param)
PQclear(res);
/*
- * Now find any candidate tables that have columns of type oid.
+ * Now find any candidate tables that have columns of type oid (or domains
+ * over oid, or composite types containing oid fields, recursively).
*
* NOTE: we ignore system tables and temp tables by the expedient of
* rejecting tables in schemas named 'pg_*'. In particular, the temp
* table formed above is ignored, and pg_largeobject will be too. If
* either of these were scanned, obviously we'd end up with nothing to
* delete...
+ *
+ * The query returns (schema, table, expr) where expr is a SQL expression
+ * built server-side with quote_ident(). For example, expr may be: loid,
+ * (val).loid, ((val).inner_comp).loid, or unnest(loids).
*/
buf[0] = '\0';
- strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
- strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
- 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') ");
- strcat(buf, " AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
- strcat(buf, " AND s.nspname !~ '^pg_'");
+ if (PQserverVersion(conn) >= 140000)
+ {
+ strcat(buf, "WITH RECURSIVE expressions(schema, tbl, expr, typid) AS ("
+ " SELECT quote_ident(s.nspname), quote_ident(c.relname), "
+ " quote_ident(a.attname), a.atttypid "
+ " FROM pg_class c "
+ " JOIN pg_attribute a ON a.attrelid = c.oid "
+ " AND a.attnum > 0 AND NOT a.attisdropped "
+ " JOIN pg_namespace s ON c.relnamespace = s.oid "
+ " WHERE c.relkind IN ("
+ CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_MATVIEW) ") "
+ " AND s.nspname !~ '^pg_' "
+ " UNION ALL "
+ " SELECT d.schema, d.tbl, x.expr, x.typid "
+ " FROM expressions d, "
+ " LATERAL ("
+ " SELECT d.expr, t.typbasetype AS typid "
+ " FROM pg_type t "
+ " WHERE t.oid = d.typid AND t.typtype = 'd' "
+ " UNION ALL "
+ " SELECT 'unnest(' || d.expr || ')', t.typelem AS typid "
+ " FROM pg_type t "
+ " WHERE t.oid = d.typid AND t.typelem != 0 "
+ " AND t.typlen = -1 "
+ " UNION ALL "
+ " SELECT '(' || d.expr || ').' || quote_ident(a.attname), "
+ " a.atttypid "
+ " FROM pg_type t "
+ " JOIN pg_attribute a ON a.attrelid = t.typrelid "
+ " AND a.attnum > 0 AND NOT a.attisdropped "
+ " WHERE t.oid = d.typid "
+ " AND t.typtype = 'c' AND t.typrelid != 0 "
+ " ) x"
+ ") "
+ "SELECT schema, tbl, expr "
+ "FROM expressions WHERE typid = 26 "
+ " OR typid IN (SELECT oid FROM pg_type"
+ " WHERE typname = 'lo')");
+ }
+ else
+ {
+ strcat(buf, "SELECT quote_ident(s.nspname), quote_ident(c.relname), quote_ident(a.attname) ");
+ strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
+ 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') ");
+ strcat(buf, " AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
+ strcat(buf, " AND s.nspname !~ '^pg_'");
+ }
res = PQexec(conn, buf);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
@@ -213,52 +261,31 @@ vacuumlo(const char *database, const struct _param *param)
{
char *schema,
*table,
- *field;
+ *expr;
schema = PQgetvalue(res, i, 0);
table = PQgetvalue(res, i, 1);
- field = PQgetvalue(res, i, 2);
+ expr = PQgetvalue(res, i, 2);
if (param->verbose)
- fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
-
- schema = PQescapeIdentifier(conn, schema, strlen(schema));
- table = PQescapeIdentifier(conn, table, strlen(table));
- field = PQescapeIdentifier(conn, field, strlen(field));
-
- if (!schema || !table || !field)
- {
- pg_log_error("%s", PQerrorMessage(conn));
- PQclear(res);
- PQfinish(conn);
- PQfreemem(schema);
- PQfreemem(table);
- PQfreemem(field);
- return -1;
- }
+ fprintf(stdout, "Checking %s in %s.%s\n", expr, schema, table);
snprintf(buf, BUFSIZE,
"DELETE FROM vacuum_l "
"WHERE lo IN (SELECT %s FROM %s.%s)",
- field, schema, table);
+ expr, schema, table);
+
res2 = PQexec(conn, buf);
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
{
pg_log_error("failed to check %s in table %s.%s: %s",
- field, schema, table, PQerrorMessage(conn));
+ expr, schema, table, PQerrorMessage(conn));
PQclear(res2);
PQclear(res);
PQfinish(conn);
- PQfreemem(schema);
- PQfreemem(table);
- PQfreemem(field);
return -1;
}
PQclear(res2);
-
- PQfreemem(schema);
- PQfreemem(table);
- PQfreemem(field);
}
PQclear(res);
@@ -542,3 +569,4 @@ main(int argc, char **argv)
return rc;
}
+
diff --git a/doc/src/sgml/vacuumlo.sgml b/doc/src/sgml/vacuumlo.sgml
index 26b764d54b7..94b5c5fae14 100644
--- a/doc/src/sgml/vacuumlo.sgml
+++ b/doc/src/sgml/vacuumlo.sgml
@@ -213,10 +213,9 @@
First, <application>vacuumlo</application> builds a temporary table which contains all
of the OIDs of the large objects in the selected database. It then scans
through all columns in the database that are of type
- <type>oid</type> or <type>lo</type>, and removes matching entries from the temporary
- table. (Note: Only types with these names are considered; in particular,
- domains over them are not considered.) The remaining entries in the
- temporary table identify orphaned LOs. These are removed.
+ <type>oid</type> or <type>lo</type>, including domains, composite types, and arrays
+ containing these types. Matching entries are removed from the temporary table.
+ The remaining entries in the temporary table identify orphaned LOs. These are removed.
</para>
</refsect1>
--
2.53.0
[application/octet-stream] v2-0002-vacuumlo-Document-data-loss-risk-for-unrecognized-co.patch (1.6K, ../../CAA5RZ0ufaaZmJP-JLHBz07QXhyfAGPY+MOsbk0-QQ3Mn8kH7Qw@mail.gmail.com/3-v2-0002-vacuumlo-Document-data-loss-risk-for-unrecognized-co.patch)
download | inline diff:
From 2d1e753000fc91725126c3be553803e2afe626a3 Mon Sep 17 00:00:00 2001
From: Sami Imseih <samimseih@gmail.com>
Date: Tue, 2 Jun 2026 20:29:58 +0000
Subject: [PATCH 2/2] vacuumlo: Document data loss risk for unrecognized column
types.
Add a caution noting that large object references stored in columns
of types not recognized by vacuumlo (such as text or bigint) will be
treated as orphans and removed, resulting in data loss. Recommend
the lo extension type to avoid this.
Discussion: https://postgr.es/m/CALsgZNAM=AYK-9ZLR7Z0YLx6Lyx5aSrjjse3T+FmsJ=jTvfhDQ@mail.gmail.com
---
doc/src/sgml/vacuumlo.sgml | 13 +++++++++++++
1 file changed, 13 insertions(+)
diff --git a/doc/src/sgml/vacuumlo.sgml b/doc/src/sgml/vacuumlo.sgml
index 94b5c5fae14..857d7fae74f 100644
--- a/doc/src/sgml/vacuumlo.sgml
+++ b/doc/src/sgml/vacuumlo.sgml
@@ -217,6 +217,19 @@
containing these types. Matching entries are removed from the temporary table.
The remaining entries in the temporary table identify orphaned LOs. These are removed.
</para>
+
+ <caution>
+ <para>
+ Large object references stored in columns of other types, such as
+ <type>text</type> or <type>bigint</type>, are not recognized by
+ <application>vacuumlo</application> and will be treated as orphans,
+ resulting in data loss. Using the <type>lo</type> type
+ from the <xref linkend="lo"/> extension is recommended for columns that
+ hold large object references, as it allows
+ <application>vacuumlo</application> to find them and the
+ <function>lo_manage</function> trigger to manage them.
+ </para>
+ </caution>
</refsect1>
<refsect1>
--
2.53.0
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-06-02 21:05 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-12 17:34 Vacuumlo improvements Shawn McCoy <shawn.the.mccoy@gmail.com>
2026-05-13 03:00 ` Nathan Bossart <nathandbossart@gmail.com>
2026-05-13 15:28 ` Sami Imseih <samimseih@gmail.com>
2026-05-14 03:10 ` Nathan Bossart <nathandbossart@gmail.com>
2026-05-15 19:36 ` Sami Imseih <samimseih@gmail.com>
2026-05-15 20:29 ` Nathan Bossart <nathandbossart@gmail.com>
2026-06-02 21:05 ` Sami Imseih <samimseih@gmail.com>
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