Re: [HACKERS] SPI procedure for removing large objects

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

> Hello!
>
> In message
>     <Pine.LNX.3.96.980805215449.793A-100000@maidast.retep.org.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@taer.maidstone.gov.uk or peter@maidstone.gov.uk
If you mail me here, please cc my home address peter@retep.org.uk