Обсуждение: CDC/ETL system on top of logical replication with pgoutput, custom client

On Mon, Jul 31, 2023 at 3:06 PM José Neves <rafaneves3@msn.com> wrote:
>
> Hi there, hope to find you well.
>
> I'm attempting to develop a CDC on top of Postgres, currently using 12, the last minor, with a custom client, and I'm
runninginto issues with data loss caused by out-of-order logical replication messages. 
>
> The problem is as follows: postgres streams A, B, D, G, K, I, P logical replication events, upon exit signal we stop
consumingnew events at LSN K, and we wait 30s for out-of-order events. Let's say that we only got A, (and K ofc) so in
thefollowing 30s, we get B, D, however, for whatever reason, G never arrived. As with pgoutput-based logical
replicationwe have no way to calculate the next LSN, we have no idea that G was missing, so we assumed that it all
arrived,committing K to postgres slot and shutdown. In the next run, our worker will start receiving data from K
forward,and G is lost forever... 
> Meanwhile postgres moves forward with archiving and we can't go back to check if we lost anything. And even if we
could,would be extremely inefficient. 
>
> In sum, the issue comes from the fact that postgres will stream events with unordered LSNs on high transactional
systems,and that pgoutput doesn't have access to enough information to calculate the next or last LSN, so we have no
wayto check if we receive all the data that we are supposed to receive, risking committing an offset that we shouldn't
aswe didn't receive yet preceding data. 
>

As per my understanding, we stream the data in the commit LSN order
and for a particular transaction, all the changes are per their LSN
order. Now, it is possible that for a parallel transaction, we send
some changes from a prior LSN after sending the commit of another
transaction. Say we have changes as follows:

T-1
change1 LSN1-1000
change2 LSN2- 2000
commit   LSN3- 3000

T-2
change1 LSN1-500
change2 LSN2-1500
commit   LSN3-4000

In such a case, all the changes including the commit of T-1 are sent
and then all the changes including the commit of T-2 are sent. So, one
can say that some of the changes from T-2 from prior LSN arrived after
T-1's commit but that shouldn't be a problem because if restart
happens after we received partial T-2, we should receive the entire
T-2.

It is possible that you are seeing something else but if so then
please try to share a more concrete example.

--
With Regards,
Amit Kapila.

I'm attempting to develop a CDC on top of Postgres, currently using 12, the last minor, with a custom client, and I'm running into issues with data loss caused by out-of-order logical replication messages.

The problem is as follows: postgres streams A, B, D, G, K, I, P logical replication events, upon exit signal we stop consuming new events at LSN K, and we wait 30s for out-of-order events. Let's say that we only got A, (and K ofc) so in the following 30s, we get B, D, however, for whatever reason, G never arrived. As with pgoutput-based logical replication we have no way to calculate the next LSN, we have no idea that G was missing, so we assumed that it all arrived, committing K to postgres slot and shutdown. In the next run, our worker will start receiving data from K forward, and G is lost forever...

Meanwhile postgres moves forward with archiving and we can't go back to check if we lost anything. And even if we could, would be extremely inefficient.

In sum, the issue comes from the fact that postgres will stream events with unordered LSNs on high transactional systems, and that pgoutput doesn't have access to enough information to calculate the next or last LSN, so we have no way to check if we receive all the data that we are supposed to receive, risking committing an offset that we shouldn't as we didn't receive yet preceding data.

It seems very either to me that none of the open-source CDC projects that I looked into care about this. They always assume that the next LSN received is... well the next one, and commit that one, so upon restart, they are vulnerable to the same issue. So... either I'm missing something... or we have a generalized assumption causing data loss under certain conditions all over.

Hi,

On 2023-07-31 14:16:22 +0000, José Neves wrote:
> Hi Amit, thanks for the reply.
>
> In our worker (custom pg replication client), we care only about INSERT,
> UPDATE, and DELETE operations, which - sure - may be part of the issue.

