polar_create_table_with_full_replica_identity
polar_create_table_with_full_replica_identity sets the default replica identity of newly created tables to FULL, so that tables without a primary key can be replicated without errors.
Background
PolarDB for PostgreSQL (Compatible with Oracle) uses a publish-and-subscribe model for logical replication. The publisher executes DML operations — INSERT, UPDATE, and DELETE — and sends changes to the subscriber, where the same operations are applied. To identify which rows to update or delete on the subscriber side, each replicated table must have a replica identity configured on the publisher.
Three replica identity types are supported:
| Replica identity | Description | Default |
|---|---|---|
| Primary key | Uses the primary key to identify rows. | Yes |
| Unique index | Uses a specified unique index to identify rows. | No |
| FULL | Uses the entire row as the identity. | No |
By default, tables without a primary key have no identity for change operations. Any change operation on such a table causes the following error:
ERROR: cannot delete from table "polardb_test" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.Setting the replica identity to FULL resolves this by using the entire row as the key.
When using logical replication — including with Data Transmission Service (DTS) — set the replica identity to FULL for every table that has no primary key.
Set the replica identity to FULL
Choose the method based on whether the table already exists or is yet to be created.
Set the replica identity for an existing table
Run ALTER TABLE to change the replica identity of an existing table:
ALTER TABLE <table_name> REPLICA IDENTITY FULL;Replace <table_name> with the name of the table to update.
Set the default replica identity for new tables
Set polar_create_table_with_full_replica_identity to on. All tables created afterward automatically use FULL as their default replica identity.