Create partition
#Create partition partition table
postgres=# create table t_partition (p timestamp,v1 varchar(2),v2 text) partition by range (p);
CREATE TABLE
#Create three partitions
postgres=# create table p0 partition of t_partition for values from ('2022-3-13') to ('2022-3-14');
CREATE TABLE
postgres=# create table p1 partition of t_partition for values from ('2022-3-14') to ('2022-3-15');
CREATE TABLE
postgres=# create table p2 partition of t_partition for values from ('2022-3-15') to ('2022-3-16');
CREATE TABLE
Verification 1: Query how many locks will be added to certain partition locks
session1 pid=12962 starts the transaction and queries the partition p0
postgres=# begin; BEGIN postgres=*# select *from t_partition where p>='2022-3-13' and p<'2022-3-14'; p | v1 | v2 --- + ---- + ---- (0 rows)
Check lock status
It was found that t_partition and partition p0 were locked with accesssharelock
postgres=# select relation::regclass,locktype,transactionid,pid,mode,granted from pg_locks;
relation | locktype | transactionid | pid | mode | granted
------------- + ------------ + --------------- + ------- + ----------------- + ---------
p0 | relation | | 12962 | AccessShareLock | t
t_partition | relation | | 12962 | AccessShareLock | t
| virtualxid | | 12962 | ExclusiveLock | t
pg_locks | relation | | 23195 | AccessShareLock | t
| virtualxid | | 23195 | ExclusiveLock | t
(5 rows)
sesssion2 pid=34336 truncate p0
postgres=# truncate table p0; Card owner here
Check lock status
postgres=# select relation::regclass,locktype,transactionid,pid,mode,granted from pg_locks;
relation | locktype | transactionid | pid | mode | granted
------------- + --------------- + --------------- + ---- --- + --------------------- + ---------
| virtualxid | | 34336 | ExclusiveLock | t
t_partition | relation | | 12962 | AccessShareLock | t
| virtualxid | | 12962 | ExclusiveLock | t
pg_locks | relation | | 23195 | AccessShareLock | t
| virtualxid | | 23195 | ExclusiveLock | t
p0 | relation | | 12962 | AccessShareLock | t
p0 | relation | | 34336 | AccessExclusiveLock | f
| transactionid | 20397 | 34336 | ExclusiveLock | t
Session 1: Two locks were added, the main table AccessShareLock and a subpartition AccessShareLock
Conclusion: When querying a partitioned table, the main table and sub-partitions within the query range will be locked by AccessSharelock. Prevent other levels of mutex locks, such as truncate locks
Verification 2: Whether partitions can be added normally during the query process of the partition table
session1 remains unchanged
session2 Muscle Building Partition Operation
postgres=# create table p3 partition of t_partition for values from ('2022-3-16') to ('2022-3-17');
Card owner here
Check the lock status
postgres=# select relation::regclass,locktype,transactionid,pid,mode,granted from pg_locks;
relation | locktype | transactionid | pid | mode | granted
------------- + --------------- + --------------- + ---- --- + --------------------- + ---------
| virtualxid | | 34336 | ExclusiveLock | t
| virtualxid | | 12962 | ExclusiveLock | t
pg_locks | relation | | 23195 | AccessShareLock | t
| virtualxid | | 23195 | ExclusiveLock | t
p0 | relation | | 12962 | AccessShareLock | t
t_partition | relation | | 12962 | AccessShareLock | t
| transactionid | 20398 | 34336 | ExclusiveLock | t
t_partition | relation | | 34336 | AccessExclusiveLock | f
| object | | 34336 | AccessShareLock | t
(9 rows)
Session 2 can be found:
(1) When adding a partition, AccessExclusiveLock will be added to the main table t_partition, the lock of the new object (p3)
AccessShareLock, and transaction lock ExclusiveLock.
(2) pid=34336 has 3 more locks, and Mode=AccessExclusiveLock lock mode
granted=f, that is, the lock conflicts with the AccessShareLock of session 1, resulting in waiting.
in conclusion:
(1) When querying a partition table, AccessSharelock locks will be added to the main table and sub-partitions within the query range, and new partitions cannot be added.
(2) During the process of adding partitions, AccessExclusiveLock will be added to the main table, causing other sessions to be unable to access the sub-partition table through the main table (direct access to the partition table is no problem).
for example:
During session 1 partition creation:
create table p4 partition of t_partition for values from ('2022-3-17') to ('2022-3-18');
Session 2 queries the subpartition through the main table: the session must wait
select *from t_partition where p>='2022-3-13' and p<'2022-3-14';
Session 3 queries subpartitions directly: no wait
select * from p0;
How to add partitions during main table query
session1 remains
sesssion2 adds partition
ppostgres=# create table p4(like t_partition including defaults including constraints);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=*# alter table t_partition attach partition p4 for values from ('2022-3-17') to ('2022-3-18');
ALTER TABLE
Add partition completed
Check lock status
postgres=# select relation::regclass,locktype,transactionid,pid,mode,granted from pg_locks;
relation | locktype | transactionid | pid | mode | granted
------------- + --------------- + --------------- + ---- --- + -------------------------- + ---------
| virtualxid | | 34336 | ExclusiveLock | t
p0 | relation | | 12962 | AccessShareLock | t
t_partition | relation | | 12962 | AccessShareLock | t
| virtualxid | | 12962 | ExclusiveLock | t
pg_locks | relation | | 23195 | AccessShareLock | t
| virtualxid | | 23195 | ExclusiveLock | t
| transactionid | 20405 | 34336 | ExclusiveLock | t
t_partition | relation | | 34336 | ShareUpdateExclusiveLock | t
p4 | relation | | 34336 | AccessExclusiveLock | t
(9 rows)
Session 2 has additional locks ShareUpdateExclusiveLock of t_partition and AccessExclusiveLock of p4.
Since ShareUpdateExclusiveLock and Session 1's AccessShareLock are not mutually exclusive the success can be increased.
The reason why the partition can be added successfully is that ShareUpdateExclusiveLock is added to the main table during attach.
The impact of partition table truncate
session1 (query partition p0):
postgres=# begin;
BEGIN
postgres=*# select * from t_partition where p>='2022-3-13' and p<'2022-3-14';
p | v1 | v2
--------------------- + ---- + ------
2022-03-13 00:00:00 | 1 | aaaa
(1 row)
postgres=*# select relation::regclass,mode,pid from pg_locks;
relation | mode | pid
------------- + ----------------- + -------
pg_locks | AccessShareLock | 64609
p0 | AccessShareLock | 64609
t_partition | AccessShareLock | 64609
| ExclusiveLock | 64609
(4 rows)
session2(truncate partition p1):
postgres=*# truncate table p1;
TRUNCATE TABLE
No card owner
session3(truncate partition p0):
postgres=*# truncate table p0;
card owner
Conclusion: While querying partitions of a range, if you truncate non-overlapping partitions, there will be no locks.
On the other hand, if you truncate the specified partition first, the query will not block as long as it does not query the partition.
The impact of modifying the table space of a partitioned table on table access
session1:
postgres=# begin;
BEGIN
postgres=*# alter table t_partition set tablespace pg_default;
ALTER TABLE
--Lock situation
postgres=# select relation::regclass,mode,pid from pg_locks;
relation | mode | pid
------------- + -------------------------- + -------
| ExclusiveLock | 65011
pg_locks | AccessShareLock | 64609
| ExclusiveLock | 64609
t_partition | AccessExclusiveLock | 65011
| ExclusiveLock | 65011
(5 rows)
session2 (access partition through main table):
postgres=# select * from t_partition where p>='2022-3-13' and p<'2022-3-14';
card owner
session3 (direct access to partition):
postgres=# select * from p0;
p | v1 | v2
--------------------- + ---- + ------
2022-03-13 00:00:00 | 1 | aaaa
(1 row)
Unblocked
Conclusion: In the case where the partition table modifies the table space, direct access to the partition will not be blocked. Accessing partitions through tables will be blocked.
Partition table modification table space
By default, it is under the pg_default tablespace. --Insert data into p0 partition postgres=# insert into t_partition select '2022-3-13','aa' from generate_series(1,100000) as id; INSERT 0 100000 --Insert data into p1 partition postgres=# insert into t_partition select '2022-3-14','aa' from generate_series(1,100000) as id; INSERT 0 100000 1 Modify the table space of the main table and find that there is no occupied space, which means that the table space of the sub table is still pg_default postgres=# alter table t_partition set tablespace tbs; ALTER TABLE [pg13@sdw2 13580]$ pwd /home/pg13/tbs/pg13/PG_13_202007201/13580 [pg13@sdw2 13580]$ du -sh 0. 2 Modify the p0 table space and find that the data of p0 has reached the specified table space. postgres=# alter table t_partition set tablespace tbs; ALTER TABLE [pg13@sdw2 13580]$ du -sh 8.5M. 3 Modify the table space of p1 and find that the data of p1 has reached the specified table space. postgres=# alter table p1 set tablespace tbs; ALTER TABLE [pg13@sdw2 13580]$ du -sh 13M .---from 8.5M to 13M
in conclusion:
If you modify the table space of a partitioned table, you need to modify each partition sub-table. Otherwise, only the table space of the main table will be modified and the sub-table will still be in the default table space.
Query the day partition using current_date
select * from t_partition,pg_sleep(100) where p=current_date In this case, there will be as many accesssharelock locks as there are partitions, which will result in many shared locks. select * from t_partition,pg_sleep(100) where p=2022-5-11’::date In this way, accessharelock is only added to the main table and the specified partition table.
Table (partitioned table) and MAX_LOCKS_PER_TRANSACTION
We modify max_connection and MAX_LOCKS_PER_TRANSACTION
postgres=# show max_connections;
max_connections
------------------
10
(1 row)
postgres=# show max_locks_per_transaction;
max_locks_per_transaction
--------------------------
11
(1 row)
#Create 1000 partition tables
BEGIN;
CREATE TABLE t_part (id int) PARTITION BY LIST (id);
SELECT 'CREATE TABLE t_part_' || id
|| ' PARTITION OF t_part FOR VALUES IN ('
|| id || ');'
FROM generate_series(1, 1000) AS id;
\gexec
...........
CREATE TABLE
CREATE TABLE
CREATE TABLE
#Error reported during creation
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
.............
The number of locks we can keep in shared memory is max_connections x max_locks_per_transaction=110. Remember, row-level locks have nothing to do with this. If it exceeds a certain number, an error will be reported.
Reference: https://www.cybertec-postgresql.com/en/postgresql-you-might-need-to-increase-max_locks_per_transaction/