Thread: SPI procedure for removing large objects
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)
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
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 :-(
> 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)
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
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
> 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)
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).
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). >
> > > 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)
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
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
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)
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
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
> 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)
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