PostgreSQL查询报错：invalid string in message 求助

问题场景 #

执行以下SELECT查询时，无论是在FastAPI还是psql终端中都触发错误：

SELECT "user".id AS user_id,
   "user".is_verified AS user_is_verified, 
   "user".name AS user_name, 
   "user".nickname AS user_nickname, 
   "user".password AS user_password, 
   "user".birthdate AS user_birthdate, 
   "user".food_restrictions AS user_food_restrictions, 
   "user".email AS user_email, 
   "user".telephone AS user_telephone, 
   "user".address AS user_address, 
   "user".shirt_size AS user_shirt_size, 
   "user".type AS user_type, 
   "user".created_at AS user_created_at, 
   "user".updated_at AS user_updated_at, 
   "user".image AS user_image, 
   "user".is_image_url AS user_is_image_url, 
   "user".code AS user_code, 
   "user".terms_accepted AS user_terms_accepted, 
   "user".recive_mails AS user_recive_mails, 
   "user".lleidacoins_claimed AS user_lleidacoins_claimed, 
   "user".token AS user_token, 
   "user".refresh_token AS user_refresh_token, 
   "user".verification_token AS user_verification_token, 
   "user".rest_password_token AS user_rest_password_token, 
   hacker.user_id AS hacker_user_id, 
   hacker.banned AS hacker_banned, 
   hacker.github AS hacker_github, 
   hacker.linkedin AS hacker_linkedin, 
   hacker.cv AS hacker_cv, 
   hacker.studies AS hacker_studies, 
   hacker.study_center AS hacker_study_center, 
   hacker.location AS hacker_location, 
   hacker.how_did_you_meet_us AS hacker_how_did_you_meet_us
FROM hacker_event_accepted, 
     "user" JOIN hacker ON "user".id = hacker.user_id
WHERE 1 = hacker_event_accepted.event_id 
      AND hacker.user_id = hacker_event_accepted.user_id

报错信息：

ERROR: invalid string in message

已知未超过PostgreSQL官方1600列的最大限制，但移除SELECT子句中任意4列后查询可正常执行。

问题原因 #

这个错误并非触发了1600列的硬限制，而是查询返回的所有列的元数据（列名、别名、数据类型等信息的总字符串长度）超过了PostgreSQL客户端或服务器内部的消息缓冲区阈值。PostgreSQL在传递查询结果的字段描述时，会将这些信息打包成固定格式的消息，当消息总长度超出缓冲区上限时，就会抛出这个模糊的"invalid string in message"错误。

解决方法 #

• 只查询必要列：删除业务逻辑中不需要的列，既解决错误，又能降低数据传输量、提升查询性能。
• 缩短列别名：简化过长的别名，比如将user_lleidacoins_claimed改为user_lc_claimed，减少总字符串长度，避免触发缓冲区限制。
• 使用短表别名：给表设置简洁的别名，简化查询语句同时减少元数据长度，例如：

  SELECT u.id AS user_id, u.is_verified AS user_is_verified, ... 
  FROM hacker_event_accepted hea, 
       "user" u JOIN hacker h ON u.id = h.user_id
  WHERE hea.event_id = 1 AND h.user_id = hea.user_id
  

• 拆分查询：将大查询拆分为多个小查询，在FastAPI层合并结果。比如先查询用户基础信息，再查询hacker专属信息，避免单次返回过多列的元数据。
• 升级PostgreSQL版本：部分旧版本存在此类缓冲区限制的bug，升级到14及以上的稳定版本可能修复该问题。

内容的提问来源于stack exchange，提问作者tonlls