Re: [RFC] Transaction management overhaul is necessary?

2016-10-21 10:24 GMT+02:00 Tsunakawa, Takayuki <
tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>