Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and moreover, that solutions for them may inspire solutions for me, so don't consider this PostgreSQL-specific.

I know I am not the first to try to solve this problem so I figure it is worth asking here but I am trying to evaluate the costs of modelling accounting data such that every transaction is fundamentally balanced. The accounting data is append-only. The overall constraint (written in pseudo-code) here might look roughly like:

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

Obviously such a check constraint will never work. It operates per row and might check over the entire db. So it will always fail and be slow doing it.

So my question is what is the best way to model this constraint? I have basically looked at two ideas so far. Wondering if these are the only ones, or if someone has a better way (other than leave it to the app level or a stored proc).

  1. I could borrow a page from the accounting world's concept of the difference between a book of original entry and a book of final entry (general journal vs general ledger). In this regard I could model this as an array of journal lines attached to the journal entry, enforce the constraint on the array (in PostgreSQL terms, select sum(amount) = 0 from unnest(je.line_items). A trigger could expand and save these to a line items table, where individual column constraints could more easily be enforced, and where indexes etc could be more useful. This is the direction I am leaning.
  2. I could try to code a constraint trigger that would enforce this per transaction with the idea that the sum of a series of 0's will always be 0.

I am weighing these against the current approach of enforcing the logic in a stored procedure. The complexity cost is being weighed against the idea that mathematical proof of constraints are superior to unit tests. The major drawback of #1 above is that types as tuples is one of those areas in PostgreSQL where one runs into inconsistent behavior and changes in assumptions regularly and so I would even hope that behavior in this area might change over time. Designing a future safe version is not so easy.

Are there other ways to solve this problem that will scale up to millions of records in each table? Am I missing something? Is there a tradeoff I have missed?

In response to Craig's point below about versions, at a minimum, this will have to run on PostgreSQL 9.2 and higher (maybe 9.1 and higher, but probably we can go with straight 9.2).

share|improve this question
1  
How about a link to the SO post and a PostgreSQL version? –  Craig Ringer Aug 31 '12 at 3:52
    
added and thanks! As for versions, I could probably swing 9.2 as a requirement, might have to go to 9.1 as a requirement, but can certainly not worry about 9.0 and lower. –  Chris Travers Aug 31 '12 at 5:11
    
So, you want to insert all the lines for an entry at once, and you want to make sure that the sum of amounts for the entry is zero? If this is what you want, I can show you how to do that in SQL Server. –  AlexKuznetsov Aug 31 '12 at 16:25
add comment

2 Answers

up vote 8 down vote accepted

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 of 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.

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()
         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.

share|improve this answer
    
I hadn't thought of using the txid for that before....I've got a project that it will help on right now! Awesome. –  rfusca Aug 31 '12 at 18:20
    
So this is a vote for item #2. The advantage is you only have a single table for all constraints and that is a complexity win there, but on the other, you are setting up triggers that are essentially procedural and therefore if we are unit testing things not proven declaratively, then that gets more complicated. How would you weight hat against having a nested storage with declarative constraints? –  Chris Travers Sep 1 '12 at 4:30
    
Also update is not possible, delete might be under certain circumstances* but would almost certainly be a very narrow, well-tested procedure. For practical purposes, delete can be ignored as a constraint issue. * For example, purging all data over 10 years old which would be possible only if using a log, aggregate, and snapshot model which is pretty typical in accounting systems anyway. –  Chris Travers Sep 1 '12 at 10:29
    
@ChrisTravers. I added an update and addressed possible DELETE. I wouldn't know what is typical or required in accounting - not my area of expertise. Just trying to provide a (pretty effective IMO) solution to the problem described. –  Erwin Brandstetter Sep 1 '12 at 19:47
    
@Erwin Brandstetter I wouldn't worry about that for deletes. The deletes, if applicable, would be subject to a much larger set of constraints and unit tests are pretty much entirely unavoidable there. I was mostly wondering about thoughts on complexity costs. At any rate deletes can be solved very simply with an on delete cascade fkey. –  Chris Travers Sep 2 '12 at 1:44
add comment

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) ;
share|improve this answer
    
Nice. The AND PreviousLineNumber > 0 part in the CHK_Lines_PreviousLineNumber_Valid constraint is not needed I think (it only ensures that every entry consists of 2 or more lines and never of 1 line). –  ypercube Sep 1 '12 at 0:37
    
that's an interesting approach. The constraints there seem to work on the statement rather than tuple or transaction level, right? Also it means that your subsets have subset ordering built in, correct? That's a really fascinating approach and although it definitely does not translate directly to Pgsql, it is still inspiring ideas. Thanks! –  Chris Travers Sep 1 '12 at 9:11
    
@Chris: I think it works just fine in Postgres (after removing the dbo. and the GO): sql-fiddle –  ypercube Sep 1 '12 at 9:38
    
Ok, I was misunderstanding it. It does look like one could use a similar solution here. However wouldn't you need a separate trigger to look up the previous line's subtotal in order to be safe? Otherwise you are trusting your app to send in sane data, right? It is still an interesting model I might be able to adapt. –  Chris Travers Sep 1 '12 at 10:35
    
BTW, upvoted both solutions. Going to be listing the other as preferable because it seems less complex. However I think this is a very interesting solution and it opens up new ways of thinking about very complex constraints for me. Thanks! –  Chris Travers Sep 2 '12 at 1:47
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.