On Tue, Dec 16, 2025 at 4:59 PM Chao Li <[email protected]> wrote:
> > On Dec 15, 2025, at 13:48, Amit Kapila <[email protected]> wrote: > > > So, without patch, there is no way we can silently replicate the > > UPDATE/DELETE. Ideally, users should alter the tables and make RI as > > FULL in such cases if they don't have PK for such tables. Falling back > > to FULL for DEFAULT when the table doesn't have PK based on GUC has a > > downside that it will increase WAL volume by a large amount. > > I agree that this downside exists, but it is an inherent cost that users > must accept if they choose to replicate all tables, including those without > a primary key. In practice, users who opt into such a configuration are > typically aware of the WAL overhead and make that trade-off consciously. > > > I don't know what is a good way to give to users who don't want to do > > the required setup but if we really want to provide something, it is > > better to allow such a thing via the publication option instead. > > Using a publication-level option could also work. One complication, > however, is that a table can belong to multiple publications. For example, > if table_a belongs to both pub_a and pub_b, and only pub_a is configured > with fallback_to_full while pub_b keeps the default behavior > (fallback_to_none), then the effective behavior for table_a would need to > remain fallback_to_none, meaning that UPDATE/DELETE would still not be > allowed if table_a has not a primary key. > > > I think it would be good to do such an enhancement if we have more > > community support and some other users also appreciate such a feature. > > Otherwise, adding something which is specific to a particular user > > sounds like a recipe of maintenance burden especially when we already > > provide a way to achieve the same thing as is required by the user. > > Let me elaborate on that point. > > My company has a very large user base in China, with over 100K deployments > across multiple industries. However, there is currently a significant gap > between this large user population and direct participation in the PG > community. I joined the company in July this year as a full-time > contributor to the PG community, and one of my responsibilities is to help > bridge this gap and bring real-world user feedback into community > discussions. > > As I mentioned in my earlier email, this requirement comes from > large-scale deployments. The database owners in these environments have > operational models that may not always align with what we consider the > ideal or fully optimized setup, but they are the result of years of > accumulated practice and operational experience. For these users, the > proposed feature would significantly simplify their day-to-day operations > and reduce operational friction. > +1 on the importance of addressing these large-scale operational realities. Beyond the scale issue, I believe there is a noticeable inconsistency between the documentation's promise of automation and the actual behavior of Replica Identity. 1. The "Practical Gap" of Schema Automation According to the documentation for FOR TABLES IN SCHEMA [1], the feature matches "all tables in the specified list of schemas, including tables created in the future". This explicitly promises an unattended, automated workflow for new tables. However, this promise is immediately broken by the default Replica Identity rules: 1) New tables are created with REPLICA IDENTITY DEFAULT [2] by standard. 2) For tables without a primary key, DEFAULT identity "cannot support UPDATE or DELETE operations" and "attempting such operations will result in an error on the publisher"[3]. This creates a logical trap: The system automatically adds the new table to the publication (as promised), but then immediately fails on the first UPDATE operation because the table creates with an incompatible default identity. This forces manual intervention (ALTER TABLE) in what is supposed to be an automated workflow. 2. Regarding the solution: I support Amit's suggestion of a Publication Option. It avoids the risks of a global GUC while allowing users to explicitly opt-in to the trade-off (accepting higher WAL volume) to ensure the automation provided by FOR TABLES IN SCHEMA is functionally complete. [1] https://www.postgresql.org/docs/current/sql-createpublication.html#SQL-CREATEPUBLICATION-PARAMS-FOR-TABLES-IN-SCHEMA [2] https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY [3] https://www.postgresql.org/docs/18/logical-replication-publication.html#LOGICAL-REPLICATION-PUBLICATION-REPLICA-IDENTITY -- Grant Zhou at Highgo Software
