Neil Conway <neilc@samurai.com> writes:
> Jon Jensen wrote:
>> How would you do a rollback if an error occurred mid-query? How would you
>> keep your table from being a big pile of junk if a power failure happened
>> during the query?
> As most non-MVCC database do: by writing WAL records.
> In theory, it seems to me that we could implement an overwriting
> storage manager for PostgreSQL (i.e. we'd have both MVCC and non-MVCC,
> and allow users to choose at runtime). It would require a *lot* of
> work, but I can't see any fundamental reason why it wouldn't be possible.
It would be possible, but what's the point? There's no evidence that
this approach is superior to what we do, and a fair number of reasons
to think it's inferior.
In particular, with this approach you have to maintain the entire
history of a transaction in WAL, so that you have the info available to
roll back if it aborts. So the OP's concern about needing 2X the disk
space to update his table still applies, it's just in a different place.
It's true that it's easier to get the space back when no longer needed
--- VACUUM FULL is expensive if it's moving lots of records. But in
general I think our VACUUM-based approach is superior to the
Oracle-style UNDO approach, because it pushes the maintenance overhead
out of foreground transaction processing and into a schedulable
background process. Certainly any Oracle DBA will tell you that huge
transactions are a headache to handle in Oracle.
I think what the OP actually wanted us to offer was non-transaction-safe
overwrite-in-place updating. That might be technically feasible (not
sure how we'd deal with changes in indexed columns though). But it's so
contrary to the basic design goals of the project that I don't think
anyone will take the suggestion seriously.
regards, tom lane
