How to Safely Create a PostgreSQL Role if it Doesn't Already Exist?

How to Create PostgreSQL ROLE if it Doesn't Exist

In PostgreSQL 9.1, creating a ROLE that doesn't exist using a simple script like CREATE ROLE my_user LOGIN PASSWORD 'my_password' can fail if the user already exists. To avoid this error, a more sophisticated approach is required.

Solution Using DO Statement

One effective solution is to use the DO statement in a procedural language like PL/pgSQL:

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$;

This script checks if the role already exists using the EXISTS statement. If it does, a notice is raised and the creation is skipped. Otherwise, the role is created.

Optimization for High-Contention Workloads

For highly contentious workloads, a further optimization can be made by nesting the CREATE ROLE statement within a nested block:

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$;

This nested block ensures that the role is only created if the unlikely race condition hits. This significantly reduces the performance overhead associated with raising and catching exceptions.

The above is the detailed content of How to Safely Create a PostgreSQL Role if it Doesn't Already Exist?. For more information, please follow other related articles on the PHP Chinese website!