Good afternoon,
I have prepared a simple test case for my question -
CREATE TABLE users (
uid SERIAL PRIMARY KEY,
name varchar(255) NOT NULL
);
CREATE TABLE reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES users ON DELETE CASCADE,
author integer NOT NULL REFERENCES users(uid) ON DELETE CASCADE,
review varchar(255),
PRIMARY KEY(uid, author)
);
Here I fill the above tables with sample data -
INSERT INTO users (uid, name) VALUES (1, 'User 1');
INSERT INTO users (uid, name) VALUES (2, 'User 2');
INSERT INTO users (uid, name) VALUES (3, 'User 3');
INSERT INTO users (uid, name) VALUES (4, 'User 4');
INSERT INTO reviews (uid, author, review) VALUES (1, 2, 'User 2 says: 1 is nice');
INSERT INTO reviews (uid, author, review) VALUES (1, 3, 'User 3 says: 1 is nice');
INSERT INTO reviews (uid, author, review) VALUES (1, 4, 'User 4 says: 1 is nice');
INSERT INTO reviews (uid, author, review) VALUES (2, 1, 'User 1 says: 2 is nice');
INSERT INTO reviews (uid, author, review) VALUES (2, 3, 'User 3 says: 2 is nice');
INSERT INTO reviews (uid, author, review) VALUES (2, 4, 'User 4 says: 2 is ugly');
INSERT INTO reviews (uid, author, review) VALUES (3, 1, 'User 1 says: 3 is nice');
INSERT INTO reviews (uid, author, review) VALUES (3, 2, 'User 2 says: 3 is ugly');
INSERT INTO reviews (uid, author, review) VALUES (3, 4, 'User 4 says: 3 is ugly');
INSERT INTO reviews (uid, author, review) VALUES (4, 1, 'User 1 says: 4 is ugly');
INSERT INTO reviews (uid, author, review) VALUES (4, 2, 'User 2 says: 4 is ugly');
INSERT INTO reviews (uid, author, review) VALUES (4, 3, 'User 3 says: 4 is ugly');
And finally here is my problematic custom stored function:
CREATE OR REPLACE FUNCTION merge_users(
in_uids integer[],
OUT out_uid integer
) RETURNS integer AS
$func$
BEGIN
SELECT
MIN(uid)
INTO STRICT
out_uid
FROM users
WHERE uid = ANY(in_uids);
-- delete self-reviews
DELETE FROM reviews
WHERE uid = out_uid
AND author = ANY(in_uids);
DELETE FROM reviews
WHERE author = out_uid
AND uid = ANY(in_uids);
-- try to copy as many reviews OF this user as possible
INSERT INTO reviews (
uid,
author,
review
) SELECT
out_uid, -- change to out_uid
author,
review
FROM reviews
WHERE uid <> out_uid
AND uid = ANY(in_uids)
ON CONFLICT DO NOTHING;
DELETE FROM reviews
WHERE uid <> out_uid
AND uid = ANY(in_uids);
-- try to copy as many reviews BY this user as possible
INSERT INTO reviews (
uid,
author,
review
) SELECT
uid,
out_uid, -- change to out_uid
review
FROM reviews
WHERE author <> out_uid
AND author = ANY(in_uids)
ON CONFLICT DO NOTHING;
DELETE FROM reviews
WHERE author <> out_uid
AND author = ANY(in_uids);
DELETE FROM users
WHERE uid <> out_uid
AND uid = ANY(in_uids);
END
$func$ LANGUAGE plpgsql;
The purpose of the function is to merge several user records to one (with the lowest uid).
While merging the reviews records I delete all self-reviews and try to copy over as many remaining reviews as possible.
However with PostgreSQL 9.5 the following 2 calls fail:
test=> SELECT out_uid FROM merge_users(ARRAY[1,2]);
out_uid
---------
1
(1 row)
test=> SELECT out_uid FROM merge_users(ARRAY[1,2,3,4]);
ERROR: new row for relation "reviews" violates check constraint "reviews_check"
DETAIL: Failing row contains (1, 1, User 4 says: 3 is ugly).
CONTEXT: SQL statement "INSERT INTO reviews (
uid,
author,
review
) SELECT
uid,
out_uid, -- change to out_uid
review
FROM reviews
WHERE author <> out_uid
AND author = ANY(in_uids)
ON CONFLICT DO NOTHING"
PL/pgSQL function merge_users(integer[]) line 38 at SQL statement
I have provided more context at
http://stackoverflow.com/questions/43168406/merging-records-in-a-table-with-2-columns-primary-key
Also I have tried to create an SQL Fiddle at
http://sqlfiddle.com/#!15/5f37e/2
for your convenience
Regards
Alex
