Обсуждение: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

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;

I have tried:

  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
        FOR UPDATE SKIP LOCKED
  LOOP
    ...
  END LOOP;

but this fails with:

    ERROR:  syntax error at or near "FOR"

I have also described my problem at SO:

    https://stackoverflow.com/questions/45015368/how-to-handle-simultaneous-for-in-update-returning-loops

Thank you
Alex

Hi David,