Re: New feature request: FlashBack Query

On 2/20/07, Hannu Krosing <hannu(at)skype(dot)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(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/