New feature request: FlashBack Query

RPK wrote:

PostgreSQL, already a mature database, needs to have more options for
recovery as compared to proprietary databases. I just worked with Oracle's
FlashBack query feature in Oracle 9i and FlashBack Table feature in 10g.

Future versions of PostgreSQL must have similar features which enable users
to bring Table(s) and/or Database(s) to a desired Time Stamp.

We can do it with databases, we can't do it with tables. Nor should we
do it with tables as it would require that all tables in relation are
also flashed backed.

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/

"Joshua D. Drake" <jd@commandprompt.com> writes:

RPK wrote:

Future versions of PostgreSQL must have similar features which enable users
to bring Table(s) and/or Database(s) to a desired Time Stamp.

We can do it with databases, we can't do it with tables. Nor should we
do it with tables as it would require that all tables in relation are
also flashed backed.

AFAICT this is a request to re-instate Time Travel, which is a feature
we removed more than ten years ago because the overhead was utterly
unacceptable.  And the project's idea of acceptable performance then
was orders of magnitude weaker than it is now.  So it's not going to
happen, at least not in the general release.  You might take a look at
contrib/spi/README.timetravel, though, for a prototype of how something
similar can be achieved without any changes to the core system.  That
module is a bit unmaintained and could doubtless do with some updates
--- for starters, it should be using timestamptz instead of the old
deprecated abstime.  If you're interested, feel free to work on it.
No one else has taken an interest in a long time.

regards, tom lane

On Sat, Feb 17, 2007 at 11:48:55AM -0500, Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

RPK wrote:

Future versions of PostgreSQL must have similar features which enable users
to bring Table(s) and/or Database(s) to a desired Time Stamp.

We can do it with databases, we can't do it with tables. Nor should we
do it with tables as it would require that all tables in relation are
also flashed backed.

AFAICT this is a request to re-instate Time Travel, which is a feature
we removed more than ten years ago because the overhead was utterly
unacceptable.  And the project's idea of acceptable performance then
was orders of magnitude weaker than it is now.  So it's not going to
happen, at least not in the general release.  You might take a look at
contrib/spi/README.timetravel, though, for a prototype of how something
similar can be achieved without any changes to the core system.  That
module is a bit unmaintained and could doubtless do with some updates
--- for starters, it should be using timestamptz instead of the old
deprecated abstime.  If you're interested, feel free to work on it.
No one else has taken an interest in a long time.

For other recent time travel ideas see: http://www.varlena.com/GeneralBits/122.php
Time travel is not cheap, though.

--elein
elein@varlena.com

On 2/17/07, elein <elein@varlena.com> wrote:

For other recent time travel ideas see:
http://www.varlena.com/GeneralBits/122.php
Time travel is not cheap, though.

I am sure this topic has probably been beaten to death in the past, but has
anyone talked about the advantages of Oracle's MVCC model versus
PostgreSQL's MVCC model? Oracle achieves multiversioning by using
rollback/undo segments, where PostgreSQL appears to place (essentially) the
undo in the same space as the table.

If I were to guess this is probably a major thing to change. Clearly there
are advantages to both, with Oracle essentially the space consumed by a
modified row is immediately available for reuse and generally there is
little row migration assuming there is enough space on the block so you
should be able to avoid updates to the index and the bloating that seems to
go along with vacuuming.

Is there any previous discussions that folks could point out here?

Chad Wagner wrote:

On 2/17/07, elein <elein@varlena.com> wrote:

For other recent time travel ideas see:
http://www.varlena.com/GeneralBits/122.php
Time travel is not cheap, though.

I am sure this topic has probably been beaten to death in the past, but has
anyone talked about the advantages of Oracle's MVCC model versus
PostgreSQL's MVCC model? Oracle achieves multiversioning by using
rollback/undo segments, where PostgreSQL appears to place (essentially) the
undo in the same space as the table.

My understanding is that the main difference is that rollbacks are
inexpensive for us, but expensive for Oracle. Talk to an Oracle DBA
about their Rollback logs :0.

However, they don't have vacuum, we do.

Joshua D. Drake

