作者
digoal
日期
2016-02-02
标签
PostgreSQL , 子事务 , command id
背景
PostgreSQL 需要为每个savepoint或者函数的exception section分配子事务号，递增。
即使这个exception没有被触发，也需要一个子事务号。
PushTransaction @ src/backend/access/transam/xact.c
/
* Assign a subtransaction ID, watching out for counter wraparound.
/
currentSubTransactionId += 1;
if (currentSubTransactionId == InvalidSubTransactionId)
{
currentSubTransactionId -= 1;
pfree(s);
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("cannot have more than 2^32-1 subtransactions in a transaction")));
}
```
command id则是记录一个事务中产生写操作(例如ddl,dml)的SQL ID，递增。
CommandCounterIncrement @ src/backend/access/transam/xact.c
if (currentCommandIdUsed)
{
currentCommandId += 1;
if (currentCommandId == InvalidCommandId)
{
currentCommandId -= 1;
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("cannot have more than 2^32-2 commands in a transaction")));
}
currentCommandIdUsed = false;
子事务 id和command id都是unsigned int类型，最大允许分配2^32-1个子事务，单个事务中最大允许分配2^32-2条COMMAND。
typedef uint32 SubTransactionId;
typedef uint32 CommandId;
子事务什么情况下可能溢出呢？
1. 在事务中累计使用的savepoint = n。
2. 在事务中有exception的函数,每个exception需要申请一个子事务，如果函数被多次调用，则需要计算多个子事务。假设函数exception需要的子事务个数=m。
如果n+m大于2^32-1，溢出。
command id什么情况下可能溢出呢？
一个事务中，包含的ddl,dml SQL超过2^32-2时。
跟踪方法：
```
currentCommandId += 1;
// 添加如下
ereport(NOTICE,
(errmsg("currentCommandId: %d", currentCommandId)));
if (currentCommandId == InvalidCommandId)
{
currentCommandId -= 1;
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("cannot have more than 2^32-2 commands in a transaction")));
}
currentCommandIdUsed = false;
...
/
* Assign a subtransaction ID, watching out for counter wraparound.
/
currentSubTransactionId += 1;
// 添加如下
ereport(NOTICE,
(errmsg("currentSubTransactionId: %d", currentSubTransactionId)));
if (currentSubTransactionId == InvalidSubTransactionId)
{
currentSubTransactionId -= 1;
pfree(s);
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("cannot have more than 2^32-1 subtransactions in a transaction")));
}
```
重新编译安装，重启数据库。
psql
设置notice消息级别
postgres=# set client_min_messages='notice';
SET
创建测试函数
postgres=# create or replace function f() returns void as $$
declare
begin
exception
when others then
raise exception 'a';
end;
$$ language plpgsql;
测试子事务号申请。
```
postgres=# select f();
NOTICE: currentSubTransactionId: 2
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
f
(1 row)
```
volatile函数，每条tuple都会触发调用
postgres=# select f() from generate_series(1,10);
NOTICE: currentSubTransactionId: 2
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 3
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 4
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 5
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 6
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 7
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 8
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 9
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 10
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 11
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
没有exception的话，不会产生子事务。
```
postgres=# select * from generate_series(1,10);
generate_series
1 2 3 4 5 6 7 8 9 10复制
(10 rows)
postgres=# create or replace function f1() returns void as $$
postgres$# declare
postgres$# begin
postgres$# end;
postgres$# $$ language plpgsql;
NOTICE: currentCommandId: 1
CREATE FUNCTION
postgres=# select f1() from generate_series(1,10);
f1
(10 rows)
```
接下来跟踪一下command id:
DDL,DML会产生command
postgres=# create table t(id int);
NOTICE: currentCommandId: 1
CREATE TABLE
postgres=# insert into t values (1);
NOTICE: currentCommandId: 2
INSERT 0 1
postgres=# insert into t values (1);
NOTICE: currentCommandId: 3
INSERT 0 1
查询不需要分配command id
```
postgres=# select 1;
?column?
1复制
(1 row)
```
savepoint 产生子事务
postgres=# savepoint a;
NOTICE: currentSubTransactionId: 12
SAVEPOINT
postgres=# savepoint a;
NOTICE: currentSubTransactionId: 13
SAVEPOINT
postgres=# savepoint a;
NOTICE: currentSubTransactionId: 14
SAVEPOINT
postgres=# savepoint a;
NOTICE: currentSubTransactionId: 15
SAVEPOINT
rollback to savepoint 产生子事务
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 16
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 17
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 18
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 19
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 20
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 21
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 22
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 23
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 24
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 25
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 26
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 27
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 28
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 29
ROLLBACK
postgres=# rollback to savepoint a;
NOTICE: currentSubTransactionId: 30
ROLLBACK
postgres=# end;
COMMIT
没有exception的函数不产生子事务：
```
postgres=# create or replace function f() returns void as $$
declare
begin
end;
$$ language plpgsql;
NOTICE: currentCommandId: 1
CREATE FUNCTION
postgres=# select f();
f
(1 row)
```
每个exception都需要分配一个子事务：
```
create or replace function f() returns void as $$
declare
begin
begin
exception when others then
return;
end;
begin
exception when others then
return;
end;
exception when others then
return;
end;
$$ language plpgsql;
postgres=# select f();
NOTICE: currentSubTransactionId: 2
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 3
CONTEXT: PL/pgSQL function f() line 6 during statement block entry
NOTICE: currentSubTransactionId: 4
CONTEXT: PL/pgSQL function f() line 11 during statement block entry
f
(1 row)
```
溢出的例子：
postgres=# select count(*) from (select f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f(),f() from generate_series(1,500000000))t;
ERROR: cannot have more than 2^32-1 subtransactions in a transaction
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
顺带讲一下函数稳定性，以前写过分享。
stable和volatile在一条SQL中，每条tuple都会被触发（实际上stable当传参一样时，不应该被多次触发，这是PG的一个问题）。
immutable则在任何情况下都只调用一次，和stable区别还有，在使用绑定变量时，immutable会自动转换成常量。
postgres=# alter function f() immutable;
ALTER FUNCTION
仅仅触发一次
```
postgres=# select f() from generate_series(1,100);
NOTICE: currentSubTransactionId: 2
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 3
CONTEXT: PL/pgSQL function f() line 6 during statement block entry
NOTICE: currentSubTransactionId: 4
CONTEXT: PL/pgSQL function f() line 11 during statement block entry
f
```
改为stable触发多次
postgres=# alter function f() stable;
ALTER FUNCTION
postgres=# select f() from generate_series(1,100);
NOTICE: currentSubTransactionId: 2
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 3
CONTEXT: PL/pgSQL function f() line 6 during statement block entry
NOTICE: currentSubTransactionId: 4
CONTEXT: PL/pgSQL function f() line 11 during statement block entry
NOTICE: currentSubTransactionId: 5
CONTEXT: PL/pgSQL function f() line 3 during statement block entry
NOTICE: currentSubTransactionId: 6
,,,,,,
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
