2016-10-21 10:24 GMT+02:00 Tsunakawa, Takayuki < tsunakawa.ta...@jp.fujitsu.com>:
> 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? > You should to implement a CALL statement - that can be independent on outer transaction. The behave inside procedure called by CALL statement should be same like client side - and there you can controll transactions explicitly without nesting. Regards Pavel > > -------------------------------------------------- > 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 > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