If I were to guess this is probably a major thing to change. Clearly there
are advantages to both, with Oracle essentially the space consumed by a
modified row is immediately available for reuse and generally there is
little row migration assuming there is enough space on the block so you
should be able to avoid updates to the index and the bloating that seems to
go along with vacuuming.

Is there any previous discussions that folks could point out here?

--

=== 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/

On 2/17/07, Joshua D. Drake <jd@commandprompt.com> wrote:

My understanding is that the main difference is that rollbacks are
inexpensive for us, but expensive for Oracle. Talk to an Oracle DBA
about their Rollback logs :0.

Yes, I have seen cases where undo segments are thrashed. Generally it works
well, and I agree it likely much cheaper on PostgreSQL as you would expect
fewer scattered reads because the old version is inline with the rest of the
data.

But if I recall undo segments are cached in Oracle, usually where I see
problems is where the DBA is completely incompetent and has undersized the
buffer cache. Oracle does direct reads (afaik) -- so undersizing the buffer
cache can be brutal. A very common mistake with Oracle was undersizing the
buffer cache and oversizing the shared pool (when the shared plans are
stored), and with 9i and later they tried to have the management tools
suggest the ideal values or have it automatically managed by the database.
Probably a step in the right direction, but I know they still have bumps to
iron it. :)

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. The disadvantages is likely
overhead to perform the "rollback" and possibly more scattered reads. I can
say that I have used it, and it has come in handy, but hardly worth it. The
benefit with the PostgreSQL model is the likelihood of the old rows being
inline with the rest of the table data, potentially reducing scattered
reads. The disadvantage is vacuuming, it seems to be often overlooked --
possibly solved by defaulting autovacuum to on? (seems to be the way Oracle
is heading, defaulting statistics collection to on and other management
features).

"Chad Wagner" <chad.wagner@gmail.com> writes:

I am sure this topic has probably been beaten to death in the past, but has
anyone talked about the advantages of Oracle's MVCC model versus
PostgreSQL's MVCC model?

Yes, we've been all through that. We like ours. See the archives.

regards, tom lane

On Saturday 17 February 2007 07:49, RPK wrote:

PostgreSQL, already a mature database, needs to have more options for
recovery as compared to proprietary databases. I just worked with Oracle's
FlashBack query feature in Oracle 9i and FlashBack Table feature in 10g.

Future versions of PostgreSQL must have similar features which enable users
to bring Table(s) and/or Database(s) to a desired Time Stamp.

Check out my proposal[1]http://archives.postgresql.org/pgsql-hackers/2007-02/msg00540.php for Temporal extensions. Ultimately, creating valid
time and transaction time tables would be possible through my proposal. Please
check it out.

[1]: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00540.php

wt
--
Warren Turkal (w00t)

Ü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"

AFAIK, Oracles flashbacks also can go as far back as there are rollback
segments.

Postgres' original design prescribed, that VACUUM would not delete dead
tuples, but just move them to history tables on cheap(er) WORM storage.
Doing that would have very little overhead (except writing the old
tuples) and would not need any fundamental changes to how we do things
currently.

The disadvantages is likely overhead to perform the "rollback" and
possibly more scattered reads.

I've also heard reports, that doing concurrent data loading and big
analysis queries is a royal pain in Oracle.

I can say that I have used it, and it has come in handy, but hardly
worth it. The benefit with the PostgreSQL model is the likelihood of
the old rows being inline with the rest of the table data, potentially
reducing scattered reads. The disadvantage is vacuuming, it seems to
be often overlooked -- possibly solved by defaulting autovacuum to on?
(seems to be the way Oracle is heading, defaulting statistics
collection to on and other management features).

--
----------------
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

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/

Hello,

On Sat, 17 Feb 2007 06:49:42 -0800 (PST)
RPK <rohitprakash123@indiatimes.com> wrote:

PostgreSQL, already a mature database, needs to have more options for
recovery as compared to proprietary databases. I just worked with Oracle's
FlashBack query feature in Oracle 9i and FlashBack Table feature in 10g.

Future versions of PostgreSQL must have similar features which enable users
to bring Table(s) and/or Database(s) to a desired Time Stamp.

There is a pgfoundry project which tries to achieve this:

