Getting an overwhelming amount of transactions with empty change sets despite using add-tables #106

bahaa · 2019-02-14T10:40:51Z

We are using Replication Streaming and wal2json to replicate some tables from AWS RDS PostgreSQL 10.6 to other application. We selectively selecting some tables using add-tables parameter. Everything works great except that sometimes we receive huge amount of transactions with empty changeset that overwhelm the server and slow down syncing real changes we care about.

We don't know what causes these huge amount of empty changesets, but I can replicate getting a single empty changeset using either REFRESH MATERIALIZED VIEW or TRUNCATE TABLE on any table even it it's not specified on add-tables filter.

The text was updated successfully, but these errors were encountered:

johanmickos · 2019-02-19T17:46:29Z

I can confirm that we're also seeing empty change sets when using add-tables and filter-tables.

pluginOpts := "\"include-xids\" '1', \"include-timestamp\" '1', \"filter-tables\" '*.test'"
err = d.conn.StartReplication(d.config.SlotName, startLSN, -1, pluginOpts)

Inserting records into test triggers a WAL change message in my consumer with {"change": [], "timestamp": "...", "xid": 123}.

eulerto · 2019-02-19T17:51:07Z

Please provide a test case.

johanmickos · 2019-02-19T18:04:54Z

I'll see if I can draft one up.

It seems like this is expected behavior, though. The callbacks for BEGIN and COMMIT write empty changesets regardless of the internal filtering happening in pg_decode_change.

eulerto · 2019-02-19T18:13:36Z

No, it doesn't.

euler=# SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
 ?column? 
----------
 init
(1 registro)

euler=# begin;
BEGIN
euler=# commit;
COMMIT
euler=# SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
 data 
------
(0 registro)
euler=# SELECT 'stop' FROM pg_drop_replication_slot('test_slot');
 ?column? 
----------
 stop
(1 registro)

It seems you have some DDL such as:

euler=# SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
 ?column? 
----------
 init
(1 registro)

euler=# begin;
BEGIN
euler=# create table foo (a int);
CREATE TABLE
euler=# commit;
COMMIT
euler=# SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL);
     data      
---------------
 {"change":[]}
(1 registro)

euler=# SELECT 'stop' FROM pg_drop_replication_slot('test_slot');
 ?column? 
----------
 stop
(1 registro)

johanmickos · 2019-02-19T18:52:59Z

I meant that the following is expected behavior:

CREATE DATABASE tests;
\connect tests;
CREATE TABLE users(
    id integer primary key,
    name varchar
);
CREATE TABLE something(
    id integer primary key,
    name varchar
);
SELECT 'init' FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

INSERT INTO users(id, name) VALUES(1, 'John Doe');
INSERT INTO something(id, name) VALUES(99, 'something');

SELECT  data FROM pg_logical_slot_peek_changes('test_slot',  NULL, NULL, 'pretty-print', '1', 'add-tables', 'public.something');

                                   data
--------------------------------------------------------------------------
 {                                                                       +
         "change": [                                                     +
         ]                                                               +
 }
 {                                                                       +
         "change": [                                                     +
                 {                                                       +
                         "kind": "insert",                               +
                         "schema": "public",                             +
                         "table": "something",                           +
                         "columnnames": ["id", "name"],                  +
                         "columntypes": ["integer", "character varying"],+
                         "columnvalues": [99, "something"]               +
                 }                                                       +
         ]                                                               +
 }
(2 rows)

The first row of the peek changes command is an empty change set, which is the expected result.

pg_decode_begin_txn and pg_decode_commit_txn (i.e. the callbacks for beginning and committing transactions) are responsible for outputting {"change": []} regardless of the filters.

bahaa · 2019-02-20T10:40:12Z

So far, here are the cases that generate empty changesets:

TRUNCATE TABLE.
REFRESH MATERIALIZED VIEW.
DDL statements.

Yet, I don't know what causes huge amount of these empty changesets. The operations above generate only one or two events.

bahaa · 2019-02-26T15:16:22Z

