Determine unused / not referenced large Objects
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
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
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