Re: "Missing" column in Postgres logical replication update message
От | Adrian Klaver |
---|---|
Тема | Re: "Missing" column in Postgres logical replication update message |
Дата | |
Msg-id | 6713b9c2-8a57-26a9-94e1-636e838b033a@aklaver.com обсуждение исходный текст |
Ответ на | "Missing" column in Postgres logical replication update message (Kevin Martin <martinkd@gmail.com>) |
Ответы |
Re: "Missing" column in Postgres logical replication update message
(Kevin Martin <martinkd@gmail.com>)
Re: "Missing" column in Postgres logical replication update message (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
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@aklaver.com > <mailto:adrian.klaver@aklaver.com>> 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@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: Adrian KlaverДата:
Сообщение: Re: "Missing" column in Postgres logical replication update message