On Thu, Dec 9, 2021 at 11:15 PM Amit Kapila <amit.kapil...@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. 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
