| From: | "yotsunaga(dot)naoki(at)fujitsu(dot)com" <yotsunaga(dot)naoki(at)fujitsu(dot)com> |
|---|---|
| To: | Pgsql-general <pgsql-general(at)postgresql(dot)org> |
| Subject: | Lock acquisition for partition table when setting generic plan |
| Date: | 2020-01-15 02:58:45 |
| Message-ID: | OSBPR01MB17999A0FB716887538A340AAF6370@OSBPR01MB1799.jpnprd01.prod.outlook.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Laurenz Albe | 2020-01-15 06:34:22 | Re: Lock leaking out of Transaction? |
| Previous Message | Michael Paquier | 2020-01-15 02:53:30 | Re: Fwd: Postgresql Data corruption |