为什么这个 PostgreSQL 事务给出“警告：没有正在进行的事务”

Why does this PostgreSQL transaction give "WARNING: there is no transaction in progress"

提问by Nihat

I am running the transaction queries in code like this (simplified):

我在这样的代码中运行事务查询（简化）：

try {
    runQuery("begin");
    runQuery("some query ...");
    runQuery("some other query ...");
    runQuery("some more query ...");
    runQuery("some extra query ...");
    runQuery("commit");
} catch (e){
    runQuery("rollback");
}

Here is the list of statements from the postgres log file.
Notice that first begin / commit works fine and the second commit gives the error mentioned in the subject:

这是 postgres 日志文件中的语句列表。
请注意，第一次开始/提交工作正常，第二次提交给出了主题中提到的错误：

2015-02-18 20:19:17 UTC [6459-7] vdsr@sails LOG:  statement: begin
2015-02-18 20:19:17 UTC [6459-8] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "groups" ("name", "parentGroupRef", "isCompany", "companyRef", "createdAt", "updatedAt") values (, , , , , ) RETURNING *
2015-02-18 20:19:17 UTC [6459-9] vdsr@sails DETAIL:  parameters:  = 'testclient',  = '5',  = 't',  = '1',  = '2015-02-18 20:19:17+00',  = '2015-02-18 20:19:17+00'
2015-02-18 20:19:17 UTC [6459-10] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "users" ("firstName", "lastName", "email", "companyRef", "isMainUser", "password", "middleName", "createdAt", "updatedAt") values (, , , , , , , , ) RETURNING *
2015-02-18 20:19:17 UTC [6459-11] vdsr@sails DETAIL:  parameters:  = 'aa',  = 'bb',  = '[email protected]',  = '18',  = 't',  = '06a8ec164adcc7db4edfb6ca20c07b20',  = '',  = '2015-02-18 20:19:17+00',  = '2015-02-18 20:19:17+00'
2015-02-18 20:19:17 UTC [6459-12] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "roles" ("name", "companyRef", "createdAt", "updatedAt") values (, , , ) RETURNING *
2015-02-18 20:19:17 UTC [6459-13] vdsr@sails DETAIL:  parameters:  = 'Master',  = '18',  = '2015-02-18 20:19:17+00',  = '2015-02-18 20:19:17+00'
2015-02-18 20:19:17 UTC [6459-14] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "userroles" ("roleRef", "userRef", "createdAt", "updatedAt") values (, , , ) RETURNING *
2015-02-18 20:19:17 UTC [6459-15] vdsr@sails DETAIL:  parameters:  = '11',  = '13',  = '2015-02-18 20:19:17+00',  = '2015-02-18 20:19:17+00'
2015-02-18 20:19:17 UTC [6459-16] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "rolepermissions" ("roleRef", "permissionRef", "createdAt", "updatedAt") values (, , , ) RETURNING *
2015-02-18 20:19:17 UTC [6459-17] vdsr@sails DETAIL:  parameters:  = '11',  = '24',  = '2015-02-18 20:19:17+00',  = '2015-02-18 20:19:17+00'
2015-02-18 20:19:17 UTC [6459-18] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "rolepermissions" ("roleRef", "permissionRef", "createdAt", "updatedAt") values (, , , ) RETURNING *
2015-02-18 20:19:17 UTC [6459-19] vdsr@sails DETAIL:  parameters:  = '11',  = '22',  = '2015-02-18 20:19:17+00',  = '2015-02-18 20:19:17+00'
2015-02-18 20:19:17 UTC [6459-20] vdsr@sails LOG:  execute <unnamed>: SELECT "groups"."name", "groups"."notes", "groups"."parentGroupRef", "groups"."isCompany", "groups"."hierPos", "groups"."companyRef", "groups"."id", "groups"."createdAt", "groups"."updatedAt" FROM "groups" AS "groups"  WHERE "groups"."companyRef" =  
2015-02-18 20:19:17 UTC [6459-21] vdsr@sails DETAIL:  parameters:  = '1'
2015-02-18 20:19:17 UTC [6461-1] vdsr@sails LOG:  statement:  update groups set "hierPos" = case id when 2 then '1' when 3 then '2' when 4 then '3' when 5 then '4' when 6 then '4.1' when 10 then '4.1.1' when 18 then '4.2' when 13 then '5' when 17 then '6' else "hierPos" end  where id in (2,3,4,5,6,10,18,13,17) 
2015-02-18 20:19:17 UTC [6459-22] vdsr@sails LOG:  statement: commit
2015-02-18 20:26:08 UTC [6477-1] vdsr@sails LOG:  execute <unnamed>: SELECT "users"."firstName", "users"."middleName", "users"."lastName", "users"."email", "users"."password", "users"."resetKey", "users"."resetKeyGeneratedAt", "users"."isMainUser", "users"."companyRef", "users"."id", "users"."createdAt", "users"."updatedAt" FROM "users" AS "users"  WHERE "users"."id" =   LIMIT 1
2015-02-18 20:26:08 UTC [6477-2] vdsr@sails DETAIL:  parameters:  = '8'
2015-02-18 20:26:08 UTC [6477-3] vdsr@sails LOG:  execute <unnamed>: select p.name from permissions p  inner join rolepermissions rp on rp."permissionRef" = p.id   inner join roles r on r.id = rp."roleRef"  inner join userroles ur on ur."roleRef" = r.id  where ur."userRef" = 
2015-02-18 20:26:08 UTC [6477-4] vdsr@sails DETAIL:  parameters:  = '8'
2015-02-18 20:26:08 UTC [6477-5] vdsr@sails LOG:  statement: begin
2015-02-18 20:26:08 UTC [6477-6] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "groups" ("name", "isCompany", "parentGroupRef", "companyRef", "createdAt", "updatedAt") values (, , , , , ) RETURNING *
2015-02-18 20:26:08 UTC [6477-7] vdsr@sails DETAIL:  parameters:  = 'ddffdfd',  = 't',  = '1',  = '1',  = '2015-02-18 20:26:08+00',  = '2015-02-18 20:26:08+00'
2015-02-18 20:26:08 UTC [6477-8] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "users" ("firstName", "lastName", "email", "companyRef", "isMainUser", "password", "middleName", "createdAt", "updatedAt") values (, , , , , , , , ) RETURNING *
2015-02-18 20:26:08 UTC [6477-9] vdsr@sails DETAIL:  parameters:  = 'fdfd',  = 'fdfd',  = '[email protected]',  = '19',  = 't',  = '7b9e87b1795e4d96bf9fbb898fa669fe',  = '',  = '2015-02-18 20:26:08+00',  = '2015-02-18 20:26:08+00'
2015-02-18 20:26:08 UTC [6477-10] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "roles" ("name", "companyRef", "createdAt", "updatedAt") values (, , , ) RETURNING *
2015-02-18 20:26:08 UTC [6477-11] vdsr@sails DETAIL:  parameters:  = 'Master',  = '19',  = '2015-02-18 20:26:08+00',  = '2015-02-18 20:26:08+00'
2015-02-18 20:26:08 UTC [6477-12] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "userroles" ("roleRef", "userRef", "createdAt", "updatedAt") values (, , , ) RETURNING *
2015-02-18 20:26:08 UTC [6477-13] vdsr@sails DETAIL:  parameters:  = '12',  = '14',  = '2015-02-18 20:26:08+00',  = '2015-02-18 20:26:08+00'
2015-02-18 20:26:08 UTC [6477-14] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "rolepermissions" ("roleRef", "permissionRef", "createdAt", "updatedAt") values (, , , ) RETURNING *
2015-02-18 20:26:08 UTC [6477-15] vdsr@sails DETAIL:  parameters:  = '12',  = '24',  = '2015-02-18 20:26:08+00',  = '2015-02-18 20:26:08+00'
2015-02-18 20:26:08 UTC [6477-16] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "rolepermissions" ("roleRef", "permissionRef", "createdAt", "updatedAt") values (, , , ) RETURNING *
2015-02-18 20:26:08 UTC [6477-17] vdsr@sails DETAIL:  parameters:  = '12',  = '22',  = '2015-02-18 20:26:08+00',  = '2015-02-18 20:26:08+00'
2015-02-18 20:26:08 UTC [6477-18] vdsr@sails LOG:  execute <unnamed>: INSERT INTO "rolepermissions" ("roleRef", "permissionRef", "createdAt", "updatedAt") values (, , , ) RETURNING *
2015-02-18 20:26:08 UTC [6477-19] vdsr@sails DETAIL:  parameters:  = '12',  = '23',  = '2015-02-18 20:26:08+00',  = '2015-02-18 20:26:08+00'
2015-02-18 20:26:08 UTC [6477-20] vdsr@sails LOG:  execute <unnamed>: SELECT "groups"."name", "groups"."notes", "groups"."parentGroupRef", "groups"."isCompany", "groups"."hierPos", "groups"."companyRef", "groups"."id", "groups"."createdAt", "groups"."updatedAt" FROM "groups" AS "groups"  WHERE "groups"."companyRef" =  
2015-02-18 20:26:08 UTC [6477-21] vdsr@sails DETAIL:  parameters:  = '1'
2015-02-18 20:26:08 UTC [6478-1] vdsr@sails LOG:  statement:  update groups set "hierPos" = case id when 2 then '1' when 3 then '2' when 4 then '3' when 5 then '4' when 6 then '4.1' when 10 then '4.1.1' when 18 then '4.2' when 13 then '5' when 17 then '6' when 19 then '7' else "hierPos" end  where id in (2,3,4,5,6,10,18,13,17,19) 
2015-02-18 20:26:08 UTC [6479-1] vdsr@sails LOG:  statement: commit
2015-02-18 20:26:08 UTC [6479-2] vdsr@sails WARNING:  there is no transaction in progress

