From: | Tobias Meyer <tm(at)allocation(dot)net> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Determine unused / not referenced large Objects |
Date: | 2012-11-28 09:02:56 |
Message-ID: | CBE54AC5C74A704D899BBDDC1521B53F97ACBDA4@PANDORA.allocation.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2012-11-28 09:20:41 | Re: Determine unused / not referenced large Objects |
Previous Message | Jeff Janes | 2012-11-28 05:26:46 | Re: Restore postgres to specific time |