Thread: Request to share information regarding deadlock in postgresql-9.3.6
Dear All, Thanks for your support. Could you please share your opinion for deadlock resolution. Process 5764 waits for AccessShareLock on relation 16459 of database 16385; blocked by process 4970. Process 4970 waits for ShareLock on relation 16502 of database 16385; blocked by process 5764. It means we have two processes that are each waiting for locks the other have. process 5764 is waiting for relation (table) with OID 16459(table2_primary_key), that table is blocked by process 4970 andprocess 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? Regards, Yogesh
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 4970and 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
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 restricton 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 4970and 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
Re: Request to share information regarding deadlock in postgresql-9.3.6
From
"Charles Clavadetscher"
Date:
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
restricton 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
Re: Request to share information regarding deadlock in postgresql-9.3.6
From
"Charles Clavadetscher"
Date:
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 restricton 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