Search Postgresql Archives

Re: "Missing" column in Postgres logical replication update message

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 8/3/22 08:09, Kevin Martin wrote:

Please reply to list also
Ccing list

I don't have answers to below at the moment, just getting thread back to list so others who might have answers can see it.

Thanks for the reply, Adrian.

We're looking at the messages in the replication slot using pg_logical_slot_peek_changes in the source db.

In those messages, we see some UPDATEs that do not include one of the columns in the table.

I'm not sure what statements are producing the updates to the table from the application, if that is what you are asking.  Does the update against the database have to include all columns in order for the replication log to have them all?

I thought that any UPDATE message in the replication logs is designed to include all values for all columns.  Is that correct?

The data is not showing up in the replica table.  In this case, though, the replication slot is being queried by Stitch to produce a copy in Snowflake.  That is probably somewhat irrelevant to the current question, though, since we appear to be seeing missing data in the replication slot messages on the source.

I'm on the receiving side of this issue and am working with my DBA on trying to figure it out, so I'm not fully versed in how all of this works.  I can try to get more information if it helps.  I have seen the output from the peek function, and there are clearly some UPDATE messages that have the column / values in question and some that do not.

On Wed, Aug 3, 2022 at 10:47 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:

    On 8/3/22 06:50, Kevin Martin wrote:
     > We have a replication slot set up on a database in Postgres 12.8.
    For
     > one of the tables, when a row is created, we see a series of records
     > come through - an INSERT followed by a handful of UPDATEs. All of
    these
     > messages in the WAL files show all columns, except for the last
    UPDATE
     > message, which is missing one of the columns. (The column in
    question is
     > a JSONB field, and the data is not overly large - less than 1000
     > chars.)  We think this is causing the data to come into our data
    lake
     > (via Stitch) with that column as NULL.

    See the messages where and/or how?

    What is the UPDATE command that is being given on primary?

    Is the data showing up in the replica table?

     >
     > My understanding is that all INSERT and UPDATE messages written
    to the
     > replication logs are supposed to include all columns. But I can't
    find a
     > definitive answer on that.
     >
     > So, my first question is: Is it normal / expected for UPDATE
    messages in
     > the replication logs to exclude any columns in the table?
     >
     > And, of course, if that is unexpected behavior, I'd love to hear any
     > thoughts on what may cause it.
     >
     > Thanks.
     >
     > -Kevin
     >
     >
     > FYI.  I have this question posted also on StackOverflow:
     >
    https://stackoverflow.com/questions/73222107/missing-column-in-postgres-logical-replication-update-message
    <https://stackoverflow.com/questions/73222107/missing-column-in-postgres-logical-replication-update-message>

     >
    <https://stackoverflow.com/questions/73222107/missing-column-in-postgres-logical-replication-update-message
    <https://stackoverflow.com/questions/73222107/missing-column-in-postgres-logical-replication-update-message>>
     >


-- Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>



--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux