Lock acquisition for partition table when setting generic plan
| Newsgroups | gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <OSBPR01MB17999A0FB716887538A340AAF6370@OSBPR01MB1799.jpnprd01.prod.outlook.com> |
Hi all,
I have a question.
I did the following.
Even though I accessed one partition table(test2 table), I also acquired lo=
cks on other partition tables(test1 table).
I expected to acquire locks on the parent table(test table) and the partiti=
on table to access(test2 table).
Why does this happen?
At the first execution, to create a generic plan, I thought it was accessin=
g all partition tables.
However, the following event occur after second time too.
* Only occurs when plan_cache_mode =3D force_generic_plan.
postgres=3D# create table test(id int) partition by range (id);
CREATE TABLE
postgres=3D# create table test1 partition of test for values from (1) to (2=
);
CREATE TABLE
postgres=3D# create table test2 partition of test for values from (2) to (3=
);
CREATE TABLE
postgres=3D# prepare hoge(int) as select * from test where id =3D $1;
PREPARE
postgres=3D# set plan_cache_mode =3D force_generic_plan ;
SET
postgres=3D# begin;
BEGIN
postgres=3D# execute hoge(2);
id=20
----
(0 rows)
postgres=3D# 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 =3D d.oid WHERE l.pid !=3D pg_backend_pid() ORDER BY l.pid;
pid | granted | datname | locktype | relation | relation | transactio=
nid | mode =20
-------+---------+----------+------------+----------+----------+-----------=
----+-----------------
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)
=20
Regards
Naoki Yotsunaga