Re: [HACKERS] SPI procedure for removing large objects

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.

>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.

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.
When this stuff installed, it is possible to create tables with new
data types, and store long data just usings existing large object functions
and type casting, like:
INSERT INTO <table> VALUES(lo_import('etc/motd')::longchar);

Best regards,
Sergey Levov (serg(at)informika(dot)ru)