New feature request: FlashBack Query
in [PgSql]
|
Prev: Piggybacking vacuum I/O
Next: pg_proc without oid?
From: Theo Schlossnagle on 20 Feb 2007 13:45 On Feb 20, 2007, at 1:40 PM, Tom Lane wrote: > RPK <rohitprakash123(a)indiatimes.com> writes: >> I did not mean asking for undo from a life-time log. Since FlashBack >> Technology is already there, I just mean that world's most >> advanced database >> (PostgreSQL, as they say), must have an optimized way for undoing >> of at >> least a week changes. > > You're living in a dream world. Do you know any Oracle DBs who keep > enough rollback segments to go back a week? Ours go for a good 6 hours sometimes :-D // Theo Schlossnagle // Esoteric Curio: http://www.lethargy.org/~jesus/ ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: "Jonah H. Harris" on 20 Feb 2007 14:48 On 2/20/07, Rod Taylor <rod.taylor(a)gmail.com> wrote: > Do 97% of transactions commit because Oracle has slow rollbacks and > developers are working around that performance issue, or because they > really commit? Again, off-topic, but 97% of all transactions commit according to Jim Gray and his research... not anything related to Oracle. > I have watched several developers that would prefer to issue numerous > selects to verify things like foreign keys in the application in > order to avoid a rollback. That's just bad development. > Anyway, I don't have experience with big Oracle applications but I'm > not so sure that 97% of transactions would commit if rollbacks were > cheaper. Again, stats not related to Oracle, but databases in general. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris(a)enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/ ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
From: "Jonah H. Harris" on 20 Feb 2007 15:04 On 2/20/07, Hannu Krosing <hannu(a)skype.net> wrote: > He probably meant longer transactions and several versions visible to > different backends. Yes, he may have... but I was responding to the statements he made. > > but why shouldn't that be a bit slower, it isn't common practice anyway. > > Not for pure OLAP, at least when you have fairly fast transactions. But > it can slow things down when you have some hotspot tables. True, but hotspots are hotspots and no matter what caused them or where they are, they slow down performance in one area or another. Limiting hotspots is generally an application-level design decision anyway. > > Same with rollbacks... why > > should they optimize for them when 97% of transactions commit? > > Or other way around, - you should write code, where most transactions > commit ;) That's what I said, Oracle shouldn't optimize for rollbacks when most transactions commit. > Hmm. How can it check visibility at block level and at the same time do > in-place updates on single tuples ? In most cases, the block-level SCN determines transaction-level visibility. Now, row locks can exist within that page, but they don't determine visibility... they determine the UNDO location which contains the data required to rebuild a read-consistent version of the block. > My proposal of keeping visibility info in a separate heap would help to > get similar results, that is mostly 1 check per page. That would also > cover much of the index lookup cases below. Most definitely. > I don't think we will ever move to rollback segments, but for some > use-cases moving visibility to a separate heap could make sense. Yes. > And if we want to bring back time travel (see another thread about > "Flashback Queries"), then we may end up implementing the original > postgresql's design spec and make VACUUM spihon dead tuples over to > archive relations, which already starts looking a little like rollback > segments, only for other purposes :) Yes. > Using a system extensively can also create blind spots about some of the > systems (mis)features. One learns to avoid doing some things without > consciously knowing about it. I've used 'em all and can certainly name issues with Oracle. However, we're discussing improving PostgreSQL, I was responding to Greg's statements, and I don't see the need to bring up unrelated Oracle implementation details which will just lead to a general anti-Oracle discussion. > To get a really meaningful discussion we should involve someone who has > *designed* them, not merely used them . True. My comment is more along the lines of uninformed discussion which leads to Oracle-bashing. Those who have at least used and administered Oracle in production tend to understand Oracle's design decisions and related issues better than those who have just heard of Oracle's issues. I live in the real world and can admit certain failures of any database system regardless of which I prefer. No single database is best for every task. I just didn't want the discussion going where it normally goes, to being one of, "we're right and they're wrong". Can we move offline or to another thread if we want to continue discussing Oracle-specifics; otherwise... let's focus on flashback-like functionality in this thread. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 3rd Floor | jharris(a)enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/ ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
From: August Zajonc on 20 Feb 2007 22:31 RPK wrote: > Andrew, > >>> Demanding unlimited undo at some time that is arbitrarilly distant in the >>> future strikes me as wholly unreasonable. > > I did not mean asking for undo from a life-time log. Since FlashBack > Technology is already there, I just mean that world's most advanced database > (PostgreSQL, as they say), must have an optimized way for undoing of at > least a week changes. A week log is enough and PostgreSQL can keep on > removing old logs automatically. > > Secondly, it must be left to the user to decide for the number of days of > archive he want to store. Again upto a week max. You might look at storing delta's or similar (perhaps with a check table ) if you need to change data a week back. Then you can just find the row representing the problematic change and delete it. If you really want to track what happens, do deltas and then instead of deleting them, put a reversing delta in, keeping your entire audit trail. You can put materialized views on top of this if you need performance. - August ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
From: Csaba Nagy on 21 Feb 2007 04:17
> Do 97% of transactions commit because Oracle has slow rollbacks and > developers are working around that performance issue, or because they > really commit? > > I have watched several developers that would prefer to issue numerous > selects to verify things like foreign keys in the application in > order to avoid a rollback. Most of the code we have will not afford a rollback because it can be part of a much bigger transaction which would have much higher performance penalty if retried than a simple rollback. And you know that in postgres you can't roll back just the last insert, you will crash the whole transaction with it... and it's simply a performance bottleneck to retry in a high contention scenario (which is usually so in our case). So I would say we don't avoid rollbacks because of the cost of the rollback, but because of the cost of the retry... Cheers, Csaba. ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo(a)postgresql.org so that your message can get through to the mailing list cleanly |