[RFC] Transaction management overhaul is necessary?

Hello,

From our experience in handling customers' problems, I feel it's necessary to evolve PostgreSQL's transaction management. The concrete problems are:

1. PostgreSQL cannot end and begin transactions in PL/pgSQL and PL/Java stored functions.
This is often the reason people could not migrate to PostgreSQL.

2. PostgreSQL does not support statement-level rollback.
When some customer ran a batch app using psqlODBC, one postgres process used dozens of GBs of memory and crashed the OS. The batch app prepares some SQL statements with parameters, execute it five millions of times with different parameter values in a single transaction. They didn't experience a problem with Oracle.

This was because psqlODBC starts and ends a subtransaction for each SQL statement by default to implement statement-level rollback. And PostgreSQL creates one CurTransactionContext memory context, which is 8KB, for each subtransaction and retain them until the top transaction ends. The total memory used becomes 40GB (8KB * 5 million subtransactions.) This was avoided by setting the Protocol parameter to 7.4-1, which means transaction-level rollback.

The savepoint approach for supporting statement-level rollback is inefficient, because it adds two roundtrips (SAVEPOINT and RELEASE) for each statement.

I know autonomous transaction is also discussed, which seems to be difficult, so I hope some kind of transaction management overhaul can be discussed to cover all these transaction-related features. How should I start? I found the following item in the TODO list (but I haven't read it yet.) What other discussions should I look at?

--------------------------------------------------
Implement stored procedures
This might involve the control of transaction state and the return of multiple result sets
PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
Proposal: real procedures again (8.4)
http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php
Gathering specs and discussion on feature (post 9.1)
--------------------------------------------------

Regards
Takayuki Tsunakawa