Good afternoon,
I am working on a small game, where the mobile app initially sends social
network user data (name, city, gender) to PostgreSQL 9.5 backend and I
store that data in a table:
CREATE TABLE words_social (
sid varchar(255) NOT NULL,
social integer NOT NULL CHECK (0 <= social AND social <= 6), /*
Facebook, Googl+, 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)
);
And then I have another larger table holding the rest of user information:
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
);
Whenever the mobile app notices, that the user authenticated against
several social networks - I merge his data in my custom function:
CREATE OR REPLACE FUNCTION words_merge_users(
in_users jsonb,
in_ip inet,
OUT out_uid integer /* the user id of the merged user */
) 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
_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
);
........
-- few users found -> merge their records to a single one
IF CARDINALITY(_uids) > 1 THEN
SELECT
MIN(uid),
MIN(created),
SUM(win),
SUM(loss),
SUM(draw),
AVG(elo),
SUM(medals),
SUM(coins)
INTO STRICT
out_uid, /* this is the new user id */
_created,
_win,
_loss,
_draw,
_elo,
_medals,
_coins
FROM words_users
WHERE uid = ANY(_uids);
-- How to merge words_reviews? Please read below...
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,
win = _win,
loss = _loss,
draw = _draw,
elo = _elo,
medals = _medals,
coins = _coins
WHERE uid = out_uid;
END IF;
END
$func$ LANGUAGE plpgsql;
This works well, but now I have introduced a table where users can rate
each other ("author" can rate "uid"):
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)
);
And now I have a problem, because while merging user data I can not just:
UPDATE words_reviews /* This will produce conflicts...
*/
SET uid = out_uid
WHERE uid = ANY(_uids);
DELETE FROM words_reviews
WHERE uid <> out_uid
AND uid = ANY(_uids);
And same for the authoring part - I can not just:
UPDATE words_reviews /* This will produce conflicts...
*/
SET author = out_uid
WHERE author = ANY(_uids);
DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids);
Because this might result in PRIMARY KEY(uid, author) conflicts in the
words_reviews table.
I wish there would be possibility to add ON CONFLICT DO NOTHING /* and drop
the review */ to the both UPDATE's above, but there is no such thing
described at https://www.postgresql.org/docs/9.5/static/sql-update.html
What would you please recommend in my situation?
I'd like to merge user data including reviews and silently drop any
conflicting review records...
Regards
Alex
