PostgreSQL 子事务 id & command id 溢出问题分析-CSDN博客

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

---

(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

-----------------

(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);

----

(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 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();

---

(1 row)

每个exception都需要分配一个子事务：

create or replace function f() returns void as $$

declare

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

---

(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

---

改为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

,,,,,,