Further clarification in documentation: No deletion of unreferenced large objects

On 12/06/2023 15:48 CEST PG Doc comments form <noreply@postgresql.org> wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/datatype-binary.html
Description:

When deleting a row that references (contains) a large object, I think that
most users expect the DBMS to take care of the, now unreferenced, BLOB. It's
good to know that PostgreSQL handles this differently and that one must
periodically use vacuumlo to avoid BLOB data piling up. At least, that's
what I understood.

I think a small paragraph in the documentation about this would help many
people. I can help with writing, if needed.

bytea and large objects are two different ways of storing binary data. vacuumlo
does not apply to bytea. Extension lo also mentions the issue with orphan
objects already: https://www.postgresql.org/docs/15/lo.html

Some further info on bytea vs large objects:
https://www.cybertec-postgresql.com/en/binary-data-performance-in-postgresql/

--
Erik

On Mon, Jun 12, 2023 at 8:32 AM PG Doc comments form <noreply@postgresql.org>
wrote:

You may be onto something, but:

Page: https://www.postgresql.org/docs/15/datatype-binary.html

This page isn't relevant to the discussion at hand as it doesn't have
anything to do with large objects. Whether it should would be a different
complaint.

When deleting a row that references (contains) a large object, I think that
most users expect the DBMS to take care of the, now unreferenced, BLOB.

On what grounds? To me this looks just like any other foreign key
situation and removing FK rows does not impact the PK. What would lead one
to think large objects behave differently?

David J.

On Mon, 2023-06-12 at 13:48 +0000, PG Doc comments form wrote:

Page: https://www.postgresql.org/docs/15/datatype-binary.html

When deleting a row that references (contains) a large object, I think that
most users expect the DBMS to take care of the, now unreferenced, BLOB. It's
good to know that PostgreSQL handles this differently and that one must
periodically use vacuumlo to avoid BLOB data piling up. At least, that's
what I understood.

I think a small paragraph in the documentation about this would help many
people. I can help with writing, if needed.

This is documented here:

https://www.postgresql.org/docs/current/lo.html

Admittedly, that is not the best place. Perhaps the introductory chapter
in https://www.postgresql.org/docs/current/lo-intro.html would be a good
place to mention that there is no referential integrity to large objects.

Yours,
Laurenz Albe

This page isn't relevant to the discussion at hand as it doesn't have anything to do with large objects. Whether it should would be a different complaint.

Thanks for pointing this out. I picked the wrong page.

When deleting a row that references (contains) a large object, I think that
most users expect the DBMS to take care of the, now unreferenced, BLOB.

On what grounds? To me this looks just like any other foreign key situation and removing FK rows does not impact the PK. What would lead one to think large objects behave differently?

OK, thinking of a LOB as a PK makes sense. It’s just that as a JDBC user, it’s not clear that users must clean up unreferenced items. For example, java.sql.Blob has no id attribute which one could use to do this. On the other hand, maybe the JDBC driver should call lo_unlink if a row with a LOB gets deleted? I have to admit, that I do not yet have fully understood all implications in this matter.

Admittedly, that is not the best place. Perhaps the introductory chapter
in https://www.postgresql.org/docs/current/lo-intro.html would be a good
place to mention that there is no referential integrity to large objects.

Yes, that could help. After reading through your comments and the documentation again, I’m not sure whether my request is on or off topic. I would understand if you decided against extending the docs.

Kind regards
Oliver Marienfeld

On Tue, 2023-06-13 at 10:57 +0000, Oliver Marienfeld wrote:

I would understand if you decided against extending the docs.

I don't get to decide that.

If you come up with a patch for the documentation, that would
increase the chances that the documentation actually gets changed.

Yours,
Laurenz Albe