Concurrent creation of the same label races and fails with "duplicate key value violates unique constraint pg_class_relname_nsp_index"

[themiguelamador]

Summary

Concurrent creation of the same not-yet-existing label — either via two Cypher CREATE statements whose label is auto-created, or two create_vlabel()/create_elabel() calls — is not serialized. Both sessions pass the label_exists() check, both proceed to build the label's backing objects, and the loser aborts with an internal catalog error instead of a friendly "label already exists":

ERROR:  duplicate key value violates unique constraint "pg_class_relname_nsp_index"
DETAIL:  Key (relname, relnamespace)=(RaceLabel_id_seq, 1523103) already exists.

We hit this in production (multi-node Elixir app, several connections doing the first-ever CREATE of a label concurrently). The failing statement's whole transaction is aborted, so real work is lost, not just the label DDL.

Environment

• Apache AGE 1.5.0, PostgreSQL 16.9 (Debian)
• The relevant code on master looks unchanged: create_label() in src/backend/commands/label_commands.c does label_exists(label_name, graph_oid) and then creates the sequence / table / ag_label row, with no lock that serializes two concurrent creators of the same label.

Deterministic reproduction

Two psql sessions on a graph where RaceLabel does not exist yet:

-- Session A
LOAD 'age'; SET search_path = ag_catalog;
BEGIN;
SELECT * FROM cypher('racetest', $$ CREATE (:RaceLabel) $$) AS (v agtype);
-- keep the transaction open ...

-- Session B (while A is still open)
LOAD 'age'; SET search_path = ag_catalog;
SELECT * FROM cypher('racetest', $$ CREATE (:RaceLabel) $$) AS (v agtype);
-- B blocks on the catalog index ...

-- Session A
COMMIT;
-- B now fails:
-- ERROR:  duplicate key value violates unique constraint "pg_class_relname_nsp_index"
-- DETAIL:  Key (relname, relnamespace)=(RaceLabel_id_seq, ...) already exists.

Without the explicit transactions the same failure occurs probabilistically under concurrent load — that is how it surfaces in production.

Root cause

create_label() is check-then-act: label_exists() runs with no lock held that would conflict with another backend concurrently creating the same label, so both backends can pass the check. The first CREATE SEQUENCE/CREATE TABLE to commit wins; the other backend errors out on the pg_class (or ag_label) unique index.

Suggested fix

Serialize label creation per graph (or per graph+label) inside create_label():

1. Acquire a self-conflicting lock before the existence check — e.g. LockRelationOid() on the ag_label catalog relation in ShareRowExclusiveLock mode, or advisory locking keyed on (graph_oid, label_name) — then re-run label_exists() under the lock and return early if the label now exists.
2. Alternatively (or additionally), catch the unique-violation from the backing-object creation / insert_label() and re-resolve the label, treating "someone else just created it" as success.

Since both the SQL-callable create_vlabel/create_elabel and the Cypher auto-creation path go through create_label(), a fix there covers all entry points.

Workaround

We currently pre-create every label our application can emit at startup, inside a transaction holding pg_advisory_xact_lock, so the lazy-creation path never runs concurrently. That works but has to be kept in sync with the application's label set; fixing the race in create_label() would make label auto-creation safe for everyone.

I'm happy to work on a PR for this if the approach in "Suggested fix" sounds right to the maintainers.