[ZODB-Dev] RelStorage - zodbconvert script and the --clear flag

[Sean Upton]

On Thu, Sep 1, 2011 at 12:45 AM, Shane Hathaway <shane at hathawaymix.org> wrote:
> On 08/31/2011 05:11 PM, Darryl Dixon - Winterhouse Consulting wrote:
>>> Just had a quick query from my friendly local DBA; he wanted to know why
>>> --clear was using DELETE rather than TRUNCATE; his comments were along the
>>> lines of:
>>> * TRUNCATE creates no UNDO
>>> * TRUNCATE cleans out the indexes nicer
>>>
>>> Is there any real downside or gotcha I should be aware of to following
>>> this suggestion in this instance?
>>
>> To be clear:
>>
>> This is RelStorage 1.5.0 running against Oracle RAC 10g.
>>
>> I tried changing the line in question (relstorage/adapters/schema.py line
>> 895) to "TRUNCATE TABLE %s" % table, and the following error is generated:
>> ORA-02266: unique/primary keys in table referenced by enabled foreign keys
>>
>> So I guess without a bunch of work it will not be possible.
>
> Is clearing the database a common operation that should be tuned for
> performance?  That part of the code is optimized for safety and
> readability, not performance.

Replying late, but coming back to this case, I figured it is worth
mentioning what I do to work around this (that deleting is slow even
on a modest database): I drop the database and re-create.  This is
quick -- 20 seconds of manual work versus 20+ minutes of waiting for a
delete to finish (PostgreSQL); I am pretty sure I will soon just wrap
zodbconvert in a shell script (templated in my buildout) -- this
avoids the whole truncate versus delete debate (and I can just keep my
blob directory intact on the destination).

Sean