Running VACUUM FULL generates a huge amount of empty change sets. This happens also with the test_decoding plugin, but the plugin has an option to exclude-empty-xacts that works just fine.

I think wal2json should have a similar option, or at least when using add-tables, the plugin should skip empty transactions since they are not related to the mentioned tables.

Here's a test case:

SELECT * FROM pg_create_logical_replication_slot('wal2json_slot', 'wal2json');

SELECT * FROM pg_logical_slot_get_changes('wal2json_slot', NULL, NULL, 'add-tables', '*.users');
lsn | xid | data
-----+-----+------
(0 rows)

VACUUM FULL;

SELECT * FROM pg_logical_slot_get_changes('wal2json_slot', NULL, NULL, 'add-tables', '*.users');
    lsn     |  xid  |     data
-------------+-------+---------------
 11/E81E3860 | 79420 | {"change":[]}
 11/E81F9AE0 | 79421 | {"change":[]}
 11/E82113D0 | 79422 | {"change":[]}
-- thousands of rows.

joekohlsdorf · 2019-08-02T16:13:02Z

Here is another case with table partitioning:

CREATE TABLE normal_table (id integer);
CREATE TABLE partitioned_table (id integer, type text, PRIMARY KEY (id,type)) PARTITION BY LIST(type);
CREATE TABLE partitioned_table_part PARTITION OF partitioned_table FOR VALUES IN ('test');

SELECT * FROM pg_create_logical_replication_slot('wal2json_slot', 'wal2json');
SELECT * FROM pg_logical_slot_get_changes('wal2json_slot', NULL, NULL, 'add-tables', '*.normal_table');

lsn | xid | data
-----+-----+------
(0 rows)

INSERT INTO partitioned_table VALUES (1,'test');
SELECT * FROM pg_logical_slot_get_changes('wal2json_slot', NULL, NULL, 'add-tables', '*.normal_table');

lsn | xid | data
-----+-----+------
0/503A7768	8396	{"change":[]}

holvianssi · 2023-04-25T17:56:14Z

PostgreSQL 15 has a fix to this. If a transaction doesn't produce any records, then begin and commit messages will be skipped. See https://www.postgresql.fastware.com/blog/how-postgresql-15-improved-communication-in-logical-replication

eulerto added the feature label May 8, 2019

eulerto added this to the v1.1 milestone May 8, 2019

bahaa linked a pull request Jun 28, 2019 that will close this issue

Resolves #106: Implementing skip-empty-xacts. #120

Open

mmd-osm mentioned this issue Jul 15, 2020

Exploring wal2json openstreetmap/osmdbt#20

Closed

adrijshikhar linked a pull request Aug 2, 2022 that will close this issue

feat: Adds 'skip-empty-xacts' support #248

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Getting an overwhelming amount of transactions with empty change sets despite using add-tables #106

Getting an overwhelming amount of transactions with empty change sets despite using add-tables #106

bahaa commented Feb 14, 2019

johanmickos commented Feb 19, 2019

eulerto commented Feb 19, 2019

johanmickos commented Feb 19, 2019

eulerto commented Feb 19, 2019

johanmickos commented Feb 19, 2019 •

edited

bahaa commented Feb 20, 2019

bahaa commented Feb 26, 2019

joekohlsdorf commented Aug 2, 2019 •

edited

holvianssi commented Apr 25, 2023

Getting an overwhelming amount of transactions with empty change sets despite using add-tables #106

Getting an overwhelming amount of transactions with empty change sets despite using add-tables #106

Comments

bahaa commented Feb 14, 2019

johanmickos commented Feb 19, 2019

eulerto commented Feb 19, 2019

johanmickos commented Feb 19, 2019

eulerto commented Feb 19, 2019

johanmickos commented Feb 19, 2019 • edited

bahaa commented Feb 20, 2019

So far, here are the cases that generate empty changesets:

bahaa commented Feb 26, 2019

Here's a test case:

joekohlsdorf commented Aug 2, 2019 • edited

holvianssi commented Apr 25, 2023

johanmickos commented Feb 19, 2019 •

edited

joekohlsdorf commented Aug 2, 2019 •

edited