When you synchronize tables that have no primary keys in PolarDB for PostgreSQL(Compatible with Oracle) to other databases by using the logical replication method, errors may be reported for the operations on the tables. You can specify the polar_create_table_with_full_replica_identity parameter to resolve this issue.

Logical replication of PolarDB for PostgreSQL(Compatible with Oracle) uses a publish and subscribe model. The operations on the publisher side can be executed on the subscriber side in a similar way to Structural Query Language (SQL) so that data can be synchronized. The replica identities of the tables must be configured on the publisher side so that the data to be updated or deleted on the subscriber side can be identified.

The following types of replica identities are supported:

  • Primary key
  • Unique index
  • FULL (a full row of data)

By default, the replica identity is the primary key. If logical replication is implemented on a table that has no primary key, an error occurs for the change operations. As a result, services cannot run as expected. The following error message is returned:

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.
Important When you use logical replication, make sure that all the replica identities of the tables that are to be synchronized and have no primary keys are set to FULL. For example, when you use Data Transmission Service (DTS) to synchronize data, comply with this rule.

PolarDB for PostgreSQL(Compatible with Oracle) provides the following two methods for you to change the replica identities of tables to FULL.

  • Run the following command to change the replica identity of an existing table to FULL:
    ALTER TABLE <table_name> REPLICA IDENTITY FULL;
  • Specify the polar_create_table_with_full_replica_identity parameter as on to set the default replica identity of a newly created table to FULL.