exhuma's questions

我知道，在 PostgreSQL 中，返回值NOW()是事务开始时间戳。因此，如果您NOW()在同一事务中多次使用，它将始终返回相同的值。

这在我的书中很好。

但是我对客户端应用程序中的单元测试有一个小问题，如果可能的话，我希望能够告诉 PostgreSQL（暂时）禁用它。

我不想（不能）使用另一个时间戳函数clock_timestamp()的原因是因为函数调用NOW()位于触发器内部，并且在生产代码中我想要“事务启动”行为。

但是在我的单元测试中，我正在修补 API 级别的“提交”功能，因此我不会在测试期间不小心将真实数据提交到数据库（别担心，我在测试期间不使用生产数据库）。所以在单元测试期间，commit从来没有命中数据库，所以我没有得到新的事务时间戳。

数据库使用时态表，只有在时间戳发生变化时才会将新条目附加到历史表中，以确保我们在每个事务中只合并历史表中的一个条目。

但是在测试时态表行为时，这会导致历史表中永远不会出现任何条目。时态表触发器的关键片段是这样的：

new_validity_period = tstzrange(
    lower(OLD.validity_period),
    NOW(),
    '[)'
);
IF isempty(new_validity_period) THEN
    RAISE DEBUG 'New entry % will not introduce a new history item', OLD;
    RETURN OLD;
END IF;

因此，当我在单元测试中执行“插入”操作并且事务时间为“2020-01-01 01:02:03”时，该条目的有效期将为[2020-01-01 01:02:02,). 如果仍然在同一个单元测试中，我删除了该条目（并测试它是否出现在历史表中），则操作发生在同一个 TX 中，上面的代码将如下所示：

new_validity_perion = tstzrange(
    '2020-01-01 01:02:03', -- the lower-bound of the 'OLD' row
    '2020-01-01 01:02:03', -- the result of 'NOW()'
    '[)'
)
-- resulting in an empty range because the two timestamps are identical
IF isempty(new_validity_periond) THEN  -- <- Resulting to TRUE
    ...
    RETURN OLD;  -- returning here, not continuing to store the history entry
END IF;
-- code below here is skipped

有没有办法将 PG 配置为在调用时始终返回挂墙时间NOW()？测试是针对 docker-container 运行的，因此我可以完全控制服务器配置。但是，如果我可以通过 SQL 命令设置配置来更改行为会更好，所以我只能更改临时表测试的行为。

如果那不可能，我需要为这些测试使用不同的数据库会话/事务设置。这也很好，但我想知道我是否可以控制这种 PG 行为。

附录：单元测试事务隔离

这是我用来隔离用户代码中的提交调用的代码：

@fixture
def rb_session():
    """
    Returns a session which will always be rolled back.
    """

    engine = create_engine(Configurations.getenv("IPBASE_DATABASE_DSN"))

    # Get a *specific* connection from the pool
    connection = engine.connect()
    # Explicitly start a new connection on the connection we got
    # This makes the normal "session.begin()" and "session.commit()"
    # calls in SQLAlchemy no-ops as there is already a transaction
    # in progress.
    transaction = connection.begin()
    # Ensure we use the "primed" connection for all our SQLAlchemy
    # session needs in our unit-tests.
    session = Session(bind=connection)
    try:
        yield session
    finally:
        transaction.rollback()
        session.close()
        connection.close()

假设我在行更新时执行了以下函数：

CREATE OR REPLACE FUNCTION versioned_update()
RETURNS TRIGGER AS $$
DECLARE
     sql TEXT;
BEGIN
    sql := 'INSERT INTO backup_table VALUES (OLD)';
    EXECUTE sql;
END;
$$ language 'plpgsql';

上面的示例不起作用，因为OLD在该执行上下文中是未知的。所以我尝试了类似的东西：

sql := format('INSERT INTO backup_table VALUES (%L)', OLD);

和

sql := format('INSERT INTO backup_table VALUES (%L)', (OLD));

和

sql := format('INSERT INTO backup_table VALUES (%L)', (OLD.*));

一切都没有运气。

这个问题是我正在处理的一个更大的触发器的一部分。孤立地看，它没有多大意义，但说明了问题。

注意：以下示例是一个非常简化的代码，但它在一个可重现的示例中说明了任务。看着这个例子，你可能会想“为什么要这样做”？实际上，完整的任务是为多个表存储审计跟踪，但前提是满足特定条件。每个表的条件都相同（它们各自共享一些列，如inserted，updated等等）。因此，每个表何时存储审计跟踪的代码都是相同的。但是每次要复制的实际列都不同。我想创建一个触发器来动态处理这个问题，这样我就不需要在每次架构更改时都触摸它。

考虑以下工作示例（下面的问题）。这演示了一个简单的模式，其中表中的每个更新data都会导致将旧值移入data2：

