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 <andres@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
asynchronousreplication, in multimaster we need to perform voting for each transaction commit, so if transactions are
appliedby single
workers, then performance will be awful and, moreover, there is big chance to get "deadlock" when none of workers can
completevoting 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
weneed 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
isstill 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
executeautonomous transaction within the same backend. So we are just storing and restoring transaction context.
Unfortunatelyit is also not
so cheap operation. Autonomous transaction should not see any changes done by parent transaction (because it can be
rollbackedafter commit of autonomous transaction). But there are catalog and relation caches inside backend, so we have
toclean this
caches before switching to ATX. It is quite expensive operation and so speed of execution of PL/pg-SQL function with
autonomoustransaction 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
ofmultiple transaction in one backend.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company