Message-ID: <4c1e9b47-f00c-8a60-d10b-a42995ccc5e5@enterprisedb.com>
Date: Fri, 11 Mar 2022 13:53:15 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.5.0
Subject: Re: logical decoding and replication of sequences
Content-Language: en-US
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 Petr Jelinek <petr.jelinek@enterprisedb.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <d045f3c2-6cfb-06d3-5540-e63c320df8bc@enterprisedb.com>
 <f5cc848c-633d-09a7-5d99-3c9cf95da7a7@enterprisedb.com>
 <90b4e402-3f07-14f4-deca-5eaca31f70a3@enterprisedb.com>
 <de7e9856-b49f-67bd-2ade-447f2d2cbc4b@enterprisedb.com>
 <22aef14d-0ea1-9044-c2e7-29f88762eaec@enterprisedb.com>
 <32540e16-fab6-f790-b99b-5a8fca8bde52@enterprisedb.com>
 <3B014BE4-EE61-4834-8FF6-937B43C46BC7@enterprisedb.com>
 <3d68b45a-ceb5-6824-3a39-f53411fd0c88@enterprisedb.com>
 <802f8b1b-a798-be52-c829-7f0fb9faafd5@enterprisedb.com>
 <983bf0c7-9757-fd13-3896-8452cf4c4c52@enterprisedb.com>
 <9211312a-6501-1caf-ee7b-c0d4814b6091@enterprisedb.com>
 <41ada110-bc7c-32a3-08bd-fb5023dd74ba@enterprisedb.com>
 <CAA4eK1Kp8sh=zQB=Jzpnnqu4xAsLM6ha7+bsBKNDroyJY81aCw@mail.gmail.com>
 <5bd18c76-c38b-86e1-78d3-73c44ac59a7f@enterprisedb.com>
 <93af17c3-a78d-4918-5f6b-76dfeb2d48bd@enterprisedb.com>
 <5709e125-a50c-e386-3dce-1d662b588ac7@enterprisedb.com>
 <CAA4eK1+sNHVWx4zF9jJa2son6mYX_gCQcURmAkbMGaEdcKJfTQ@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <CAA4eK1+sNHVWx4zF9jJa2son6mYX_gCQcURmAkbMGaEdcKJfTQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/4c1e9b47-f00c-8a60-d10b-a42995ccc5e5%40enterprisedb.com>
Precedence: bulk


On 3/11/22 12:34, Amit Kapila wrote:
> On Tue, Mar 8, 2022 at 11:59 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> On 3/7/22 22:25, Tomas Vondra wrote:
>>>>
>>>> Interesting. I can think of one reason that might cause this - we log
>>>> the first sequence increment after a checkpoint. So if a checkpoint
>>>> happens in an unfortunate place, there'll be an extra WAL record. On
>>>> slow / busy machines that's quite possible, I guess.
>>>>
>>>
>>> I've tweaked the checkpoint_interval to make checkpoints more aggressive
>>> (set it to 1s), and it seems my hunch was correct - it produces failures
>>> exactly like this one. The best fix probably is to just disable decoding
>>> of sequences in those tests that are not aimed at testing sequence decoding.
>>>
>>
>> I've pushed a fix for this, adding "include-sequences=0" to a couple
>> test_decoding tests, which were failing with concurrent checkpoints.
>>
>> Unfortunately, I realized we have a similar issue in the "sequences"
>> tests too :-( Imagine you do a series of sequence increments, e.g.
>>
>>   SELECT nextval('s') FROM generate_sequences(1,100);
>>
>> If there's a concurrent checkpoint, this may add an extra WAL record,
>> affecting the decoded output (and also the data stored in the sequence
>> relation itself). Not sure what to do about this ...
>>
> 
> I am also not sure what to do for it but maybe if in some way we can
> increase checkpoint timeout or other parameters for these tests then
> it would reduce the chances of such failures. The other idea could be
> to perform checkpoint before the start of tests to reduce the
> possibility of another checkpoint.
> 

Yeah, I had the same ideas, but I'm not sure I like any of them. I doubt
we want to make checkpoints extremely rare, and even if we do that it'll
still fail on slow machines (e.g. with valgrind, clobber cache etc.).


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company