Обсуждение: Further clarification in documentation: No deletion of unreferenced large objects

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.

Kind regards
Oliver Marienfeld

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

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.

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