Modelowanie ograniczeń agregatów podzbiorów?

Korzystam z PostgreSQL, ale uważam, że większość najlepszych baz danych musi mieć podobne możliwości, a ponadto, że rozwiązania dla nich mogą inspirować rozwiązania dla mnie, więc nie rozważaj tego specyficznego dla PostgreSQL.

Wiem, że nie jestem pierwszym, który próbuje rozwiązać ten problem, więc myślę, że warto o to zapytać, ale staram się oszacować koszty modelowania danych księgowych, aby każda transakcja była zasadniczo zrównoważona. Dane księgowe są tylko do dodawania. Ogólne ograniczenie (zapisane w pseudokodzie) może wyglądać mniej więcej tak:

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

Oczywiście takie ograniczenie sprawdzania nigdy nie zadziała. Działa dla każdego wiersza i może sprawdzać całą bazę danych. Tak więc zawsze to zawiedzie i będzie powolne.

Więc moje pytanie brzmi: jaki jest najlepszy sposób modelowania tego ograniczenia? Do tej pory w zasadzie przyjrzałem się dwóm pomysłom. Zastanawiasz się, czy są to jedyne, czy ktoś ma lepszy sposób (inny niż pozostaw to na poziomie aplikacji lub przechowywanym proc).

1. Mógłbym pożyczyć stronę ze świata rachunkowości dotyczącą różnicy między księgą oryginalnego wpisu a księgą końcowego wpisu (dziennik ogólny vs. księga główna). W związku z tym mógłbym zamodelować to jako tablicę wierszy dziennika dołączonych do wpisu dziennika, wymusić ograniczenie na tablicy (w terminach PostgreSQL wybierz sumę (ilość) = 0 z Unnest (je.line_items). Wyzwalacz może się rozwinąć i zapisz je w tabeli elementów zamówienia, gdzie można by łatwiej egzekwować ograniczenia poszczególnych kolumn, i gdzie indeksy itp. mogłyby być bardziej przydatne. W tym kierunku się opieram.
2. Mógłbym spróbować zakodować wyzwalacz ograniczenia, który wymuszałby tę na transakcję, przy założeniu, że suma serii zer zawsze będzie wynosić 0.

Porównuję je z obecnym podejściem polegającym na wymuszaniu logiki w procedurze przechowywanej. Koszt złożoności porównuje się z ideą, że matematyczny dowód ograniczeń przewyższa testy jednostkowe. Główną wadą powyższego punktu 1 jest to, że typy jako krotki są jednym z tych obszarów w PostgreSQL, w których regularnie występują niespójne zachowania i zmiany w założeniach, dlatego miałbym nawet nadzieję, że zachowanie w tym obszarze może się z czasem zmienić. Projektowanie przyszłej bezpiecznej wersji nie jest takie łatwe.

Czy istnieją inne sposoby rozwiązania tego problemu, które skalują się do milionów rekordów w każdej tabeli? Czy coś brakuje? Czy brakuje mi kompromisu?

W odpowiedzi na poniższy punkt Craiga na temat wersji, będzie to musiało działać przynajmniej na PostgreSQL 9.2 i nowszych wersjach (może 9.1 i nowszych, ale prawdopodobnie możemy przejść do wersji 9.2).

Ponieważ musimy rozciągać się na wiele wierszy, nie można go wdrożyć z prostym CHECKograniczeniem.

Możemy również wykluczyć ograniczenia wykluczenia . Obejmowałyby one wiele wierszy, ale sprawdzałyby tylko nierówności. Złożone operacje, takie jak suma w wielu wierszach, nie są możliwe.

Narzędzie, które wydaje się najlepiej pasować do twojego przypadku to CONSTRAINT TRIGGER(Lub nawet zwykły TRIGGER- jedyną różnicą w obecnej implementacji jest to, że możesz dostosować czas wyzwalania za pomocą SET CONSTRAINTS.

To twoja opcja 2 .

Gdy będziemy mogli polegać na wymuszaniu ograniczenia przez cały czas, nie musimy już sprawdzać całej tabeli. Sprawdzanie tylko wierszy wstawionych w bieżącej transakcji - na koniec transakcji - jest wystarczające. Wydajność powinna być w porządku.

Także jako

Dane księgowe są tylko do dodawania.

... musimy tylko dbać o nowo wstawione wiersze. (Zakładając UPDATElub DELETEnie są możliwe.)

Korzystam z kolumny systemowej xidi porównuję ją z funkcją txid_current()- która zwraca xidbieżącą transakcję. Aby porównać typy, konieczne jest odlewanie ... Powinno to być dość bezpieczne. Zastanów się nad tym, a następnie odpowiedz bezpieczniejszą metodą:

• Jak wyświetlić krotki zmienione w transakcji PostgreSQL?

Próbny

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

Odroczony , więc jest sprawdzany dopiero pod koniec transakcji.

Testy

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

Pracuje.

INSERT INTO journal_line(amount) VALUES (1);

Nie działa:

BŁĄD: Wpisy niezrównoważone!

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;

Pracuje. :)

Jeśli chcesz wymusić ograniczenie przed końcem transakcji, możesz to zrobić w dowolnym momencie transakcji, nawet na początku:

SET CONSTRAINTS insaft_check_balance IMMEDIATE;

Szybszy dzięki zwykłemu spustowi

Jeśli operujesz z wieloma wierszami INSERT, bardziej skuteczne jest wyzwalanie według instrukcji - co nie jest możliwe w przypadku wyzwalaczy ograniczeń :

Wyzwalacze ograniczeń można określić tylko FOR EACH ROW.

Zamiast tego użyj zwykłego spustu i strzelaj, FOR EACH STATEMENTaby ...

• stracić opcję SET CONSTRAINTS.
• uzyskać wydajność.

USUŃ możliwe

W odpowiedzi na Twój komentarz: Jeśli DELETEto możliwe, możesz dodać podobny wyzwalacz, sprawdzając saldo całej tabeli po usunięciu. Byłoby to znacznie droższe, ale nie ma znaczenia, ponieważ rzadko się zdarza.

Poniższe rozwiązanie SQL Server wykorzystuje tylko ograniczenia. Korzystam z podobnych podejść w wielu miejscach w moim systemie.

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