Hello!

I've written the SPI procedure which allows to delete large
objects referenced by currently deleted or updated tuples.
I tested it with PostgreSQL v6.3.2 and seem it works.
If PostgreSQL developers team is interested in it, I'll send
the sources to this mailing list or to somebody personally.

Sorry for my English.

Best regards,
Sergey E. Levov (serg@informika.ru)

Wed,  5 Aug 1998 18:22:22 +0400 (MSD)
"Sergey E. Levov" <serg@gate.informika.ru>
SPI procedure for removing large objects
On Wed, 5 Aug 1998, Sergey E. Levov wrote:

> Hello!
> 
> I've written the SPI procedure which allows to delete large
> objects referenced by currently deleted or updated tuples.
> I tested it with PostgreSQL v6.3.2 and seem it works.
> If PostgreSQL developers team is interested in it, I'll send
> the sources to this mailing list or to somebody personally.

I'll be interested (I wrote the copy in contrib/lo - post 6.3.2 source
tree).

There was some talk about 5 weeks ago about using vacuum to pick up any
stragglers. I have some ideas on this, but can't try them out at the
moment - I haven't had a working backend for about 3 weeks now :-(

> Sorry for my English.

No problem - I'm English, and sometimes it's worse ;-)

-- 
Peter T Mount peter@retep.org.uk or petermount@earthling.net
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres


Wed, 5 Aug 1998 19:00:12 +0100 (GMT)
Peter T Mount <peter@retep.org.uk>
Re: [HACKERS] SPI procedure for removing large objects
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.

Peter T Mount wrote:

> On Wed, 5 Aug 1998, Sergey E. Levov wrote:
>
> > Hello!
> >
> > I've written the SPI procedure which allows to delete large
> > objects referenced by currently deleted or updated tuples.
> > I tested it with PostgreSQL v6.3.2 and seem it works.
> > If PostgreSQL developers team is interested in it, I'll send
> > the sources to this mailing list or to somebody personally.
>
> I'll be interested (I wrote the copy in contrib/lo - post 6.3.2 source
> tree).
>
> There was some talk about 5 weeks ago about using vacuum to pick up any
> stragglers. I have some ideas on this, but can't try them out at the
> moment - I haven't had a working backend for about 3 weeks now :-(




Wed, 05 Aug 1998 14:50:42 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] SPI procedure for removing large objects
> 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.
> 

We should also make a large object type, rather than using inv_ to
identify it.  It is on the TODO list, and I can implement it whenever
you want.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Wed, 5 Aug 1998 15:31:37 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] SPI procedure for removing large objects
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).

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

-- 
Peter T Mount peter@retep.org.uk or petermount@earthling.net
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres


Wed, 5 Aug 1998 22:01:48 +0100 (GMT)
Peter T Mount <peter@retep.org.uk>
Re: [HACKERS] SPI procedure for removing large objects
On Wed, 5 Aug 1998, Bruce Momjian 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.
> > 
> 
> We should also make a large object type, rather than using inv_ to
> identify it.  It is on the TODO list, and I can implement it whenever
> you want.

agreed - although that would imply a different method of storing them. One
of the problems I have with VACUUM LO is that using the existing oid
method (for compatibility) would not work with the new type.

Either using a different form of storage, or a different prefix would sort
this problem (the latter would be the easiest).

> 
> -- 
> Bruce Momjian                          |  830 Blythe Avenue
> maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
>   +  If your life is a hard drive,     |  (610) 353-9879(w)
>   +  Christ can be your backup.        |  (610) 853-3000(h)
> 

-- 
Peter T Mount peter@retep.org.uk or petermount@earthling.net
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres


Wed, 5 Aug 1998 22:04:43 +0100 (GMT)
Peter T Mount <peter@retep.org.uk>
Re: [HACKERS] SPI procedure for removing large objects
> On Wed, 5 Aug 1998, Bruce Momjian 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.
> > > 
> > 
> > We should also make a large object type, rather than using inv_ to
> > identify it.  It is on the TODO list, and I can implement it whenever
> > you want.
> 
> agreed - although that would imply a different method of storing them. One
> of the problems I have with VACUUM LO is that using the existing oid
> method (for compatibility) would not work with the new type.
> 
> Either using a different form of storage, or a different prefix would sort
> this problem (the latter would be the easiest).
> 

