Обсуждение: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING
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)
);
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 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"):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;
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)
);
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);
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);
I have come up with the following (when trying to merge array _uids to a single out_uid):
-- 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;
-- 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;
Sorry, missed the last DELETE:
DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids);
DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids);
On 03/14/2017 06:52 AM, Alexander Farber wrote:
> I have come up with the following (when trying to merge array _uids to a
> single out_uid):
>
> -- 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);
Am I missing something, I thought word_reviews was?:
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)
);
So where are you getting?:
SELECT
out_uid,
...
FROM words_reviews
>
> -- 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;
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi Adrian,
in _uids array I have all user ids of player.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;
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);
-- 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);
On 03/14/2017 07:23 AM, Alexander Farber wrote:
> 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
Alright I see that you are setting out_uid above the INSERT.
So you are INSERTing rows and if they CONFLICT you leave them alone and
have the DELETE remove them, correct?
> 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);
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Yes, Adrian -
On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 03/14/2017 07:23 AM, Alexander Farber wrote:
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
Alright I see that you are setting out_uid above the INSERT.
So you are INSERTing rows and if they CONFLICT you leave them alone and have the DELETE remove them, correct?
that is correct and my question is:
copying records for later INSERT ON CONFLICT DO NOTHING seems ineffective to me.
I would prefer to just use UPDATE (for changing "uid" or "author" columns to out_uid value) - but how to handle the conflicts with UPDATE?
Thank you
Alex
P.S. I have just donated 20 Euro to paypal@postgresql.eu
On 03/14/2017 08:38 AM, Alexander Farber wrote: > Yes, Adrian - > > On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 03/14/2017 07:23 AM, Alexander Farber wrote: > > > 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 > > > Alright I see that you are setting out_uid above the INSERT. > So you are INSERTing rows and if they CONFLICT you leave them alone > and have the DELETE remove them, correct? > > > that is correct and my question is: > > copying records for later INSERT ON CONFLICT DO NOTHING seems > ineffective to me. > > I would prefer to just use UPDATE (for changing "uid" or "author" > columns to out_uid value) - but how to handle the conflicts with UPDATE? The more I think about it the more I think you are doing the same thing as INSERT ON CONFLICT DO UPDATE, where if there is a PK conflict you just want to update the values with data from excluded. There is inverse logic in the function that I have not wrapped my head around yet, so this is more a hunch then anything else. > > Thank you > Alex > > P.S. I have just donated 20 Euro to paypal@postgresql.eu > <mailto:paypal@postgresql.eu> > -- Adrian Klaver adrian.klaver@aklaver.com
My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then DELETE the rest (as I can't update them without a conflict) but I haven't figured it out yet...
Thank you anyway
On 03/14/2017 09:00 AM, Alexander Farber wrote: > My initial idea has been not to use ON CONFLICT at all, but instead only > UPDATE the words_reviews records (set "uid" or "author" to out_uid) for > which NO EXISTS already such a record with PK (uid, author)... and then I am afraid the logic is escaping me. If the record does not exist how can you UPDATE it? > DELETE the rest (as I can't update them without a conflict) but I > haven't figured it out yet... > > Thank you anyway -- Adrian Klaver adrian.klaver@aklaver.com
Have you tried with inner block and do nothing on exception;
WHERE uid = ANY(_uids);
BEGIN
<your code>
..............
BEGIN
UPDATE words_social
SET uid = out_uidWHERE uid = ANY(_uids);
EXCEPTION WHEN OTHERS THEN
--do nothing or write NULL means do nothing
END;
<your code>
..............
END;
14 Mar 2017 Sal, 19:01 tarihinde, Alexander Farber <alexander.farber@gmail.com> şunu yazdı:
My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then DELETE the rest (as I can't update them without a conflict) but I haven't figured it out yet...Thank you anyway
Hi Adrian -
On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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)
On 03/14/2017 09:00 AM, Alexander Farber wrote:My initial idea has been not to use ON CONFLICT at all, but instead only
UPDATE the words_reviews records (set "uid" or "author" to out_uid) for
which NO EXISTS already such a record with PK (uid, author)... and then
I am afraid the logic is escaping me. If the record does not exist how can you UPDATE it?
here the table with reviews of users "uid" done by users "author":
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)
);
Since I am merging user ids (from several to one), I need to change the records in the above table too.
I need to update the PK by changing either "uid" or "author".
But this might give me conflicts, because there might be such a PK already...
Regards
Alex
On 03/14/2017 01:25 PM, Alexander Farber wrote: > Hi Adrian - > > On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 03/14/2017 09:00 AM, Alexander Farber wrote: > > My initial idea has been not to use ON CONFLICT at all, but > instead only > UPDATE the words_reviews records (set "uid" or "author" to > out_uid) for > which NO EXISTS already such a record with PK (uid, author)... > and then > > > I am afraid the logic is escaping me. If the record does not exist > how can you UPDATE it? > > > here the table with reviews of users "uid" done by users "author": > > 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) > ); > > Since I am merging user ids (from several to one), I need to change the > records in the above table too. Yeah, still trying to figure out why the smallest uid becomes the merged uid, but that is another train of thought:) > > I need to update the PK by changing either "uid" or "author". > > But this might give me conflicts, because there might be such a PK > already... Hence my suggestion for INSERT ON CONFLICT UPDATE. If the PK combination does not exist you INSERT a new record. If it does exist you leave the PK combination alone and UPDATE the rest of the information in the row to the current data. > > Regards > Alex > > -- Adrian Klaver adrian.klaver@aklaver.com
But this might give me conflicts, because there might be such a PK already...
You need to remove the "might" and figure out which ones will and which will not. You can update the ones will not and delete the ones that do.
Free-thinking:
WITH figure_things_outs AS (
-- not exactly sure how this looks but I would probably try to construct the new relation with the updates in place
-- and use something like row_number() OVER (PARTITION BY uid, author ORDER BY pk) to detect future duplicates
),
delete_these AS (
DELETE FROM tbl
USING figure_things_out
WHERE tbl.pk = figure_things_out.pk
AND figure_things_out.row_number > 1
),
and_update_the_rest AS (
UPDATE tbl
WHERE EXISTS ( ... WHERE row_number = 1 )
-- consider using a trigger here to avoid writing no-op updates to disc
)
SELECT * FROM delete_these
UNION ALL
SELECT * FROM and_update_the_rest
;
I'd suggest you find some way to abstract away the table name and columns so you can write the logic once and specify the table name and columns as part of the input.
OR
WITH going_away_author_change AS (
DELETE FROM tbl
RETURNING uid, author
),
WITH going_away_user_change AS (
DELETE FROM tbl
RETURNING uid, author
)
INSERT INTO tbl
SELECT uid, new_author FROM going_away_author_change
UNION ALL
SELECT new_uid, author FROM going_away_user_change
ON CONFLICT DO NOTHING -- haven't used personally, you should get the idea though
OR
WITH nuclear_option AS (
DELETE FROM tbl
RETURNING *
), make_new_records AS (
SELECT DISTINCT ON (uid, author) uid, author, ... -- ensure this outputs records meeting your PK constraint
FROM nuclear_option -- sprinkle renaming logic where needed
ORDER BY uid, author, pk
)
INSERT INTO tbl
SELECT * FROM make_new_records;
David J.
On Tue, Mar 14, 2017 at 4:55 AM, Alexander Farber <alexander.farber@gmail.com> wrote: > 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 Heikki wanted to keep the door open to a feature like this around the time that speculative insertion (the underlying mechanism used by UPSERT) went in. I eventually convinced him that it is drastically more complicated than it appears. This is essentially because it's much harder to back out of an UPDATE than it is to back out of an INSERT. If you're really interested, search through the -hackers archives from around April of 2015. -- Peter Geoghegan
The convention on these lists is to inline or bottom-post.
Have you tried with inner block and do nothing on exception;
I suppose that would work - though using an exception path for expected logic is messy - and considerably slower than detection.
After doing something like this the OP would then want to DELETE any remaining records that still have the uid or author with the old value. Do nothing by itself would just leave them alone.
BEGIN<your code>..............BEGINUPDATE words_socialSET uid = out_uid
WHERE uid = ANY(_uids);EXCEPTION WHEN OTHERS THEN--do nothing or write NULL means do nothing
David J.
Yasin, thank you for this suggestion, but -
Have you tried with inner block and do nothing on exception;BEGIN<your code>..............BEGINUPDATE words_socialSET uid = out_uid
WHERE uid = ANY(_uids);EXCEPTION WHEN OTHERS THEN--do nothing or write NULL means do nothing
but it seems to me that my method of INSERTing copies would be faster than the 2 loops