Re: New feature request: FlashBack Query - Mailing list pgsql-hackers

Ühel kenal päeval, P, 2007-02-18 kell 14:27, kirjutas Joshua D. Drake:
> 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?

First we must run the query in serializable mode and replace the
snapshot with a synthetic one, which defines visibility at the start of
the desired transaction

probably it is a good idea to take a lock on all tables involved to
avoid a vacuum to be started on them when the query is running.

also, we can't trust the DELETED flags in index pages, so we should
forbid index scans, or just always re-check the visibility in heap.

Otherways it would probably be enough to just scan tuples as usual, and
check if they were visible to desired transaction, that is they were
inserted before that transaction and they are not deleted before that
trx.

Of course this will not be true, once we have HOT/WIP with in-page
vacuuming.

-- 
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com