Re: deleting large objects from jdbc

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(at)xythos(dot)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(at)famoice(dot)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(at)postgresql(dot)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(at)famoice(dot)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(at)xythos(dot)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(at)famoice(dot)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(at)postgresql(dot)org
>
>>
>
>