2016-10-21 10:24 GMT+02:00 Tsunakawa, Takayuki <

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



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

Reply via email to