Re: PREPARE TRANSACTION and webapps

At 02:22 PM 11/11/2005 +0100, Martijn van Oosterhout wrote:

>But once you've prepared a transaction, you can't reopen it, all you
>can do is either commit it or abort it. I don't see how prepared
>transaction relate to webapps at all.
>
>See also the docs:
>http://www.postgresql.org/docs/8.1/static/sql-prepare-transaction.html

Oh. I thought one could reopen transactions. Why can't we do that? ;)

Would it be reasonably possible to add a postgresql feature to save
transactions to disk, disconnect from the database, reconnect to the
database, reopen and continue a desired transaction? e.g. CONTINUE
TRANSACTION transaction_id

I think I asked for such a feature in postgresql years ago, but didn't get
a positive reply, so I figured it was not possible, but with the recent
announcement of the "prepare transaction" feature, I'm getting a bit more
hopeful :).

Is there a reason why transactions should be so tightly linked to database
connections?  Being able to decoupling transactions from database
connections could make a lot of tasks easier.

Vacuum not being able to reclaim dead rows isn't a big issue. Unless I'm
mistaken, doing such a thing at the application level will by necessity
result in a similar situation. It's all a necessary cost of supporting that
many concurrent outstanding _transactions_ (in contrast with the cost of
supporting "real" concurrent DB connections).

Deadlocking is an issue of course. But are there any differences in the
locking situation? Wouldn't it be the same as having a normal transaction
that takes a long time to complete? We already have users with transactions
that are open for days at least.

I think it's easier to use NOWAIT than to reimplement MVCC at a
webapplication level :).

If a CONTINUE TRANSACTION feature is possible, it could also allow
postgresql systems to "pretend" to support many more concurrent open
"database connections" ;).

Regards,

Link.