Postgres Pro
Downloads
Home   >   mailing lists

Thread: Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

From
Alexander Farber
Date:
Good afternoon,

I have a question please.

In one table I store user ids and their IP addresses -

        CREATE TABLE words_users (
                uid SERIAL PRIMARY KEY,
                ip inet NOT NULL
        );

And in another table I keep 2-player games and timestamps of last moves (NULL if a player hasn't played yet):

        CREATE TABLE words_games (
                gid SERIAL PRIMARY KEY,
                finished timestamptz,

                player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL,
                player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,

                played1 timestamptz,
                played2 timestamptz
        );

When a user wants to start a new game, I first check if there is maybe a new game already available - with just 1 player while the other "seat" is vacant:

                UPDATE words_games g1
                SET    player2 = in_uid
                FROM (
                        SELECT gid
                        FROM   words_games
                        WHERE  finished IS NULL
                        AND    player1 <> in_uid
                        AND    played1 IS NOT NULL
                        AND    player2 IS NULL
                        LIMIT  1
                        FOR    UPDATE SKIP LOCKED
                ) g2
                WHERE     g1.gid = g2.gid
                RETURNING g1.gid
                INTO      out_gid;

This code works great, but now I am trying to add an (obviously not solving all cheating/proxy/etc. problems) check, that the IP addresses of both users must be different.

Fetching "ip" in the internal SELECT statement is trivial with:

                UPDATE words_games g1
                SET    player2 = in_uid
                FROM (
                        SELECT g.gid, u.ip
                        FROM   words_games g, words_users u
                        WHERE  g.finished IS NULL
                        AND    g.player1 <> in_uid
                        AND    g.played1 IS NOT NULL
                        AND    g.player2 IS NULL
                        ON (g.player1 = u.uid)
                        LIMIT  1
                        FOR    UPDATE SKIP LOCKED
                ) g2
                WHERE     g1.gid = g2.gid
                RETURNING g1.gid
                INTO      out_gid;

But how to fetch the "ip" column in the surrounding UPDATE statement?

Thank you
Alex

Re: Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

From
Alexander Farber
Date:
Thank you Brian and others, but -

On Fri, Sep 9, 2016 at 5:22 PM, Brian Dunavant <brian@omniti.com> wrote:
I'm making the assumption that you only have one ip/user in words_users.

with lockrow as (
   SELECT g.gid, u.ip
   FROM   words_games g join words_users u
        ON (g.player1 = u.uid)
  WHERE  g.finished IS NULL
    AND    g.player1 <> in_uid
    AND    g.played1 IS NOT NULL
    AND    g.player2 IS NULL
   LIMIT  1
   FOR    UPDATE SKIP LOCKED
), do_the_update as (
   UPDATE words_games g1
   SET    player2 = in_uid
   FROM lockrow g2
   WHERE     g1.gid = g2.gid
   RETURNING g1.gid, g1.player2
)
select m.gid into out_gid, u.ip into out_uip
from do_the_update m
  join lockrow u on (gid)
;

The general idea being lock the row in the first CTE, update it in the
second, returning your values, and then query against those in the
final select to get the ip.  If it didn't update anything, you'll get
no results.

unfortunately, the above query does not seem to ensure, that players with same ip can not join the same game, which is actually my question... 

But thanks for showing the CTE for UPDATE ... RETURNING - that is probably the way to go for me

Regards
Alex