Hello > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Yogesh Sharma > Sent: Donnerstag, 17. November 2016 08:31 > To: John R Pierce <pierce@xxxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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@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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
