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@xxxxxxxxxxx
