在PostgreSQL 9.4.2中'CREATE TRIGGER'使用什么锁（如果有的话） [英] What lock, if any, does 'CREATE TRIGGER' use in PostgreSQL 9.4.2

问题描述

根据 postgres-xl ， CREATE TRIGGER 使用 SHARE ROW EXCLUSIVE 锁，但根据官方Postgres文档，用于共享行独享：

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

此锁定模式不会由任何PostgreSQL
命令自动获取。

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

推荐答案

您正在将Postgres-XL与PostgreSQL主要文档进行比较。两种具有相同历史的产品。 Postgres-XL在现有PostgreSQL中有很多更改。

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.

创建触发器应该在Pg文档中列出，但这不是一个疏忽。

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

快速查看源代码可以发现 CREATE TRIGGER 需要 ShareRowExclusiveLock ，因此在这种情况下，XL的文档与PostgreSQL的行为匹配。

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

它使用了AccessExclusiveLock。

it took an AccessExclusiveLock.

这篇关于在PostgreSQL 9.4.2中'CREATE TRIGGER'使用什么锁（如果有的话）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！