Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE EXTENSION btree_gist; -- required for GiST idx on integer

CREATE EXTENSION


CREATE TABLE events (
  id serial PRIMARY KEY
, user_id int  -- REFERENCES users(id) NOT NULL  -- not in the fiddle
, created_at timestamp NOT NULL DEFAULT LOCALTIMESTAMP  -- timestamp!
, updated_at timestamp NOT NULL DEFAULT LOCALTIMESTAMP
, data text   -- "... some other event columns here"
);

-- essential EXCLUSION constraint
ALTER TABLE events
  ADD CONSTRAINT user_90sec
  EXCLUDE USING gist (user_id WITH =, tsrange(updated_at, updated_at + interval '90 sec') WITH &&);


INSERT INTO events (user_id, created_at, updated_at, data) VALUES
  (1, LOCALTIMESTAMP - interval '200 sec', LOCALTIMESTAMP - interval '100 sec', 'foo')  -- old
, (2, LOCALTIMESTAMP - interval  '40 sec', LOCALTIMESTAMP - interval  '40 sec', 'bar')  -- current
RETURNING *;

CREATE TABLE



ALTER TABLE




id
user_id
created_at
updated_at
data


1
1
2023-11-22 01:18:48.292674
2023-11-22 01:20:28.292674
foo

2
2
2023-11-22 01:21:28.292674
2023-11-22 01:21:28.292674
bar



INSERT 0 2


WITH input_rows(user_id, data) AS (
   VALUES
      (1, 'foo_new')
    , (2, 'bar_new')
    , (3, 'baz_new')
    -- more?
   )
, ins AS (
   INSERT INTO events (user_id, data) 
   SELECT user_id, data FROM input_rows
   ON CONFLICT ON CONSTRAINT user_90sec DO NOTHING
   RETURNING user_id
   )
UPDATE events e
SET    updated_at = LOCALTIMESTAMP
     , data = i.data
FROM   input_rows i
LEFT   JOIN ins USING (user_id)
WHERE  ins.user_id IS NULL
AND    e.user_id = i.user_id
AND    e.updated_at > LOCALTIMESTAMP - interval '90 sec';

UPDATE 1


TABLE events;


id
user_id
created_at
updated_at
data


1
1
2023-11-22 01:18:48.292674
2023-11-22 01:20:28.292674
foo

3
1
2023-11-22 01:22:08.304324
2023-11-22 01:22:08.304324
foo_new

5
3
2023-11-22 01:22:08.304324
2023-11-22 01:22:08.304324
baz_new

2
2
2023-11-22 01:21:28.292674
2023-11-22 01:22:08.304324
bar_new



SELECT 4

id	user_id	created_at	updated_at	data
1	1	2023-11-22 01:18:48.292674	2023-11-22 01:20:28.292674	foo
2	2	2023-11-22 01:21:28.292674	2023-11-22 01:21:28.292674	bar

id	user_id	created_at	updated_at	data
1	1	2023-11-22 01:18:48.292674	2023-11-22 01:20:28.292674	foo
3	1	2023-11-22 01:22:08.304324	2023-11-22 01:22:08.304324	foo_new
5	3	2023-11-22 01:22:08.304324	2023-11-22 01:22:08.304324	baz_new
2	2	2023-11-22 01:21:28.292674	2023-11-22 01:22:08.304324	bar_new