Delete all large objects from the PostgreSQL database

Question

I am trying to remove all large objects from a PostgreSQL database.

We work with large sets of images. They were originally saved as large objects in postgres along with their metadata. I am going to convert these databases into simply storing file system links to an image in order to better manage the sometimes conflicting requirements for database disks and image data.

After exporting all the large objects, creating links to them and testing them, I am ready to delete the large objects.

I tried to write a function that would allow me to delete all of them, but to no avail. This seemed to work, but because not every number in the range corresponds to a large object, it fell.

CREATE OR REPLACE FUNCTION "DeleteLOs"() RETURNS INTEGER AS $$ DECLARE BEGIN FOR i IN 1620762..1801116 LOOP SELECT lo_unlink(i); END LOOP; RETURN 0; END; $$ LANGUAGE plpgsql; 

Ideally, I could combine such a function with a query to make sure I got them all, instead of specifying a range that might not be complete:

 SELECT DISTINCT loid FROM pg_largeobject 

Answer 1

To delete all existing large objects in your database:

 SELECT lo_unlink(l.oid) FROM pg_largeobject_metadata l; 

Related dba.SE answer with more details:

• Is it possible to make VACUUM FULL in the pg_largeobject table?

Answer 2

Erwin Brandstetter's answer is implemented as follows:

 SELECT lo_unlink(l.oid) FROM pg_largeobject_metadata l; vacuum full cluster