主页 / dba / 问题 / 23475
Accepted
Chris Travers
Asked: 2012-08-31 17:27:40 +0800 CST

子集聚合的建模约束？

  • 772

我正在使用 PostgreSQL，但我认为大多数高端数据库必须具有一些类似的功能，而且，它们的解决方案可能会激发我的解决方案，所以不要考虑这个 PostgreSQL 特定的。

我知道我不是第一个尝试解决这个问题的人，所以我认为值得在这里提问，但我正在尝试评估建模会计数据的成本，以使每笔交易都能从根本上平衡。会计数据是仅附加的。这里的整体约束（用伪代码编写）可能大致如下：

CREATE TABLE journal_entry (
    id bigserial not null unique, --artificial candidate key
    journal_type_id int references  journal_type(id),
    reference text, -- source document identifier, unique per journal
    date_posted date not null,
    PRIMARY KEY (journal_type_id, reference)
);

CREATE TABLE journal_line (
    entry_id bigint references journal_entry(id),
    account_id int not null references account(id),
    amount numeric not null,
    line_id bigserial not null unique,
    CHECK ((sum(amount) over (partition by entry_id) = 0) -- this won't work
);

显然，这样的检查约束永远不会起作用。它按行运行，并可能检查整个数据库。所以它总是会失败并且做起来很慢。

所以我的问题是模拟这个约束的最佳方法是什么？到目前为止，我基本上看过两个想法。想知道这些是否是唯一的，或者是否有人有更好的方法（除了将其留给应用程序级别或存储过程）。

  1. 我可以从会计界关于原始分录簿和最终分录簿（普通日记帐与总帐）之间区别的概念中借一页。在这方面，我可以将其建模为附加到日记条目的日记行数组，对数组强制执行约束（在 PostgreSQL 术语中，从 unnest(je.line_items) 中选择 sum(amount) = 0。触发器可以扩展和将这些保存到行项目表中，可以更轻松地执行单个列约束，并且索引等可能更有用。这是我倾向于的方向。
  2. 我可以尝试编写一个约束触发器，该触发器将强制每个事务执行此操作，并认为一系列 0 的总和将始终为 0。

我正在权衡这些与当前在存储过程中执行逻辑的方法。复杂性成本正在与约束的数学证明优于单元测试的想法进行权衡。上面 #1 的主要缺点是，作为元组的类型是 PostgreSQL 中的一个领域之一，在该领域中，人们会遇到不一致的行为和定期更改假设，因此我什至希望该领域的行为可能会随着时间而改变。设计未来的安全版本并不容易。

是否有其他方法可以解决这个问题，将每个表中的记录扩展到数百万条？我错过了什么吗？有没有我错过的权衡？

为了回应 Craig 关于版本的以下观点，至少必须在 PostgreSQL 9.2 及更高版本上运行（可能是 9.1 及更高版本，但可能我们可以直接使用 9.2）。

database-design postgresql

2 个回答

  1. Best Answer

    由于我们必须跨越多行，因此无法通过简单的CHECK约束来实现。

    我们也可以排除排除约束。这些将跨越多行，但只检查不等式。不可能进行复杂的操作，例如对多行求和。

    似乎最适合您的情况的工具是一个CONSTRAINT TRIGGER（甚至只是一个简单TRIGGER的 - 当前实现的唯一区别是您可以使用SET CONSTRAINTS.

    所以这是你的选择 2。

    一旦我们可以依赖始终强制执行的约束，我们就不需要再检查整个表了。仅检查当前事务中插入的行（事务结束时）就足够了。性能应该没问题。

    另外，作为

    会计数据是仅附加的。

    ...我们只需要关心新插入的行。（假设UPDATE或DELETE不可能。）

    我使用系统列xid并将其与txid_current()返回xid当前事务的函数进行比较。为了比较类型，需要强制转换...... 这应该是相当安全的。考虑这个相关的，以后用更安全的方法回答：

    演示

    CREATE TABLE journal_line(amount int); -- simplistic table for demo

CREATE OR REPLACE FUNCTION trg_insaft_check_balance()
    RETURNS trigger AS
$func$
BEGIN
   IF sum(amount) <> 0
      FROM journal_line 
      WHERE xmin::text::bigint = txid_current()  -- consider link above
         THEN
      RAISE EXCEPTION 'Entries not balanced!';
   END IF;

   RETURN NULL;  -- RETURN value of AFTER trigger is ignored anyway
END;
$func$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER insaft_check_balance
    AFTER INSERT ON journal_line
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE PROCEDURE trg_insaft_check_balance();

    Deferred，因此仅在事务结束时检查。

    测试

    INSERT INTO journal_line(amount) VALUES (1), (-1);

    作品。

    INSERT INTO journal_line(amount) VALUES (1);

    失败：

    错误：条目不平衡！

    BEGIN;
INSERT INTO journal_line(amount) VALUES (7), (-5);
-- do other stuff
SELECT * FROM journal_line;
INSERT INTO journal_line(amount) VALUES (-2);
-- INSERT INTO journal_line(amount) VALUES (-1); -- make it fail
COMMIT;

    作品。:)

    如果您需要在事务结束之前强制执行您的约束，您可以在事务中的任何时候执行此操作，甚至在开始时：

    SET CONSTRAINTS insaft_check_balance IMMEDIATE;

    使用普通触发器更快

    如果您使用多行操作，INSERT则触发每个语句会更有效 - 这对于约束触发器是不可能的：

    只能指定约束触发器FOR EACH ROW。

    改用普通触发器并开火FOR EACH STATEMENT...

    • 失去选择权SET CONSTRAINTS。
    • 获得性能。

    可以删除

    回复您的评论：如果DELETE可能，您可能会在 DELETE 发生后添加类似的触发器来执行全表余额检查。这会更昂贵，但并不重要，因为它很少发生。

    • 13

  2. 以下 SQL Server 解决方案仅使用约束。我在系统中的多个地方使用了类似的方法。

    CREATE TABLE dbo.Lines
  (
    EntryID INT NOT NULL ,
    LineNumber SMALLINT NOT NULL ,
    CONSTRAINT PK_Lines PRIMARY KEY ( EntryID, LineNumber ) ,
    PreviousLineNumber SMALLINT NOT NULL ,
    CONSTRAINT UNQ_Lines UNIQUE ( EntryID, PreviousLineNumber ) ,
    CONSTRAINT CHK_Lines_PreviousLineNumber_Valid CHECK ( ( LineNumber > 0
            AND PreviousLineNumber = LineNumber - 1
          )
          OR ( LineNumber = 0 ) ) ,
    Amount INT NOT NULL ,
    RunningTotal INT NOT NULL ,
    CONSTRAINT UNQ_Lines_FkTarget UNIQUE ( EntryID, LineNumber, RunningTotal ) ,
    PreviousRunningTotal INT NOT NULL ,
    CONSTRAINT CHK_Lines_PreviousRunningTotal_Valid CHECK 
        ( PreviousRunningTotal + Amount = RunningTotal ) ,
    CONSTRAINT CHK_Lines_TotalAmount_Zero CHECK ( 
            ( LineNumber = 0
                AND PreviousRunningTotal = 0
              )
              OR ( LineNumber > 0 ) ),
    CONSTRAINT FK_Lines_PreviousLine 
        FOREIGN KEY ( EntryID, PreviousLineNumber, PreviousRunningTotal )
        REFERENCES dbo.Lines ( EntryID, LineNumber, RunningTotal )
  ) ;
GO

-- valid subset inserts
INSERT INTO dbo.Lines(EntryID ,
        LineNumber ,
        PreviousLineNumber ,
        Amount ,
        RunningTotal ,
        PreviousRunningTotal )
VALUES(1, 0, 2, 10, 10, 0),
(1, 1, 0, -5, 5, 10),
(1, 2, 1, -5, 0, 5);

-- invalid subset fails
INSERT INTO dbo.Lines(EntryID ,
        LineNumber ,
        PreviousLineNumber ,
        Amount ,
        RunningTotal ,
        PreviousRunningTotal )
VALUES(2, 0, 1, 10, 10, 5),
(2, 1, 0, -5, 5, 10) ;
    • 4

相关问题