Re: Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)

On Wed, Apr 12, 2017 at 3:56 PM, Gavin Wahl <gwahl(at)fusionbox(dot)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.