Re: Non-superuser subscription owners

On Fri, Dec 10, 2021 at 7:39 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Thu, Dec 9, 2021 at 11:15 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > Yeah, to me also (b) sounds better than (a). However, a few points
> > that we might want to consider in that regard are as follows: 1.
> > locking the subscription for each transaction will add new blocking
> > areas considering we acquire AccessExclusiveLock to change any
> > property of subscription. But as Alter Subscription won't be that
> > frequent operation it might be acceptable.
>
> The problem isn't the cost of the locks taken by ALTER SUBSCRIPTION.
> It's the cost of locking and unlocking the relation for every
> transaction we apply.
>

This point is not clear to me as we are already locking the relation
while applying changes. I think here additional cost is to lock a
particular subscription as well in addition to the relation on which
we are going to perform apply. I agree that has a cost and that is why
I mentioned it as one of the points above and then also the
concurrency effect as you also noted could make this idea moot.

> Suppose it's a pgbench-type workload with a
> single UPDATE per transaction. You've just limited the maximum
> possible apply speed to about, I think, 30,000 transactions per second
> no matter how many parallel workers you use, because that's how fast
> the lock manager is (or was, unless newer hardware or newer PG
> versions have changed things in a way I don't know about). That seems
> like a poor idea. There's nothing wrong with noticing changes at the
> next transaction boundary, as long as we document it.
>

If we want to just document this then I think we should also keep in
mind that these could be N transactions as well if say tomorrow we
have N parallel apply workers applying the N transactions in parallel.
I think it might also be possible that RLS policies won't be applied
for initial table sync whereas those will be applied for later changes
even though the ownership has changed before both operations and one
of those happens to miss it. If that is possible, then it might be
better to avoid the same as it could appear inconsistent as mentioned
by Mark [1] as well. Now, it might be possible to avoid this by
implementation or we can say that we don't care about this or just
document it. But it seems to me that if we have some way to detect the
change of ownership at each operation level then no such possibilities
would arise.

The other alternative we discussed was to allow a change of ownership
on disabled subscriptions that way the apply behavior will always be
predictable.

There is clearly a merit in noticing the change of ownership at
transaction boundary but just wanted to consider other possibilities.
It could be that detecting at transaction-boundary is the best we can
do but I think there is no harm in considering other possibilities.

> So why would we
> incur a possibly-significant performance cost to provide a stricter
> guarantee?
>
> I bet users wouldn't even like this behavior. It would mean that if
> you are replicating a long-running transaction, an ALTER SUBSCRIPTION
> command might block for a long time until replication of that
> transaction completes.
>

Agreed and if we decide to lock the subscription during the initial
table sync phase then that could also take a long time for which again
users might not be happy.

[1] - https://www.postgresql.org/message-id/FE7D7024-6723-4ACB-82AB-94F6A194BE0D%40enterprisedb.com

-- 
With Regards,
Amit Kapila.