创建分区
#创建分区分区表
postgres=# create table t_partition (p timestamp,v1 varchar(2),v2 text) partition by range ( p);
CREATE TABLE
# 创建三个分区
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
验证1:查询某几个分区锁会加多少锁
session1 pid=12962 开启事务,查询分区p0
postgres=# begin;
BEGIN
postgres=*# select *from t_partition where p>='2022-3-13' and p<'2022-3-14';
p | v1 | v2
---+----+----
(0 rows)
查看锁情况
发现t_partition和分区p0加上了锁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;
此处卡主
查看锁情况
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
会话1:加上了两把锁,主表AccessShareLock和一个子分区AccessShareLock
结论:在查询分区表时,主表以及查询范围内的子分区都会被加上AccessSharelock锁。阻止其他级别的互斥锁,比如truncate锁
验证2:对分区表查询过程是否可以正常增加分区
session1保持不动
session2 增肌分区操作
postgres=# create table p3 partition of t_partition for values from ( '2022-3-16') to ('2022-3-17');
此处卡主
查看锁情况
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)
会话2可以发现 :
(1)增加分区时,会在主表t_partition上加AccessExclusiveLock,新对象(p3)的锁
AccessShareLock,以及事务锁ExclusiveLock。
(2) pid=34336多了3把锁,并且Mode=AccessExclusiveLock的锁模式的
granted=f,即该锁和会话1的锁AccessShareLock冲突,导致等待。
结论:
(1)在查询分区表时候,主表以及查询范围内的子分区都会增加AccessSharelock锁,无法新增分区。
(2)在增加分区过程中,会对主表加AccessExclusiveLock,导致其他会话无法通过主表访问子分区表(直接访问分区表没有问题)。
比如:
会话1创建分区期间:
create table p4 partition of t_partition for values from ( '2022-3-17') to ('2022-3-18');
会话2通过主表查询子分区:该会话必须等待
select *from t_partition where p>='2022-3-13' and p<'2022-3-14';
会话3直接查询子分区:没有等待
select * from p0;
如何在主表查询过程中增加分区
session1保持
sesssion2增加分区
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
增加分区完成
查看锁情况
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)
会话2多了t_partition的锁ShareUpdateExclusiveLock,和p4的AccessExclusiveLock。
由于ShareUpdateExclusiveLock和会话1的AccessShareLock不互斥可以增加成功。
之所以分区可以增加成功,是以为在attach时候对主表增加了ShareUpdateExclusiveLock。
分区表truncate的影响
session1(查询分区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 分区p1):
postgres=*# truncate table p1;
TRUNCATE TABLE
没有卡主
session3(truncate 分区p0):
postgres=*# truncate table p0;
卡主
结论:在查询范围的分区的同时,如果truncate不重叠的分区,则不会有锁。
反过来,假如先truncate指定分区,查询只要不查询该分区就不会阻塞。
分区表修改表空间对表访问的影响
session1:
postgres=# begin;
BEGIN
postgres=*# alter table t_partition set tablespace pg_default ;
ALTER TABLE
--锁情况
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(通过主表访问分区):
postgres=# select * from t_partition where p>='2022-3-13' and p<'2022-3-14';
卡主
session3(直接访问分区):
postgres=# select * from p0;
p | v1 | v2
---------------------+----+------
2022-03-13 00:00:00 | 1 | aaaa
(1 row)
未被卡主
结论:在分区表修改表空间的情况下,直接访问分区不会被阻塞。而通过表访问分区会被阻塞。
分区表修改表空间
默认情况下都是在pg_default表空间下
--p0分区插入数据
postgres=# insert into t_partition select '2022-3-13','aa' from generate_series(1,100000) as id;
INSERT 0 100000
--p1分区插入数据
postgres=# insert into t_partition select '2022-3-14','aa' from generate_series(1,100000) as id;
INSERT 0 100000
1 修改主表的表空间,发现没有占用空间,也就是说子表的表空间仍是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 修改p0表空间,发现p0的数据已经到了指定的表空间
postgres=# alter table t_partition set tablespace tbs;
ALTER TABLE
[pg13@sdw2 13580]$ du -sh
8.5M .
3 修改p1的表表空间,发现p1的数据已经到了指定的表空间
postgres=# alter table p1 set tablespace tbs;
ALTER TABLE
[pg13@sdw2 13580]$ du -sh
13M .---从8.5M变成13M
结论:
如果修改分区表的表空间,需要对各个分区子表都修改,不然只会修改主表的表空间而子表还是在默认表空间
查询日分区使用current_date的雷
select * from t_partition,pg_sleep(100) where p=current_date
这种情况下有多少个分区,就会有都多少个accesssharelock锁,这样会造成很多个共享锁
select * from t_partition,pg_sleep(100) where p=‘2022-5-11’::date
这样只对总表和指定分区表加accessharelock锁
表(分区表)和MAX_LOCKS_PER_TRANSACTION
我们修改max_connection和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)
#创建1000个分区表
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: 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
...............
我们可以在共享内存中保留的锁的数量是 max_connections x max_locks_per_ transaction=110。请记住,行级别的锁与此无关。如果超过一定的数量会报错。
参考:https://www.cybertec-postgresql.com/en/postgresql-you-might-need-to-increase-max_locks_per_transaction/