Re: PREPARE and transactions

On Thu, Jun 24, 2004 at 04:19:36PM -0400, Merlin Moncure wrote:

> I am using PostgreSQL as a backend for legacy COBOL applications and
> have written a driver which maps the COBOL I/O statements to SQL
> statements. To save a little bit on parsing time and for various other
> reasons these SQL statements are handled as prepared queries. Each
> COBOL file has a corresponding SQL table in the database and each table
> can have up to 7 prepared statements that the application creates when
> it needs them. Unless I am misunderstanding things, if you change the
> prepared statement's lifetime, I am forced to prepare a bunch of
> statements all at once instead of when they are needed. I am prepared
> to do this, however (pun intended).

Sounds like a challenging (is that word still fashionable?) job. Now if
only you weren't dealing with legacy applications, this would be a natural
for stored procedures I guess. Well okay, maybe you could define stored
procedures on demand but then who would clear them up afterwards...

What if prepared statement semantics were modeled after those of session
variables? You can change a session variable from within a transaction
and keep the change afterwards, but it does go by transaction rules.

What that leaves us is something that works _almost_ the way things work
now, so your code would work unchanged in the normal case. The difference
would be that PREPARE would roll back like any other statement.

Taking this one step further, if compatibility with current semantics is
important, I could imagine adding a hack-I admit it's not pretty--that
keeps the statement allocated despite a rollback, but sets a "soft" bit.
The backend could silently accept identical redefinitions of prepared
statements that have the "soft" bit set.

I think that would be the most compatible way, and probably the easiest
as well, to turn PREPARE into a regular statement:

1. Add a "soft" bit to prepared-statement plans
2. Add rollback bookkeeping for prepared statements, which sets the bit
3. Accept identical re-preparations of "soft" statements, clearing the bit

Deallocation, lookup, execution etc. would not need to change. There would
still be the risk of "leaking" prepared statements, but that is a problem
of the current semantics anyway.

Jeroen