BUG #14785: Logical replication does not work after adding a column. Bug?

From: yxq(at)o2(dot)pl
To: pgsql-bugs(at)postgresql(dot)org
Cc: yxq(at)o2(dot)pl
Subject: BUG #14785: Logical replication does not work after adding a column. Bug?
Date: 2017-08-20 19:17:20
Message-ID: 20170820191720.1454.19868@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14785
Logged by: yxq
Email address: yxq(at)o2(dot)pl
PostgreSQL version: 10beta3
Operating system: Linux Debian Stretch 64-bit
Description:

Server Master:
port = 5432
wal_level = logical

Server Slave:
port = 5433
wal_level = logical

***** Steps to reproduce:

* Master:

CREATE TABLE users
(
id SERIAL PRIMARY KEY,
firstname VARCHAR(100) NOT NULL,
lastname VARCHAR(100) NOT NULL
);

INSERT INTO users(firstname,lastname) VALUES('fu1','lu1');
INSERT INTO users(firstname,lastname) VALUES('fu2','lu2');

SELECT * FROM users;

result:

id | firstname | lastname
----+-----------+----------
1 | fu1 | lu1
2 | fu2 | lu2
(2 rows)

CREATE PUBLICATION mypub FOR ALL TABLES;

*** Slave:

CREATE TABLE users
(
id SERIAL PRIMARY KEY,
firstname VARCHAR(100) NOT NULL,
lastname VARCHAR(100) NOT NULL
);

CREATE SUBSCRIPTION mysub
CONNECTION 'port=5432 dbname=postgres'
PUBLICATION mypub;

SELECT * FROM users;

result:

id | firstname | lastname
----+-----------+----------
1 | fu1 | lu1
2 | fu2 | lu2
(2 rows)

*** Master:

INSERT INTO users(firstname,lastname) VALUES('fu3','lu3');

SELECT * FROM users;

result:

id | firstname | lastname
----+-----------+----------
1 | fu1 | lu1
2 | fu2 | lu2
3 | fu3 | lu3
(3 rows)

*** Slave:

SELECT * FROM users;

result:

id | firstname | lastname
----+-----------+----------
1 | fu1 | lu1
2 | fu2 | lu2
3 | fu3 | lu3
(3 rows)

ALTER TABLE users ADD enabled BOOLEAN NOT NULL DEFAULT true;

*** Master:

ALTER TABLE users ADD enabled BOOLEAN NOT NULL DEFAULT true;

INSERT INTO users(firstname,lastname) VALUES('fu4','lu4');

SELECT * FROM users;

result:

id | firstname | lastname | enabled
----+-----------+----------+---------
1 | fu1 | lu1 | t
2 | fu2 | lu2 | t
3 | fu3 | lu3 | t
4 | fu4 | lu4 | t
(4 rows)

*** Slave:

SELECT * FROM users;

result:

id | firstname | lastname | enabled
----+-----------+----------+---------
1 | fu1 | lu1 | t
2 | fu2 | lu2 | t
3 | fu3 | lu3 | t
(3 rows)

*** Slave Log:

2017-08-20 18:25:59.430 UTC [4764] LOG: database system is ready to accept
connections
2017-08-20 18:27:09.938 UTC [4781] LOG: logical replication apply worker
for subscription "mysub" has started
2017-08-20 18:27:09.947 UTC [4783] LOG: logical replication table
synchronization worker for subscription "mysub", table "users" has started
2017-08-20 18:27:10.354 UTC [4783] LOG: logical replication table
synchronization worker for subscription "mysub", table "users" has
finished
2017-08-20 18:42:46.121 UTC [4781] ERROR: logical replication target
relation "public.pg_temp_16386" does not exist
2017-08-20 18:42:46.122 UTC [4764] LOG: worker process: logical replication
worker for subscription 16392 (PID 4781) exited with exit code 1
2017-08-20 18:42:46.124 UTC [4837] LOG: logical replication apply worker
for subscription "mysub" has started
2017-08-20 18:42:46.154 UTC [4837] ERROR: logical replication target
relation "public.pg_temp_16386" does not exist
2017-08-20 18:42:46.155 UTC [4764] LOG: worker process: logical replication
worker for subscription 16392 (PID 4837) exited with exit code 1
2017-08-20 18:42:51.163 UTC [4839] LOG: logical replication apply worker
for subscription "mysub" has started
2017-08-20 18:42:51.221 UTC [4839] ERROR: logical replication target
relation "public.pg_temp_16386" does not exist
2017-08-20 18:42:51.223 UTC [4764] LOG: worker process: logical replication
worker for subscription 16392 (PID 4839) exited with exit code 1
2017-08-20 18:42:56.234 UTC [4841] LOG: logical replication apply worker
for subscription "mysub" has started
2017-08-20 18:42:56.378 UTC [4841] ERROR: logical replication target
relation "public.pg_temp_16386" does not exist
2017-08-20 18:42:56.380 UTC [4764] LOG: worker process: logical replication
worker for subscription 16392 (PID 4841) exited with exit code 1
2017-08-20 18:43:01.390 UTC [4845] LOG: logical replication apply worker
for subscription "mysub" has started
2017-08-20 18:43:01.568 UTC [4845] ERROR: logical replication target
relation "public.pg_temp_16386" does not exist
2017-08-20 18:43:01.570 UTC [4764] LOG: worker process: logical replication
worker for subscription 16392 (PID 4845) exited with exit code 1
2017-08-20 18:43:06.580 UTC [4847] LOG: logical replication apply worker
for subscription "mysub" has started
2017-08-20 18:43:06.726 UTC [4847] ERROR: logical replication target
relation "public.pg_temp_16386" does not exist
2017-08-20 18:43:06.729 UTC [4764] LOG: worker process: logical replication
worker for subscription 16392 (PID 4847) exited with exit code 1
2017-08-20 18:43:11.739 UTC [4849] LOG: logical replication apply worker
for subscription "mysub" has started
2017-08-20 18:43:11.906 UTC [4849] ERROR: logical replication target
relation "public.pg_temp_16386" does not exist
2017-08-20 18:43:11.908 UTC [4764] LOG: worker process: logical replication
worker for subscription 16392 (PID 4849) exited with exit code 1
2017-08-20 18:43:16.918 UTC [4851] LOG: logical replication apply worker
for subscription "mysub" has started
2017-08-20 18:43:17.077 UTC [4851] ERROR: logical replication target
relation "public.pg_temp_16386" does not exist
2017-08-20 18:43:17.079 UTC [4764] LOG: worker process: logical replication
worker for subscription 16392 (PID 4851) exited with exit code 1

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Atsushi Ide 2017-08-21 04:35:52 json and jsonb bug report
Previous Message John Pierce 2017-08-20 17:26:22 Re: centos 7 repository