采纳答案by Nihat

This is caused by the sails-postgresql node module I am using. It is causing a new session to be created in the middle of my transaction queries and so the commit query was being issued by a different session. That was causing the warning.

这是由我使用的sails-postgresql 节点模块引起的。它导致在我的事务查询中间创建一个新会话，因此提交查询是由不同的会话发出的。这就是导致警告的原因。

回答by Erwin Brandstetter

This seems to be a misunderstanding. Consider my bold emphasis:

这似乎是一种误解。考虑一下我大胆的强调：

[6459-7]vdsr@sails LOG: statement: begin
[6459-8]vdsr@sails LOG: execute : INSERT INTO "groups" ("name", "parentGroupRef", "isCompany", "companyRef", "createdAt", "updatedAt") values ($1, $2, $3, $4, $5, $6) RETURNING *
...
[6459-22]vdsr@sails LOG: statement: commit
...
[6477-5]vdsr@sails LOG: statement: begin
[6477-6]vdsr@sails LOG: execute : INSERT INTO "groups" ("name", "isCompany", "parentGroupRef", "companyRef", "createdAt", "updatedAt") values ($1, $2, $3, $4, $5, $6) RETURNING *
...
[6479-1]vdsr@sails LOG: statement: commit
[6479-2]vdsr@sails WARNING: there is no transaction in progress

