Re: Logical replication from 11.x to 12.x and "unique key violations"

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