Re: persistant transactions

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