Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-965

PostgreSQL replication slots not updated in transactions

    Details

    • Steps to Reproduce:
      Hide

      1. Use the attached docker-compose file to bring up an environment (everything comes from debezium basic examples)
      2. Run the attached script that:

      • Creates a couple of tables ('data' and 'datahistory') in the postgres database
      • Registers a connector for the table 'data'
      • Periodically inserts rows in a transaction to 'data' and 'datahistory' (in that order)

      (keep it running for some long time)

      3. Check pg_replication_slots periodically. The replication slot for the connector never gets updated.

      4. To get the replication slot updated and confirm that the problem has to do with how changes in transactions are handled, you can perform some manual changes on the table 'data' (around every minute, the offsets are committed by the connector). Or just remove the transaction in the script. Or, even better, just change the order in which the tables are modified. If 'data' is changed at the end of the transaction, then it behaves.

      Show
      1. Use the attached docker-compose file to bring up an environment (everything comes from debezium basic examples) 2. Run the attached script that: Creates a couple of tables ('data' and 'datahistory') in the postgres database Registers a connector for the table 'data' Periodically inserts rows in a transaction to 'data' and 'datahistory' (in that order) (keep it running for some long time) 3. Check pg_replication_slots periodically. The replication slot for the connector never gets updated. 4. To get the replication slot updated and confirm that the problem has to do with how changes in transactions are handled, you can perform some manual changes on the table 'data' (around every minute, the offsets are committed by the connector). Or just remove the transaction in the script. Or, even better, just change the order in which the tables are modified. If 'data' is changed at the end of the transaction, then it behaves.

      Description

      Extended summary:

      When:

      • You setup a connector with one single table whitelisted
      • The table is always (and only) modified in a transaction
      • The transaction performs some other operations

      Then

      • The replication slot will never be updated

      In the context of a transaction, the connector can receive multiple change events. Only when the last change event of the transaction is processed the lastProcessedLsn value will be updated.

      https://github.com/debezium/debezium/blob/v0.8.3.Final/debezium-connector-postgres/src/main/java/io/debezium/connector/postgresql/PostgresConnectorTask.java#L151

      But if the last change performed by the transaction affects a table that is not whitelisted then it won't even be processed, resulting in the same (old) lsn being commited once and again:

      https://github.com/debezium/debezium/blob/v0.8.3.Final/debezium-connector-postgres/src/main/java/io/debezium/connector/postgresql/RecordsStreamProducer.java#L238

      (tableSchema will be null if the table is filtered out: https://github.com/debezium/debezium/blob/v0.8.3.Final/debezium-connector-postgres/src/main/java/io/debezium/connector/postgresql/RecordsStreamProducer.java#L498)

      It took me a while to follow what was going on. I can provide some further explanations on why exactly this is happening but I hope it will be clear enough for anyone familiar with the code.

      I tried and failed to fix it myself and do a PR. I didn't find an easy way to do it without making major design changes (or minor super dirty hacks). But I'm totally new to this, so maybe someone else can fix it neatly in five minutes.

      Might (or might not) be related to:
      https://issues.jboss.org/projects/DBZ/issues/DBZ-926
      and
      https://issues.jboss.org/projects/DBZ/issues/DBZ-892

        Gliffy Diagrams

          Attachments

          1. correct-script.sh
            1 kB
          2. docker-compose.yml
            0.8 kB
          3. script.sh
            1 kB

            Activity

              People

              • Assignee:
                jpechanec Jiri Pechanec
                Reporter:
                miguel.angel.martin Miguel Angel Martin Mendez
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: