Re: [Re] Re: PREPARE and transactions - Mailing list pgsql-hackers

From Oliver Jowett
Subject Re: [Re] Re: PREPARE and transactions
Date
Msg-id 40DC4914.2050509@opencloud.com
Whole thread Raw
In response to Re: [Re] Re: PREPARE and transactions  ("Jeroen T. Vermeulen" <jtv@xs4all.nl>)
Responses Re: [Re] Re: PREPARE and transactions
List pgsql-hackers
Jeroen T. Vermeulen wrote:

> There was also the middleware argument--some intermediate software layer
> may be in control of bracketing.  But in such cases, can you even rely
> on two independent transactions executing in the same session?  You'd
> need to assume that to make the current semantics work in that situation.
> What if the middleware does connection pooling, or restores a broken
> connection between two transactions?  The latter might happen because of
> a timed-out firewall, for instance, when there is a long pause between
> two unrelated transactions.

The current JDBC driver uses PREPARE/EXECUTE to prepare arbitrary 
queries, requested either directly from the application or via a 
middleware layer. For queries where use of PREPARE/EXECUTE is requested, 
the driver sends a PREPARE/EXECUTE pair on query execution. If the 
PREPARE succeeds, subsequent query executions for the same query send 
only the EXECUTE.

This might all happen either inside or outside a transaction -- the 
mechanics of transaction demarcation are done by the driver, but the 
transaction model used is up to the application and is effectively 
invisible to the driver.

The set of PREPAREd queries is per-connection state, so whatever 
connection pooling etc. logic runs on top of the driver isn't an issue. 
The driver currently stores that state in the statement objects 
allocated by the application -- which are also tied to a particular 
connection -- but there's no reason why the driver couldn't, for 
example, maintain a cache of prepared statements per connection and 
match that against newly requested queries.

If PREPAREd statements did DEALLOCATE on transaction rollback, the 
driver would have to track the set of statements that were first 
PREPAREd in the current transaction so it can fix the state on the 
driver side if the transaction rolls back. This is a lot of extra 
complexity for no benefit I can see. And it'd get pretty nasty if nested 
transactions were involved..

It's all somewhat moot for the JDBC driver as it's moving to using 
protocol-level Parse/Bind messages instead of PREPARE/EXECUTE statements 
anyway. That said, I would be very unhappy if Parse suddenly became 
transactional to match the behaviour of PREPARE.

-O


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Compile failure with SSL
Next
From: Gaetano Mendola
Date:
Subject: Re: warning missing