Erreur : ALTER TYPE ... ADD ne peut pas s'exécuter à l'intérieur d'un bloc transactionnel

Question

Je suis en train d'essayer d'ajouter une nouvelle valeur de type à mes types existants dans PostgreSQL. Mais je reçois l'erreur suivante

erreur : ALTER TYPE ... ADD ne peut pas s'exécuter à l'intérieur d'un bloc transactionnel

La requête que j'ai utilisée pour ajouter une nouvelle valeur au type est

ALTER TYPE public.request_type ADD VALUE "Check";

En fait, j'exécute la requête ci-dessus dans un fichier de migrations qui est créé en utilisant node-pg-migrate

Ici public est mon schéma.

Une idée pourquoi cela échoue ?

Éditer :

La requête ci-dessous s'exécute correctement lorsque je l'exécute dans pgadmin

ALTER TYPE public.request_type ADD VALUE "Check";

Mais lorsque j'exécute la commande ci-dessus à travers les migrations node-pg-migrate, cela échoue et lance l'erreur ci-dessus

Answer 1

Comme mentionné précédemment, vous ne pouvez pas modifier une énumération dans un bloc transactionnel. Mais vous pouvez en créer une nouvelle. Voici les étapes :

1. Changer le type de request_type en varchar pour toutes les colonnes/tables qui utilisent ce type :

   ALTER TABLE nom_table ALTER COLUMN request_type TYPE VARCHAR(255);

2. Supprimer et recréer l'énumération request_type :

   DROP TYPE IF EXISTS request_type; CREATE TYPE request_type AS ENUM ('OLD_VALUE_1', 'OLD_VALUE_2', 'NEW_VALUE_1', 'NEW_VALUE_2');

3. Revenir au type request_type depuis varchar pour toutes les colonnes/tables (revenir à l'étape un) :

   ALTER TABLE nom_table ALTER COLUMN request_type TYPE request_type USING (request_type::request_type);

Answer 2

La raison est donnée dans le commentaire suivant dans AlterEnum dans src/backend/commands/typecmds.c:

/*
 * Ordinairement, nous interdisons d'ajouter des valeurs dans des blocs de transaction,
 * car nous ne pouvons pas gérer le fait que des valeurs OID d'énumération se retrouvent dans des index et
 * ensuite que leurs entrées de pg_enum définissantes disparaissent. Cependant, c'est
 * correct si le type d'énumération a été créé dans la transaction actuelle, car
 * il ne peut alors y avoir aucun index de ce type qui ne disparaîtrait pas
 * en cas de Rollback. (Nous prenons en charge ce cas parce que pg_dump
 * --binary-upgrade en a besoin).

Notez que cette restriction a été levée dans commit 212fab99; le message de commit indique:

Pour éviter que les index sur les colonnes enum soient potentiellement cassés, nous devons nous assurer que
les valeurs enum non engagées ne sont pas stockées dans les tables, sauf si nous
sommes sûrs qu'une telle colonne est nouvelle dans la transaction en cours.

Auparavant, nous imposions cela en interdisant l'exécution de ALTER TYPE ... ADD VALUE
dans un bloc de transaction, sauf si le type enum cible avait été créé dans la transaction en cours.  Cette
patch supprime cette restriction, et insiste plutôt sur le fait qu'une valeur enum non engagée
ne peut être référencée que si elle appartient à un type enum créé
dans la même transaction que la valeur. Suite à la discussion, cela devrait être
un peu moins contraignant. Il nécessite que chaque fonction susceptible de
renvoyer une nouvelle valeur enum aux opérations SQL vérifie cette restriction,
mais il n'y en a pas tant que ça qui rende cela insoutenable.

Il serait donc peut-être temps de passer à PostgreSQL v12 :^)

Answer 3

Contournement pour les anciennes versions de PostgreSQL montré ici:

Notez que cela nécessitera des autorisations spéciales car cela modifie une table système.

• Remplacez 'NEW_ENUM_VALUE' par la valeur souhaitée.
• Remplacez 'type_egais_units' par l'oid de l'énumération que vous souhaitez modifier. (Utilisez SELECT * FROM pg_enum pour trouver l'énumération que vous souhaitez mettre à jour, dans mon cas c'était un nombre à 5 chiffres comme '19969')

L'instruction:

INSERT INTO pg_enum (
    enumtypid, 
    enumlabel, 
    enumsortorder
)
SELECT 
    'type_egais_units'::regtype::oid, 
    'NEW_ENUM_VALUE', 
    (SELECT MAX(enumsortorder) + 1 FROM pg_enum WHERE enumtypid = 'type_egais_units'::regtype)

Évidemment, mettre à niveau PostgreSQL comme suggéré dans la réponse acceptée est probablement la meilleure solution.

Est-ce que quelqu'un sait comment éviter d'utiliser des transactions lors de l'exécution de requêtes à partir de pgAdmin Version 3.5? (c'est-à-dire lors de l'exécution avec F5?)

Answer 4

Vous pouvez modifier votre requête pour :

COMMIT;
ALTER TYPE public.request_type ADD VALUE "Check";