Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)
I have a table that stores user notifications:
CREATE TABLE notifications (
user_id INT,
type CHAR(1),
PRIMARY KEY (user_id, type)
);
When a user edits their notifications, I need to atomically replace the old set
with the new set. My first instinct is to do this:
BEGIN;
DELETE FROM notifications WHERE user_id = 1;
INSERT INTO notifications (user_id, type) VALUES (1, 'a'), (1, 'b');
COMMIT;
This of course doesn't work when two transactions run concurrently though --
one of them will get a unique constraint violation.
My next thought was to use upsert:
BEGIN;
DELETE FROM notifications WHERE user_id = 1;
INSERT INTO notifications (user_id, type) VALUES (1, 'a'), (1, 'b') ON
CONFLICT DO NOTHING;
COMMIT;
This doesn't give an error for concurrent transactions, but doesn't do
the right thing. Consider if one transaction runs to replace the set with
{'a', 'b'} and another runs with {'b', 'c'}. The result should either
be {'a', 'b'} or {'b', 'c'}, but they actually get merged together and
the user ends up with notifications {'a', 'b', 'c'}.
Is there any way to do this correctly without SERIALIZABLE transactions? It
would be nice to avoid having to retry transactions. Ideally I'd like to avoid
explicit locking as well.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Apr 12, 2017 at 3:32 PM, Gavin Wahl <gwahl@fusionbox.com> wrote:
I have a table that stores user notifications:
CREATE TABLE notifications (
user_id INT,
type CHAR(1),
PRIMARY KEY (user_id, type)
);
[...]Is there any way to do this correctly without SERIALIZABLE transactions? It
would be nice to avoid having to retry transactions. Ideally I'd like to
avoid
explicit locking as well.
Given this limited example I'd probably choose to model notifications as
an array on the user table. Then just "UPDATE user SET notifications =
array['a','b']::text WHERE user_id = 1;
David J.
Given this limited example I'd probably choose to model notifications as an
array on the user table. Then just "UPDATE user SET notifications =
array['a','b']::text WHERE user_id = 1;
I'm hesitant to ditch the first normal form just to get around this. Anyway,
there's actually extra data in the table that makes it hard to use an array:
CREATE TABLE notifications (
user_id INT,
type CHAR(1),
threshold INT,
some_options BOOLEAN,
PRIMARY KEY (user_id, type)
);
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Apr 12, 2017 at 3:56 PM, Gavin Wahl <gwahl@fusionbox.com> wrote:
Given this limited example I'd probably choose to model notifications as
an
array on the user table. Then just "UPDATE user SET notifications =
array['a','b']::text WHERE user_id = 1;I'm hesitant to ditch the first normal form just to get around this.
Anyway,
there's actually extra data in the table that makes it hard to use an
array:CREATE TABLE notifications (
user_id INT,
type CHAR(1),
threshold INT,
some_options BOOLEAN,
PRIMARY KEY (user_id, type)
);
A custom composite type would solve that part of the problem.
You're going to have to pick you poison here. No serializable, no locking,
and no atomic data type. I don't have any other reasonable ideas that
aren't any worse than any one of those three. You would need to introduce
some kind of "notification set id" and make (user_id,
active_notification_set_id) the linking multi-column key.
Or wait and see if anyone more clever than I has some ideas.
David J.