Re: Request to share information regarding deadlock in postgresql-9.3.6 - Mailing list 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
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