purchasing item by client - how to ensure that user has enough funds? I have to tables: 

users (id serial, coins integer not null default 0)
purchases (id serial, price integer not null, user_id)


When new row is inserted into purchases table I want to subtract amount from user.coins column and ensure that user.coins &gt;= 0 (so user had enough founds to make a purchase).

I want to do it in AFTER INSERT trigger:

UPDATE users SET coins = coins - NEW.price WHERE id = NEW.user_id;
RETURN NULL;


My question is how to ensure that user won't spend more coins than he posses? My first idea is to just make CHECK constraint on users table like this CHECK (coins &gt;= 0) - will that work with multiple concurrent inserts on users table?
You've got to be really careful in cases like these. The default transaction isolation level PostgreSQL uses is READ COMMITTED, and in that mode you can easily get nasty cases where two transactions are performing checks like this:

-- You will get READ COMMITTED by default.
BEGIN;

-- Make sure user has enough coins.
-- DANGER Nasty race condition in READ COMMITTED!
SELECT coins FROM users WHERE id = 123;

-- Just to make the race condition visible...
SELECT pg_sleep(10);

-- We think we have enough coins, go ahead and perform the UPDATE
UPDATE users SET coins = ... WHERE id = 123;

-- And since we think we've deducted the coins, record the purchase
INSERT INTO purchases ... ;

COMMIT;


If you want to see the race condition in action, run that same SQL through two psql sessions at the same time. You'll see that both sessions will think that the user has enough coins to purchase an item, and will both record a purchase. (Note, the exact same problem would exist with a CHECK constraint on the users table with READ COMMITTED transactions.)

How to fix this race condition? Read up on transaction isolation levels, you probably want to be using SERIALIZABLE for important accounting code like checking account balances and recording purchases. Another option is to use  SELECT ... FOR UPDATE to obtain a row-level lock on, say, the users table while you're checking the account balance, which will ensure that two transactions can't be performing that same delicate check at the same time.