[ZODB-Dev] Relstorage Blob support and Oracle

[Martijn Pieters]

On Mon, Jun 13, 2011 at 12:16, Martijn Pieters <mj at zopatista.com> wrote:
>> WRT using PostgreSQL OIDs for blobs: the extra burden of deleting unused
>> OIDs doesn't seem worth the extra effort to me.  I would be happy to
>> hear that you found a reasonably simple way to do it.  My guess is that
>> an on-delete trigger will be the best way.
>
> Already solved with a trigger, see
>
>  http://zope3.pov.lt/trac/changeset/121922/relstorage/branches/postgres_blob_oid
>
> :-)
>
> The trigger is really really simple; the example I first found had to
> deal with UPDATE too, making things a little more complicated, but
> RelStorage never does that; it's either INSERT or DELETE.

In the end the trigger had to be slightly more complicated, but it was
worth it. Now, when you undo a transaction and thus copy a (zoid, tid)
-> blob oid forward to the current transaction, you still keep only
*one* copy of the blob data. The oid is the same, and the deletion
trigger takes this into account. Only when there are no more
references left to the oid is the underlying data removed.

This also applies to the temp_blob_chunk table, where blobs are first
uploaded during the first phase of the two-phase transaction commit.
If the transaction never finishes (second phase), these oids are later
harvested if the zoid is re-used.

I am considering altering the ON COMMIT behaviour of the
temp_blob_chunk table to TRUNCATE to extend this behaviour to all
remaining blob rows on commit. However, such events should be rare
enough, and there is a `vacuumlo` command that can find orphaned blob
objects in the pg_largeobjects table and clean these out.

I'm also writing a unittest that'll exercise the blob storage a little
more, by creating a file larger than the maximum storable in one
chunk, then upload, commit and re-download to see if the data survived
intact. This'll be set to test level 2 to not clog up the normal test
cycle as generating a 4GB+ random file can take a little while! :-)

Last but not least I'll need to write a migration script for those
users of RelStorage 1.5.0b2 already in production.

-- 
Martijn Pieters