Re: Non-superuser subscription owners

On Thu, Dec 9, 2021 at 11:15 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)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. 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. 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. I have a hard time understanding why anyone
would consider that an improvement.

--
Robert Haas
EDB: http://www.enterprisedb.com