Enforce multi-table database logic in PostgreSQL

Question

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.

Community · Accepted Answer · 2017-05-23 12:29:58Z

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

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jun 17, 2015 at 7:38

jurhas

6631 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

rosuav Over a year ago

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 Over a year ago

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.

jurhas Over a year ago

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.

David Aldridge Over a year ago

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.

rosuav Over a year ago

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.

|

Collectives™ on Stack Overflow

Enforce multi-table database logic in PostgreSQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related