Thread: deleting large objects from jdbc
Hi, I've been browsing the newsgroup archives and came across the following: > > > > > Can you see a scenario where a programmer would forget to delete the > > > > > data from pg_largeobject and the database becoming very large filled > > > > > with orphaned large objects? Does this mean that to delete my large object I need to delete the row in my table, and also the row or rows in pg_largeobject. Currently I am only deleting the row in my table. I have just been looking at the pg_largeobject table, it seems to have more rows than I would expect in it. Is the solution to pass these two commands to jdbc? delete from my_table where lob_oid = 1234 delete from pg_largeobject where loid = 1234 Thanks in advance Heather.
Heather, Your understanding is correct. You need to delete the large object when you no longer reference it in any other rows. The SQL statements below should work for that purpose. thanks, --Barry heatherm@famoice.com wrote: > Hi, > > I've been browsing the newsgroup archives and came across the following: > > >>>>>>Can you see a scenario where a programmer would forget to >>>>>> > delete the > >>>>>>data from pg_largeobject and the database becoming very large >>>>>> > filled > >>>>>>with orphaned large objects? >>>>>> > > Does this mean that to delete my large object I need to delete the row > in my table, and also the row or rows in pg_largeobject. Currently I am > only deleting the row in my table. I have just been looking at the > pg_largeobject table, it seems to have more rows than I would expect in > it. Is the solution to pass these two commands to jdbc? > delete from my_table where lob_oid = 1234 > delete from pg_largeobject where loid = 1234 > > Thanks in advance > Heather. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
Shouldn't the JDBC driver fix up things like that though? I've just changed my delete code, which was quite straight forward. My update code was more interesting. If the blob was updated then the lob_oid in my_table was changed and the new data put in the pg_largeobject table, but the old blob data was left there, orphaned. It's lucky I found out about this now and the beginning of development. Thanks Heather. -----Original Message----- From: barry [mailto:barry@xythos.com] Sent: Thursday, 9 August 2001 13:10 To: Heather Murray Cc: barry; pgsql-jdbc Subject: Re: deleting large objects from jdbc Heather, Your understanding is correct. You need to delete the large object when you no longer reference it in any other rows. The SQL statements below should work for that purpose. thanks, --Barry heatherm@famoice.com wrote: > Hi, > > I've been browsing the newsgroup archives and came across the following: > > >>>>>>Can you see a scenario where a programmer would forget to >>>>>> > delete the > >>>>>>data from pg_largeobject and the database becoming very large >>>>>> > filled > >>>>>>with orphaned large objects? >>>>>> > > Does this mean that to delete my large object I need to delete the row > in my table, and also the row or rows in pg_largeobject. Currently I am > only deleting the row in my table. I have just been looking at the > pg_largeobject table, it seems to have more rows than I would expect in > it. Is the solution to pass these two commands to jdbc? > delete from my_table where lob_oid = 1234 > delete from pg_largeobject where loid = 1234 > > Thanks in advance > Heather. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
On Thu, 9 Aug 2001 14:58:13 +1000, Heather wrote: [delete Lob when no longer referenced] >Shouldn't the JDBC driver fix up things like that though? Its not an interface issue. You'll see exactly the same behaviour using the interfaces for C/C++, Perl, Python etc. This is not something an interface can easily fix. The driver on the client side does not know if there are any rows referencing the Lob. If this needs to be changed, it would have to be changed on the backend. Barry suggested last week to eventually change the Blob-implementation to the toast model. That would make a Blob part of a row and the delete kludge would no longer be needed. Regards, René Pijlman
heatherm@famoice.com wrote: > Shouldn't the JDBC driver fix up things like that though? This is how LargeObjects are designed to work in PostgreSQL. Many people consider this a good feature and depend on this functionality. Unfortunately it works in a way that is different from other RDBMS, and therefore people often don't realize the differences. The documentation certainly needs to be improved in this area. thanks, --Barry > > I've just changed my delete code, which was quite straight forward. My > update code was more interesting. If the blob was updated then the > lob_oid in my_table was changed and the new data put in the > pg_largeobject table, but the old blob data was left there, orphaned. > It's lucky I found out about this now and the beginning of development. > > Thanks > Heather. > > -----Original Message----- > From: barry [mailto:barry@xythos.com] > Sent: Thursday, 9 August 2001 13:10 > To: Heather Murray > Cc: barry; pgsql-jdbc > Subject: Re: deleting large objects from jdbc > > > Heather, > > Your understanding is correct. You need to delete the large object > when > you no longer reference it in any other rows. The SQL statements below > should work for that purpose. > > thanks, > --Barry > > heatherm@famoice.com wrote: > >>Hi, >> >>I've been browsing the newsgroup archives and came across the >> > following: > >> >>>>>>>Can you see a scenario where a programmer would forget to >>>>>>> >>>>>>> >>delete the >> >> >>>>>>>data from pg_largeobject and the database becoming very large >>>>>>> >>>>>>> >>filled >> >> >>>>>>>with orphaned large objects? >>>>>>> >>>>>>> >>Does this mean that to delete my large object I need to delete the >> > row > >>in my table, and also the row or rows in pg_largeobject. Currently I >> > am > >>only deleting the row in my table. I have just been looking at the >>pg_largeobject table, it seems to have more rows than I would expect >> > in > >>it. Is the solution to pass these two commands to jdbc? >>delete from my_table where lob_oid = 1234 >>delete from pg_largeobject where loid = 1234 >> >>Thanks in advance >>Heather. >> >>---------------------------(end of >> > broadcast)--------------------------- > >>TIP 1: subscribe and unsubscribe commands go to >> > majordomo@postgresql.org > >> > >