Re: [QUESTIONS] Re: [HACKERS] text should be a blob field

> outstanding issue with them, that I was waiting for 6.3 to be released
> before starting on it (and almost certainly starting a discussion here
> about it).
>
> Allowing text to use blobs for values larger than the current block size
> would hit the same problem.
>
> Ok, here's what the problem is at the moment:
>
> The JDBC example ImageViewer uses a table to store the name of an image,
> and the OID of the associated blob.
>
> # create table images (imgname name,imgoid oid);
>
> Ok, we now create an entry in the table for an image with:
>
> # insert into images values ('test.gif',lo_import('/home/pmount/test.gif'));
>
> This is fine so far. Now say we delete that row with:
>
> # delete from images where name = 'test.gif';
>
> Fine again, except that the blob is still in the database. To get round
> this, you would have to add extra statements to handle this, and for JDBC,
> there is no standard way to do this.
>
> What I was thinking of, was to create a new type 'blob' which would delete
> the associated large object when the row is deleted. However, here's the
> problems against this:
>
> 1. Is there a call made by the backend to each datatype when a row is
> deleted? I can't see one.

Well, you could have a RULE that deletes the large object at row
deletion time. However, if two rows point to the same large object, the
first one deleting it would delete the large object for the other. The
only solution to this is to have a separate large object table, and use
reference counts so only the last user of the object deletes it.

>
> 2. When we update a row, we don't want the overhead of copying a very
> large blob when a row is first copied, then the original deleted, etc.

Again, a deletion-only rule, but if the update the row and change the
large object, you would have to delete the old stuff.

Seems very messy to me. Perhaps put all the large objects in a table,
and have a process clean up all the unreferenced large objects.

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)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)