Re: 7.3.4 on Linux: UPDATE .. foo=foo+1 degrades massivly

Philipp Buehler wrote:

>On 22/04/2004, Guy Fraser <guy(at)incentre(dot)net> wrote To pgsql-general(at)postgresql(dot)org:
>
>
>>>Shouldn't the Database server be the entity that decides when vacuum is
>>>needed?
>>>
>>>
>>How is the database supposed to know when you want to purge records?
>>Once a vacuum has been run, the table can not be rolled back or time
>>traveled.
>>
>>
>
>Hmm, if the UPDATE is in a transaction block. After this block is
>committed, the deleted tuple could be purged if there is a flag. Like,
>either in the schema 'purge-on-commit', or as an option like 'UPDATE PURGE'?
>
>Just an idea.. (Brainfart?) :)
>
>ciao
>
>
What if your not the only person accessing the database and someone else
has an active transaction that was initiated before your transaction was
committed?

If you delete the 'stale' data, then you would have to abort their
transaction or their transaction would have data with mixed results from
before your update and after your update. When to remove 'stale' data is
not a simple thing to determine in an active database. In order to
preserve transactional integrity, all transactions that are being
handled during the transaction that modifies the data, must be completed
before the data can be considered 'stale' and once the 'stale' data is
purged roll backs can not be permitted for any transaction before the
purge. Eventually the automatic purging of 'stale' data will be
supported, but hopefully it will be configurable to allow 'time travel'
when required, and allow for a reasonable time to be able to roll back
transactions.

I am not an RDBMS developer and don't claim to know how the inner
workings work, but I have been using RDBMS application for many years
and have worked with a number of different products. I prefer PostGreSQL
over all others, but I do use others for some projects where their
support or special features out way those of PostGreSQL.