How transactions work in case of Postgres Functions-CodePudding

I am trying to understand how transaction works in Postgres and what happens when multiple commands try to work on the same table. My doubt is related to a small experiment that I carried out.

Consider a table called experiment with a trigger (experiment_log) on it that is fired after every update, delete, or insert.

Now consider this function.

CREATE OR REPLACE FUNCTION test_func() RETURNS void AS $body$
DECLARE
  _q_txt text;
  version_var integer;
BEGIN
    EXECUTE 'DROP TRIGGER IF EXISTS experiment_log ON experiment';
    
    SELECT version INTO version_var FROM experiment;
    RAISE NOTICE 'VERSION AFTER DROPPING TRIGGER: %', version_var;
    
    EXECUTE 'SELECT pg_sleep(20);';
    
    SELECT version INTO version_var FROM experiment;
    RAISE NOTICE 'VERSION BEFORE RECREATING TRIGGER: %', version_var;
    
    EXECUTE 'CREATE TRIGGER experiment_log AFTER INSERT OR UPDATE OR DELETE ON experiment FOR EACH ROW EXECUTE PROCEDURE experiment_log_trigger_func();';

END;
$body$
language 'plpgsql';

So, this function drops the trigger and waits for 20 seconds before re-creating this trigger. Now any update operation performed during the time when function is sleeping, the update operation blocks. It means that I can not update the experiment table until the function test_func has executed completely.

Can anyone explain this behaviour? It seems I am missing something out to reason this behaviour.

CodePudding user response：

That is because DROP TRIGGER places an ACCESS EXCLUSIVE lock on the table, and the lock is held until the transaction ends, that is, for the whole duration of the function call.

If you want to disable a trigger temporarily, use

ALTER TABLE experiment DISABLE TRIGGER experiment_log;

I would like to give you a reference from the documentation, but the lock level of DROP TRIGGER is not documented. However, it is documented that the SQL statement takes the lock:

Also, most PostgreSQL commands automatically acquire locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the command executes.

There you can also find how long a lock is held:

Once acquired, a lock is normally held until the end of the transaction.

To find the lock taken by DROP TRIGGER, try this simple experiment:

CREATE TABLE t();

CREATE TRIGGER whatever BEFORE UPDATE ON t
   FOR EACH ROW EXECUTE FUNCTION suppress_redundant_updates_trigger();

BEGIN;  -- start a transaction

DROP TRIGGER whatever ON t;

SELECT mode FROM pg_locks
WHERE pid = pg_backend_pid()    -- only locks for the current session
  AND relation = 't'::regclass; -- only locks on "t"

        mode         
═════════════════════
 AccessShareLock
 AccessExclusiveLock
(2 rows)

COMMIT;

You see that an ACCESS SHARE lock and an ACCESS EXCLUSIVE lock are held on the table.