OK, give me a day or two.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Wed, 5 Aug 1998 17:40:06 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] SPI procedure for removing large objects


Peter T Mount wrote:

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

I will move on to something else unless their is something I can assist you
with.

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

Actually, the trigger is still worth having in the bag-o-tricks.   It can keep
numerous additions and removals of tuples with  LOs from filling up the disk
before the vacuum gets executed.

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




Wed, 05 Aug 1998 18:05:58 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] SPI procedure for removing large objects


Peter T Mount wrote:

> On Wed, 5 Aug 1998, Bruce Momjian 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.
> > >
> >
> > We should also make a large object type, rather than using inv_ to
> > identify it.  It is on the TODO list, and I can implement it whenever
> > you want.
>
> agreed - although that would imply a different method of storing them. One
> of the problems I have with VACUUM LO is that using the existing oid
> method (for compatibility) would not work with the new type.
>

I see it that way also.   But I do not perceive this to be a problem.   Users who
have been using OIDs to link to their large_objects will continue to operate as
they always have.   I can't see how we could attempt to promote the functionality
of existing install base.   The problem, which is the essential problem, is that
we can presume nothing about the relationship between an arbitrary OID type column
and the large objects themselves.

However as part of a conversion, the DBA may be able to UPDATE pg_attribute
manually and  change the type from OID to LO.  ???  Or we provide a script to do
this where the DBA enters the large object columns???

> Either using a different form of storage, or a different prefix would sort
> this problem (the latter would be the easiest).
>




Wed, 05 Aug 1998 18:06:18 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] SPI procedure for removing large objects
> 
> 
> Peter T Mount wrote:
> 
> > On Wed, 5 Aug 1998, Bruce Momjian 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.
> > > >
> > >
> > > We should also make a large object type, rather than using inv_ to
> > > identify it.  It is on the TODO list, and I can implement it whenever
> > > you want.
> >
> > agreed - although that would imply a different method of storing them. One
> > of the problems I have with VACUUM LO is that using the existing oid
> > method (for compatibility) would not work with the new type.
> >

My change will allow large objects themselves to be identified inside
the backend without have to see if their name is inv_.  Currently, we
flag indexes and normal relations, and view/rules.  Large objects
themselves will have their own type.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Wed, 5 Aug 1998 22:04:48 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] SPI procedure for removing large objects
On Wed, 5 Aug 1998, David Hartwig wrote:

> 
> 
> Peter T Mount wrote:
> 
> > On Wed, 5 Aug 1998, Bruce Momjian 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.
> > > >
> > >
> > > We should also make a large object type, rather than using inv_ to
> > > identify it.  It is on the TODO list, and I can implement it whenever
> > > you want.
> >
> > agreed - although that would imply a different method of storing them. One
> > of the problems I have with VACUUM LO is that using the existing oid
> > method (for compatibility) would not work with the new type.
> >
> 
> I see it that way also.   But I do not perceive this to be a problem.   Users who
> have been using OIDs to link to their large_objects will continue to operate as
> they always have.   I can't see how we could attempt to promote the functionality
> of existing install base.   The problem, which is the essential problem, is that
> we can presume nothing about the relationship between an arbitrary OID type column
> and the large objects themselves.

Actually, if what Bruce is saying about LO then this problem may go away.

> However as part of a conversion, the DBA may be able to UPDATE pg_attribute
> manually and  change the type from OID to LO.  ???  Or we provide a script to do
> this where the DBA enters the large object columns???

I don't think it would hurt to have a script to to the conversion -
especially for those who are upgrading existing databases.

> > Either using a different form of storage, or a different prefix would sort
> > this problem (the latter would be the easiest).
> >

-- 
Peter T Mount peter@retep.org.uk or petermount@earthling.net
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres


Thu, 6 Aug 1998 06:45:16 +0100 (GMT)
Peter T Mount <peter@retep.org.uk>
Re: [HACKERS] SPI procedure for removing large objects
On Wed, 5 Aug 1998, David Hartwig wrote:

> Peter T Mount wrote:
> 
> > 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.
> 
> I will move on to something else unless their is something I can assist you
> with.
> 
> >  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).
> >
> 
> Actually, the trigger is still worth having in the bag-o-tricks.   It can keep
> numerous additions and removals of tuples with  LOs from filling up the disk
> before the vacuum gets executed.

I think for small tables, then relying on VACUUM LO would be enough.
However, the trigger would be a viable option for DBA's when either the
database is huge, or has a lot of traffic.

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

-- 
Peter T Mount peter@retep.org.uk or petermount@earthling.net
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres


Thu, 6 Aug 1998 06:47:14 +0100 (GMT)
Peter T Mount <peter@retep.org.uk>
Re: [HACKERS] SPI procedure for removing large objects
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.

>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@informika.ru)

Thu,  6 Aug 1998 12:53:01 +0400 (MSD)
"Sergey E. Levov" <serg@gate.informika.ru>
Re: [HACKERS] SPI procedure for removing large objects
Hello Peter,

In message
    <Pine.LNX.3.96.980805185640.13326R-100000@maidast.retep.org.uk>
    Peter T Mount writes:

>On Wed, 5 Aug 1998, Sergey E. Levov wrote:
>> 
>> I've written the SPI procedure which allows to delete large
>> objects referenced by currently deleted or updated tuples.
>> I tested it with PostgreSQL v6.3.2 and seem it works.
>> If PostgreSQL developers team is interested in it, I'll send
>> the sources to this mailing list or to somebody personally.

>I'll be interested (I wrote the copy in contrib/lo - post 6.3.2 source
>tree).

Below is an uuencoded .tgz archive with sources.

Best regards,
Sergey Levov (serg@informika.ru)

