Re: [HACKERS] BDR duplicate key value violates unique constraint error
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 wrote:
> On 24 November 2014 at 09:55, Jirayut Nimsaeng
> 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
>
Re: [HACKERS] BDR duplicate key value violates unique constraint error
On 24 November 2014 at 09:55, Jirayut Nimsaeng
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
[HACKERS] 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?
Regard,
Jirayut
