0

Is there a way to enforce database logic that spans tables? As a very VERY simple super-cut-down example, imagine a two-table invoice system:

create table accounts (id serial primary key, balance integer not null default 0);
create table invoices (id serial primary key, acct integer references accounts, total integer not null);

Raising an invoice consists of inserting a row into the invoices table, and updating the accounts table with the new balance. (Yes, I'm aware there are normalization issues here, but there are other reasons for wanting the balance to be in the primary table. Also, this is hugely oversimplified.)

There is an invariant here: For any given account, (select balance from accounts where id=N)==(select sum(total) from invoices where acct=N) - or, putting it another way, select acct,sum(total) from invoices group by acct should be the same as select id,balance from accounts (although the latter will have zeroes for those with no invoices).

Is there a way to enforce this in PostgreSQL? I'd rather not have to trust the client code.

rosuav
  • 466
  • 4
  • 12

1 Answers1

1

I think you need also a trigger. Catch the Before Insert or After Insert event, and Before Update or After Update event. (I catched ever before, but for portability reason now I catch the after) On Insert and update, you updates also all the columns you need. Here how you build a trigger: https://stackoverflow.com/questions/30824126/postgresql-how-to-autofill-column-based-on-other-previous-values/30824693?noredirect=1#comment49795847_30824693

Community
  • 1
  • 1
jurhas
  • 613
  • 1
  • 4
  • 12
  • Okay. I was thinking a constraint was the more normal way to implement, yaknow, constraints, but if it has to be a trigger, so be it. – rosuav Jun 17 '15 at 07:42
  • FWIW I don't want to autofill the balance based on the invoice; the logic is "first update the accounts table, then insert into the invoices table", because that ensures that a lock will be held while updates are being done. – rosuav Jun 17 '15 at 07:43
  • I don't know if there is a possibility what you need. A constrain would need I think I third table. But it is dangerous not update both table in the same transaction. May be you do not give at all the insert rights on accouts, and you build the trigger with owner superuser. – jurhas Jun 17 '15 at 07:53
  • Triggers cannot take into account any changes made by uncommited transactions, so they are not a 100% reliable mechanism for enforcing constraints of this sort. – David Aldridge Jun 17 '15 at 09:56
  • 1
    jurhas: Naturally both tables will be updated in the same transaction. I'll poke around with ways of doing this; absolute worst case, I just deny direct inserts and updates, and make a stored procedure that does the work. But rather than do that, I'll do my proof-of-concept by simply having the application maintain the invariant - unideal for production but fine for a POC. – rosuav Jun 17 '15 at 09:56
  • David: The trigger shouldn't ever need to see any uncommitted transaction other than its own. – rosuav Jun 17 '15 at 09:57
  • This is Postgres. It has also constraint trigger. That allow to act like a costraint. I love this db. – jurhas Jun 17 '15 at 21:17