Determine unused / not referenced large Objects

Started by Tobias Meyerover 13 years ago3 messagesgeneral
Jump to latest
#1Tobias Meyer
tm@allocation.net

Hello list,

we have a database with several tables containing blobs (as oid), that reference large objects in pg_largeobject.
It is my understanding, that the large objects will not be deleted if a row containing the oid is deleted - you have to do that yourself in application logic. (please correct me if wrong)
I think we might not have done that consistently in the past.

Is it safe to assume that pg_largeobject only contains large objects added by application code, or might there be any postgresql-internal objects?

Providing that all BLOB colums are of type oid, I was thinking of getting the fields with
select table_name,column_name from information_schema.columns where data_type = 'oid' and table_name not like 'pg_%' and table_name not like '_pg%';

to create a
select comum1 from table1 UNION select column2 from table2...

and comparing the output with
select distinct loid from pg_largeobject

and subsequently deleting the abandoned ones.

Though we do have backups of course I'm afraid I might be missing something...

regards
Tobias

#2Thomas Kellerer
spam_eater@gmx.net
In reply to: Tobias Meyer (#1)
Re: Determine unused / not referenced large Objects

Tobias Meyer, 28.11.2012 10:02:

Hello list,

we have a database with several tables containing blobs (as oid),
that reference large objects in pg_largeobject.

It is my understanding, that the large objects will not be deleted if
a row containing the oid is deleted – you have to do that yourself in
application logic. (please correct me if wrong)

I think we might not have done that consistently in the past.

Is it safe to assume that pg_largeobject only contains large objects
added by application code, or might there be any postgresql-internal
objects?

Isn't vacuumlo supposed to do this kind of cleanup?

http://www.postgresql.org/docs/current/static/vacuumlo.html

Regards
Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#3Tobias Meyer
tm@allocation.net
In reply to: Thomas Kellerer (#2)
Re: Determine unused / not referenced large Objects

Isn't vacuumlo supposed to do this kind of cleanup?

http://www.postgresql.org/docs/current/static/vacuumlo.html

Regards
Thomas

Thank you - I wasn't aware of that utility program - just what I was looking for.

Tobias

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general