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

On Mon, 2003-04-07 at 14:59, Ron Peacetree wrote:
> 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.

If "stepping through a largish table in chunks" is implemented as a
single SQL query, PostgreSQL already does this internally (the parsing,
planning, rewriting, and execution phases are distinct operations inside
the backend).

If the stepping is done as a bunch of similar queries, you can use
prepared queries (as of PostgreSQL 7.3) to do the parsing, planning and
rewriting only once, and then reuse the query plan multiple times.

> 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.

Prepared queries are per-backend as of PostgreSQL 7.3, so this can't be
done (and I'm a little skeptical that it would be very useful...)

Cheers,

Neil