OMG! You’re RIGHT!
Don’t know why our solution worked (as in delete messages) and LIMIT wasn’t FATAL.
The columns don’t exist - room_name, user_id_one, user_id_two, group_id are NOT in the actual message table schema
We dived into the internals and the actual message structure is:
TABLE public.message (
id UUID NOT NULL,
code INT2 NOT NULL DEFAULT 0:::INT8,
sender_id UUID NOT NULL,
username VARCHAR(128) NOT NULL,
stream_mode INT2 NOT NULL,
stream_subject UUID NOT NULL,
stream_descriptor UUID NOT NULL,
stream_label VARCHAR(128) NOT NULL,
content JSONB NOT NULL DEFAULT '{}':::JSONB,
create_time TIMESTAMPTZ NOT NULL DEFAULT now():::TIMESTAMPTZ,
update_time TIMESTAMPTZ NOT NULL DEFAULT now():::TIMESTAMPTZ,
CONSTRAINT message_pkey PRIMARY KEY (stream_mode ASC, stream_subject ASC, stream_descriptor ASC, stream_label ASC, create_time ASC, id ASC),
CONSTRAINT message_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES public.users(id) ON DELETE CASCADE,
UNIQUE INDEX message_id_key (id ASC),
UNIQUE INDEX message_sender_id_id_key (sender_id ASC, id ASC)
)
Stream modes
From: Streams - Heroic Labs Documentation
we can identify three internal streams which we can use:
const (
interval = 6 * time.Hour
// Stream mode constants from Nakama
StreamModeChannel uint8 = 2
StreamModeGroup uint8 = 3
StreamModeDM uint8 = 4
)
for our jobs:
jobs := []struct {
name string
mode uint8
hours int
batchSize int
}{
{
name: "room messages",
mode: StreamModeChannel,
hours: cfg.RoomHours,
batchSize: cfg.BatchSize,
},
{
name: "direct messages",
mode: StreamModeDM,
hours: cfg.DirectDays * 24,
batchSize: cfg.BatchSize,
},
{
name: "group messages",
mode: StreamModeGroup,
hours: cfg.GroupDays * 24,
batchSize: cfg.BatchSize,
},
}
SQL
LIMIT is indeed not supported in Postgres and Cockroach, so we use a CTE query as a work around for this. So we can run batches.
query := `
WITH old_messages AS (
SELECT id
FROM message
WHERE stream_mode = $1
AND create_time < now() - (($2::INT * 3600) * INTERVAL '1 second')
ORDER BY create_time
LIMIT $3
)
DELETE FROM message
WHERE id IN (SELECT id FROM old_messages)
`
Writing some tests for our sanity. Will be back with a response. 
Update:
Ok, for now, the current behavior of our CRON job is:
‘Delete all messages from StreamModeDM -> Direct Chat, StreamModeGroup -> Group Chat and StreamModeChannel -> Rooms where messages are older than n retention time’.
The query above, behaves like expected.
As we have - at this very moment - only direct chat implemented in our game.
So notice that:
The cron job needs to be improved with:
- Delete inactive rooms after
n retention (not messages)
For Groups:
- Old messages getting deleted
- The group channel should get deleted, when a group (clan) is being removed. Not to be handled by this CRON job.
I can update this topic if someone is interested.
For now I think we’re on the right path!
Thanks and cheers!