BDR duplicate key value violates unique constraint error

From: Jirayut Nimsaeng <jirayut(at)proteus-tech(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: BDR duplicate key value violates unique constraint error
Date: 2014-11-24 09:55:50
Message-ID: CACbDkQNS-zRtv2n802HfXh2eCUfwdRQRSDBiaqp9fOVZ9KJrdw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I'm using PostgreSQL BDR 9.4beta2 to test BDR capability right now.

$ psql --version
psql (PostgreSQL) 9.4beta2

I used database name bdrdemo for BDR then I've created tables with this DDL

CREATE TABLE DEPARTMENT(
ID SERIAL PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);

I can confirm that both sides have table created with \d

bdrdemo=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------+----------+----------
public | department | table | postgres
public | department_id_seq | sequence | postgres
(2 rows)

then someone give me this command to make sure that serial primary key will
have it own sequence so I put it on both nodes

bdrdemo=# ALTER DATABASE bdrdemo SET default_sequenceam=department_id_seq;
ALTER DATABASE

Then I insert data with command

bdrdemo=# insert into DEPARTMENT (DEPT, EMP_ID) values
('RANDOM_INSERT','1234');
INSERT 0 1

I can confirm it works on both side

bdrdemo=# SELECT * FROM department;
id | dept | emp_id
----+----------------------------------------------------+--------
1 | RANDOM_INSERT | 1234
(1 row)

But as you can see the id start from 1 instead of high number. I knew
because I got this working before and if you insert data from another node
I will get this error

bdrdemo=# insert into DEPARTMENT (DEPT, EMP_ID) values
('RANDOM_INSERT','1234');
ERROR: duplicate key value violates unique constraint "department_pkey"
DETAIL: Key (id)=(1) already exists.

Anyone has idea on this?

Regard,
Jirayut

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2014-11-24 10:52:45 Re: WIP: Access method extendability
Previous Message Heikki Linnakangas 2014-11-24 09:17:35 Re: Change in HEAP_NEWPAGE logging makes diagnosis harder