increasing timeout doesn't work?

[pierroo]

I have a table "chat_room" and then another one "chat_msg" linked to it on its FK room_id (all chat room messages get deleted on cascade when a room is deleted)

Unfortunately when I run the sql "delete from chat_room" it timesout regardless of the "set statement_timeout to xxx;" value I put
there are 500K chat_rooms, 2M messages, so I tried to reduce the query with a :

set statement_timeout to 500000;
DELETE FROM chat_room
WHERE id IN (
SELECT id
FROM chat_room
ORDER BY id
LIMIT 10000
);

and yet this keeps timing out to, even for just 10K deletion
what am I missing?

EDIT: this is the error:

<title>504 Gateway Time-out</title>

504 Gateway Time-out

[mwoss]

Are you running this query from the Supabase SQL editor? If so, the default timeout of 15 sec cannot be changed - see more details in this SO post here. You can workaround this by connecting to DB directly via a client like psql, pgAdmin, TablePlus, or calling this statement via Supabase API.

[mwoss]

Have you correctly set a timeout for an API user on the DB level? You can do it via alter role authenticator set statement_timeout = 'XXXs';

[pierroo]

I am using the service api key
in const supabase = createClient(supa_url, process.env.SUPABASE_SECRET_KEY)

so which role would need to be altered here?

[mwoss]

As far as I know the authenticator, one from the example above. :D

[AbdullahAhmadAAK]

@mwoss I have run alter role authenticator set statement_timeout = '999s'; in the SQL editor
But I am still getting this issue. Do you know how this can be resolved?

[aly-tq]

It should be 'authenticated' not 'authenticator'!