From: | Ed Behn <ed(dot)behn(at)rockwellcollins(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Partitioned Data and Locking |
Date: | 2017-05-19 15:58:25 |
Message-ID: | CAE1kc7Vmc2NQ-Tv_2u6X3MtXx0evNGtq+7n792+oN8woWS43mw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I've found what to me is a surprising locking behavior when querying
partitioned data as described in section 5.10 of the User's Manual.
I have an empty parent table with a number of child tables containing data.
Each child has a CHECK condition on the relevant column. I am executing a
SELECT query against the parent table with a condition on the column in the
CHECK in the WHERE clause.
I have constraint_exclusion set to partition.
If I run EXPLAIN on the query, I get a result that shows that only the
child tables whose CHECKs are consistent with the WHERE clause are
searched. This is exactly what I expected.
However, when I run the query, AccessShareLocks are obtained by the
transaction for all child tables (and their indices).
Am I misunderstanding something? I seems that these locks shouldn't exist
if the query plan doesn't use most of the child tables.
If this is a bug, perhaps it could be fixed in a future release. I would be
beneficial to my application, as we most often are only writing to one
partition in any given day. If a query against older data is running, the
write transaction could still proceed.
-Ed
--
Ed Behn / Staff Engineer / Airline and Network Services
Information Management Services
2551 Riva Road, Annapolis, MD 21401 USA
Phone: 410-266-4426 / Cell: 240-696-7443
ed(dot)behn(at)rockwellcollins(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | cen | 2017-05-19 16:26:03 | Re: Weird periodical pg log |
Previous Message | Jeff Janes | 2017-05-19 15:22:52 | Re: Encrypt with BouncyCastle and decrypt with pg_pub_decrypt |