------------------------- cut --------------------------
begin 644 spi.tgz
M'XL(`.BJRC4``^T9:5/CR)6OZE_QAF$*B<$G-J2XLL86K%,>F\@FNUM)RBM+
M;5M!2!X=,&0W_SVOU=TZ?,`FN[!;-711(+U^]]6O1;AP*ELOO*!1/6HV80O8
MJB[]%2]PV&PVCNJUHT8=WX^.CJI;T'QIQ=B*P\@,`+8"WX^>PGN84^J^AD*O
MNT*,O^N/`WKGW].R]2(R:M7J8:.Q,?Z']8.ZB'^MV:P=`=3J]?KA%E1?1)NE
M]97'O[)'8`]R&0"EDF)3ET847#.84?`G_Z)6!`&=TH!Z%K5A\@@<PP9&O';Y
M`<0+VV0X4;QPZ4;$(44ACZ"7H4?O_7M00P1\XWA3/[AS;LUR$&M(6R'DO>-9
M;FQ3.`TCV_'+\W-%42I[@(@0+@+'BZ8,+X^&P%D1+PHL,Z36W:*(2K]0*X[\
MH(+ED!`@?C1W0L"?A[D9P:,?@T>9,3X\^,$M/#C1'(;7W2(?R[^[,ST[K*#H
MV8P&R"MG*C(M;9<`$4#LAXEE)'I<4)M.`6UH8"R\V<3QS.#Q9'7#FIO!"2'?
M4G,Q8FY5TLBI][YC:R=DX-B*XEIC?%,E!6`@:[@GWQ7<'+N6BKARBY--4C*N
M09Z00SCI)$]*\OIP7\_->\I<%=`H#CQ(MQE,%[YFIF?::^0GPGPTXHYAR2)\
M=,)9(N6,1A((GGE'&0=&8U#7C!S?P\>`NA*?(V+(`KDMT#-M>&K"&?1O>KT3
M3B;5%*JOTOBNG2`MX4]H6A2YW%\E]^A#CAS?!`N!F6!U:&@ERMGX(.U)L!@@
M<!9Y<S`W%)%@J//),LSA$!9V48&85,Q[&5Q@?HYI\/CW6OU/_UQAXF(3&#OV
M"AQK:LRMQC3@&=XV79?W"!DJK`B3_?USDNP)ARFH[]IQ@/TD$@'OF)&I$5DI
MU/5GJFX8`V,?MM,D.<[*Q@PH>'Z$NCB18[K.OZF]K7$=F!+#N1^[-HN)Q=5A
MQ6\,ODMURFLR,KI75[HQONP:>F=\.3#&PU%KI'_2^R-U5<G2>30;TWL$:AI1
M-BEJF=YN!(O`MV@80LH/$L(PKRJS(ERG;K<_U(V1"/0&92]^&'.TYQ1]SK%%
M?3G/O+()`YGY6#`;Q#'W\NQ."&2N;R9(JX&A)S0R0D_*8&V!\(QW-V/*PN>H
MHIP0'>&E\\`>FY$LEW6.[>@]?:3_$L?^E+HWJP<44SM)X=(+:>](=OZ#X0CI
M4RRJJRQ2CW$6,H\X'F8^C2(\]=:XA#4IUN<PRCRP8#L!.]E9SH5L5WA6LIK&
MGL7<AZ>@ES0'AX9EZ..YC`2/^VFD,CS9]M-$EJS6=`/+QP/3]]S'LD`22;Y&
M<=&?LR)0L=$A%(_?L>5['AJA:AJ<0O6)BLPAB]Z.=?;!WMYG;[(>6=VI3A([
M<.#T3"9-Z=S#9`D1^/'C<LRQAJTYM6X38G9@\[/)GP*2!,XDCFAJ7*[]HI!$
M)SS4&)$J).V#HV5!3XQ]ETTMJB!.3.-G^39:_O//L!9K.SNW$2WK`IGR;(GF
MC@J9D>^H0JE[TXVI*A-V'W+ZY11D2PQ?:G)^H&8A9B\Z&7T?>Z[CW:H?'`W=
M+,1H)[+2\T9F6:\5]HJ:LL748^.:%%=+&*:K@)\KL,T,F7CI@G?K?"`KKN@#
M[==I1HI/_#?C,,4C+9RKFNQOR12B)@H@#.L=)R^R=KX3$Y0D03PMV<BHUH]W
M3]))$61Y9%RBDGA+I%P.61X:UQ!SS!SY[WTS^CH6N_]_,F_IU''I2\EXYOY?
M:]1J_/Y?.ZK7:PV$U)JUQMO]_S76>_(>9/R3$RP],M-SO8PH`?T<X[00PE7_
M!H_P6_H.+^37?AC-`CK\:P]"/PYP=!0CA8\E3X9&N],USJ`2AT%EX0=16,$[
MD3G!,RK$]X0T_.Q6V'4Z]UXZ+!^4ZY4PL`B1]^H=E3/3TE0MS[!EFRXJ`1'[
M2A'E)+<'?1SF+ICP';67/&CL8D](^[+7NAI^/$.&_'$\[&E0ZF;\A404/67W
M;D._[/9'X[_IQL5@J&?TI<[2#O5L9TK(J&5<Z:/A6>;%';73&]Y<7G:_UW)?
M6=!.U*:GM_J7W9[.-1*T&B$X.AT?%R".AUF:0/$WTSXU42/OE1T5!_91J\=L
ML5#G5N>37D2J<"!16(39YP38*],OYEUR\63:P%Y.T1.P?2@RW=F9+G%$",/S
MT%>%C6.BW-UB,*"T6%*4?&"2C@'_)-E"E.`.2LCWFQ/X!U':9S\N'NP?D^<0
M9[,2Q5'B>#RX^,MWG?%Q4<9LFV&QYB&PI.X,,;6#H9W"SBF<HPQ"+)>:WC&D
M8E/_H@O\K_6X*7[_Y8'YK64\T_\/JHVF_/Y[=-"L8_\_.*C6W_K_:ZS>H-6!
M75%E629D!;6+5Z..,;B&RYM^>]0=]',G!)M2E_;X8.HO3!R"5W<G3^V*"?.I
MW4FV2]K8TT;Z>K6(8NBC&Z,_!(Y/E-;P:2N)TFOUKVY:5SKL6KM$&CWZX5K/
M?7@MPN176JE*`1G4[!;G133PV(78FT7SL\9^]CGF2V&G5MW/$2WBZ(Q]^'6\
M#.K'D03C8P;'P\J,W>AL=S>#+<P0^^CD,;G%$&V-GN(6\,?5="FO<M<,29O&
MV;$EZ-E0"[SE@*_(G&0RQ>WD%:3R*@"5?<U?%I=>QEY"YF2S3'&+^W525^M5
M,B[(?4)P(EE\71#M8*>FI?)R`G'.V&3HNG"NBV=>F.A;_Z,P&:W-YN5CN6*<
M]?\:5ZB/9TVS?H%IO_<A];9>;!7G/W$G^(UE/#/_0:UYE,Q_!XU&K5IG]_^#
M1O7@;?Y[C25'`O[?CVR((LJ%?CDP=.#_!H&!`3?7'8:*G>84K_$N/2<*8H#>
J:G^;_&^-*/KW>OL&4:Z-05OOW!CZTJSX]E'O;;VM/]3Z+SAEY:H`*```
`
end
size 2157

Fri,  7 Aug 1998 11:26:50 +0400 (MSD)
"Sergey E. Levov" <serg@gate.informika.ru>
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



Fri, 7 Aug 1998 10:16:18 +0100 (BST)
Peter T Mount <peter@taer.maidstone.gov.uk>
Re: [HACKERS] SPI procedure for removing large objects
> Hello Peter,
> 
> In message
>     <Pine.LNX.3.96.980805185640.13326R-100000@maidast.retep.org.uk>
>     Peter T Mount writes:
> 
> >On Wed, 5 Aug 1998, Sergey E. Levov wrote:
> >> 
> >> I've written the SPI procedure which allows to delete large
> >> objects referenced by currently deleted or updated tuples.
> >> I tested it with PostgreSQL v6.3.2 and seem it works.
> >> If PostgreSQL developers team is interested in it, I'll send
> >> the sources to this mailing list or to somebody personally.
> 
> >I'll be interested (I wrote the copy in contrib/lo - post 6.3.2 source
> >tree).
> 
> Below is an uuencoded .tgz archive with sources.
> 
> Best regards,
> Sergey Levov (serg@informika.ru)
> 

Not sure what to do with this.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Sat, 22 Aug 1998 07:17:23 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] SPI procedure for removing large objects
On Sat, 22 Aug 1998, Bruce Momjian wrote:

> > Hello Peter,
> > 
> > In message
> >     <Pine.LNX.3.96.980805185640.13326R-100000@maidast.retep.org.uk>
> >     Peter T Mount writes:
> > 
> > >On Wed, 5 Aug 1998, Sergey E. Levov wrote:
> > >> 
> > >> I've written the SPI procedure which allows to delete large
> > >> objects referenced by currently deleted or updated tuples.
> > >> I tested it with PostgreSQL v6.3.2 and seem it works.
> > >> If PostgreSQL developers team is interested in it, I'll send
> > >> the sources to this mailing list or to somebody personally.
> > 
> > >I'll be interested (I wrote the copy in contrib/lo - post 6.3.2 source
> > >tree).
> > 
> > Below is an uuencoded .tgz archive with sources.
> > 
> > Best regards,
> > Sergey Levov (serg@informika.ru)
> > 
> 
> Not sure what to do with this.

I first had a quick look just after I received it, and it looks similar to
what's in contrib/lo.

I'm not sure what to do with it either.

-- 
       Peter T Mount peter@retep.org.uk
      Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
 Java PDF Generator: http://www.retep.org.uk/pdf


Sun, 23 Aug 1998 20:34:17 +0100 (GMT)
Peter T Mount <peter@retep.org.uk>
Re: [HACKERS] SPI procedure for removing large objects