BUG #15044: materialized views incompatibility with logical replication in postgres 10
The following bug has been logged on the website:
Bug reference: 15044
Logged by: Chad T
Email address: chad@iris.washington.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).
On Thursday, February 1, 2018, PG Bug reporting form <noreply@postgresql.org>
wrote:
Bug reference: 15044
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-replicatio
n-restrictions.htmlUnfortunately, there appears to be an incompatibility with logical
replication and materialized views.
[...]
# Create publication and subscription
psql -p 5433 -d postgres -c "CREATE PUBLICATION pub FOR ALL TABLES;"[...]
# 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
It seems the work-around is to not use "for all tables" in your publication
definition.
As described it does seem bugged. The table matview itself is not being
published, as documented, but knowledge of its existence as part of the
publication is...
David J.
On February 1, 2018 17:16:08 "David G. Johnston"
<david.g.johnston@gmail.com> wrote:
Bug reference: 15044
It seems the work-around is to not use "for all tables" in your publication
definition.
Indeed. My real world case 700+ tables with semi regular additions and two
materialized views so ALL TABLES was the right fit.
As described it does seem bugged. The table matview itself is not being
published, as documented, but knowledge of its existence as part of the
publication is...
Exactly. The matview does not show up in pg_publication_tables but it's
registered at some level.
Hi,
On 02/02/18 02:39, Chad Trabant wrote:
On February 1, 2018 17:16:08 "David G. Johnston"
<david.g.johnston@gmail.com> wrote:Bug reference:����� 15044
It seems the work-around is to not use "for all tables" in your
publication
definition.
Indeed.� My real world case 700+ tables with semi regular additions and
two materialized views so ALL TABLES was the right fit.As described it does seem bugged.� The table matview itself is not being
published, as documented, but knowledge of its existence as part of the
publication is...Exactly.� The matview does not show up in pg_publication_tables but it's
registered at some level.
Indeed this is a bug. For normal publications we take care of this when
adding the relation to the publication but since ALL TABLES publications
don't check for membership we have to filter this directly in the output
plugin.
The attached patch should fix it (CCing to PeterE as the original
committer).
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
0001-Fix-filtering-of-unsupported-relations-in-pgoutput.patchtext/x-patch; name=0001-Fix-filtering-of-unsupported-relations-in-pgoutput.patchDownload+21-13
On 2/5/18 10:33, Petr Jelinek wrote:
Exactly.� The matview does not show up in pg_publication_tables but it's
registered at some level.Indeed this is a bug. For normal publications we take care of this when
adding the relation to the publication but since ALL TABLES publications
don't check for membership we have to filter this directly in the output
plugin.
I think the filtering in pgoutput ought to make use of
is_publishable_class() in some way. That takes care of non-tables such
as materialized views, but it also filters out the information_schema
tables for example. Right now, if you insert something into one of the
IS tables, it gets shipped over the wire but is then dropped by the
apply because there is no pg_subscription_rel entry of the table. That
doesn't quite have the user-visible effect as this bug, but it's bogus
nonetheless.
So I propose this alternative patch that covers all these cases.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
0001-Fix-filtering-of-unsupported-relations-in-pgoutput.patchtext/plain; charset=UTF-8; name=0001-Fix-filtering-of-unsupported-relations-in-pgoutput.patch; x-mac-creator=0; x-mac-type=0Download+13-1
Adding -hackers to this in the interest of getting it committed by Monday's
wrap-up.
/messages/by-id/6e375316-91a4-7825-ef8b-9b8915ab6980@2ndquadrant.com
David J.
On Sat, Feb 17, 2018 at 8:43 PM, Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:
Show quoted text
On 2/5/18 10:33, Petr Jelinek wrote:
Exactly. The matview does not show up in pg_publication_tables but it's
registered at some level.Indeed this is a bug. For normal publications we take care of this when
adding the relation to the publication but since ALL TABLES publications
don't check for membership we have to filter this directly in the output
plugin.I think the filtering in pgoutput ought to make use of
is_publishable_class() in some way. That takes care of non-tables such
as materialized views, but it also filters out the information_schema
tables for example. Right now, if you insert something into one of the
IS tables, it gets shipped over the wire but is then dropped by the
apply because there is no pg_subscription_rel entry of the table. That
doesn't quite have the user-visible effect as this bug, but it's bogus
nonetheless.So I propose this alternative patch that covers all these cases.
Attachments:
0001-Fix-filtering-of-unsupported-relations-in-pgoutput.patchapplication/octet-stream; name=0001-Fix-filtering-of-unsupported-relations-in-pgoutput.patchDownload+13-1
On 2/17/18 22:43, Peter Eisentraut wrote:
On 2/5/18 10:33, Petr Jelinek wrote:
Exactly.� The matview does not show up in pg_publication_tables but it's
registered at some level.Indeed this is a bug. For normal publications we take care of this when
adding the relation to the publication but since ALL TABLES publications
don't check for membership we have to filter this directly in the output
plugin.I think the filtering in pgoutput ought to make use of
is_publishable_class() in some way. That takes care of non-tables such
as materialized views, but it also filters out the information_schema
tables for example. Right now, if you insert something into one of the
IS tables, it gets shipped over the wire but is then dropped by the
apply because there is no pg_subscription_rel entry of the table. That
doesn't quite have the user-visible effect as this bug, but it's bogus
nonetheless.So I propose this alternative patch that covers all these cases.
Committed, and also added a new test in the master branch.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services