Обсуждение: BUG #15044: materialized views incompatibility with logicalreplication in postgres 10
BUG #15044: materialized views incompatibility with logicalreplication in postgres 10
От
PG Bug reporting form
Дата:
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).
BUG #15044: materialized views incompatibility with logicalreplication in postgres 10
От
"David G. Johnston"
Дата:
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.html
Unfortunately, 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
Вложения
Re: BUG #15044: materialized views incompatibility with logicalreplication in postgres 10
От
Peter Eisentraut
Дата:
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
Вложения
Re: BUG #15044: materialized views incompatibility with logicalreplication in postgres 10
От
Peter Eisentraut
Дата:
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