Good afternoon, in PostgreSQL 9.5 with pgbouncer (having "pool_mode = session" and "server_reset_query = DISCARD ALL") 2-player games are stored in the following table:
CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, finished timestamptz, player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2), player2 integer REFERENCES words_users(uid) ON DELETE CASCADE, played1 timestamptz, played2 timestamptz ); And every hour I run a custom PL/pgSQL function to forcibly finish games, where one of the players hasn't played any move since more than 24h: https://gist.github.com/afarber/416da460e5722ab1e3ed25385cea6cae (also copy-pasted at the bottom of this mail). However there is a problem: I can not use a "single-instance" cronjob to run words_expire_games hourly. I have to use the HttpServlet (so that I can send notifications to the websocket-clients) and as result multiple servlet's might end up running at the same time. My question is if I should ensure that only 1 servlet runs the custom PL/pgSQL function by using "synchronized" in Java as I do it right now: private static final String SQL_EXPIRE_GAMES = "SELECT " + "out_uid AS uid, " + "out_gid AS gid, " + "out_fcm AS fcm, " + "out_apns AS apns, " + "out_sns AS sns, " + "out_note AS note " + "FROM words_expire_games()"; // the timestamp in milliseconds of the last successful hourly job run private static long sLastRun = 0L; // this method is run every time the servlet is called (i.e. very often) private void hourlyJob() throws SQLException, IOException { if (System.currentTimeMillis() - sLastRun < ONE_HOUR) { return; } synchronized (MyListener.class) { if (System.currentTimeMillis() - sLastRun < ONE_HOUR) { return; } try (PreparedStatement st = mDatabase.prepareStatement(SQL_EXPIRE_GAMES)) { try (ResultSet rs = st.executeQuery()) { while (rs.next()) { Notification n = new Notification( rs.getInt(KEY_UID), rs.getInt(KEY_GID), true, rs.getString(KEY_FCM), rs.getString(KEY_APNS), rs.getString(KEY_SNS), rs.getString(KEY_NOTE) ); sendNotification(n); // send notifications about forcibly finished games via websockets } } } sLastRun = System.currentTimeMillis(); } } Or if maybe there is some condition (maybe "UPDATE SKIP LOCKED"?) I could add to my custom function copy-pasted below? - Thank you for any insights Alex CREATE OR REPLACE FUNCTION words_expire_games( ) RETURNS TABLE ( out_uid integer, -- the player to be notified out_gid integer, -- which game has expired out_fcm text, out_apns text, out_sns text, out_note text ) AS $func$ DECLARE _gid integer; _loser integer; _winner integer; BEGIN FOR _gid, _loser, _winner IN UPDATE words_games SET finished = CURRENT_TIMESTAMP WHERE finished IS NULL AND played1 IS NOT NULL AND played2 IS NOT NULL AND (played1 < CURRENT_TIMESTAMP - INTERVAL '24 hours' OR played2 < CURRENT_TIMESTAMP - INTERVAL '24 hours') RETURNING gid, CASE WHEN played1 < played2 THEN player1 ELSE player2 END, CASE WHEN played1 < played2 THEN player2 ELSE player1 END LOOP -- log the last "move" INSERT INTO words_moves ( action, gid, uid, played, tiles ) VALUES ( 'expire', _gid, _loser, CURRENT_TIMESTAMP, null ); -- notify the loser SELECT uid, _gid, fcm, apns, sns, 'You have lost (game expired)!' FROM words_users WHERE uid = _loser INTO STRICT out_uid, out_gid, out_fcm, out_apns, out_sns, out_note; RETURN NEXT; -- notify the winner SELECT uid, _gid, fcm, apns, sns, 'You have won (game expired)!' FROM words_users WHERE uid = _winner INTO STRICT out_uid, out_gid, out_fcm, out_apns, out_sns, out_note; RETURN NEXT; END LOOP; END $func$ LANGUAGE plpgsql;