Message-ID: <c044630b-f99b-33a0-b678-5dd55d6cc10a@enterprisedb.com>
Date: Wed, 15 Dec 2021 14:58:39 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.2.0
Subject: Re: logical decoding and replication of sequences
Content-Language: en-US
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Petr Jelinek <petr.jelinek@enterprisedb.com>
References: <d045f3c2-6cfb-06d3-5540-e63c320df8bc@enterprisedb.com>
 <CALDaNm3gZZjBKWs7gufQep9ikJMtoY9Xv4CsYTpYRaT720RpbQ@mail.gmail.com>
 <3005299b-267d-8e4a-68a3-abdad3e96aba@enterprisedb.com>
 <3d6df331-5532-6848-eb45-344b265e0238@enterprisedb.com>
 <482c10c1-b110-819f-b0e7-17549fa2f0dc@enterprisedb.com>
 <12a9ecd6-4edb-a367-33da-4e2d03b0aace@enterprisedb.com>
 <CAMT0RQQeDR51xs8zTa25YpfKB1B34nS-Q4hhsRPznVsjMB_P1w@mail.gmail.com>
 <bdc688ef-9339-8234-d786-409f1e603711@enterprisedb.com>
 <e6ef8862-31e4-1582-9274-f8f57b7f1cab@enterprisedb.com>
 <2cd38bab-c874-8e0b-98e7-d9abaaf9806a@enterprisedb.com>
 <c0bd317a-fef5-9ef9-66be-a10eae686b96@enterprisedb.com>
 <118b249f-e4ef-2faf-8e25-dd25236dc964@enterprisedb.com>
 <d0e077e9-6440-fcf5-6b8a-b2a32be408e1@enterprisedb.com>
 <ae3cab67-c31e-b527-dd73-08f196999ad4@enterprisedb.com>
 <CAA4eK1J5R=3H1kdt4B0-K6A=7PoELdOW23HpYaTXQfE0y4p6KQ@mail.gmail.com>
 <f5cc848c-633d-09a7-5d99-3c9cf95da7a7@enterprisedb.com>
In-Reply-To: <f5cc848c-633d-09a7-5d99-3c9cf95da7a7@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/c044630b-f99b-33a0-b678-5dd55d6cc10a%40enterprisedb.com>
Precedence: bulk


On 12/15/21 14:51, Tomas Vondra wrote:
> On 12/15/21 14:20, Amit Kapila wrote:
>> On Tue, Dec 14, 2021 at 7:02 AM Tomas Vondra
>> <tomas.vondra@enterprisedb.com> wrote:
>>>
>>> Hi,
>>>
>>> here's an updated version of the patches, dealing with almost all of the
>>> issues (at least in the 0001 and 0002 parts). The main changes:
>>>
>>> 1) I've removed the  'created' flag from fill_seq_with_data, as
>>> discussed. I don't think it's needed by any of the parts (not even 0003,
>>> AFAICS). We still need it in xl_seq_rec, though.
>>>
>>> 2) GetCurrentTransactionId() added to sequence.c are called only with
>>> wal_level=logical, to minimize the overhead.
>>>
>>>
>>> There's still one remaining problem, that I already explained in [1].
>>> The problem is that with this:
>>>
>>>   BEGIN;
>>>   SELECT nextval('s') FROM generate_series(1,100);
>>>   ROLLBACK;
>>>
>>>
>>> The root cause is that pg_current_wal_lsn() uses the LogwrtResult.Write,
>>> which is updated by XLogFlush() - but only in RecordTransactionCommit.
>>> Which makes sense, because only the committed stuff is "visible".
>>>
>>> But the non-transactional behavior of sequence decoding disagrees with
>>> this, because now some of the changes from aborted transactions may be
>>> replicated. Which means the wait_for_catchup() ends up not waiting for
>>> the sequence change to be replicated. This is an issue for tests in
>>> patch 0003, at least.
>>>
>>> My concern is this actually affects other places waiting for things
>>> getting replicated :-/
>>>
>>
>> By any chance, will this impact synchronous replication as well which
>> waits for commits to be replicated?
>>
> 
> Physical or logical replication? Physical is certainly not replicated.
> 
> For logical replication, it's more complicated.
> 

Apologies, sent too early ... I think it's more complicated for logical
sync replication, because of a scenario like this:

  BEGIN;
  SELECT nextval('s') FROM generate_series(1,100);  <-- writes WAL
  ROLLBACK;

  SELECT nextval('s');

The first transaction advances the sequence enough to generate a WAL,
which we do every 32 values. But it's rolled back, so it does not update
LogwrtResult.Write, because that happens only at commit.

And then the nextval() generates a value from the sequence without
generating WAL, so it doesn't update the LSN either (IIRC). That'd mean
a sync replication may not wait for this change to reach the subscriber.


regards

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