Re: [HACKERS] SPI procedure for removing large objects

On Thu, 6 Aug 1998, Sergey E. Levov wrote:

> Hello!
>
> In message
> <Pine(dot)LNX(dot)3(dot)96(dot)980805215449(dot)793A-100000(at)maidast(dot)retep(dot)org(dot)uk> Peter
> T Mount writes:
>
> >On Wed, 5 Aug 1998, David Hartwig wrote:
>
> >> Peter,
> >>
> >> I have just finished up some other stuff in the backend, and I was
> >> wondering what to do next. My personal list include a cleanup of the lo
> >> type. Specifically:
> >>
> >> 1. Assign a fixed OID to the LO type so that attributes of this type
> >> can easily be identified.
> >>
> >> 2. Write a VACUUM LO procedure.
> >>
> >> 3. Extend/verify the existing internal lo functions to work with the
> >> new type.
> >>
> >> I know that more can/should be done in this area, but I only have so much
> >> time. I am aware the you have done some work on this in the contrib area.
> >> Were you planning on handling any (or all) of these issues as part of the
> >> 6.4 base release? I will gladly move on to something else.
>
> >I claimed the parts of the TODO list that deal with these issues a few
> >weeks ago. Since then, I've tried several solutions (the one in contrib
> >was an attempt that uses triggers. It works but has holes - like DROP
> >TABLE doesnt fire a trigger).
>
> My procedure uses triggers also. As for DROP TABLE, I think, user always
> can do DELETE FROM <table> before dropping table which use large objects.

Because JDBC & ODBC (WinTel mainly) allow existing apps (which were never
intented to be used with postgresql, but now can), we can't assume that
the client will be able to issue a DELETE FROM.

It was partly because of this, why I started to look at using Vacuum to
pick up unreferenced large objects.

I have heared that some people simply delete the files - but that screws
up that database's system tables.

> >The method I think is best is the vacuum procedure. Now, I have here the
> >basic outline for it, and how it interacts with the existing system using
> >oid's, but currently I can't test it as postgresql is still broken (for
> >me).
>
> I think, in some cases triggers have such advantage as they allow
> remove an unused large objects on the fly, and therefore save disc space.

True, which is why I think the trigger should be available in the
backend with the rest of the lo_ functions. Otherwise it will be the only
one the user has to add in manually.

> And couple words about my procedure. It was written as add-on for
> my PostgreSQL ODBC driver for UNIX to allow driver's users to work
> with SQL_LONGVARCHAR and SQL_LONGVARBINARY data types. This add-on
> includes script which create two new data types (longchar and longbinary)
> and procedures for conversion between new types and oids, C source
> for SPI procedure and conversion functions(which are very simple),
> and trigger creation example.

I'd be interested in looking at those types for JDBC.

--
Peter Mount (at work) peter(at)taer(dot)maidstone(dot)gov(dot)uk or peter(at)maidstone(dot)gov(dot)uk
If you mail me here, please cc my home address peter(at)retep(dot)org(dot)uk