1. Oracle GoldenGate Classic Documentation
  2. Prepare
  3. Prepare Your Database for Oracle GoldenGate Classic Architecture
  4. PostgreSQL
  5. Preparing the Database for Oracle GoldenGate
  6. Preparing Tables for Processing
  7. Ensuring Row Uniqueness for Tables

Ensuring Row Uniqueness for Tables

Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

  1. Primary key

  2. First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.

  3. If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration. For PostgreSQL LOB types such as text, xml, bytea, char, varchar, Oracle GoldenGate supports these columns as a primary key in source or target tables up to a length of 8191 bytes.

    Note:

    If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.

    For tables that have no uniqueness and have repeat rows with the same values, Replicat will Abend on update and delete operations for these rows.

  4. If a table does not have an appropriate key, or if you prefer that the existing key(s) are not used, you can define a substitute key, if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Reference for Oracle GoldenGate.