Обсуждение: BUG #15044: materialized views incompatibility with logicalreplication 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).

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-replication-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

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

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

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