Re: 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

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?