Files
mtusi2026lab5/db/queries/message_idempotence.sql

23 lines
838 B
SQL

-- name: InsertMessageIdempotency :execrows
INSERT INTO message_idempotency(user_id, chat_id, idempotency_key, message_id)
VALUES ($1, $2, $3, $4)
ON CONFLICT (user_id, chat_id, idempotency_key) DO NOTHING;
-- name: GetIdempotentMessage :one
SELECT m.id, m.chat_id, m.sender_id, m.text, m.created_at
FROM message_idempotency AS i
JOIN messages AS m ON m.id = i.message_id
WHERE i.user_id = $1
AND i.chat_id = $2
AND i.idempotency_key = $3;
-- name: LockMessageRequest :exec
SELECT pg_advisory_xact_lock(hashtextextended(
'message/' || (sqlc.arg(user_id)::uuid)::text || '/' ||
(sqlc.arg(chat_id)::uuid)::text || '/' || sqlc.arg(idempotency_key)::text, 0));
-- name: DeleteExpiredMessageIdempotency :exec
DELETE FROM message_idempotency
WHERE user_id = $1 AND chat_id = $2 AND idempotency_key = $3 AND expires_at <= now();