Logical Replication - "invalid ordering of speculative insertion changes"

From: "Joe Wildish" <joe(at)lateraljoin(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Logical Replication - "invalid ordering of speculative insertion changes"
Date: 2023-01-31 18:10:59
Message-ID: d95816a4-d0df-4c6f-a58a-21a3f8318826@app.fastmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

We have a logical replication publisher (13.7) and subscriber (14.6) where we are seeing the following error on the subscriber. IP address and publication name changed, otherwise verbatim:

2023-01-31 15:24:49 UTC:x.x.x.x(56276):super(at)pubdb:[1040971]: WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
2023-01-31 15:24:50 UTC::@:[1040975]: LOG: logical replication apply worker for subscription "pub" has started
2023-01-31 15:24:50 UTC::@:[1040975]: ERROR: could not receive data from WAL stream: ERROR: invalid ordering of speculative insertion changes

This error occurs during the initial set up of the subscription. We hit REFRESH, and then immediately it goes into this error state. It then repeats as it is retrying from here onwards and keeps hitting the same error.

My understanding is that the subscriber is performing some kind of reordering of the events contained within the WAL message. As it cannot then consume the message, it aborts, retries, and gets the same message and errors again. Looking in the source code it seems there is only one place where this error can be emitted --- reorderbuffer.c:2179. Moreover I can't tell if this is an error that I can be expected to recover from as a user.

We see this error only sometimes. Other times, we REFRESH the subscription and it makes progress as one would expect.

Can anyone advise on what we are doing wrong here?

-Joe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message jacktby@gmail.com 2023-02-01 05:40:46 How to create a new operator inpg for spec data type?
Previous Message Tom Lane 2023-01-31 17:15:29 Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches