Re: BDR duplicate key value violates unique constraint error

On 24 November 2014 at 09:55, Jirayut Nimsaeng <jirayut(at)proteus-tech(dot)com>
wrote:

> 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?
>

You'll need to use global sequences with BDR:
https://wiki.postgresql.org/wiki/BDR_Global_Sequences

Thom