http://pgfoundry.org/projects/tablelog/

Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL Usergroup: http://www.pgug.de

Ü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

Hannu Krosing wrote:

Ü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:
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.

Would the xmin exported by that transaction prevent vacuum from removing
any tuples still needed for the flashback snapshot?

greetings, Florian Pflug

Florian G. Pflug escribi�:

Hannu Krosing wrote:

�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:
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.

Would the xmin exported by that transaction prevent vacuum from removing
any tuples still needed for the flashback snapshot?

Sure, and that makes the mentioned lock unnecessary.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

We could use something that controls "global xmin".
It would ensure, that global xmin does not advance bejond
what still needs to be visible. This would probably be a
sliding time window, or a fixed point in time that is
released by the dba/user.

Then all below is not really different from a situation where
you had a long running tx.

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.

Currently I think HOT does honor "global xmin". There is no
lookup for relevant xids, so parts of an update chain where
only a previous tuple or a later tuple can be visible are reused.
Else Hot would need to be told not to, in a scenario where
a backend can choose a snapshot at will.

Andreas

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.

Would the xmin exported by that transaction prevent vacuum from
removing any tuples still needed for the flashback snapshot?

Sure, and that makes the mentioned lock unnecessary.

Problem is, that that transaction sets a historic snapshot at a later
time, so it is not yet running when vacuum looks at "global xmin".
So something else needs to hold up global xmin (see prev post).

Andreas

Zeugswetter Andreas ADI SD wrote:

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.

Would the xmin exported by that transaction prevent vacuum from
removing any tuples still needed for the flashback snapshot?

Sure, and that makes the mentioned lock unnecessary.

Problem is, that that transaction sets a historic snapshot at a later
time, so it is not yet running when vacuum looks at "global xmin".
So something else needs to hold up global xmin (see prev post).

I think to make this flashback stuff fly, you'd need to know the
earliest xmin that you can still flashback too. Vacuum would advance
that xmin, as soon as it starts working. So the case you'd need to
protect against would be a race condition when you start a vacuum
and a flashback transaction at the same time. But for that, some simple
semaphore should suffice, and a well-thought-out ordering of the actions
taken.

In the long run, you'd probably want to store the commit-times of
transactions somewhere, and add some guc that makes a vacuum assume
that recently comitted transaction (say, in the last hour) are still
considered active. That allow the dba to guarantee that he can always
flashback at least a hour.

greetings, Florian Pflug

"Zeugswetter Andreas ADI SD" <ZeugswetterA@spardat.at> writes:

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

We could use something that controls "global xmin".
It would ensure, that global xmin does not advance bejond
what still needs to be visible. This would probably be a
sliding time window, or a fixed point in time that is
released by the dba/user.

Well there's another detail you have to cover aside from rolling back your
xmin. You have to find the rest of the snapshot including knowing what other
transactions were in-progress at the time you want to flash back to.

If you just roll back xmin and set xmax to the same value you'll get a
consistent view of the database but it may not match a view that was ever
current. That is, some of the transactions after the target xmin may have
committed before that xmin. So there was never a time in the database when
they were invisible but your new xmin was visible.

I think to do this you'll need to periodically record a snapshot and then
later restore one of those saved snapshots. Not sure where would be a good
place to record them. The WAL seems like a handy place but digging through the
WAL would be annoying.

Incidentally this is one of the things that would be useful for read-only
access to PITR warm standby machines.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, Feb 19, 2007 at 04:00:09PM +0100, Florian G. Pflug wrote:
[...]

In the long run, you'd probably want to store the commit-times of
transactions somewhere, and add some guc that makes a vacuum assume
that recently comitted transaction (say, in the last hour) are still
considered active [...]

Funny how some things recur:

<http://archives.postgresql.org/pgsql-hackers/2007-01/msg01301.php&gt;

(says I and seeks shelter beneath a big rock ;-)

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFF2c3RBcgs9XrR2kYRAh1PAJ442IXzr0CjN0w5a3BpwBrKgVGvsgCcCmyh
mnM5AUTHo4uIZ/WCnWxLVM0=
=1aUG
-----END PGP SIGNATURE-----