Thread: persistant transactions
I was wondering if it's feasible to have transactions that persist outside of connections for certain purposes. I'm picturing something where something like a web app could open a transacation and using transparent sessions in php or something like that, it could open a multipage transaction that could be accessed using non-persistant connections. I could see this being a complete nightmare if the transactions didn't get closed eventually, but with some kind of timeout setting this could be a right useful feature. But I'm not sure it belongs in the database proper. I'm thinking the way to do something like this is to do it in plpgsql as a set of functions that can initiate a pseudo transaction and a cron job that checks for timeouts every x minutes and dumps the old transactions. Does this idea make any sense at all? Is it a feature that would make sense in the postgresql core code? Would it be something that would be an absolute nightmare to actually code because of the connection orientedness of the postgresql backend design and therefore should be forever relegated to being done in a procedural language / cron job combo? Scott Marlowe
On Fri, Jan 10, 2003 at 09:23:53AM -0700, scott.marlowe wrote: > I was wondering if it's feasible to have transactions that persist outside > of connections for certain purposes. I'm picturing something where > something like a web app could open a transacation and using transparent > sessions in php or something like that, it could open a multipage > transaction that could be accessed using non-persistant connections. > > I could see this being a complete nightmare if the transactions didn't get > closed eventually, but with some kind of timeout setting this could be a > right useful feature. > > But I'm not sure it belongs in the database proper. I'm thinking the way > to do something like this is to do it in plpgsql as a set of functions > that can initiate a pseudo transaction and a cron job that checks for > timeouts every x minutes and dumps the old transactions. > Does this idea make any sense at all? Is it a feature that would > make sense in the postgresql core code? Would it be something that would > be an absolute nightmare to actually code because of the connection > orientedness of the postgresql backend design and therefore should be > forever relegated to being done in a procedural language / cron job combo? It sounds more like something to implement in an external daemon. User code connects to the daemon, says "give me a connection" and the daemon opens a new pgsql connection (or, more likely, reuses an open one from the pool) and returns a unique token. The user code can then disconnect and reconnect to the daemon as many times as it likes and maintain the same DB connection. If SQLRelay <http://sqlrelay.sourceforge.net/> doesn't do this already I'd bet it could be persuaded to fairly easily. Cheers, Steve
On Fri, 10 Jan 2003, Steve Atkins wrote: > On Fri, Jan 10, 2003 at 09:23:53AM -0700, scott.marlowe wrote: > > I was wondering if it's feasible to have transactions that persist outside > > of connections for certain purposes. I'm picturing something where > > something like a web app could open a transacation and using transparent > > sessions in php or something like that, it could open a multipage > > transaction that could be accessed using non-persistant connections. *SNIP* > If SQLRelay <http://sqlrelay.sourceforge.net/> doesn't do this already I'd > bet it could be persuaded to fairly easily. Thanks for the tip, I'll go look it over.