[RFC] Transaction management overhaul is necessary?
От | Tsunakawa, Takayuki |
---|---|
Тема | [RFC] Transaction management overhaul is necessary? |
Дата | |
Msg-id | 0A3221C70F24FB45833433255569204D1F634D6D@G01JPEXMBYT05 обсуждение исходный текст |
Ответы |
Re: [RFC] Transaction management overhaul is necessary?
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: