What lock, if any, does 'CREATE TRIGGER' use in PostgreSQL 9.4.2

Question

according to postgres-xl, CREATE TRIGGER uses the SHARE ROW EXCLUSIVE lock, but according to the official Postgres docs for SHARE ROW EXCLUSIVE:

This lock mode is not automatically acquired by any PostgreSQL command.

Answer 1

You're comparing Postgres-XL with the main PostgreSQL documentation. Two different products, albeit with a shared history. Postgres-XL has lots of changes from stock PostgreSQL.

CREATE TRIGGER should be listed in the Pg docs and isn't, though, and that's an oversight.

A quick look at the source code shows that CREATE TRIGGER takes a ShareRowExclusiveLock, so in this case XL's documentation matches PostgreSQL's behaviour.

You could check this yourself without looking at the sources by doing something like this:

CREATE TABLE test();

CREATE OR REPLACE FUNCTION dummy_tg() RETURNS TRIGGER
LANGUAGE plpgsql AS $$ BEGIN END; $$;

BEGIN;

CREATE TRIGGER blah BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE dummy_tg();

\x

SELECT * FROM pg_locks 
WHERE pid = pg_backend_pid() 
AND relation = 'test'::regclass;

ROLLBACK;

... which shows that I was wrong about my reading of the sources, because:

locktype | relation
mode     | AccessExclusiveLock

it took an AccessExclusiveLock.