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

On 7/21/20 10:36 PM, Thomas Kellerer wrote:
> 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

I know your subsequent post explained the problem. Still I'm not fully 
understanding the above. I understand the "duplicate key value violates 
unique constraint "emp_status_pkey" part. What I don't understand is 
where employee_available_status_id and employee_available_status are 
coming from? Or is this a copy/paste issue?

> 
> Thomas
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com