Search Postgresql Archives

Re: Request to share information regarding deadlock in postgresql-9.3.6

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of John R Pierce
Sent: Thursday, November 17, 2016 12:04 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re:  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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux