On 21 October 2016 at 18:57, Pavel Stehule <pavel.steh...@gmail.com> wrote: > 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.
I've run into relatively few for whom this has landed up being a showstopper, but I agree it's a pain. There'll probably be more as bigger outfits seek to move from That Other Database where it's routine to do this. >> 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. Surely that's where to start then. Find a way to pool and re-use, fully release, or otherwise be done with transaction contexts for released savepoints. >> 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. You can control transaction level rollback in psqlODBC directly. You do not need to fall back to the old protocol. Check the driver options. >> The savepoint approach for supporting statement-level rollback is >> inefficient, because it adds two roundtrips (SAVEPOINT and RELEASE) for each >> statement. Right. We can't just fire off each statement wrapped in SAVEPOINT and RELEASE SAVEPOINT because we need to get the result of the statement and decide whether to ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT. It only requires two round trips if you shove the SAVEPOINT in with the intended statement, but it's still messy. I'd like to see an alternative statement with semantics more akin to COMMIT - which automatically into ROLLBACK if the tx is aborted. COMMIT SAVEPOINT would be too confusing since it's not truly committed. I don't know what to call it. But basically something that does RELEASE SAVEPOINT [named savepoint] unless the subxact is in aborted state, in which case it does ROLLBACK TO [named savepoint]. Bonus points for letting it remember the last savepoint created and use that. Furthermore, we should really add it on the protocol level so drivers can send subtransaction control messages more compactly, without needing to go through the parser etc, and without massively spamming the logs. For this purpose savepoint names would be internally generated so the driver wouldn't have to send them. We'd log savepoint boundaries when transaction logging was enabled. Since the client would send the first such protocol request we could do it on the sly without a protocol version bump; clients could just check server version and not use the new messages for older servers. If they send it to an older server they get a protocol error, which is fine. > 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. I agree that'd be desirable. Top level "procedures" are necessary for this, really. This would also enable us to return multiple result sets. We'd probably have to start at least one small read-only tx for the initial cache access to look up the proc and set everything up, but if we don't allocate xids local transactions are super cheap. However, I think trying to tackle the memory context bloat reported upthread would be a more effective starting point since it immediately targets the problem actually experienced. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
