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).
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 |
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 |