Message-ID: <410c39ec-efd2-cb5f-8590-33d4fc0302d3@dunslane.net>
Date: Fri, 10 Dec 2021 10:20:02 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.2.0
Subject: Re: Non-superuser subscription owners
Content-Language: en-US
To: Robert Haas <robertmhaas@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, Jeff Davis
 <pgsql@j-davis.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <9DFC88D3-1300-4DE8-ACBC-4CEF84399A53@enterprisedb.com>
 <1BB0A553-3FE9-4A91-A975-6F9D8C157FBD@enterprisedb.com>
 <CAA4eK1LBjtqHH2h4TO7FHG=FJRfygfkeEb7AFj-dh6rqg3xqUA@mail.gmail.com>
 <CC5DAC9C-F767-4256-BFEF-32D5A566D254@enterprisedb.com>
 <7C62876F-5D19-4B5C-96AC-5590B10A49B2@enterprisedb.com>
 <66f55a6b9ff7bdd6bfd0d05e8fd8351690e69e23.camel@j-davis.com>
 <CAA4eK1KnEptxtJTzo9LNb2VgRBYcBWxY+NSx2hJR8iUM8JXktw@mail.gmail.com>
 <18b267939a9d1ae27e9b3643d53395f4e29ea6ce.camel@j-davis.com>
 <CAA4eK1K1=DbXCNfA32urxZga6YxeiCvcTHvJu-+BtLCtVgqJ2w@mail.gmail.com>
 <785e8b8ab06f123cf754862557bd3171aeba4d65.camel@j-davis.com>
 <CAA4eK1JJsBzJsStA_LYrJu0WmOG+tWXcS9G4mY1G4CsQVBcaQw@mail.gmail.com>
 <CA+Tgmob7OUFa0-1NS3tDrbyR+VmvmcWWw8qd61=aDQ7nn6suyA@mail.gmail.com>
 <7C8B9FA7-FECA-4C1C-9D0D-FCFE68C93EC3@enterprisedb.com>
 <CAA4eK1L_ZCFHS_Dg1FG8vP2FH=MFOUi7Of-oH6qD4QRZH4L3RA@mail.gmail.com>
 <CA+TgmoZzoLzcbrP9=Y3E=6xfyp=uH8CobowhuDV8fLbisQtbyA@mail.gmail.com>
From: Andrew Dunstan <andrew@dunslane.net>
In-Reply-To: 
 <CA+TgmoZzoLzcbrP9=Y3E=6xfyp=uH8CobowhuDV8fLbisQtbyA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/410c39ec-efd2-cb5f-8590-33d4fc0302d3%40dunslane.net>
Precedence: bulk


On 12/10/21 09:09, Robert Haas 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. 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.
>


+1


I think noticing changes at the transaction boundary is perfectly
acceptable.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com