By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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