Read Write Conflicts
Something I learnt the hard way by getting the computer to play games over-and-over and then update the average scores each time was it crashed my Postgresql functions with
ERROR: relation "_states" does not exist
LINE 1: SELECT id FROM _states WHERE state = NULL
The problem was garbage values were getting returned from queries if their table was being updated, and the solution was:
SELECT FOR UPDATE
Note ERROR: FOR UPDATE is not allowed with aggregate functions.
Postgresql offers a variant which may be more efficient, but I didn’t use.
SELECT FOR SHARE
An alternative approach is Transaction Isolation.
Eliminate explicit locks, SELECT FOR UPDATE, and SELECT FOR SHARE where no longer needed due to the protections automatically provided by Serializable transactions.
Increasing locks a good idea?
Before hitting the above snag, I got these crashes:
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
This involved editing the postgresql.conf setting:
grep max_locks_per_transaction /var/lib/postgres/data/postgresql.conf
#max_locks_per_transaction = 64 # min 10
Changed to max_locks_per_transaction = 1024 and restarted the server.
The next snag was a hung psql from CALL playgame('{"game": "chinese_checkers_2", "times": 1}');
pg_locks, as in:
SELECT count(*) FROM pg_locks;
returned 111612 locks.
Handling hangs
SELECT pid, query FROM pg_stat_activity where state = 'active';
SELECT pg_cancel_backend(PID);