Обсуждение: Lock acquisition for partition table when setting generic plan

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