Large OBjectS) are stored within a table, and if that entry is changed, the

associated BLOB is deleted from the database.

As PostgreSQL stands, this doesn't occur. It allocates an OID for each object,

and it is up to the application to store, and ultimately delete the objects.

Now this is fine for new postgresql specific applications, but existing ones

using JDBC or ODBC wont delete the objects, arising to orphaning - objects

As PostgreSQL stands, this doesn't occur. Large objects are treated as

objects in their own right; a table entry can reference a large object by

OID, but there can be multiple table entries referencing the same large

object OID, so the system doesn't delete the large object just because you

change or remove one such entry.

Now this is fine for new PostgreSQL-specific applications, but existing ones

using JDBC or ODBC won't delete the objects, resulting in orphaning - objects

that are not referenced by anything, and simply occupy disk space.

The Fix

I've fixed this by creating a new data type 'lo', some support functions, and

a Trigger which handles the orphaning problem.

a Trigger which handles the orphaning problem. The trigger essentially just

does a 'lo_unlink' whenever you delete or modify a value referencing a large

object. When you use this trigger, you are assuming that there is only one

database reference to any large object that is referenced in a

trigger-controlled column!

The 'lo' type was created because we needed to differentiate between plain

OIDs and Large Objects. Currently the JDBC driver handles this dilemma easily,

but (after talking to Byron), the ODBC driver needed a unique type. They had

created an 'lo' type, but not the solution to orphaning.

You don't actually have to use the 'lo' type to use the trigger, but it may be

convenient to use it to keep track of which columns in your database represent

large objects that you are managing with the trigger.

The 'lo' type was created because we needed to differenciate between normal

Oid's and Large Objects. Currently the JDBC driver handles this dilema easily,

but (after talking to Byron), the ODBC driver needed a unique type. They had created an 'lo' type, but not the solution to orphaning.

Install

Ok, first build the shared library, and install. Typing 'make install' in the

contrib/lo directory should do it.

Then, as the postgres super user, run the lo.sql script. This will install the

type, and define the support functions.

Then, as the postgres super user, run the lo.sql script in any database that

needs the features. This will install the type, and define the support

functions. You can run the script once in template1, and the objects will be

inherited by subsequently-created databases.

How to Use

The easiest way is by an example:

> create table image (title text,raster lo);

> create trigger t_image before update or delete on image for each row execute procedure lo_manage(raster);

> create table image (title text, raster lo);

> create trigger t_raster before update or delete on image

> for each row execute procedure lo_manage(raster);

Here, a trigger is created for each column that contains a lo type.

Create a trigger for each column that contains a lo type, and give the column

name as the trigger procedure argument. You can have more than one trigger on

a table if you need multiple lo columns in the same table, but don't forget to

give a different name to each trigger.

Issues

* dropping a table will still orphan any objects it contains, as the trigger

is not actioned.

Issues

'select lo_unlink({colname}::oid)from {tablename}'

Avoid this by preceding the 'drop table' with 'deletefrom {table}'.

for each column, before actually dropping the table.

If you already have, or suspect you have, orphaned large objects, see

the contrib/vacuumlo module to help you clean them up. It's a good idea

to run contrib/vacuumlo occasionally as a back-stop to the lo_manage

trigger.

* Some frontends may create their own tables, and will not create the

associated trigger(s). Also, users may not remember (or know) to create

the triggers.

This can be solved, but would involve changes to the parser.

As the ODBC driver needs a permanent lo type (& JDBC could be optimised to

use it if it's Oid is fixed), and as the above issues can only be fixed by

some internal changes, I feel it should become a permanent built-in type.

I'm releasing this into contrib, just to get it out, and tested.

Peter Mount <peter@retep.org.uk> June 13 1998

#defineatooid(x) ((Oid) strtoul((x), NULL, 10))

typedefOidBlob;

Blob*lo_in(char*str);/* Create from String*/

char*lo_out(Blob*addr);/* Output oid as String*/

Oidlo_oid(Blob*addr);/* Return oid as an oid*/

Blob*lo(Oidoid);/* Return Blob based on oid */

Datumlo_manage(PG_FUNCTION_ARGS);/* Trigger handler */

lo_in(char*str)

{

Blob*result;

Oidoid;

intcount;

if (strlen(str)>0)

{

count=sscanf(str,"%u",&oid);

if (count<1)

ereport(ERROR,

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("error in parsing \"%s\"",str)));

if (oid==InvalidOid)

ereport(ERROR,

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("illegal oid: \"%s\"",str)));

}

{

oid=DatumGetObjectId(DirectFunctionCall1(lo_creat,

Int32GetDatum(INV_READ |INV_WRITE)));

if (oid==InvalidOid)

elog(ERROR,"InvalidOid returned from lo_creat");

}