DROP TABLE IF EXISTS data CASCADE;
DROP TABLE IF EXISTS data2 CASCADE;
CREATE TABLE data (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);
CREATE TABLE data2 (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);

CREATE OR REPLACE FUNCTION update_trigger_func()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.updated = NOW();
        INSERT INTO data2 VALUES (OLD.*);
        RETURN NEW;
    END;
    $$ language 'plpgsql';

CREATE TRIGGER update_trigger
    BEFORE UPDATE ON data
    FOR EACH ROW
    EXECUTE PROCEDURE update_trigger_func();


SET client_min_messages TO 'debug';
INSERT INTO data (name) VALUES ('foo');
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'bar';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'baz';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

请注意，该函数update_trigger_func具有硬编码的“history”表名data2，如下行所示：

INSERT INTO data2 VALUES (OLD.*);

如果data2是一个参数，这个函数也可以重用于其他表。但到目前为止我没能找到合适的咒语。到目前为止，我已经尝试了以下两个版本：

INSERT INTO TG_ARGV[0] VALUES (OLD.*);

但这会导致语法错误：

psql:temptable.sql:28: ERROR:  syntax error at or near "VALUES"
LINE 11:         INSERT INTO TG_ARGV[0] VALUES (OLD.*);

因此，或者我尝试使用动态 SQL：

sql := 'INSERT INTO' || TG_ARGV[0] || 'VALUES (OLD.*)';
EXECUTE sql;

但这失败了，因为该OLD变量在执行上下文中不可用：

psql:temptable.sql:58: ERROR:  missing FROM-clause entry for table "old"
LINE 1: INSERT INTO data2 VALUES (OLD.*)
                                    ^
QUERY:  INSERT INTO data2 VALUES (OLD.*)
CONTEXT:  PL/pgSQL function versioned_update() line 11 at EXECUTE

鉴于我想在其他表上使用此触发函数，我无法对列名进行硬编码。我怎么能做到这一点？

假设我有下表：

CREATE TABLE data (
    key TEXT primary key,
    some_interesting_value TEXT,
    inserted TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    last_seen TIMESTAMP WITH TIME ZONE
);

然后我可以创建一个触发器来设置“更新”列，如下所示：

CREATE TRIGGER set_updated_col
    AFTER UPDATE ON data
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE set_updated_timestamp();

本last_seen例中的列包含一个时间戳，每次运行进程（在某些条件下）时都会更新该时间戳。但是，这个值对于跟踪表更新没有意义。就我而言，我有很多这样的表。它们都包含有关物理网络设备的信息，并且“last_seen”值对每个实体都很重要。

即使只有“last_seen”值被修改，上面的触发器也会设置“updated”时间戳。

就我而言，我想避免这种情况。

我可以手动命名子句中的所有列WHEN，但是，如果我向表中添加一列，则有可能会在他的触发器中忘记它。

有没有办法从中“删除”last_seen列OLD.*？

作为代码示例：

-- This should NOT trigger a change to `updated`
UPDATE data SET last_seen=NOW() WHERE key='foobar';

-- this SHOULD trigger a change to `updated`
UPDATE data SET some_interesting_value='foo', last_seen=NOW() WHERE key='foobar'

假设我有以下功能：

 CREATE OR REPLACE FUNCTION myfun(IN TEXT)
     RETURNS boolean
     LANGUAGE SQL
     IMMUTABLE
     LEAKPROOF
     AS $CODE$
         SELECT COALESCE(
             $1 ILIKE ANY (ARRAY[
                 'test',
                 'bar'
             ]),
             false
         );
     $CODE$;

true如果$1匹配则返回'test'or 'bar'。

如何在另一个ANY查询中使用此函数？例如：

 SELECT myfun ANY(ARRAY['hello', 'world']);  -- should return false
 SELECT myfun ANY(ARRAY['hello', 'foo']);  -- should return true ('foo' matches)
 SELECT myfun ANY(ARRAY['test', 'world']);  -- should return true ('test matches')

但是这个语法是不正确的。而且我不太清楚如何正确地写这个。

编辑：更多细节

本质上我想要做的是检查一组列中的任何一个是否包含任何一个禁止的术语。换句话说，我有一组禁止的术语，并且必须检查多个列以包含该词。这稍后将用于 RLS 策略。在下面的示例中，函数contains_forbidden_term是我希望能够编写的函数。

一个小而重要的注意事项：必须将相同的函数应用于其他表，但参数数量不同（因此数组作为参数类型）。

CREATE TABLE foo (
    name TEXT,
    last_name TEXT
);
CREATE USER bob;
ALTER TABLE foo ENABLE ROW LEVEL SECURITY;

CREATE POLICY bob_foo ON foo TO bob USING (contains_forbidden_term(ARRAY[name, last_name]));

GRANT SELECT ON foo TO bob;

我遇到了一个关于 MD5 值的数据类型的问题。该问题中的建议指出uuid对该字段使用 as 类型。

论据扎实。但是我发现这可能会使不了解上述问题中揭示的要点的人感到困惑。使用 MD5 类型的决定uuid是我希望在任何执行此操作的项目中以某种形式的文档看到的。

作为任何查看 DB Schema 的人的“帮手”，有人可能会争辩说要创建一个md5继承自uuid. 这样，列类型会更加明确，意图也会更加清晰。

但除了“重命名”现有类型外，它不会出于其他原因使用域。

正确记录它仍然是有意义的。但是该文档可以集中到解释数据库中域的部分。因此，您不会在文档方面获得任何收益。如前所述，我看到的优势是在查看表 DDL 时意图变得清晰。

这有什么缺点吗？

我正在尝试编写一个 munin 插件来绘制数据库大小。除了使用pg_database_size我还想绘制其组件。

到目前为止，我想出了以下几点：

SELECT
    SUM(pg_relation_size(oid, 'main')) AS main_size,
    SUM(pg_relation_size(oid, 'vm')) AS vm_size,
    SUM(pg_relation_size(oid, 'fsm')) AS fsm_size,
    SUM(
        CASE reltoastrelid
        WHEN 0 THEN 0
        ELSE pg_total_relation_size(reltoastrelid)
        END
    ) AS toast_size,
    SUM(pg_indexes_size(oid)) AS indexes_size
    FROM pg_class
    WHERE reltype != 0 -- 0=indices, covered by pg_indexes_size

但是，总结这 5 个值会给我一些与pg_database_size. 对于较大的数据库，差异似乎不太显着。

较大数据库上的示例：

┌──────────┬────────┬─────────┬─────────┬──────────┬───────────────┬──────────────────┬─────────┐
│   main   │   vm   │   fsm   │  toast  │ indexes  │ sum_of_values │ pg_database_size │  diff   │
├──────────┼────────┼─────────┼─────────┼──────────┼───────────────┼──────────────────┼─────────┤
│ 72441856 │ 753664 │ 2392064 │ 4677632 │ 41377792 │ 116 MB        │ 111 MB           │ 5222 kB │
└──────────┴────────┴─────────┴─────────┴──────────┴───────────────┴──────────────────┴─────────┘
(1 row)

较小数据库的示例：

┌─────────┬────────┬─────────┬────────┬─────────┬───────────────┬──────────────────┬─────────┐
│  main   │   vm   │   fsm   │ toast  │ indexes │ sum_of_values │ pg_database_size │  diff   │
├─────────┼────────┼─────────┼────────┼─────────┼───────────────┼──────────────────┼─────────┤
│ 2809856 │ 385024 │ 1351680 │ 557056 │ 2924544 │ 7840 kB       │ 6642 kB          │ 1198 kB │
└─────────┴────────┴─────────┴────────┴─────────┴───────────────┴──────────────────┴─────────┘
(1 row)

我错过了什么？

也许相关，也许不相关：看到索引大小我很震惊。他们是巨大的。我的查询中有问题吗？

这是我用来检查不同值的脚本：

SELECT
    SUM(pg_relation_size(oid, 'main')) AS main,
    SUM(pg_relation_size(oid, 'vm')) AS vm,
    SUM(pg_relation_size(oid, 'fsm')) AS fsm,
    SUM(
        CASE reltoastrelid
        WHEN 0 THEN 0
        ELSE pg_total_relation_size(reltoastrelid)
        END
    ) AS toast,
    SUM(pg_indexes_size(oid)) AS indexes,
    pg_size_pretty(
        SUM(pg_relation_size(oid, 'main'))::bigint +
        SUM(pg_relation_size(oid, 'vm'))::bigint +
        SUM(pg_relation_size(oid, 'fsm'))::bigint +
        SUM(pg_indexes_size(oid))::bigint +
        SUM(
            CASE reltoastrelid
            WHEN 0 THEN 0
            ELSE pg_total_relation_size(reltoastrelid)
            END
        )::bigint
    ) AS sum_of_values,
    pg_size_pretty(pg_database_size(current_database())) AS pg_database_size,

    pg_size_pretty(
        SUM(pg_relation_size(oid, 'main'))::bigint +
        SUM(pg_relation_size(oid, 'vm'))::bigint +
        SUM(pg_relation_size(oid, 'fsm'))::bigint +
        SUM(pg_indexes_size(oid))::bigint +
        SUM(
            CASE reltoastrelid
            WHEN 0 THEN 0
            ELSE pg_total_relation_size(reltoastrelid)
            END
        )::bigint - pg_database_size(current_database())::bigint
    ) AS diff

FROM pg_class
WHERE reltype != 0;