Thread

1. Unlinking large objects

   Jan Oberlaender <mindriot@gmx.net> — 2000-05-30T08:05:20Z

   hi,
   
   From a php script working with postgres 6.5.2, I created some large
   objects to store image data; the oid was stored in a table containing an
   image database. Now when I delete an entry from that image database and
   forget unlinking the large object, I lose the oid because it was only
   stored in that table entry. Is the large object still there? How can I
   find out what large objects are stored in the database and get their oids
   to delete them?
   
   Thanks!
   
   --
   
   +-------------------------------------+
   | Jan Oberländer   <mindriot@gmx.net> |
   |          PGP key available          |
   +-------------------------------------+
   
   
   

2. Re: Unlinking large objects

   Robert B. Easter <reaster@comptechnews.com> — 2000-05-30T13:10:27Z

   On Tue, 30 May 2000, Jan Oberlaender wrote:
   > hi,
   > 
   > >From a php script working with postgres 6.5.2, I created some large
   > objects to store image data; the oid was stored in a table containing an
   > image database. Now when I delete an entry from that image database and
   > forget unlinking the large object, I lose the oid because it was only
   > stored in that table entry. Is the large object still there? How can I
   > find out what large objects are stored in the database and get their oids
   > to delete them?
   
   You might try something like this to delete the large objects automatically:
   
   CREATE TABLE images (
   	id	SERIAL PRIMARY KEY,
   	image	OID NOT NULL,
   	name	TEXT NOT NULL,
   	type	TEXT NOT NULL CHECK (type IN ('gif', 'jpg', 'png') ),	
   	width		INTEGER NOT NULL,
   	height		INTEGER NOT NULL,
   	sizebytes		INTEGER NOT NULL
   );
   CREATE RULE image_delete_lo AS
   ON DELETE TO images
   DO SELECT lo_unlink(old.image);
   
   For the RULE to work, the user doing the delete might need to be a Postgres
   superuser.
   
   You can find the large objects a couple ways:
   1.
   Look at the pg_class system table.  Entries with relname like xin are large
   objects.
   
   2.
   Use psql's \lo_list command.
   
   
   -- 
   Robert B. Easter
   reaster@comptechnews.com