WITH and exclusive locks

From: Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: WITH and exclusive locks
Date: 2013-11-23 12:16:28
Message-ID: CAFS1N4h1PfYZrEKbp-3FHXEZoE4NN6u2WskGRm50Vsf6ae0Y=Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

When conducting performance tests of our application, I saw quite a few
exclusive locks in SELECTs with WITH clause. So I created a big table and
executed a query which takes time.
with x as (select * from emp where first_name like 'Sco%')
select * from x;
I got 2 locks - one accesshare lock against the table and one ExclusiveLock
with out any database/relation.
2417 postgres 10/95 10/95 ExclusiveLock Yes
2013-11-23 17:38:58+05:30

My guess is that the Exclusive lock is against some in-memory structure
(the temp table created by the WITH clause) and hence is not an issue. Is
that correct?
Regards,
Jayadevan

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2013-11-23 15:11:39 Re: Getting non_NULL right-side values on a non-matching join?
Previous Message Alban Hertroys 2013-11-23 10:48:36 Re: Recursive function