Modelling constraints on subset aggregates?

As we have to span multiple rows it cannot be implemented with a simple CHECK constraint.

We can also rule out exclusion constraints. Those would span multiple rows, but only check for inequality. Complex operations like a sum over multiple rows are not possible.

The tool that seems to best fit your case is a CONSTRAINT TRIGGER (Or even just a plain TRIGGER - the only difference in the current implementation is that you can adjust the timing of the trigger with SET CONSTRAINTS.

So that's your option 2.

Once we can rely on the constraint being enforced at all times, we need not check the whole table any more. Checking only rows inserted in the current transaction - at the end of the transaction - is sufficient. Performance should be ok.

Also, as

The accounting data is append-only.

... we only need to care about newly inserted rows. (Assuming UPDATE or DELETE are not possible.)

I use the system column xid and compare it to the function txid_current() - which returns the xid of the current transaction. To compare the types, casting is needed ... This should be reasonably safe. Consider this related, later answer with a safer method:

• How to view tuples changed in a PostgreSQL transaction?

Demo

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, so it is only checked at the end of the transaction.

Tests

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

Works.

INSERT INTO journal_line(amount) VALUES (1);

Fails:

ERROR: Entries not balanced!

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;

Works. :)

If you need to enforce your constraint before the end of the transaction, you can do so at any point in the transaction, even at the start:

SET CONSTRAINTS insaft_check_balance IMMEDIATE;

Faster with plain trigger

If you operate with multi-row INSERT it is more effective to trigger per statement - which is not possible with constraint triggers:

Constraint triggers can only be specified FOR EACH ROW.

Use a plain trigger instead and fire FOR EACH STATEMENT to ...

• lose the option of SET CONSTRAINTS.
• gain performance.

DELETE possible

In reply to your comment: If DELETE is possible you might add similar trigger doing a whole-table balance check after a DELETE has happened. This would be much more expensive, but won't matter much as it rarely happens.

The following SQL Server solution uses only constraints. I am using similar approaches in multiple places in my system.

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) ;