I just noticed that publishing tables on multiple publications with
different row filters and column lists has somewhat surprising behavior.
To wit: if a column is published in any row-filtered publication, then
the values for that column are sent to the subscriber even for rows that
don't match the row filter, as long as the row matches the row filter
for any other publication, even if that other publication doesn't
include the column.
Here's an example.
Publisher:
create table uno (a int primary key, b int, c int);
create publication uno for table uno (a, b) where (a > 0);
create publication dos for table uno (a, c) where (a < 0);
Here, we specify: publish columns a,b for rows with positive a, and
publish columns a,c for rows with negative a.
What happened next will surprise you! Well, maybe not. On subscriber:
create table uno (a int primary key, b int, c int);
create subscription sub_uno connection 'port=55432 dbname=alvherre' publication uno,dos;
Publisher:
insert into uno values (1, 2, 3), (-1, 3, 4);
Publication 'uno' only has columns a and b, so row with a=1 should not
have value c=3. And publication 'dos' only has columns a and c, so row
with a=-1 should not have value b=3. But, on subscriber:
table uno;
a │ b │ c
────┼───┼───
1 │ 2 │ 3
-1 │ 3 │ 4
q.e.d.
I think results from a too simplistic view on how to mix multiple
publications with row filters and column lists. IIRC we are saying "if
column X appears in *any* publication, then the value is published",
period, and don't stop to evaluate the row filter corresponding to each
of those publications.
The desired result on subscriber is:
table uno;
a │ b │ c
────┼───┼───
1 │ 2 │
-1 │ │ 4
Thoughts?
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
