PostgreSQL扩展开发：创建表时检测bytea类型列失败的问题求助

嘿，我刚看完你的问题，你在开发PostgreSQL事件触发器扩展时遇到的这个bytea列检测问题其实挺典型的，咱们一步步来搞定它~

问题重现 #

你写了一个事件触发器，想要在CREATE TABLE时自动检测表中是否包含bytea类型的列，如果有的话就把表名记录到table_log表中。但测试时明明创建了带bytea列的表：

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    data bytea
);

却收到了No BYTEA columns found in table test_table的提示，触发器没有把表名写入日志。

问题原因分析 #

看你的C代码里的判断逻辑，你只处理了带pg_catalog前缀的类型名：

if (list_length(colDef->typeName->names) == 2) // {"pg_catalog", "bytea"}

但当你在SQL里直接写bytea时，PostgreSQL会把它解析成单元素的名称列表（只有"bytea"，没有前缀），你的代码完全漏掉了这种情况，所以检测不到bytea列。

修复方案 #

修改循环里的类型判断逻辑，同时处理带前缀和不带前缀的两种情况：

foreach(cell, createStmt->tableElts)
{
    ColumnDef *colDef = (ColumnDef *) lfirst(cell);
    const char *type = NULL;

    // 处理两种情况：带pg_catalog前缀，或者直接写bytea
    if (list_length(colDef->typeName->names) == 1)
    {
        type = strVal(linitial(colDef->typeName->names));
    }
    else if (list_length(colDef->typeName->names) == 2)
    {
        const char *schema = strVal(linitial(colDef->typeName->names));
        // 只处理pg_catalog下的类型
        if (strcmp(schema, "pg_catalog") != 0)
            continue;
        type = strVal(lsecond(colDef->typeName->names));
    }
    else
    {
        // 跳过其他复杂类型（比如自定义schema下的类型）
        continue;
    }

    if (type != NULL && strcmp(type, "bytea") == 0)
    {
        has_bytea_column = true;
        break;
    }
}

额外的新手友好提示 #

1. 调试小技巧：开发扩展时可以用elog(NOTICE, "Column %s type names length: %d", colDef->colname, list_length(colDef->typeName->names));这类语句打印中间值，方便快速定位问题。
2. 避免SQL注入：你当前用psprintf直接拼接表名的写法有安全风险，如果表名包含单引号等特殊字符会出错甚至引发注入，建议用quote_identifier转义：

   char *quoted_relname = quote_identifier(relation->relname);
   char *query = psprintf("INSERT INTO table_log (table_name) VALUES (%s)", quoted_relname);
   pfree(quoted_relname); // 记得释放内存，避免内存泄漏
   

3. 学习资源推荐：PostgreSQL官方文档的Server Programming部分是最权威的扩展开发指南，另外可以参考PostgreSQL源码中contrib目录下的示例扩展（比如uuid-ossp、pg_stat_statements），这些都是非常实用的学习素材。

你的原始代码参考 #

C扩展代码

#include "postgres.h"
#include "fmgr.h"
#include "commands/event_trigger.h"
#include "parser/parse_node.h"
#include "executor/spi.h"
#include "utils/builtins.h"
#include "catalog/pg_type.h"
#include "nodes/pg_list.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(log_table_creation);

Datum
log_table_creation(PG_FUNCTION_ARGS)
{
    EventTriggerData *trigdata;
    const char *tag;
    int ret;

    if (!CALLED_AS_EVENT_TRIGGER(fcinfo))
        elog(ERROR, "not fired by event trigger manager");

    trigdata = (EventTriggerData *) fcinfo->context;
    tag = GetCommandTagName(trigdata->tag);

    if (strcmp(tag, "CREATE TABLE") != 0)
        PG_RETURN_NULL();

    // Cast parsetree to CreateStmt to access the table structure
    CreateStmt *createStmt = (CreateStmt *) trigdata->parsetree;
    RangeVar *relation = createStmt->relation;

    // Check if any column has type `bytea`
    bool has_bytea_column = false;
    ListCell *cell;

    foreach(cell, createStmt->tableElts)
    {
        ColumnDef *colDef = (ColumnDef *) lfirst(cell);

        // Check for type name "bytea" explicitly
        if (list_length(colDef->typeName->names) == 2) // {"pg_catalog", "bytea"}
        {
            // Extract schema and type as strings
            const char *schema = strVal(linitial(colDef->typeName->names));
            const char *type = strVal(lsecond(colDef->typeName->names));

            if (strcmp(schema, "pg_catalog") == 0 && strcmp(type, "bytea") == 0)
            {
                has_bytea_column = true;
                break;
            }
        }
    }

    // Only log the table if it has a `bytea` column
    if (has_bytea_column)
    {
        // Prepare and execute the insertion into table_log
        SPI_connect();
        char *query = psprintf("INSERT INTO table_log (table_name) VALUES ('%s')", relation->relname);
        ret = SPI_execute(query, false, 0);
        SPI_finish();

        if (ret != SPI_OK_INSERT)
            elog(ERROR, "Failed to insert into table_log");
    }
    else
    {
        elog(NOTICE, "No BYTEA columns found in table %s", relation->relname);
    }

    PG_RETURN_NULL();
}

SQL安装脚本

CREATE TABLE table_log (
id SERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE FUNCTION log_table_creation()
RETURNS event_trigger
LANGUAGE c
AS 'MODULE_PATHNAME', 'log_table_creation';

CREATE EVENT TRIGGER table_creation_logger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION log_table_creation();

备注：内容来源于stack exchange，提问作者Rahma Begag