Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jMifL-0000tG-1g for pgsql-general@arkaria.postgresql.org; Fri, 10 Apr 2020 01:41:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jMifJ-0002jA-T4 for pgsql-general@arkaria.postgresql.org; Fri, 10 Apr 2020 01:41:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jMifJ-0002j2-DS for pgsql-general@lists.postgresql.org; Fri, 10 Apr 2020 01:41:05 +0000 Received: from sonic314-21.consmr.mail.ne1.yahoo.com ([66.163.189.147]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jMifC-0008Iq-Kf for pgsql-general@postgresql.org; Fri, 10 Apr 2020 01:41:04 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1586482857; bh=ZEdq70aWAHwv8klZC5KJ32D91wDByr176FwMjTWwifM=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=bPZXhe5HA/jvWb1xctTzlKGh7zNuQd/PTJOOtqtr64s+V63/Kjq0xbIwwMYenB+OggUAnneAVGN/AFNxphJHGrmicdybmseXmZsJ+qCuY4LEzMh9hN5RQ54H3jIvpLZLDD4Fxe80ppRb71vWi/pVA1iVHM0bDP/Bei/ZrrhFklfzj4ULC+/Eq/CpCLKR6yx7rmrXYKQ/ps+DTnZGzF7fB2zJXpqOE5AEYL5+VPKd2cnFIZUP/h6ZW4eqAgl/nl8BTqMq5DmdLVDZpdYFNZJL7p9slcNpflNypZelxybzuqWe6Os7LHjKuWhnT+nzyQSq1PoWU9rJLH7PKtN/R7qQiQ== X-YMail-OSG: MwEoD_8VM1mYNFbosKwQLbREqbLCW89AyBceOehSXSos7Hgrgx2rmWo.7rK9Ilw ZGC9X2yfbtM9pbAjPbRzviSoq7Nqi0XhjZSifZODHLPmqeRG7jCKEBgMLBvPhV4RJOm22YJ6Y8K6 gbmZBG.wM_fgs4XMmB6m8rLcqlecKIvXUwLoUkOstUOjkRYBcBktNuu9tD5rh.l_7U9pa60JFZYb 6CSsakETIRnueqlye1nP7.BnBH3lF0YyqNC_0.VJaz3eTFyc1pGx8hjJEqACTugYaAVI0qITvZkj O.ycinABUU_RpJrvtyAyIg9phdbwmGZBZPYFLm3lQzmxjNdWSG0ESM8W..wNdN.es9DxaxEvOYDk dMxPkpWrEVy1giqps_WPpexUyGL_Ci7A5jr3JlknJRTuvDXlpIX3FbtG6h6Ur72JLMa3cTQEcfsy nOV0bxr4afuyY2W3Qph_WDgXJf8M3bGAITKKSOu55a.RBt6bgSKyivKP.uFmFtW7g1Ucbxr8zfzz LcX0kZdqDl8iOtcUI8tQ64gU47Mz.7JeP3RSVjGUVoEEfHXGGcIa.8thCL_snH5kdp1oUYzgRcZQ EE8ZcWM_5JxaqoK2eLC.75.j1ujKOXs0f9uvo3L2LftDMhY3ueShCryu7MBnxsbQFeDZ2gqYZlZr 6ZWEwnCvbB_8QerMnBQLEfe92MqCcmGkfdgZJkSYfXZIh7J3vSGihfiKeJocPD80Ypf.2EHRkhXm SmTYVrlPd3S9.paOlitf.9PL6eMy4c3o_g4Tgo9PO4bqgs7E_kAaxiDmP_n.dVytfHptiUa3RHna 1.vqd.mFf2eDB6NnduiW2Edn9C_hHrPvMP.tuD6UQRyWhBgeyVtp8Gc_GDy1uSr7BqkJ5MkpibPN NH39JE9LmGXfvXaRQaX4C9MuIjX0_zi4FSmPdPrgMiL1UUfyPhNux5wG6YaYVtKmTrJotFINeJao ktUD1pDi5iKdk1Zl9.Mw3upgk1Gh4gGWMH8.nVp5uvCfeGGbt8kGI4YKDkJkycgG3mTca.4hKSTu uUpR4dAEcWBc4RHGD7MKYFsR7J1cFL5yGYwEq.8enOjnaUl_Q1EwSP8REAN9wmb_4Fx3D5vfZXLj KDJ.Rl1k3NtLOF3SMLaSyZsFlV8WZVfAR3bC.bJ3Aw7tEo9WW40moc6Kyw598l9KfSF6fPR4E9E8 wMBa63SgtqmbAR_oEl1oz_00A422kpjS36eXUJOsSvdw2293P1.TJBXblRGTpalL.NNMdHjXZFsQ vFtL8a32dkLYOSu37fdgcrf3Swt3pYUazSvZRgxsknxMF9b.n4muGFeHQCoaYG9AyHOQzNy2pycD f9mNChxwaZ08fccF0p1Qk0X0- Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.ne1.yahoo.com with HTTP; Fri, 10 Apr 2020 01:40:57 +0000 Date: Fri, 10 Apr 2020 01:40:52 +0000 (UTC) From: Virendra Kumar Reply-To: Virendra Kumar To: Pgsql-general General Message-ID: <834603375.3470346.1586482852542@mail.yahoo.com> Subject: Different Lock Behavior With Create and Drop Foreign Key MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3470345_1152361666.1586482852540" References: <834603375.3470346.1586482852542.ref@mail.yahoo.com> X-Mailer: WebService/1.1.15620 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0) Gecko/20100101 Firefox/68.0 Content-Length: 3874 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3470345_1152361666.1586482852540 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Team, Here is test case.----create table testx ( =C2=A0=C2=A0=C2=A0 v_code character varying(32), =C2=A0=C2=A0=C2=A0 client_id bigint ); alter table testx add constraint testx_pkey primary key (v_code); create table testy ( =C2=A0=C2=A0=C2=A0 dxid bigint, =C2=A0=C2=A0=C2=A0 v_code character varying(32) ); alter table testy add constraint testy_pkey primary key (dxid);create index= on testy (v_code); Let's begin two session, in session1 I am going begin a transaction and run= select on parent table: Session1----begin;select * from testx; On another session let's call session2, I am running create FK on second ta= ble Session2--- alter table testy add constraint testy_fkey foreign key (v_code= ) references testx(v_code);=C2=A0<--This works.alter table testy drop const= raint testy_fkey; <--Hangs Regards,Virendra Kumar ------=_Part_3470345_1152361666.1586482852540 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Team,

H= ere is test case.
----
create table testx
(
 &= nbsp;  v_code character varying(32),
    client_id b= igint
);
alter table testx add constraint testx_pkey primary key (v_c= ode);

create table testy
(
    dxid bigint,
=     v_code character varying(32)
);
alter table t= esty add constraint testy_pkey primary key (dxid);
create index on testy (v_code);
<= /div>

Let's begin two = session, in session1 I am going begin a transaction and run select on paren= t table:

Session1
----
begin;
select * from testx;=

=

=
On a= nother session let's call session2, I am running create FK on second table<= /span>

<= /span>
Sessi= on2
-= --
alter table testy add constraint testy_fkey foreign key (v_code)= references testx(v_code); <--This works.
alte= r table testy drop constraint testy_fkey; <--Hangs
<= span>
R= egards,
Virendra Kumar

<= /div>
------=_Part_3470345_1152361666.1586482852540--