Message-ID: <5de939c3-830e-59fd-5280-b8c52a1ad410@enterprisedb.com>
Date: Tue, 23 Nov 2021 14:36:17 +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
To: Andres Freund <andres@anarazel.de>, Hannu Krosing <hannuk@google.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 vignesh C <vignesh21@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Petr Jelinek <petr.jelinek@enterprisedb.com>
References: <d045f3c2-6cfb-06d3-5540-e63c320df8bc@enterprisedb.com>
 <377c6fda-c3eb-7a69-e981-c77ae459c5db@enterprisedb.com>
 <bb890134-a566-efbc-f418-cd0f92eb4b70@enterprisedb.com>
 <5105c3ad-d726-fc22-1d09-514b6852a15e@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>
 <20211123010114.5jdp35p7p5cuzwsl@alap3.anarazel.de>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <20211123010114.5jdp35p7p5cuzwsl@alap3.anarazel.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/5de939c3-830e-59fd-5280-b8c52a1ad410%40enterprisedb.com>
Precedence: bulk


On 11/23/21 02:01, Andres Freund wrote:
> Hi,
> 
> On 2021-09-25 22:05:43 +0200, Hannu Krosing wrote:
>> If our aim is just to make sure that all user-visible data in
>> *transactional* tables is consistent with sequence state then  one
>> very much simplified approach to this could be to track the results of
>> nextval() calls in a transaction at COMMIT put the latest sequence
>> value in WAL (or just track the sequences affected and put the latest
>> sequence state in WAL at commit which needs extra read of sequence but
>> protects against race conditions with parallel transactions which get
>> rolled back later)
> 
> I think this is a bad idea. It's architecturally more complicated and prevents
> use cases because sequence values aren't guaranteed to be as new as on the
> original system. You'd need to track all sequence use somehow *even if there
> is no relevant WAL generated* in a transaction. There's simply no evidence of
> sequence use in a transaction if that transaction uses a previously logged
> sequence value.
> 

Not quite. We already have a cache of all sequences used by a session 
(see seqhashtab in sequence.c), and it's not that hard to extend it to 
per-transaction tracking. That's what the last two versions do, mostly.

But there are various issues with that approach, described in my last 
message(s).


regards

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