PostgreSQL表显示异常，关联GET请求失效问题排查求助

问题描述 #

PostgreSQL数据库的questionnaire_entity表突然显示异常，出现大量加减号分隔线，此前显示正常。该表通过Spring集成的Liquibase创建，现在GET请求无法正常工作，不确定是否与表结构问题相关。

Liquibase变更日志 #

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.9.xsd">
    <changeSet id="create_questionnaire_entity_11" author="Iman Gharib">
        <preConditions onFail="MARK_RAN">
            <not>
                <tableExists tableName="questionnaire_entity"/>
            </not>
        </preConditions>
        <createTable tableName="questionnaire_entity" schemaName="public">
            <column name="id" autoIncrement="true" type="bigint"/>
            <column name="patient_number" type="varchar"/>
            <column name="questionnaire_id" type="varchar"/>
            <column name="received_date" type="datetime"/>
            <column name="question_type" type="varchar"/>
            <column name="question_text" type="varchar"/>
        </createTable>
        <addPrimaryKey tableName="questionnaire_entity" columnNames="id"/>
    </changeSet>
    <changeSet id="extend_text_size_2" author="Iman Gharib">
        <modifyDataType
                columnName="question_text"
                newDataType="varchar(2000)"
                tableName="questionnaire_entity"/>
    </changeSet>
</databaseChangeLog>

表查询的异常显示结果 #

id  | patient_number |      questionnaire_id       |      received_date      | question_type |       

                                                                                                         
                                                                                                                                                                                                               
                                                                                                                                                                                                               
                                                                                                                                                                                                               
                                                                                                                                                                                                               
                               question_text    

------+----------------+-----------------------------+-------------------------+---------------+-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

排查与解决方向 #

• 检查question_text字段内容：异常分隔线大概率是该字段存储了大量换行、空格或不可见控制字符，导致psql客户端格式化输出时被撑开。执行以下SQL查看字段长度和前50个字符，确认是否包含异常字符：

  SELECT id, length(question_text), left(question_text, 50) FROM questionnaire_entity;
  

• 验证表结构有效性：执行\d questionnaire_entity查看表字段类型、约束是否符合预期，确认Liquibase的modifyDataType变更是否生效（即question_text是否为varchar(2000)）。
• 关联GET请求故障排查：如果接口直接返回该表数据，字段中的异常字符可能导致JSON序列化失败（比如未转义的换行符），查看应用日志是否存在序列化相关报错；也可能是字段内容过大导致响应超时或传输异常。
• 修复字段内容：若确认是异常字符导致，可通过以下SQL清理换行、制表符等控制字符（根据实际情况调整规则）：

  UPDATE questionnaire_entity SET question_text = regexp_replace(question_text, E'[\\n\\r\\t]+', ' ', 'g');
  

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