Thread: Lock acquisition for partition table when setting generic plan
Lock acquisition for partition table when setting generic plan
From
"yotsunaga.naoki@fujitsu.com"
Date:
Hi all, I have a question. I did the following. Even though I accessed one partition table(test2 table), I also acquired locks on other partition tables(test1 table). I expected to acquire locks on the parent table(test table) and the partition table to access(test2 table). Why does this happen? At the first execution, to create a generic plan, I thought it was accessing all partition tables. However, the following event occur after second time too. * Only occurs when plan_cache_mode = force_generic_plan. postgres=# create table test(id int) partition by range (id); CREATE TABLE postgres=# create table test1 partition of test for values from (1) to (2); CREATE TABLE postgres=# create table test2 partition of test for values from (2) to (3); CREATE TABLE postgres=# prepare hoge(int) as select * from test where id = $1; PREPARE postgres=# set plan_cache_mode = force_generic_plan ; SET postgres=# begin; BEGIN postgres=# execute hoge(2); id ---- (0 rows) postgres=# SELECT l.pid,l.granted,d.datname,l.locktype,relation,relation::regclass,transactionid,l.mode FROM pg_locks l LEFT JOIN pg_database d ON l.database = d.oid WHERE l.pid != pg_backend_pid() ORDER BY l.pid; pid | granted | datname | locktype | relation | relation | transactionid | mode -------+---------+----------+------------+----------+----------+---------------+----------------- 16921 | t | postgres | relation | 16562 | test2 | | AccessShareLock 16921 | t | postgres | relation | 16559 | test1 | | AccessShareLock 16921 | t | postgres | relation | 16556 | test | | AccessShareLock 16921 | t | | virtualxid | | | | ExclusiveLock (4 rows) Regards Naoki Yotsunaga
"yotsunaga.naoki@fujitsu.com" <yotsunaga.naoki@fujitsu.com> writes: > I did the following. > Even though I accessed one partition table(test2 table), I also acquired locks on other partition tables(test1 table). > I expected to acquire locks on the parent table(test table) and the partition table to access(test2 table). > Why does this happen? You specified a generic plan: > postgres=# set plan_cache_mode = force_generic_plan ; so you are not going to get any plan-time optimization based on knowing the id parameter. Therefore the plan must include sub-plan nodes for every child table, so executing it requires locking all those tables to make sure their schemas haven't changed. regards, tom lane