Re: PREPARE TRANSACTION and webapps - Mailing list pgsql-general

At 11:27 PM 11/15/2005 -0500, Tom Lane wrote:

>That said, it seems to me that the prepared-xacts infrastructure could
>possibly support a separate "suspend transaction" and "resume
>transaction" facility, if anyone wants to do the legwork to make it
>happen.  What this would actually be useful for is a fair question
>though --- what's it do that you don't have now?

MVCC-style transactions that are not limited by/to database connections.

This could be useful if you want to have X pending database transactions
and Y max concurrent database connections, where X is significantly greater
than Y (magnitudes higher?).

My assumption is that pending transactions (e.g. locks and other metainfo)
will take much less memory than database backends.

It'll be nice (but it might be difficult) to have an implementation that
allowed migration of transactions to a different node in a cluster - so
that one could bring down a database node server in the middle of a
transactions without affecting database users/applications severely. A
suitable protocol might allow a database client to automatically save its
transaction, and then resume it on another node, without the database
user/app noticing much (not sure if this is a good idea though).

With respect to concerns about users leaving transactions open for long
periods, this sort of thing already happens with the current implementation.

As such, similar measures can be taken: rollback/commit the offending
transactions. One needs a way of listing information about pending
transactions, and some methods to manage them.

My assumption is managing pending transactions would be easier than
reimplementing MVCC and the other stuff. Especially if only controlled
types of transactions are saved and resumed - one scenario might even put
such transactions in a different database so as not to affect other
transactions. But I could be wrong :).

Sure one could create a tangled mess with thousands of transactions. But I
don't think that's the fault of supplying X amounts of rope instead of Y
amounts of rope, where X >> Y.

Are there RDBMSes out there with this feature already? I'm not sure what
keywords to search for.

I suspect it might be very difficult to do on a database without an MVCC
architecture.

Regards,
Link.