Search Postgresql Archives

Partitioned Data and Locking

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.behn@xxxxxxxxxxxxxxxxxxx



www.rockwellcollins.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux