From: | Yogesh Sharma <Yogesh1(dot)Sharma(at)nectechnologies(dot)in> |
---|---|
To: | John R Pierce <pierce(at)hogranch(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Request to share information regarding deadlock in postgresql-9.3.6 |
Date: | 2016-11-17 07:30:54 |
Message-ID: | 8F86F8F397DDD345B25EA2EF3E2164829ECBA8B7@EXCH-MB02-U1.nectechnologies.in |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of John R Pierce
Sent: Thursday, November 17, 2016 12:04 PM
To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Arnaud L. | 2016-11-17 08:06:44 | Re: pg_restore --clean failing due to dependancies |
Previous Message | Amul Sul | 2016-11-17 06:15:41 | Re: Exclude pg_largeobject form pg_dump |