New feature request: FlashBack Query
in [PgSql]
|
Prev: Piggybacking vacuum I/O
Next: pg_proc without oid?
From: RPK on 17 Feb 2007 09:49 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. -- View this message in context: http://www.nabble.com/New-feature-request%3A-FlashBack-Query-tf3245023.html#a9020502 Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: "Joshua D. Drake" on 17 Feb 2007 10:50 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/ ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: Tom Lane on 17 Feb 2007 11:48 "Joshua D. Drake" <jd(a)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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
From: elein on 17 Feb 2007 19:06 On Sat, Feb 17, 2007 at 11:48:55AM -0500, Tom Lane wrote: > "Joshua D. Drake" <jd(a)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(a)varlena.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: "Chad Wagner" on 17 Feb 2007 19:43
On 2/17/07, elein <elein(a)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? |