On Mon, Jun 13, 2011 at 12:16, Martijn Pieters <m...@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 _______________________________________________ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev