如果不存在，则创建 PostgreSQL ROLE（用户）

小编典典

如果不存在，则创建 PostgreSQL ROLE（用户）

all

如何编写 SQL 脚本以在 PostgreSQL 9.1 中创建 ROLE，但如果它已经存在则不会引发错误？

当前脚本只有：

CREATE ROLE my_user LOGIN PASSWORD 'my_password';

如果用户已经存在，这将失败。我想要类似的东西：

IF NOT EXISTS (SELECT * FROM pg_user WHERE username = 'my_user')
BEGIN
    CREATE ROLE my_user LOGIN PASSWORD 'my_password';
END;

…但这不起作用 -IF普通 SQL 似乎不支持。

我有一个创建 PostgreSQL 9.1 数据库、角色和其他一些东西的批处理文件。它调用 psql.exe，传入要运行的 SQL
脚本的名称。到目前为止，所有这些脚本都是纯 SQL，如果可能的话，我想避免使用 PL/pgSQL 等。

阅读 96

2022-08-07

共1个答案

小编典典

简单脚本（提问）

以答案为基础，并通过@Gregory[的评论:ErwinBrandstetter 这不适用于具有 NOLOGIN 的角色。它们出现在 pg_roles 中，但不在 pg_user 中]进行了改进：

DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = 'my_user') THEN

      RAISE NOTICE 'Role "my_user" already exists. Skipping.';
   ELSE
      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
END
$do$;

不像，例如，CREATE TABLE没有IF NOT EXISTS子句 for CREATE ROLE（至少
Postgres 14）。而且您不能在纯 SQL 中执行动态 DDL 语句。

您要求“避免 PL/pgSQL”是不可能的，除非使用另一个
PL。该DO语句使用 PL/pgSQL
作为默认过程语言：

DO [ LANGUAGE lang_name ] code
… 编写代码的过程语言的名称。如果省略，默认为.
lang_name
plpgsql

没有比赛条件

上述简单的解决方案允许在查找角色和创建角色之间的微小时间范围内出现竞争条件。如果并发事务在两者之间创建角色，我们毕竟会得到一个异常。在大多数工作负载中，这永远不会发生，因为创建角色是管理员很少执行的操作。但是有像提到的那样有争议的工作负载。
@Pali
添加了一个捕获异常的解决方案。但是带有EXCEPTION子句的代码块很昂贵。手册：

包含EXCEPTION子句的块的进入和退出成本明显高于没有子句的块。因此，不要在EXCEPTION没有必要的情况下使用。

实际上，引发异常（然后捕获它）在此之上相对昂贵。所有这一切只对经常执行它的工作负载很重要——这恰好是主要目标受众。优化：

DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = 'my_user') THEN

      RAISE NOTICE 'Role "my_user" already exists. Skipping.';
   ELSE
      BEGIN   -- nested block
         CREATE ROLE my_user LOGIN PASSWORD 'my_password';
      EXCEPTION
         WHEN duplicate_object THEN
            RAISE NOTICE 'Role "my_user" was just created by a concurrent transaction. Skipping.';
      END;
   END IF;
END
$do$;

便宜得多：

如果角色已经存在，我们永远不会进入昂贵的代码块。
如果我们输入昂贵的代码块，那么角色只有在不太可能的竞争条件发生时才存在。所以我们几乎没有真正引发异常（并捕获它）。

2022-08-07