result= (Blob*)palloc(sizeof(Blob));

*result=oid;

return (result);

}

lo_out(Blob*addr)

{

char*result;

if (addr==NULL)

return (NULL);

result= (char*)palloc(32);

snprintf(result,32,"%u",*addr);

return (result);

}

lo_oid(Blob*addr)

{

if (addr==NULL)

returnInvalidOid;

return (Oid) (*addr);

}

lo(Oidoid)

{

Blob*result= (Blob*)palloc(sizeof(Blob));

Datumlo_manage(PG_FUNCTION_ARGS);

*result=oid;

return (result);

}

PG_FUNCTION_INFO_V1(lo_manage);

TupleDesctupdesc;/* Tuple Descriptor*/

HeapTuplerettuple;/* Tuple to be returned*/

boolisdelete;/* are we deleting?*/

HeapTupletrigtuple;/* The original value of tuple*/

if (!CALLED_AS_TRIGGER(fcinfo))

if (!CALLED_AS_TRIGGER(fcinfo))/* internal error */

elog(ERROR,"not fired by trigger manager");

isdelete=TRIGGER_FIRED_BY_DELETE(trigdata->tg_event);

attnum=SPI_fnumber(tupdesc,args[0]);

if (attnum <=0)

elog(ERROR,"column \"%s\" does not exist",args[0]);

char*orig=SPI_getvalue(trigtuple,tupdesc,attnum);

char*newv=SPI_getvalue(newtuple,tupdesc,attnum);

if (orig!=NULL&& (newv==NULL||strcmp(orig,newv)))

if (orig!=NULL&& (newv==NULL||strcmp(orig,newv)!=0))

DirectFunctionCall1(lo_unlink,

ObjectIdGetDatum(atooid(orig)));

if (isdelete)

{

--

--PostgreSQL code forLargeObjects

--PostgreSQL code formanaged Large Objects

--

--$PostgreSQL: pgsql/contrib/lo/lo.sql.in,v 1.12 2005/01/29 22:35:02 tgl Exp $

--

--

--Create the data type

--$PostgreSQL: pgsql/contrib/lo/lo.sql.in,v 1.13 2005/06/23 00:06:37 tgl Exp $

--

-- used by the lo type, it takes an oid and returns an lo object

-- Adjust this setting to control where the objects get created.

SET search_path = public;

CREATE FUNCTION lo_in(cstring)

RETURNS lo

AS 'MODULE_PATHNAME'

LANGUAGE C IMMUTABLE STRICT;

-- used by the lo type, it returns the oid of the object

CREATE FUNCTION lo_out(lo)

RETURNS cstring

AS 'MODULE_PATHNAME'

LANGUAGE C IMMUTABLE STRICT;

-- finally the type itself

CREATE TYPE lo (

INTERNALLENGTH = 4,

EXTERNALLENGTH = variable,

INPUT = lo_in,

OUTPUT = lo_out

);

-- this returns the oid associated with a lo object

CREATE FUNCTION lo_oid(lo)

RETURNS oid

AS 'MODULE_PATHNAME'

LANGUAGE C IMMUTABLE STRICT;

--

--Create the data type ... now just a domain over OID

--

-- same function, named to allow it to be used as a type coercion, eg:

-- CREATE TABLE a (image lo);

-- SELECT image::oid FROM a;

--

CREATE FUNCTION oid(lo)

RETURNS oid

AS 'MODULE_PATHNAME', 'lo_oid'

LANGUAGE C IMMUTABLE STRICT;

CREATE CAST (lo as oid) WITH FUNCTION oid(lo) AS IMPLICIT;

CREATE DOMAIN lo AS pg_catalog.oid;

-- this allows us to convert an oid to a managed lo object

-- ie: insert into test values (lo_import('/fullpath/file')::lo);

CREATE FUNCTION lo(oid)

RETURNS lo

AS 'MODULE_PATHNAME'

LANGUAGE C IMMUTABLE STRICT;

CREATE CAST (oid as lo) WITH FUNCTION lo(oid) AS IMPLICIT;

--

-- For backwards compatibility, define a function named lo_oid.

--

-- The other functions that formerly existed are not needed because

-- the implicit casts between a domain and its underlying type handle them.

--

CREATE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid AS

'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT IMMUTABLE;

-- This is used in triggers

CREATE FUNCTION lo_manage()

RETURNS trigger

RETURNSpg_catalog.trigger

AS 'MODULE_PATHNAME'

LANGUAGE C;

SET search_path= public;

DROPTABLE a;

DROPTYPE lo CASCADE;