Re: Logical replication from 11.x to 12.x and "unique key violations" - Mailing list pgsql-general

Adrian Klaver schrieb am 21.07.2020 um 17:07:
>> No, as mentioned, those are varchar(20) columns.
>> The values are generated by the application (no default value defined for the column)
>
> Aah I see my mistake I was going off your follow up question not the
> original post. In that original post though you had the PK containing
> a varchar(100) column. Can we see the table schema and the PK
> definition for at least one of the tables that threw an error?
>

Sorry about the confusion, some PKs are indeed defined as varchar(100) some as varchar(20) and some as varchar(15)
And I was also wrong about the generation, there is indeed a default value defined using a self-written ID generation
function.
But during replication, that function isn't called, so it shouldn't matter, I guess.

Here are two examples of failing tables:

    CREATE TABLE IF NOT EXISTS emp_status
    (
       emp_status_id   varchar(15)   DEFAULT generate_id('EA') NOT NULL PRIMARY KEY,
       status_name     varchar(20)   NOT NULL UNIQUE
    );

    CREATE TABLE IF NOT EXISTS company
    (
       comp_id     varchar(15)   DEFAULT generate_id('CO') NOT NULL PRIMARY KEY,
       name        varchar(50)   NOT NULL UNIQUE,
       country     varchar(50)   NOT NULL,
       code        varchar(20)   NOT NULL
    );

Both tables only contain only a few rows (less than 10) and e.g. for the status lookup, the log entry was:

LOG:  logical replication table synchronization worker for subscription "foo", table "emp_status" has started
ERROR:  duplicate key value violates unique constraint "emp_status_pkey"
DETAIL:  Key (employee_available_status_id)=(BUJ4XFZ7ATY27EA) already exists.
CONTEXT:  COPY employee_available_status, line 1

Thomas