Request to share information regarding deadlock in postgresql-9.3.6

On 11/16/2016 6:22 PM, Yogesh Sharma wrote:

process 5764 is waiting for relation (table) with OID 16459(table2_primary_key), that table is blocked by process 4970 and process 4970 is waiting for a lock on another table, OID 16502(table1), which the first process has a lock on.
Process 5764: INSERT INTO table2 ( id , roll_number, name) VALUES( '1', '4','abc' )
Process 4970: REINDEX TABLE table1, table2 etc..

How to resolve this problem?

don't do reindex when the tables are in use.

or

why does process 5764 have a lock on table 1 while its inserting into
table 2?

or

do each reindex as a seperate transaction so only one table gets locked
at a time.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Dear John,

Thanks for sharing solution approaches.

do each reindex as a seperate transaction so only one table gets locked at a time.

Transaction is already in separate like REINDEX TABLE table1, REINDEX TABLE table2 etc..
But this problem is occurred.

One more question regarding below.
alter table table1 add constraint fk_key foreign key (id, roll_number) references table2 (id, roll_number) on delete restrict on update restrict;
It shows below error:
ERROR: there is no unique constraint matching given keys for referenced table "table2"

Table1 contains below structure:
create table table1
(
id char(6) not null,
roll_number varchar(20) not null,
-----
primary key (id, roll_number)

Table2 contains below structure:

create table table2
(
id char(6) not null,
roll_number varchar(20) not null,
account_id varchar(20) not null default '-',
-----
primary key (id, roll_number, account_id)

How to resolve this issue?

Regards,
Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, November 17, 2016 12:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding deadlock in postgresql-9.3.6

On 11/16/2016 6:22 PM, Yogesh Sharma wrote:

process 5764 is waiting for relation (table) with OID 16459(table2_primary_key), that table is blocked by process 4970 and process 4970 is waiting for a lock on another table, OID 16502(table1), which the first process has a lock on.
Process 5764: INSERT INTO table2 ( id , roll_number, name) VALUES(
'1', '4','abc' ) Process 4970: REINDEX TABLE table1, table2 etc..

How to resolve this problem?

don't do reindex when the tables are in use.

or

why does process 5764 have a lock on table 1 while its inserting into table 2?

or

do each reindex as a seperate transaction so only one table gets locked at a time.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Yogesh Sharma
Sent: Donnerstag, 17. November 2016 08:31
To: John R Pierce <pierce@hogranch.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding deadlock in postgresql-9.3.6

Dear John,

Thanks for sharing solution approaches.

do each reindex as a seperate transaction so only one table gets locked at a time.

Transaction is already in separate like REINDEX TABLE table1, REINDEX TABLE table2 etc..
But this problem is occurred.

One more question regarding below.
alter table table1 add constraint fk_key foreign key (id, roll_number) references table2 (id, roll_number) on delete
restrict on update restrict; It shows below error:
ERROR: there is no unique constraint matching given keys for referenced table "table2"

As mentioned upthread, the foreign key must match the primary key (more precisely a unique key) of the table it references.
Now the primary key of table2 is (id, roll_number, account_id) but you are trying to reference only a part of it.

references table2 (id, roll_number)

^^^^^^^^^^^^^^^^

You can add a unique contraint in table2:

create table table2
(
id char(6) not null,
roll_number varchar(20) not null,
account_id varchar(20) not null default '-',
primary key (id, roll_number, account_id),
unique (id, roll_number)
);

create table table1
(
id char(6) not null,
roll_number varchar(20) not null,
primary key (id, roll_number)
);

alter table table1 add constraint fk_key foreign key (id, roll_number) references table2 (id, roll_number) on delete restrict on update restrict;

db.localhost=> \d table1
Table "public.table1"
Column | Type | Modifiers
-------------+-----------------------+-----------
id | character(6) | not null
roll_number | character varying(20) | not null
Indexes:
"table1_pkey" PRIMARY KEY, btree (id, roll_number)
Foreign-key constraints:
"fk_key" FOREIGN KEY (id, roll_number) REFERENCES table2(id, roll_number) ON UPDATE RESTRICT ON DELETE RESTRICT

Regards
Charles

Table1 contains below structure:
create table table1
(
id char(6) not null,
roll_number varchar(20) not null,
-----
primary key (id, roll_number)

Table2 contains below structure:

create table table2
(
id char(6) not null,
roll_number varchar(20) not null,
account_id varchar(20) not null default '-',
-----
primary key (id, roll_number, account_id)

How to resolve this issue?

Regards,
Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, November 17, 2016 12:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding deadlock in postgresql-9.3.6

On 11/16/2016 6:22 PM, Yogesh Sharma wrote:

process 5764 is waiting for relation (table) with OID 16459(table2_primary_key), that table is blocked by process

4970 and process 4970 is waiting for a lock on another table, OID 16502(table1), which the first process has a lock
on.

Process 5764: INSERT INTO table2 ( id , roll_number, name) VALUES(
'1', '4','abc' ) Process 4970: REINDEX TABLE table1, table2 etc..

How to resolve this problem?

don't do reindex when the tables are in use.

or

why does process 5764 have a lock on table 1 while its inserting into table 2?

or

do each reindex as a seperate transaction so only one table gets locked at a time.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Rethinking that

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles
Clavadetscher
Sent: Donnerstag, 17. November 2016 09:12
To: 'Yogesh Sharma' <Yogesh1.Sharma@nectechnologies.in>; 'John R Pierce' <pierce@hogranch.com>; pgsql-
general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding deadlock in postgresql-9.3.6

Hello

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Yogesh Sharma
Sent: Donnerstag, 17. November 2016 08:31
To: John R Pierce <pierce@hogranch.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding deadlock
in postgresql-9.3.6

Dear John,

Thanks for sharing solution approaches.

do each reindex as a seperate transaction so only one table gets locked at a time.

Transaction is already in separate like REINDEX TABLE table1, REINDEX TABLE table2 etc..
But this problem is occurred.

One more question regarding below.
alter table table1 add constraint fk_key foreign key (id, roll_number)
references table2 (id, roll_number) on delete restrict on update restrict; It shows below error:
ERROR: there is no unique constraint matching given keys for referenced table "table2"

At second thought I think that your example does not really make sense. You probably want something like this:

create table table1
(
id char(6) not null,
roll_number varchar(20) not null,
primary key (id, roll_number)
);

create table table2
(
id char(6) not null,
roll_number varchar(20) not null,
account_id varchar(20) not null default '-',
primary key (id, roll_number, account_id),
);

alter table table2 add constraint fk_key foreign key (id, roll_number) references table1 (id, roll_number) on delete restrict on update restrict;

It would to know what you want to eventually achieve.

Bye
Charles

As mentioned upthread, the foreign key must match the primary key (more precisely a unique key) of the table it
references.
Now the primary key of table2 is (id, roll_number, account_id) but you are trying to reference only a part of it.

references table2 (id, roll_number)

^^^^^^^^^^^^^^^^

You can add a unique contraint in table2:

create table table2
(
id char(6) not null,
roll_number varchar(20) not null,
account_id varchar(20) not null default '-',
primary key (id, roll_number, account_id),
unique (id, roll_number)
);

create table table1
(
id char(6) not null,
roll_number varchar(20) not null,
primary key (id, roll_number)
);

alter table table1 add constraint fk_key foreign key (id, roll_number) references table2 (id, roll_number) on delete
restrict on update restrict;

db.localhost=> \d table1
Table "public.table1"
Column | Type | Modifiers
-------------+-----------------------+-----------
id | character(6) | not null
roll_number | character varying(20) | not null
Indexes:
"table1_pkey" PRIMARY KEY, btree (id, roll_number) Foreign-key constraints:
"fk_key" FOREIGN KEY (id, roll_number) REFERENCES table2(id, roll_number) ON UPDATE RESTRICT ON DELETE RESTRICT

Regards
Charles

Table1 contains below structure:
create table table1
(
id char(6) not null,
roll_number varchar(20) not null,
-----
primary key (id, roll_number)

Table2 contains below structure:

create table table2
(
id char(6) not null,
roll_number varchar(20) not null,
account_id varchar(20) not null default '-',
-----
primary key (id, roll_number, account_id)

How to resolve this issue?

Regards,
Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, November 17, 2016 12:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding deadlock
in postgresql-9.3.6

On 11/16/2016 6:22 PM, Yogesh Sharma wrote:

process 5764 is waiting for relation (table) with OID
16459(table2_primary_key), that table is blocked by process

4970 and process 4970 is waiting for a lock on another table, OID
16502(table1), which the first process has a lock on.

Process 5764: INSERT INTO table2 ( id , roll_number, name) VALUES(
'1', '4','abc' ) Process 4970: REINDEX TABLE table1, table2 etc..

How to resolve this problem?

don't do reindex when the tables are in use.

or

why does process 5764 have a lock on table 1 while its inserting into table 2?

or

do each reindex as a seperate transaction so only one table gets locked at a time.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general