Hi All, (CC: Amit, Vignesh, and Nisha, who were involved in the initial discussion)
While working on a new feature to provide an option to skip a set of tables from ALL TABLES publication in [1], we came across the following behavior related to inherited tables and would like to confirm whether this is expected. Please see the test below. CREATE TABLE t_inh (id int); CREATE TABLE t_inh_c1 (c1 int) INHERITS (t_inh); CREATE TABLE t_inh_c2 (c2 int) INHERITS (t_inh); CREATE PUBLICATION pub1 FOR TABLE t_inh; --Descendant tables included in publication postgres=# \dRp+ Owner | All tables | All sequences | --------+------------+---------------+ shveta | f | f | Tables: "public.t_inh" "public.t_inh_c1" "public.t_inh_c2" Remove the inheritance for one of the child tables: ALTER TABLE t_inh_c1 NO INHERIT t_inh; t_inh_c1 is removed from the inheritance tree of t_inh. postgres=# \d+ t_inh Column | Type | --------+------ id | integer | Publications: "pub1" Child tables: t_inh_c2 However, the publication still lists t_inh_c1: postgres=# \dRp+ Owner | All tables | All sequences | --------+------------+---------------+ shveta | f | f | Tables: "public.t_inh" "public.t_inh_c1" "public.t_inh_c2" Even if we add a new descendant table, it does not get reflected in the publication: postgres=# CREATE TABLE t_inh_c3 (c3 int) INHERITS (t_inh); CREATE TABLE postgres=# \d+ t_inh Table "public.t_inh" Column | Type | --------+---------+ id | integer | Publications: "pub1" Child tables: t_inh_c2, t_inh_c3 postgres=# \dRp+ Publication pub1 Owner | All tables | All sequences | --------+------------+---------------+ shveta | f | f | Tables: "public.t_inh" "public.t_inh_c1" "public.t_inh_c2" This is different from how partitioned tables behave. In the case of partitioned tables, adding or removing a partition automatically includes or excludes it from the publication. I am slightly leaning towards the opinion that current behavior for inherited tables is expected. Descendant tables are independent relations, and once they are added to a publication, they are treated as independent entities rather than being dynamically tied to the parent through the inheritance hierarchy. However, I would like to get feedback from others. Does this look like a bug, or is this the intended behavior? If we consider changing the behavior so that removing t_inh_c1 from the inheritance tree also removes it from the publication, there is currently no way to determine whether the user created the publication for t_inh (implicitly expanding to descendants at creation time) or explicitly for t_inh, t_inh_c1, and t_inh_c2. This makes it difficult to decide whether an automatic change would be correct. Should we update the documentation to briefly mention that changes in the inheritance tree are not reflected in the publication after its creation unless done manually? Or is there a better approach? [1]: https://www.postgresql.org/message-id/CALDaNm1gwuOgdeffd5yUOe%3DkP6QCq8uSkX1YFMjx8%3D5PbCvt-Q%40mail.gmail.com thanks Shveta
