Re: Unlinking large objects

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