PostgreSQL 子事务 id & command id 溢出问题分析

4 minute read

背景

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  
,,,,,,  

digoal’s 大量PostgreSQL文章入口

Twitter Facebook Google+ LinkedIn

Digoal.zhou

PostgreSQL 子事务 id & command id 溢出问题分析

背景

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

PostgreSQL plpgsql 存储过程、函数 - 状态、异常变量打印、异常捕获… - GET [STACKED] DIAGNOSTICS

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法