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
