New feature request: FlashBack Query
in [PgSql]
|
Prev: Piggybacking vacuum I/O
Next: pg_proc without oid?
From: Alvaro Herrera on 21 Feb 2007 09:14 Florian G. Pflug wrote: > However, I just realized that doing this is much harder than I initially > thought, because catalog access always happens with SnapshotNow, and > e.g. "drop table" deletes datafiles at commit time, and not during vacuum. Not to mention the likenesses of CLUSTER and TRUNCATE, which would need to be taught how to keep the old datafiles for an additional week/hour. What I don't understand is why people isn't working in improving contrib/spi/timetravel. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
From: "Florian G. Pflug" on 21 Feb 2007 10:09 Alvaro Herrera wrote: > Florian G. Pflug wrote: > >> However, I just realized that doing this is much harder than I initially >> thought, because catalog access always happens with SnapshotNow, and >> e.g. "drop table" deletes datafiles at commit time, and not during vacuum. > > Not to mention the likenesses of CLUSTER and TRUNCATE, which would need > to be taught how to keep the old datafiles for an additional week/hour. > > What I don't understand is why people isn't working in improving > contrib/spi/timetravel. Because it serves different usecase I think - flashback is an administrative tool, not something you design your application around. Flashback is more similar to PITR recovery than to contrib/spi/timetravel. greetings, Florian Pflug ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
From: August Zajonc on 1 Mar 2007 12:48
Florian G. Pflug wrote: > Alvaro Herrera wrote: >> Florian G. Pflug wrote: >> >>> However, I just realized that doing this is much harder than I initially >>> thought, because catalog access always happens with SnapshotNow, and >>> e.g. "drop table" deletes datafiles at commit time, and not during >>> vacuum. >> >> Not to mention the likenesses of CLUSTER and TRUNCATE, which would need >> to be taught how to keep the old datafiles for an additional week/hour. >> >> What I don't understand is why people isn't working in improving >> contrib/spi/timetravel. > > Because it serves different usecase I think - flashback is an > administrative tool, not something you design your application around. > Flashback is more similar to PITR recovery than to contrib/spi/timetravel. Drat. I remember when truncate wasn't even transaction safe, but I think it was since cut so that the non-rollbackable portion happened after commit. Ultimately, anything that changed data would need to basically deferred into the vacuum or other cycle. Basically, super MVCC, a truncate would basically do the tuple type action on the underlying files. Catalog stuff too, HOT all would need those semantics. Not doable. A lot of places that grab an AccessExclusiveLock are probably subject to this issue. Unless there was a bog standard way of doing this, and I don't see a good option, no go. So fun to think about and probably all sorts of neat things you could do with super MVCC, TRUNCATE a table with open transactions concurrent, but way too work for the gain of this tiny use feature... contrib/timetravel I think has some of these same issues (ie, drop table, can you still time travel?) along with a fair bit of trigger based overhead... - August ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |