CODEWITHSUNDEEP
sqlpostgresqltriggerstruncate
Luffydude
Luffydude

Reputation: 802

How to restrict a user from truncating a table when a long transaction is occurring?

There is a refresh materialized concurrently that takes several hours to run. One of the users regularly has to truncate one of the tables that the materialized view uses. This table is also used in multiple projects so other users run selects on it

Problem is that this truncate stays locked until the refresh finishes, and anyone selecting the table then gets completely stuck and then the database gets jammed. I've instructed the user to only do this truncate at a specific time but he did not listen

How to create a trigger that prevents the user from doing this truncate? Something along the lines

create trigger before truncate on table for each row execute function stoptruncate()

CREATE OR REPLACE FUNCTION stoptruncate()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$

BEGIN   
 if truncate = true and if 'the refresh query is running'
 then raise exception 'cannot run truncate while background refresh is running';
end if;
 RETURN NEW;                                                                    
END;
        
$function$
;

Upvotes: 0

Views: 280

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

A trigger comes too late to do anything about that. The trigger function is called after the table lock has been granted.

You could keep the user from waiting forever by setting a low default value for lock_timeout for that user:

ALTER ROLE trunc_user SET lock_timeout = '200ms';

Upvotes: 1

Related Questions