Re: Unlinking large objects

From: "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com>
To: Jan Oberlaender <mindriot(at)gmx(dot)net>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Unlinking large objects
Date: 2000-05-30 13:10:27
Message-ID: 00053009193401.12938@comptechnews
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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(at)comptechnews(dot)com

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message gomathir 2000-05-30 18:28:48 Suggest a query
Previous Message Jan Oberlaender 2000-05-30 08:05:20 Unlinking large objects