Re: Anyone know why PostgreSQL doesn't support 2 phase execution?

"Rod Taylor" <rbt(at)rbt(dot)ca> wrote in message
news:1049735504(dot)40144(dot)35(dot)camel(at)jester(dot)(dot)(dot)
> --=-v98gp7DTtZFJa7ee6YMf
> Content-Type: text/plain
> Content-Transfer-Encoding: quoted-printable
>
> On Sat, 2003-04-05 at 11:35, Ron Peacetree wrote:
> > ...and what kind of performance hit we take (and under what
> > circumstances) for not having it?
>
> Do you mean 2-phase commits? If so, how do you take a performance
hit
> from *not* having it? PostgreSQL doesn't have it (prepare & forget
> phases)) simply because nobody has completed and submitted an
> implementation. Satoshi is working on the problem.
>
> If not, what do you mean by 2 phase execution?
>
The performance hit as in "in comparison to DB's that =do= have two
phase execution (and commit for that matter), just how much slower is
PostgreSQL?"

Two phase execution and two phase commit are two different concepts.
Two phase execution splits the execution of queries explicitly into a
"do all the book keeping and setup stuff before execution" phase and
an actual execution phase. The benefit is that if you are going to
say, step through a largish table in chunks, doing the same query on
each chunk, two phase execution allows the DB to do everything (syntax
checking, query planning, blah, blah) except the actual execution
=once= and reuse it for each subsequent chunk. Think of it as a close
cousin to loop unrolling. It also helps parallel performance since
you can hand the "blessed" set up query plan to multiple processes and
those processes can focus on just getting work done.

The lack of two phase =commit= is a also a potential performance hit
in comparison to DB products that have it, but the more important
issue there is that there are SMP/distributed apps that really can't
work acceptably unless a DB product has two phase commit.

The three "biggies" in DB land, SQL Server, Oracle, and DB2, have both
features. I suspect that PostgreSQL will need to as well...