[6459-7]vdsr@sails LOG: statement: begin
[6459-8]vdsr@sails LOG: execute : INSERT INTO "groups" ("name", "parentGroupRef", "isCompany", "companyRef", "createdAt" , "updatedAt") 值 ($1, $2, $3, $4, $5, $6) RETURNING*
...
[6459-22]vdsr@sails LOG: statement: commit
...
[6477-5]vdsr@sails LOG:语句：begin
[6477-6]vdsr@sails 日志：执行：INSERT INTO "groups" ("name", "isCompany", "parentGroupRef", "companyRef", "createdAt", "updatedAt") values ($1, $2 , $3, $4, $5, $6) 返回 *
...
[6479-1]vdsr@sails LOG：声明：提交
[6479-2]vdsr@sails 警告：没有正在进行的交易

The WARNING obviously belongs to a different concurrent transaction, which did not start an explicit transaction. The commitis misplaced there, since it's operating in autocommit mode.

WARNING 显然属于不同的并发事务，它没有启动显式事务。该commit是放错了地方存在，因为它是在自动提交模式下运行。

If you keep reading your log, you will probably find an entry for the commit further down:

如果你继续阅读你的日志，你可能会在下面找到提交的条目：

[6477-??]vdsr@sails LOG: statement: commit

[6477-??]vdsr@sails LOG：声明：提交

Debug

调试

If you find neither that nor a rollbackentry nor an error, I would check for problems in your app leaving uncommitted zombi transactions, which would be a bad thing.

如果您既没有发现那个，也没有发现rollback条目或错误，我会检查您的应用程序中是否存在问题，留下未提交的僵尸事务，这将是一件坏事。

Start the investigation by checking the system view pg_stat_activitywhile connected to your database:

通过pg_stat_activity在连接到数据库时检查系统视图来开始调查：

SELECT *
FROM   pg_stat_activity
WHERE  datname = current_database()  -- only current database
AND    pid <> pg_backend_pid()       -- except your current session
AND    state LIKE 'idle%';

The statevalue idleis not necessarily suspicious - just a session that's waiting for input.
But idle in transactionand idle in transaction (aborted)are.

该state值idle不一定是可疑的 - 只是一个等待输入的会话。
但是idle in transaction和idle in transaction (aborted)是。

More in the manual hereor these related answers:

更多在这里手动或这些相关答案：

• How to drop a PostgreSQL database if there are active connections to it?
• Is there a timeout for idle PostgreSQL connections?

• 如果有活动连接，如何删除 PostgreSQL 数据库？
• 空闲的 PostgreSQL 连接是否有超时？