Re: Skipping schema changes in publication

Approaches for Supporting EXCEPT in Partitioned Tables
------------------------------------------------------------------------

In an offline discussion with Peter Smith, Amit, and Shlok, we
identified several approaches for supporting EXCEPT with partitioned
tables and their partitions. I’d like to hear others’ opinions on
these approaches.

Consider the following partition hierarchy:
tab_root
├─ tab_part_1
│ ├─ tab_part_1_p1
│ └─ tab_part_1_p2
└─ tab_part_2
├─ tab_part_2_p1
└─ tab_part_2_p2

Approach 1:
---------------------------------
If we exclude a table, then the data in that table and all of its
partitions (i.e., the entire subtree under that table) should not be
replicated.

For example EXCEPT (tab_part_1) skips replication of tab_part_1 and
all of its partitions.

This behaviour remains the same with or without
publish_via_partition_root. The publish_via_partition_root flag only
affects publish_via_relid, i.e., the relation through which data is
published.

This approach involves certain implementation challenges. For brevity,
these are documented in the attached 'Approach1_challenges' document.

Approach 2:
---------------------------------------------------
Assign meaning to ONLY and '*' for partition tables in the EXCEPT
list. In HEAD, ONLY and '*' do not have any meaning for partitioned
tables or partitions, and these keywords are currently ignored.

Examples:
1. EXCEPT (ONLY tab_part_1) skips replication of only the table
tab_part_1. Changes for tab_root, tab_part_1_p1, and tab_part_1_p2 are
still replicated.

ii. EXCEPT (tab_part_1*) skips replication of tables tab_part_1,
tab_part_1_p1, and tab_part_1_p2

The challenges described in Approach 1, particularly around tablesync
handling and COPY behaviour, would still need to be addressed under
this approach as well. ONLY or '*' with partitioned tables is not
supported in HEAD, supporting it specifically for ALL TABLES EXCEPT
may introduce additional confusion for users.

Approach 3:
----------------
Do not allow partitions to be specified in the EXCEPT clause.

Only EXCEPT (tab_root) is supported, which excludes tab_root and all
of its partitions. Specifying EXCEPT (tab_part_1) or EXCEPT
(tab_part_1_p1) will result in an error.

~~

While Approach 1 and Approach 2 offer more flexibility to the user
compared to Approach 3, they also introduce additional design
complexity which does not seem simpler to address.

Approach 3, OTOH, provides a simpler solution and removes ambiguity
around the meaning of EXCEPT. It also aligns, to some extent, with the
behavior of ALTER PUBLICATION … DROP TABLE. For example, when a
publication is created explicitly for a ROOT partitioned table,
ALTER PUBLICATION DROP TABLE is not allowed for a partition,
regardless of the publish_via_partition_root setting. This behavior is
demonstrated in the attached AlterPubDropTable file.

That said, the behaviors of DROP TABLE and EXCEPT (table) are not
directly comparable. EXCEPT (table) is supported only for ALL TABLES
publications, whereas DROP TABLE is not supported in that context.
Similarly, when a publication is created explicitly for a partition,
ALTER PUBLICATION does allow dropping that partition.

The key point here is that 'publish_via_partition_root' does not
influence what can or cannot be dropped using ALTER PUBLICATION … DROP
TABLE. This approach follows the same principle.

OTOH when we see row-filter behaviour, publish_via_partition_root
plays a role in determining whether a row filter can be created
itself. This is demonstrated in the attached RowFilterBehaviour
experiment.

This leads us to Approach 4.

Approach 4:
----------------
Do not allow partitions to be specified in the EXCEPT clause when
publish_via_partition_root is true, but allow them when
publish_via_partition_root is false.

When publish_via_partition_root = true:
EXCEPT (tab_part_1_p1) results in an error.
EXCEPT (tab_part_1) results in an error.
EXCEPT (tab_root) excludes the entire hierarchy under the root table.

When publish_via_partition_root = false:
EXCEPT (tab_part_1_p1) skips replication of that partition.
EXCEPT (tab_part_1) skips replication of that partition and all of its
child partitions.
EXCEPT (tab_root) skips replication of the partitioned table and all
partitions beneath it.

This design is guided by the way row filters behave. It offers more
flexibility than Approach 3 while avoiding the complexity of
Approaches 1 and 2. However, it may cause some user confusion since
EXCEPT behavior depends on publish_via_partition_root.

Given this, the current plan is to implement Approach 3 as the initial
patch, and then implement the changes required for Approach 4 in a
top-up patch. This will allow us to evaluate if at all Approach 4
causes any implementation complexities.

Please share your thoughts on the approaches described.

thanks
Shveta