BUG #15044: materialized views incompatibility with logical replication in postgres 10

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: chad(at)iris(dot)washington(dot)edu
Subject: BUG #15044: materialized views incompatibility with logical replication in postgres 10
Date: 2018-02-02 00:56:39
Message-ID: 151753299917.1235.5710750500940066850@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 15044
Logged by: Chad T
Email address: chad(at)iris(dot)washington(dot)edu
PostgreSQL version: 10.1
Operating system: CentOS 7.4.1708 and macOS 10.13.3
Description:

The built-in logical replication in postgres 10 is documented as not being
able to replication materialized views, notably here:
https://www.postgresql.org/docs/10/static/logical-replication-restrictions.html

Unfortunately, there appears to be an incompatibility with logical
replication and materialized views.

Here is a procedure to illustrate the problem:

# Create data directories for publisher and subscriber and initialize
mkdir pub sub

initdb pub
initdb sub

echo "wal_level = logical" >> pub/postgresql.conf
echo "wal_level = logical" >> sub/postgresql.conf

# Start servers:
pg_ctl -D pub -l pub.log -o "-p 5433" start
pg_ctl -D sub -l sub.log -o "-p 5434" start

# Create tables on both publisher and subscriber
psql -p 5433 -d postgres -c "CREATE TABLE testtable (id int,value text);"
psql -p 5434 -d postgres -c "CREATE TABLE testtable (id int,value text);"

# Create publication and subscription
psql -p 5433 -d postgres -c "CREATE PUBLICATION pub FOR ALL TABLES;"
psql -p 5434 -d postgres -c "CREATE SUBSCRIPTION sub CONNECTION
'host=localhost port=5433 dbname=postgres' PUBLICATION pub;"

# Insert a row into the test table and verify that replication is in a
streaming state
psql -p 5433 -d postgres -c "INSERT INTO testtable (id,value) VALUES
(1,'string');"
psql -p 5433 -d postgres -c "SELECT
state,sent_lsn,write_lsn,flush_lsn,replay_lsn FROM pg_stat_replication;"

# Create materialized view on publisher
psql -p 5433 -d postgres -c "CREATE MATERIALIZED VIEW mvid AS SELECT id FROM
testtable;"

With that CREATE MATERIALIZED VIEW statement the replication broken, with
these errors in the subscriber's log:

2018-02-01 16:34:27.639 PST [68409] ERROR: logical replication target
relation "public.mvid" does not exist
2018-02-01 16:34:27.642 PST [68391] LOG: worker process: logical
replication worker for subscription 16390 (PID 68409) exited with exit code
1

I have tried variations to work around this, all in vain, that include 1)
creating a regular table on the subscriber (allows replication to stream,
but the table is not populated) and 2) creating the MATERIALIZED VIEW before
creating the publication/subscription link (then it breaks on a REFRESH).

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2018-02-02 01:16:04 BUG #15044: materialized views incompatibility with logical replication in postgres 10
Previous Message David Kohn 2018-02-01 22:54:22 Re: BUG #15036: Un-killable queries Hanging in BgWorkerShutdown

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2018-02-02 01:05:55 Re: JIT compiling with LLVM v9.0
Previous Message Masahiko Sawada 2018-02-02 00:34:20 Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently