greenplum异常，参数max_prepared_transactions_greenplum closed the connection unexpectedly this -优快云博客

本文链接：https://blog.youkuaiyun.com/Jun_ming/article/details/117377314

当业务方反馈GP数据库查询失败时，通过gpstate检查集群状态正常，但会话堆积导致异常。查询pg_stat_activity发现超过max_prepared_transactions限制，而该参数值低于max_connections。调整会话，清理非正常进程，问题暂时解决。查阅文档了解到，max_prepared_transactions应与max_connections一致以确保数据完整性。最终建议调整参数值以避免类似问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景：某夜晚，业务方反馈GP数据库无法查询。速看集群状态gpstate -m 集群状态正常无任何实例异常，登录数据库查询运行会话,此时以无法查询抛出如下异常信息。

=#select * from pg_stat_activity;

FATAL: the limit of 250 distributed transactions has been reached.

DETAL: the global user configuration (GUC) server parameter max_prepared_transactions controls this limit.

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost .Attempting reset:succeeded

命令行查看数据库进程ps -ef|grep postgres发现数据库连接爆满，会话为正常繁忙状态下3倍，会话堆积阻塞，所有进程显示为waiting状态。于是紧急处理清理部分非常规业务进程，连接数据下降，任务正常运行通畅，登录数据库正常查询，堆积任务迅速执行完成。

查询max_prepared_transactions参数：show max_prepared_transactions 为默认值，小于数据库连接数据max_connections。

官方文档：

Sets the maximum number of transactions that can be in the prepared state simultaneously. Greenplum uses prepared transactions internally to ensure data integrity across the segments. This value must be at least as large as the value of max_connections on the master. Segment instances should be set to the same value as the master.

百度翻译：设置可同时处于准备状态的最大事务数。Greenplum在内部使用准备好的事务来确保跨段的数据完整性。此值必须至少与主机上最大连接数值一样大。段实例应设置为与主实例相同的值。

max_prepared_transactions参数值建议与max_connections参数值一致。

改