Subject: a lot of session wait on lock relation

From: James Pang <[email-masked]>
Date: Thu, 15 May 2025 16:27:31 +0800

Hi,
   PGv 15.10,  many session coming in doing "insert into tablexxx
values...." in parallel, this is a range partition tables, total 12
partitions plus one default partition, only three btree indexes, no others
constraints ,no foreign key. but we see hundreds of session waiting on
"lock:relation", why inserts into partition table cause "relation lock" ?

Thanks,

James

---

From: Chetan <[email-masked]>
Date: Thu, 15 May 2025 10:31:12 +0100

Hi James,

Could share the related test setup details?
Would like to look into this.

Thanks,
Chetan

-- 
-- 
Regards,
Chetan

+353899475147
+919665562626

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 15 May 2025 14:08:05 +0200

On Thu, 2025-05-15 at 16:27 +0800, James Pang wrote:
> PGv 15.10,  many session coming in doing "insert into tablexxx values...." in parallel,
> this is a range partition tables, total 12 partitions plus one default partition,
> only three btree indexes, no others constraints ,no foreign key. but we see hundreds
> of session waiting on "lock:relation", why inserts into partition table cause "relation lock" ?

Something else does; use the pg_blocking_pids() function with the process ID of
a blocked backend to find out who is holding the lock.

If you have hundreds of sessions, you are allowing to many connections.

Yours,
Laurenz Albe

---

From: Tom Lane <[email-masked]>
Date: Thu, 15 May 2025 09:24:30 -0400

Laurenz Albe <[email-masked]> writes:
>> why inserts into partition table cause "relation lock" ?

> a blocked backend to find out who is holding the lock.

More specifically: the inserts are only trying to get a shared lock.
If they are blocked, it's because some other operation is already
holding an exclusive lock on the table and is not letting go.
Look for uncommitted DDL changes.

More details about that at [1].

			regards, tom lane

[1] https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES

---

From: James Pang <[email-masked]>
Date: Thu, 15 May 2025 21:32:38 +0800

thanks, we are checking  partition   maintain jobs ,that hold access
exclusive lock.

Tom Lane <[email-masked]> 於 2025年5月15日週四 下午9:24寫道：

> [1]
> https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES
>
>
>

---

From: James Pang <[email-masked]>
Date: Thu, 15 May 2025 21:43:17 +0800

Thanks, we are checking the partition maintain job , we have 12 partitions
, each week one partition there is a default partition attached with this
table and huge rows in default partition too , default partition has
primary key that include partition key (time range based) too.  partition
job detach/drop old partition and add/attach new partition each week.
  when add/attach new partition , with default partition to verify that it
contains no records which should be located in the partition being
attached,  it will full scan or use partition key(part of primary key) scan
instead ?  primary key (columa, partitionkey).

Thanks,

James