c - postgresql C 输入/输出函数奇怪的行为

Question

最近我一直在使用 postgresql 及其在 C 中的自定义用户定义类型。

在我实现用户定义类型 EmailAddress 的输入/输出函数之前，这是奇怪的行为

如果我使用psql -f选项从文件中插入数据，用户名列中的电子邮件地址就会变得困惑。

但是如果我使用 psql [dbname] 中的 \i data.sql 选项从文件插入数据，我会得到这个结果。所有电子邮件均正确显示

---email.c------

PG_MODULE_MAGIC;
typedef struct EmailAdress {
    char* domain;
    char* local;
} EmailAddress;

PG_FUNCTION_INFO_V1(email_in);
Datum email_in(PG_FUNCTION_ARGS) {
    // Get arg c string
    char* pStr = palloc(strlen(PG_GETARG_CSTRING(0)));
    strcpy(pStr, PG_GETARG_CSTRING(0));

    // Convert to connical form
    int i;
    for (i = 0; pStr[i]; i++) {
        pStr[i] = tolower(pStr[i]);
    }

    EmailAddress* pEmail = (EmailAddress*) palloc(sizeof(EmailAddress));

    char* pToken;
    pToken = strtok(pStr, AT);
    pEmail->local = malloc(strlen(pToken) + 1);
    strcpy(pEmail->local, pToken);

    pToken = strtok(NULL, AT);
    pEmail->domain = malloc(strlen(pToken) + 1);
    strcpy(pEmail->domain, pToken);

    pfree(pStr);

    if (!valid(pEmail)) {
        ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid EmailAddress: \"%s@%s\" - pointer: %p", pEmail->local, pEmail->domain, pEmail)));
    }

    PG_RETURN_POINTER(pEmail);
}


PG_FUNCTION_INFO_V1(email_out);
Datum email_out(PG_FUNCTION_ARGS) {
    EmailAddress* pEmail = (EmailAddress *) PG_GETARG_POINTER(0);
    char* pResult = psprintf("%s@%s", pEmail->local, pEmail->domain);

    PG_RETURN_CSTRING(pResult);
}

----电子邮件.来源----

CREATE FUNCTION email_in(cstring)
    RETURNS EmailAddress
    AS '_OBJWD_/email'
    LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION email_out(EmailAddress)
    RETURNS cstring
    AS '_OBJWD_/email'
    LANGUAGE C IMMUTABLE STRICT;

CREATE TYPE EmailAddress (
    internallength   = 16,
    input            = email_in,
    output           = email_out,
    alignment        = double
);


CREATE FUNCTION email_eq(EmailAddress, EmailAddress) RETURNS bool
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION email_neq(EmailAddress, EmailAddress) RETURNS bool
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION email_gt(EmailAddress, EmailAddress) RETURNS bool
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION email_ge(EmailAddress, EmailAddress) RETURNS bool
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION email_lt(EmailAddress, EmailAddress) RETURNS bool
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION email_le(EmailAddress, EmailAddress) RETURNS bool
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION email_deq(EmailAddress, EmailAddress) RETURNS bool
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION email_ndeq(EmailAddress, EmailAddress) RETURNS bool
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR < (
    leftarg = EmailAddress, rightarg = EmailAddress, procedure = email_lt,
    commutator = > , negator = >=
);

CREATE OPERATOR <= (
    leftarg = EmailAddress, rightarg = EmailAddress, procedure = email_le,
    commutator = >= , negator = >
);

CREATE OPERATOR = (
    leftarg = EmailAddress, rightarg = EmailAddress, procedure = email_eq,
    commutator = = , negator = <>
);

CREATE OPERATOR <> (
    leftarg = EmailAddress, rightarg = EmailAddress, procedure = email_neq,
    commutator = <> , negator = =
);

CREATE OPERATOR >= (
    leftarg = EmailAddress, rightarg = EmailAddress, procedure = email_ge,
    commutator = <= , negator = <
);

CREATE OPERATOR > (
    leftarg = EmailAddress, rightarg = EmailAddress, procedure = email_gt,
    commutator = < , negator = <=
);

CREATE OPERATOR ~ (
    leftarg = EmailAddress, rightarg = EmailAddress, procedure = email_deq,
    commutator = ~ , negator = !~
);

CREATE OPERATOR !~ (
    leftarg = EmailAddress, rightarg = EmailAddress, procedure = email_ndeq,
    commutator = !~ , negator = ~
);

CREATE FUNCTION email_cmp(EmailAddress, EmailAddress) RETURNS int4
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION email_hash(EmailAddress) RETURNS int4
    AS '_OBJWD_/email' LANGUAGE C IMMUTABLE STRICT;

-- now we can make the operator class
CREATE OPERATOR CLASS email_btree_ops
    DEFAULT FOR TYPE EmailAddress USING btree AS
        OPERATOR        1       <   ,
        OPERATOR        2       <=  ,
        OPERATOR        3       =   ,
        OPERATOR        4       >=  ,
        OPERATOR        5       >   ,
        FUNCTION        1       email_cmp(EmailAddress, EmailAddress);

CREATE OPERATOR CLASS email_hash_ops
    DEFAULT FOR TYPE EmailAddress USING hash AS
        OPERATOR        1       =   ,
        FUNCTION        1       email_hash(EmailAddress);

Answer 1

好的。在 @Antti 和我的教授等优秀人士的帮助和支持下，

这是此行为背后的解决方案和原因(总结):

问题出在这里:

typedef struct EmailAdress {
    char* domain;
    char* local;
} EmailAddress;

我为本地和域分配的缓冲区仅存在于原始 SQL 进程的内存上下文中。

我实际存储在数据库中的是指向这些缓冲区的指针，并且在原始 sql 进程运行时这些指针可以正常工作。当我启动一个新的sql进程时，元组中仍然有相同的缓冲区地址，但缓冲区在新的内存上下文中不存在。

有两种可能的解决方案:

• 一种简单，但空间效率低下，另一种更困难，但浪费的空间更少。
• 一个更复杂但节省空间的方法(使用 varlena 数据类型 - 我不知道如何实现这种方法，所以我放弃了)

最简单的一个(大多数人都用过)是将结构定义为

typedef struct EmailAdress {
    char domain[MAX];
    char local[MAX];
} EmailAddress;

然后，在此策略下，我不再将 malloc() 用于本地和域。当您执行 palloc() 时，会创建它们的缓冲区空间

这是可行的，因为所有字符串数据都包含在由 palloc() 创建的内存块中，因此当 PostgreSQL 插入元组时会被写入磁盘。

就相应地更改 email.source 而言，唯一需要的更改是:

CREATE TYPE EmailAddress (
    internallength   = 258,
    input            = email_in,
    output           = email_out,
    alignment        = int4,
    storage          = plain
);

请注意内部长度 = 258 (129 * 2)，因为在我的情况下，我可以假设所有本地/域部分最大为 128 位 + 1 位作为空终止符。