0

Is constraint exclusion possible during inserts? 

create table parent(id int)

    create table c1 ( constraint  c1_constarint CHECK (id = 1)) inherits parent
    create table c2 ( constraint  c2_constarint CHECK (id = 2)) inherits parent
    create table c3 ( constraint  c3_constarint CHECK (id = 3)) inherits parent

when doing insert into c1 values(1) it is acquiring RowExclusive locks on all children(c2, c3)

Is there a way can we do constraint exclusion during insert? Or any hints available to perform this action

Giri
  • 1
  • 2
  • Why is the `ROW EXCLUSIVE LOCK` a problem? – Laurenz Albe Aug 13 '18 at 07:05
  • I'm running 2 sessions concurrently, 1st sessions: Truncate c1; Insert into c1;- Truncate is creating AccessExclusive and insert checking all Childs with RowExclusive Lock. 2nd Session: Truncate c2; Insert into c2; - Truncate is creating AccessExclusive on c2 and Insert is trying to create RowExclusive on all Childs. In this scenario Session 2 c1 and Session 1 c2 RowExclusive locks are colliding. This is creating deadlock. – Giri Aug 13 '18 at 18:12
  • I just tried on v10, and inserting into `c1` only locks `c1`. Can you come up with a reproducible test case? – Laurenz Albe Aug 14 '18 at 06:22
  • The version in which I'm running is 8.2, maybe its corrected going forward? – Giri Aug 14 '18 at 15:15
  • 8.2?? You tagged 9.3. 8.2 has been dead for ~7 years. – Laurenz Albe Aug 14 '18 at 20:25

0 Answers0