在PostgreSQL日志中是否有任何方法可以通过事务id或进程获得SQL查询?

Question

我试图解决一些死锁，发生在我的生产PostgreSQL服务器。我有的:

1. 哨兵，显示第一个锁定查询(下面日志中的进程2840554)
PostgreSQL死锁日志:

2022-05-24 21:48:52 MSK [2840554-1] carrotquest@carrot_shard_1 LOG:  process 2840554 detected deadlock while waiting for ShareLock on transaction 4153893838 after 1000.029 ms
2022-05-24 21:48:52 MSK [2840554-2] carrotquest@carrot_shard_1 DETAIL:  Process holding the lock: 2879765. Wait queue: .
2022-05-24 21:48:52 MSK [2840554-3] carrotquest@carrot_shard_1 CONTEXT:  while locking tuple (72609310,2) in relation "users_user"
2022-05-24 21:48:52 MSK [2840554-4] carrotquest@carrot_shard_1 STATEMENT:  SELECT "users_user"."id", "users_user"."app_id", "users_user"."props_json", "users_user"."props_events_json", "users_user"."removed", "users_user"."hidden", "users_user"."email_status_id", "users_user"."es_index" FROM "users_user" WHERE "users_user"."id" = 1138071193757156022 LIMIT 21 FOR UPDATE
2022-05-24 21:48:52 MSK [2879765-2] carrotquest@carrot_shard_1 DETAIL:  Process holding the lock: 2840554. Wait queue: 2879765, 2883036.
2022-05-24 21:48:52 MSK [2840554-5] carrotquest@carrot_shard_1 ERROR:  deadlock detected
2022-05-24 21:48:52 MSK [2840554-6] carrotquest@carrot_shard_1 DETAIL:  Process 2840554 waits for ShareLock on transaction 4153893838; blocked by process 2879765.
Process 2879765 waits for ShareLock on transaction 4153893850; blocked by process 2840554.
Process 2840554: SELECT "users_user"."id", "users_user"."app_id", "users_user"."props_json", "users_user"."props_events_json", "users_user"."removed", "users_user"."hidden", "users_user"."email_status_id", "users_user"."es_index>
Process 2879765: COMMIT
2022-05-24 21:48:52 MSK [2840554-7] carrotquest@carrot_shard_1 HINT:  See server log for query details.
2022-05-24 21:48:52 MSK [2840554-8] carrotquest@carrot_shard_1 CONTEXT:  while locking tuple (72609310,2) in relation "users_user"
2022-05-24 21:48:52 MSK [2840554-9] carrotquest@carrot_shard_1 STATEMENT:  SELECT "users_user"."id", "users_user"."app_id", "users_user"."props_json", "users_user"."props_events_json", "users_user"."removed", "users_user"."hidden", "users_user"."email_status_id", "users_user"."es_index" FROM "users_user" WHERE "users_user"."id" = 1138071193757156022 LIMIT 21 FOR UPDATE

正如你看到的，持有锁的第二个进程是未知的——它是某个执行"COMMIT;"的事务。阻塞事务4153893838.

我的问题是:有没有办法找到有关这笔交易的一些信息?SQL，或其他线索，可以帮助我找到它在我的代码?

感谢

Answer 1

将%x添加到您的log_line_prefix中，那么事务ID也将被记录。如果您记录了所有的语句，那么您可以很容易地识别属于该事务的所有语句。

我知道记录每件事可能不可行，但这是PostgreSQL中唯一的方法。

Answer 2

一段时间过去了，我也找到了一个很好的方法来调试问题所在。我添加了一个评论，以结束每个查询像SELECT something FROM somewhere; /* file/view.py function():10 */。所以我可以很容易地找到有问题的查询来自哪里。

p。在我的情况下，我使用Django python框架。所以我装饰了django.db.utils.ConnectionHandler.create_connection。此方法修改后会自动将conn.execute_wrappers添加到每个连接中。包装器使用traceback.extract_stack()结果为sql添加标记。