Thread: BDR duplicate key value violates unique constraint error
Hi,
I'm using PostgreSQL BDR 9.4beta2 to test BDR capability right now.$ psql --version
psql (PostgreSQL) 9.4beta2
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)
bdrdemo=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------+----------+----------
public | department | table | postgres
public | department_id_seq | sequence | postgres
(2 rows)
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
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)
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.
ERROR: duplicate key value violates unique constraint "department_pkey"
DETAIL: Key (id)=(1) already exists.
Anyone has idea on this?
Regard,
Jirayut
On 24 November 2014 at 09:55, Jirayut Nimsaeng <jirayut@proteus-tech.com> wrote:
I used database name bdrdemo for BDR then I've created tables with this DDLHi,I'm using PostgreSQL BDR 9.4beta2 to test BDR capability right now.
$ psql --version
psql (PostgreSQL) 9.4beta2
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 DATABASEThen I insert data with command
bdrdemo=# insert into DEPARTMENT (DEPT, EMP_ID) values ('RANDOM_INSERT','1234');
INSERT 0 1I 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 errorbdrdemo=# 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?
You'll need to use global sequences with BDR: https://wiki.postgresql.org/wiki/BDR_Global_Sequences
Thom
NVM. I asked people in IRC and it turns out that after I used ALTER DATABASE bdrdemo SET default_sequenceam=department_id_seq; command I have to exit from psql session first and it works again :)
On Mon, Nov 24, 2014 at 6:29 PM, Thom Brown <thom@linux.com> wrote:
On 24 November 2014 at 09:55, Jirayut Nimsaeng <jirayut@proteus-tech.com> wrote:I used database name bdrdemo for BDR then I've created tables with this DDLHi,I'm using PostgreSQL BDR 9.4beta2 to test BDR capability right now.
$ psql --version
psql (PostgreSQL) 9.4beta2
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 DATABASEThen I insert data with command
bdrdemo=# insert into DEPARTMENT (DEPT, EMP_ID) values ('RANDOM_INSERT','1234');
INSERT 0 1I 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 errorbdrdemo=# 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?You'll need to use global sequences with BDR: https://wiki.postgresql.org/wiki/BDR_Global_SequencesThom