>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 
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 

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) 
Gathering specs and discussion on feature (post 9.1) 

Takayuki Tsunakawa

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to