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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
