Re: New feature request: FlashBack Query

Hannu Krosing wrote:
> Ühel kenal päeval, L, 2007-02-17 kell 22:49, kirjutas Chad Wagner:
> 
>>
>>         However, they don't have vacuum, we do.
>>
>> Right, and I think that is more or less because Oracle doesn't need
>> it.  Vacuum's main purpose (correct me if I am wrong) is to
>> recover/mark rows that are no longer used, and Oracle essentially
>> reuses the space immediately. 
>>
>> Obviously with Oracle if you bloat out a table and delete a ton of
>> rows then you have to rebuild the table, but that is more or less the
>> same problem that PostgreSQL has and where vacuum full comes into
>> play.
>>
>> The only benefit with the Oracle model is that you can achieve
>> flashback, which is a very rarely used feature in my book.
> 
> We can have flashbacks up to the last vacuum. It is just not exposed.
> Don't vacuum, and you have the whole history. (Actually you can't go for
> more than 2G transactions, or you get trx id rollover).
> 
> To get a flashback query, you "just" have to construct a snapshot from
> that time and you are done. We don't store transaction times anywere, so
> the flashback has to be by transaction id, but there is very little
> extra work involved. We just don't have syntax for saying "SELECT ... AS
> SEEN BY TRANSACTION XXX"

Well this is certainly interesting. What do we think it would take to
enable the functionality?

Joshua D. Drake



-- 
     === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997            http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/