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(dot)klaver(at)aklaver(dot)com