Understanding Transactions And Locking In PostgreSQL
Automation With PLPGSQL
Now we need to actually do something useful with this information.
We will create a plpgsql function block that will determine if the user has a 5 dollar positive balance, insert into some other table (to simulate the purchase of some item), and then post a 5 dollar debit to the users account.
For those not familiar with plpgsql it’s extremely easy to use. Just be sure to use the
createlang binary in the PostgreSQL distribution to add plpgsql support to your database. (
yourdbname) as the postgres user.
CREATE OR REPLACE FUNCTION buysomething(varchar(255),TEXT) RETURNS integer AS $$ DECLARE account_balance NUMERIC(22,9); BEGIN SELECT (SUM(COALESCE(credit,0)) - SUM(COALESCE(debit,0)) INTO account_balance FROM accounting WHERE account_id = $1; IF account_balance >= 5 THEN INSERT INTO purchases (SomeTextCol) VALUES ($2); INSERT INTO accounting (account_id, debit) VALUES ($1, 5); ELSE RAISE EXCEPTION ‘The account % does not have enough free balance’, $1; END IF; END; $$ Language plpgsql;
Note: there are better ways to write this function, but this method demonstrates the most functionality.
So this function looks like it would do the job, but there’s a trick. What happens if this function is called twice at the same identical time?
Well what happens is between the balance selection and the insert into accounting, another debit could have been committed to the accounting table. There is no guarantee that after the select is run the balance will not change until the end of the function.