Message-ID: <3dd6f01e-918b-4bea-97dd-65cee886b9b8@wi3ck.info>
Date: Tue, 12 Nov 2024 09:45:07 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Commit Timestamp and LSN Inversion issue
To: Amit Kapila <amit.kapila16@gmail.com>, Tomas Vondra <tomas@vondra.me>
Cc: Andres Freund <andres@anarazel.de>,
 "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>,
 Aleksander Alekseev <aleksander@timescale.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>,
 shveta malik <shveta.malik@gmail.com>
References: <CAJpy0uBxEJnabEp3JS=n9X19Vx2ZK3k5AR7N0h-cSMtOwYV3fA@mail.gmail.com>
 <CAJ7c6TNZY3idBDoGOaoS_fF6vJuBMjnDhZC4i5V0J58s2SJ3AA@mail.gmail.com>
 <CAA4eK1LOmtMpmJAxsmN0yXJb-Q9vi6KThDYXdpVGL_WZW5ZgvQ@mail.gmail.com>
 <CAJ7c6TOcQPcydC7HW=xPEDN6_Ec7iSTP9j=1VTAeG=haguOQHw@mail.gmail.com>
 <CAA4eK1KhAxe6=VTxa6xEbc22_VVBLsVuvRH8Wxws8i50gH=eqw@mail.gmail.com>
 <9f7f4def-7ea8-4ad3-83e7-a8cc9d18c58a@wi3ck.info>
 <gkbrjxwyjbqckjbrkep3uy2mvrcq2mrfzprfmegbeiqxidtmbu@3cgs5iocjykk>
 <OS0PR01MB57162A227EC357482FEB4470945D2@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <xhcapr7lur35itqrbk4qdqn7rzfaqr3g6dvyxzfx6jeyknhvbv@h3wkgixuuuon>
 <CAA4eK1+ZdL=sC4sdUOQz+X9=7Z8Uxhkd0VqwcB9rgzXjvu=6yA@mail.gmail.com>
 <11ee3489-40ba-45d6-ba6a-7c3def725e84@vondra.me>
 <CAA4eK1+OZLh7vA1CQkoq0ba4J_P-8JFHnt0a_YC2xfB0t3+akA@mail.gmail.com>
Content-Language: en-US
From: Jan Wieck <jan@wi3ck.info>
In-Reply-To: <CAA4eK1+OZLh7vA1CQkoq0ba4J_P-8JFHnt0a_YC2xfB0t3+akA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/3dd6f01e-918b-4bea-97dd-65cee886b9b8%40wi3ck.info>
Precedence: bulk

On 11/11/24 23:22, Amit Kapila wrote:
> On Mon, Nov 11, 2024 at 9:05 PM Tomas Vondra <tomas@vondra.me> wrote:
>>
>> Alternatively, we could simply stop relying on the timestamps recorded
>> in the commit, and instead derive "monotonic" commit timestamps after
>> the fact. For example, we could track timestamps for some subset of
>> commits, and then approximate the rest using LSN.
>>
>> AFAIK the inversion can happen only for concurrent commits, and there's
>> can't be that many of those. So if we record the (LSN, timestamp) for
>> every 1MB of WAL, we approximate timestamps for commits in that 1MB by
>> linear approximation.
>>
>> Of course, there's a lot of questions and details to solve - e.g. how
>> often would it need to happen, when exactly would it happen, etc. And
>> also how would that integrate with the logical decoding - it's easy to
>> just get the timestamp from the WAL record, this would require more work
>> to actually calculate it. It's only a very rough idea.
>>
> 
> I think for logical decoding it would be probably easy because it
> reads all the WAL. So, it can remember the commit time of the previous
> commit, and if any future commit has a commit timestamp lower than
> that it can fix it by incrementing it. But outside logical decoding,
> it would be tricky because we may need a separate process to fix up
> commit timestamps by using linear approximation. IIUC, the bigger
> challenge is that such a solution would require us to read the WAL on
> a continuous basis and keep fixing commit timestamps or we need to
> read the extra WAL before using or relying on commit timestamp. This
> sounds to be a somewhat complex and costlier solution though the cost
> is outside the hot-code path but still, it matters as it leads to
> extra read I/O.
> 

I had originally experimented with adjusting the timestamps sent to the 
subscriber in the output plugin. This works for the most part but has 
two major flaws:

1) In case the system is restarted in a situation where it has some 
forward clock skew and the restart LSN is right in the middle of it, the 
output plugin has no knowledge of that and again emits backwards running 
timestamps.

2) The origin and the subscriber now have a different timestamp 
associated with the same transaction. That can lead to different 
behavior in conflict resolution when a third node gets involved.


Best Regards, Jan