Message-ID: <7e5f41be-ec0f-b6f2-2e2a-60fd5648ee84@gmail.com>
Date: Sat, 6 May 2023 18:02:10 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.10.1
Subject: Re: Add two missing tests in 035_standby_logical_decoding.pl
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: vignesh C <vignesh21@gmail.com>, "Yu Shi (Fujitsu)"
 <shiy.fnst@fujitsu.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <2fefa454-5a70-2174-ddbf-4a0e41537139@gmail.com>
 <CAA4eK1+FhvUm6w-TYqXAAMNDPZcin05pyCu7Lzn-AnafFYYhBg@mail.gmail.com>
 <CALDaNm3GOW-7DfdqUiJADhEmQNQkFA+OonV4kvKkZcbK=0yr5w@mail.gmail.com>
 <CAA4eK1La2uVZdSNdp_AwpxVP85SvWHMT3ecfJgVYqPMjGVViyw@mail.gmail.com>
 <6711e200-0d5c-98c3-ba39-7d64a6936f7a@gmail.com>
 <CAA4eK1LQmnSroXZcR+w1JuHsOyQLdj=-QWUw8ExYem3kUpBE3A@mail.gmail.com>
 <a3f02689-fd8b-e290-2e62-9f7a5eb6f4d8@gmail.com>
 <CAA4eK1+-H9TBSG-qEd7aykdKHhRo3YCsQ_i6mquTdiL2zXVAPw@mail.gmail.com>
 <2c544b9f-419d-d18e-47be-f3d6f3d24e4e@gmail.com>
 <CAA4eK1KvDAgQC9kzHn41MJBmk-7X226-gBXVDFNubtr7rnSu6A@mail.gmail.com>
 <a155f702-a9b0-d473-0c44-06287af600b9@gmail.com>
 <CAA4eK1KhmUopfMDR+0ZkBSGerXet1XWGyguhE=ZwV5ZUUcQt7Q@mail.gmail.com>
 <3576f684-718d-ddd6-f4b0-f8ad64ad3330@gmail.com>
 <CAA4eK1+-uuH+sC42_=wA=xPyzCN+U3VNeW_d-BRGvt-pGkNT+Q@mail.gmail.com>
 <2894687f-141d-85a9-e8c3-2151e5b7e662@gmail.com>
 <CAA4eK1KD4pmEjqPkPjNdB5rTegJftzJjsZ3xuBndbPANQrueEw@mail.gmail.com>
Content-Language: en-US
From: "Drouvot, Bertrand" <bertranddrouvot.pg@gmail.com>
In-Reply-To: 
 <CAA4eK1KD4pmEjqPkPjNdB5rTegJftzJjsZ3xuBndbPANQrueEw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/7e5f41be-ec0f-b6f2-2e2a-60fd5648ee84%40gmail.com>
Precedence: bulk

Hi,

On 5/6/23 3:28 PM, Amit Kapila wrote:
> On Sat, May 6, 2023 at 1:52 PM Drouvot, Bertrand
> <bertranddrouvot.pg@gmail.com> wrote:
>>
>> There is 2 runs with this extra info in place:
>>
>> A successful one: https://cirrus-ci.com/task/6528745436086272
>> A failed one: https://cirrus-ci.com/task/4558139312308224
>>
> 
> Thanks, I think I got some clue as to why this test is failing
> randomly. 

Great, thanks!

> Observations:
> ============
> 1. In the failed run, the KeepLogSeg(), reduced the _logSegNo to 3
> which is the reason for the failure because now the standby won't be
> able to remove/recycle the WAL file corresponding to segment number 3
> which the test was expecting.

Agree.

> 2.  We didn't expect the KeepLogSeg() to reduce the _logSegNo because
> all logical slots were invalidated. However, I think we forgot that
> both standby and primary have physical slots which might also
> influence the XLogGetReplicationSlotMinimumLSN() calculation in
> KeepLogSeg().

Oh right...

> Next steps:
> =========
> 1. The first thing is we should verify this theory by adding some LOG
> in KeepLogSeg() to see if the _logSegNo is reduced due to the value
> returned by XLogGetReplicationSlotMinimumLSN().

Yeah, will do that early next week.

> 2. The reason for the required file not being removed in the primary
> is also that it has a physical slot which prevents the file removal.

Yeah, agree. But this one is not an issue as we are not
checking for the WAL file removal on the primary, do you agree?

> 3. If the above theory is correct then I see a few possibilities to
> fix this test (a) somehow ensure that restart_lsn of the physical slot
> on standby is advanced up to the point where we can safely remove the
> required files; (b) just create a separate test case by initializing a
> fresh node for primary and standby where we only have logical slots on
> standby. This will be a bit costly but probably less risky. (c) any
> better ideas?
> 

(c): Since, I think, the physical slot on the primary is not a concern for
the reason mentioned above, then instead of (b):

What about postponing the physical slot creation on the standby and the
cascading standby node initialization after this test?

That way, this test would be done without a physical slot on the standby and
we could also get rid of the "Wait for the cascading standby to catchup before
removing the WAL file(s)" part.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com