Re: Vacuumlo improvements

> > 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)