Re: UPDATE ... ON CONFLICT DO NOTHING

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Hi Adrian,

in _uids array I have all user ids of player.

I want to merge his or her data, including reviews, to a single user id: out_uid.

So I make a copy of related words_reviews records (where this user has been rated or this user has rated someone) and then re-INSERT ON CONFLICT DO NOTHING those records into same table (but change the "uid" or "author" column). And finally DELETE old records.

I hope my question is not too annoying, just trying to pick up tricks and better strategies here.

Thank you

Alex

P.S. Below is my table data and the complete custom function for your convenience -

CREATE TABLE words_social (
        sid varchar(255) NOT NULL,

        social integer NOT NULL CHECK (0 <= social AND social <= 6),  /* Facebook, Google+, Twitter, ... */
        female integer NOT NULL CHECK (female = 0 OR female = 1),
        given  varchar(255) NOT NULL CHECK (given ~ '\S'),
        family varchar(255),
        photo  varchar(255) CHECK (photo ~* '^https?://...'),
        place  varchar(255),
        stamp  integer NOT NULL,

        uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
        PRIMARY KEY(sid, social)
);

CREATE TABLE words_users (
        uid SERIAL PRIMARY KEY,

        created timestamptz NOT NULL,
        visited timestamptz NOT NULL,
        ip inet NOT NULL,
.....
        win    integer NOT NULL CHECK (win >= 0),
        loss   integer NOT NULL CHECK (loss >= 0),
        draw   integer NOT NULL CHECK (draw >= 0),

        elo    integer NOT NULL CHECK (elo >= 0),
        medals integer NOT NULL CHECK (medals >= 0),
        coins  integer NOT NULL
);

CREATE TABLE words_reviews (
        uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users ON DELETE CASCADE,
        author integer NOT NULL REFERENCES words_users(uid) ON DELETE CASCADE,
        nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
        review varchar(255),
        updated timestamptz NOT NULL,
        PRIMARY KEY(uid, author)
);

CREATE OR REPLACE FUNCTION words_merge_users(
                in_users jsonb,
                in_ip inet,
                OUT out_uid integer
        ) RETURNS RECORD AS
$func$
DECLARE
        _user          jsonb;
        _uids          integer[];
        -- the variables below are used to temporary save new user stats
        _created       timestamptz;
        _win           integer;
        _loss          integer;
        _draw          integer;
        _elo           integer;
        _medals        integer;
        _coins         integer;
BEGIN
        -- in_users must be a JSON array with at least 1 element
        IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
                RAISE EXCEPTION 'Invalid users = %', in_users;
        END IF;

        _uids := (
                SELECT ARRAY_AGG(DISTINCT uid)
                FROM words_social
                JOIN JSONB_ARRAY_ELEMENTS(in_users) x
                        ON sid = x->>'sid'
                        AND social = (x->>'social')::int
        );

        IF _uids IS NULL THEN
                -- no users found -> create a new user
                INSERT INTO words_users (
                        created,
                        visited,
                        ip,
                        win,
                        loss,
                        draw,
                        elo,
                        medals,
                        coins
                ) VALUES (
                        CURRENT_TIMESTAMP,
                        CURRENT_TIMESTAMP,
                        in_ip,
                        0,
                        0,
                        0,
                        1500,
                        0,
                        0
                ) RETURNING uid INTO STRICT out_uid;

        ELSIF CARDINALITY(_uids) = 1 THEN
                -- just 1 user found -> update timestamp and IP address
                SELECT
                        uid
                INTO STRICT
                        out_uid
                FROM words_users
                WHERE uid = _uids[1];

                UPDATE words_users SET
                        visited = CURRENT_TIMESTAMP,
                        ip      = in_ip
                WHERE uid = out_uid;
        ELSE       
                -- few users found -> merge their records to a single one
                SELECT
                        MIN(uid),
                        MIN(created),
                        SUM(win),
                        SUM(loss),
                        SUM(draw),
                        AVG(elo),
                        SUM(medals),
                        SUM(coins)
                INTO STRICT
                        out_uid,
                        _created,
                        _win,
                        _loss,
                        _draw,
                        _elo,
                        _medals,
                        _coins
                FROM words_users
                WHERE uid = ANY(_uids);

                -- try to copy as many reviews of this user as possible
                INSERT INTO words_reviews (
                        uid,
                        author,
                        nice,
                        review,
                        updated
                ) SELECT
                        out_uid,
                        author,
                        nice,
                        review,
                        updated
                FROM words_reviews
                WHERE uid <> out_uid
                AND uid = ANY(_uids)
                ON CONFLICT DO NOTHING;

                DELETE FROM words_reviews
                WHERE uid <> out_uid
                AND uid = ANY(_uids);

                -- try to copy as many reviews by this user as possible
                INSERT INTO words_reviews (
                        uid,
                        author,
                        nice,
                        review,
                        updated
                ) SELECT
                        uid,
                        out_uid,
                        nice,
                        review,
                        updated
                FROM words_reviews
                WHERE author <> out_uid
                AND author = ANY(_uids)
                ON CONFLICT DO NOTHING;

                DELETE FROM words_reviews
                WHERE author <> out_uid
                AND author = ANY(_uids);

                UPDATE words_social
                SET uid = out_uid
                WHERE uid = ANY(_uids);

                DELETE FROM words_users
                WHERE uid <> out_uid
                AND uid = ANY(_uids);

                UPDATE words_users SET
                        visited       = CURRENT_TIMESTAMP,
                        ip            = in_ip,
                        created       = _created,
                        vip_until     = out_vip,
                        grand_until   = out_grand,
                        banned_until  = out_banned,
                        banned_reason = out_reason,
                        win           = _win,
                        loss          = _loss,
                        draw          = _draw,
                        elo           = _elo,
                        medals        = _medals,
                        coins         = _coins
                WHERE uid = out_uid;

                -- TODO merge playing stats here
        END IF;

        FOR _user IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
        LOOP
                IF NOT words_valid_user((_user->>'social')::int,
                                         _user->>'sid',
                                         _user->>'auth') THEN
                        RAISE EXCEPTION 'Invalid user = %', _user;
                END IF;

                UPDATE words_social SET
                        social = (_user->>'social')::int,
                        female = (_user->>'female')::int,
                        given  =  _user->>'given',
                        family =  _user->>'family',
                        photo  =  _user->>'photo',
                        place  =  _user->>'place',
                        stamp  = (_user->>'stamp')::int,
                        uid    = out_uid                                            
                WHERE sid = _user->>'sid'
                AND social = (_user->>'social')::int;

                IF NOT FOUND THEN
                        INSERT INTO words_social (
                                sid,
                                social,
                                female,
                                given,
                                family,
                                photo,
                                place,
                                stamp,
                                uid
                        ) VALUES (
                                 _user->>'sid',
                                (_user->>'social')::int,
                                (_user->>'female')::int,
                                 _user->>'given',
                                 _user->>'family',
                                 _user->>'photo',
                                 _user->>'place',
                                (_user->>'stamp')::int,
                                out_uid
                        );
                END IF;
        END LOOP;
END
$func$ LANGUAGE plpgsql;

-- usage example:

-- SELECT out_uid FROM words_merge_users('[{"sid":"abcde","auth":"1fe693affff84cb1e961857cccffffff","social":1,"given":"Abcde1","female":0,"stamp":1450102770},{"sid":"abcde","auth":"2fe693affff84cb1e961857cccffffff","social":2,"given":"Abcde2","female":0,"stamp":1450102880},{"sid":"abcde","auth":"3fe693affff84cb1e961857cccffffff","social":3,"given":"Abcde3","female":0,"stamp":1450102990},{"sid":"abcde","auth":"4fe693affff84cb1e961857cccffffff","social":4,"given":"Abcde4","female":0,"stamp":1450109999}]'::jsonb, '0.0.0.0'::inet);