8 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link
7 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link
6 explain VARIADIC some more
source | link

Since there is no provision to explicitly lock not-yet-existing rows ("value locking") in Postgres, it's hard to defend against concurrent INSERT commands in the default isolation level READ COMMITTED. The more expensive isolation level SERIALIZABLE should solve this, I didn't take a closer look, I think I have a better idea. Keep reading.

There is a smarter alternative: take a FOR NO KEY UPDATE lock on the one row in the master table art. (FOR NO KEY UPDATE is a bit less restrictive than the common FOR UPDATE and good enough for our purpose.)

BEGIN;

SELECT * FROM art WHERE id = 1      -- we do not actually retrieve rows
FOR NO KEY UPDATE;                  -- only to lock the master row exclusively

DELETE FROM art_tag
WHERE  tag_ref <> ALL ('{1,2,3}'::bigint[]);

INSERT INTO art_tag (article_ref, tag_ref)
SELECT 1, unnest('{1,2,3}'::bigint[])
ON CONFLICT DO NOTHING;             -- requires Postgres 9.5 or later

COMMIT;                             -- releases locks released at end of transaction

To fine-tune behavior in case of conflicts you may be interested inconsider the NOWAIT or SKIP LOCKED modifiers.

You can wrap this into a function to simplify parameter passing and have an automatic transaction wrapper. For small sets of tags (maximum 100), a VARIADIC parameter should be particularly convenient:

SELECT f_set_tags(1,1,2,3);  -- 1st param is article_ref, rest are tag_ref

Or, if you have an actual array to pass:

SELECT f_set_tags(1, VARIADIC '{1,2,3}');
SELECT f_set_tags(1, VARIADIC your_array_variable);

For small sets of tags (max. 100), a VARIADIC parameter should be particularly convenient, to pass a list of values for the array. You can still pass an actual ARRAY (with any number of elements). Details:

Since there is no provision to explicitly lock not-yet-existing rows ("value locking") in Postgres, it's hard to defend against concurrent INSERT commands in the default isolation level READ COMMITTED. The more expensive isolation level SERIALIZABLE should solve this, I didn't take a closer look, I think I have a better idea. Keep reading.

There is a smarter alternative: take a FOR NO KEY UPDATE lock on the one row in the master table art. FOR NO KEY UPDATE is a bit less restrictive than the common FOR UPDATE and good enough for our purpose.)

BEGIN;

SELECT * FROM art WHERE id = 1      -- we do not actually retrieve rows
FOR NO KEY UPDATE;                  -- only to lock the master row exclusively

DELETE FROM art_tag
WHERE  tag_ref <> ALL ('{1,2,3}'::bigint[]);

INSERT INTO art_tag (article_ref, tag_ref)
SELECT 1, unnest('{1,2,3}'::bigint[])
ON CONFLICT DO NOTHING;             -- requires Postgres 9.5 or later

COMMIT;  -- releases locks

To fine-tune behavior in case of conflicts you may be interested in NOWAIT or SKIP LOCKED modifiers.

You can wrap this into a function to simplify parameter passing and have an automatic transaction wrapper. For small sets of tags (maximum 100), a VARIADIC parameter should be particularly convenient:

SELECT f_set_tags(1,1,2,3);  -- 1st param is article_ref, rest are tag_ref

Since there is no provision to explicitly lock not-yet-existing rows ("value locking") in Postgres, it's hard to defend against concurrent INSERT commands in the default isolation level READ COMMITTED. The more expensive isolation level SERIALIZABLE should solve this, I didn't take a closer look, I think I have a better idea.

There is a smarter alternative: take a FOR NO KEY UPDATE lock on the one row in the master table art. (FOR NO KEY UPDATE is a bit less restrictive than the common FOR UPDATE and good enough for our purpose.)

BEGIN;

SELECT * FROM art WHERE id = 1      -- we do not actually retrieve rows
FOR NO KEY UPDATE;                  -- only to lock the master row exclusively

DELETE FROM art_tag
WHERE  tag_ref <> ALL ('{1,2,3}'::bigint[]);

INSERT INTO art_tag (article_ref, tag_ref)
SELECT 1, unnest('{1,2,3}'::bigint[])
ON CONFLICT DO NOTHING;             -- requires Postgres 9.5 or later

COMMIT;                             -- locks released at end of transaction

To fine-tune behavior in case of conflicts consider the NOWAIT or SKIP LOCKED modifiers.

You can wrap this into a function to simplify parameter passing and have an automatic transaction wrapper:

SELECT f_set_tags(1,1,2,3);  -- 1st param is article_ref, rest are tag_ref

Or, if you have an actual array to pass:

SELECT f_set_tags(1, VARIADIC '{1,2,3}');
SELECT f_set_tags(1, VARIADIC your_array_variable);

For small sets of tags (max. 100), a VARIADIC parameter should be particularly convenient, to pass a list of values for the array. You can still pass an actual ARRAY (with any number of elements). Details:

5 deleted 13 characters in body
source | link
4 adapt to Q update
source | link
3 added 64 characters in body
source | link
2 added 184 characters in body
source | link
1
source | link