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
parametervalues 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-levelrollback. And PostgreSQL creates one CurTransactionContext memory context, which is 8KB, for each
subtransactionand 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
(SAVEPOINTand RELEASE) for each statement.
I know autonomous transaction is also discussed, which seems to be difficult, so I hope some kind of transaction
managementoverhaul can be discussed to cover all these transaction-related features. How should I start? I found the
followingitem 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