On 02/04/2017 03:08 AM, Andres Freund wrote: > On 2017-02-03 18:47:23 -0500, Robert Haas wrote: >> On Fri, Feb 3, 2017 at 6:00 PM, Andres Freund <and...@anarazel.de> wrote: >>> I still haven't seen a credible model for being able to apply a stream >>> of interleaved transactions that can roll back individually; I think we >>> really need the ability to have multiple transactions alive in one >>> backend for that. >> Hmm, yeah, that's a problem. That smells like autonomous transactions. > Unfortunately the last few proposals, like spawning backends, to deal > with autonomous xacts aren't really suitable for replication, unless you > only have very large ones. And it really needs to be an implementation > where ATs can freely be switched inbetween. On the other hand, a good > deal of problems (like locking) shouldn't be an issue, since there's > obviously a possible execution schedule. > > I suspect this'd need some low-level implemention close to xact.c that'd > allow switching between transactions.
Let me add my two coins here: 1. We are using logical decoding in our multimaster and applying transactions concurrently by pool of workers. Unlike asynchronous replication, in multimaster we need to perform voting for each transaction commit, so if transactions are applied by single workers, then performance will be awful and, moreover, there is big chance to get "deadlock" when none of workers can complete voting because different nodes are performing voting for different transactions. I could not say that there are no problems with this approach. There are definitely a lot of challenges. First of all we need to use special DTM (distributed transaction manager) to provide consistent applying of transaction at different nodes. Second problem is once again related with kind of "deadlock" explained above. Even if we apply transactions concurrently, it is still possible to get such deadlock if we do not have enough workers. This is why we allow to launch extra workers dynamically (but finally it is limited by maximal number of configures bgworkers). But in any case, I think that "parallel apply" is "must have" mode for logical replication. 2. We have implemented autonomous transactions in PgPro EE. Unlike proposal currently present at commit fest, we execute autonomous transaction within the same backend. So we are just storing and restoring transaction context. Unfortunately it is also not so cheap operation. Autonomous transaction should not see any changes done by parent transaction (because it can be rollbacked after commit of autonomous transaction). But there are catalog and relation caches inside backend, so we have to clean this caches before switching to ATX. It is quite expensive operation and so speed of execution of PL/pg-SQL function with autonomous transaction is several order of magnitude slower than without it. So autonomous transaction can be used for audits (its the primary goal of using ATX in Oracle PL/SQL applications) but this mechanism is not efficient for concurrent execution of multiple transaction in one backend. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