That seems likely. Postgres streams out changes in commit order, not in order
of the changes having been made (that'd not work due to rollbacks etc). If you
just disregard transactions entirely, you'll get something bogus after
retries.

You don't need to store the details for each commit in the target system, just
up to which LSN you have processed *commit records*. E.g. if you have received
and safely stored up to commit 0/1000, you need to remember that.


Are you using the 'streaming' mode / option to pgoutput?


> 1. We have no way to match LSN operations with the respective commit, as
> they have unordered offsets.

Not sure what you mean with "unordered offsets"?


> Assuming that all of them were received in order, we would commit all data with the commit message LSN4-4000 as other
eventswould match the transaction start and end LSN interval of it.
 

Logical decoding sends out changes in a deterministic order and you won't see
out of order data when using TCP (the entire connection can obviously fail
though).

Andres

Hi,

On 2023-07-31 21:25:06 +0000, José Neves wrote:
> Ok, if I understood you correctly, I start to see where my logic is faulty. Just to make sure that I got it right,
takingthe following example again:
 
>
> T-1
> INSERT LSN1-1000
> UPDATE LSN2-2000
> UPDATE LSN3-3000
> COMMIT  LSN4-4000
>
> T-2
> INSERT LSN1-500
> UPDATE LSN2-1500
> UPDATE LSN3-2500
> COMMIT  LSN4-5500
>
> Where data will arrive in this order:
>
> INSERT LSN1-500
> INSERT LSN1-1000
> UPDATE LSN2-1500
> UPDATE LSN2-2000
> UPDATE LSN3-2500
> UPDATE LSN3-3000
> COMMIT  LSN4-4000
> COMMIT  LSN4-5500

No, they won't arrive in that order. They will arive as

BEGIN
INSERT LSN1-1000
UPDATE LSN2-2000
UPDATE LSN3-3000
COMMIT  LSN4-4000
BEGIN
INSERT LSN1-500
UPDATE LSN2-1500
UPDATE LSN3-2500
COMMIT  LSN4-5500

Because T1 committed before T2. Changes are only streamed out at commit /
prepare transaction (*). Within a transaction, they however *will* be ordered
by LSN.

(*) Unless you use streaming mode, in which case it'll all be more
complicated, as you'll also receive changes for transactions that might still
abort.


> You are saying that the LSN3-3000 will never be missing, either the entire
> connection will fail at that point, or all should be received in the
> expected order (which is different from the "numeric order" of LSNs).

I'm not quite sure what you mean with the "different from the numeric order"
bit...


> If the connection is down, upon restart, I will receive the entire T-1
> transaction again (well, all example data again).

Yes, unless you already acknowledged receipt up to LSN4-4000 and/or are only
asking for newer transactions when reconnecting.


> In addition to that, if I commit LSN4-4000, even tho that LSN has a "bigger
> numeric value" than the ones representing INSERT and UPDATE events on T-2, I
> will be receiving the entire T-2 transaction again, as the LSN4-5500 is
> still uncommitted.

I don't quite know what you mean with "commit LSN4-4000" here.


> This makes sense to me, but just to be extra clear, I will never receive a
> transaction commit before receiving all other events for that transaction.

Correct.

Greetings,

Andres Freund

On Sun, Aug 6, 2023 at 7:54 PM José Neves <rafaneves3@msn.com> wrote:
>
> A follow-up on this. Indeed, a new commit-based approach solved my missing data issues.
> But, getting back to the previous examples, how are server times expected to be logged for the xlogs containing these
records?
>

I think it should be based on commit_time because as far as I see we
can only get that on the client.

--
With Regards,
Amit Kapila.

On Mon, Aug 7, 2023 at 1:46 PM José Neves <rafaneves3@msn.com> wrote:
>
> Humm, that's... challenging. I faced some issues after "the fix" because I had a couple of transactions with 25k
updates,and I had to split it to be able to push to our event messaging system, as our max message size is 10MB.
Relyingon commit time would mean that all transaction operations will have the same timestamp. If something goes wrong
whilemy worker is pushing that transaction data chunks, I will duplicate some data in the next run, so... this wouldn't
allowme to deal with data duplication. 
> Is there any other way that you see to deal with it?
>
> Right now I only see an option, which is to store all processed LSNs on the other side of the ETL. I'm trying to
avoidthat overhead. 
>

Sorry, I don't understand your system enough to give you suggestions
but if you have any questions related to how logical replication work
then I might be able to help.

--
With Regards,
Amit Kapila.

On Tue, Oct 24, 2023 at 8:53 PM José Neves <rafaneves3@msn.com> wrote:
>
> Hi there, hope to find you well.
>
> I have a follow-up question to this already long thread.
>
> Upon deploying my PostgreSQL logical replication fed application on a stale database, I ended up running out of
space,as the replication slot is being held back till the next time that we receive a data-changing event, and we
advanceto that new LSN offset. 
> I think that the solution for this is to advance our LSN offset every time a keep-alive message is received ('k' //
107).
> My doubt is, can the keep-alive messages be received in between open transaction events? I think not, but I would
liketo get your input to be extra sure as if this happens, and I commit that offset, I may introduce again faulty logic
leadingto data loss. 
>
> In sum, something like this wouldn't happen:
> BEGIN LSN001
> INSERT LSN002
> KEEP LIVE LSN003
> UPDATE LSN004
> COMMIT LSN005
>

If the downstream acknowledges receipt of LSN003 and saves it locally
and crashes, upon restart the upstream will resend all the
transactions that committed after LSN003 including the one ended at
LSN005. So this is safe.

--
Best Wishes,
Ashutosh

On Wed, Oct 25, 2023 at 4:23 PM José Neves <rafaneves3@msn.com> wrote:
>
> Ok, I see. In that situation is safe indeed, as the offset is lower than the current transaction commit.
> But I think that I asked the wrong question. I guess that the right question is: Can we receive a keep-alive message
withan LSN offset bigger than the commit of the open or following transactions? 
> Something like:
>
> BEGIN LSN001
> INSERT LSN002
> KEEP LIVE LSN006
> UPDATE LSN004
> COMMIT LSN005
>
> Or:
>
> KEEP LIVE LSN006
> BEGIN LSN001
> INSERT LSN002
> UPDATE LSN004
> COMMIT LSN005
> KEEP LIVE LSN007
>

AFAIU the code in walsender this isn't possible. Keep alive sends the
LSN of the last WAL record it read (sentPtr). Upon reading a commit
WAL record, the whole transaction is decoded. Till that point sentPtr
is not updated.

Please take a look at XLogSendLogical(void) and the places where
WalSndKeepaliveIfNecessary() is called.

--
Best Wishes,
Ashutosh Bapat