Echec lors de l'ajout d'une colonne

La norme SQL évolue : tirez le meilleur d'un SGBD qui la respecte en écrivant du SQL moderne avec en complément PL/pgSQL, php, java etc.
Répondre
Phil
Administrateur du site
Messages : 290
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Echec lors de l'ajout d'une colonne

Message par Phil »

Merci à Julien pour sa question :

"J'ai un échec lors de l'ajout d'une colonne avec PostgreSQL 11.7 :

alter table CPI_TEMP ADD NBTESTJH text;

PostgreSQL me répond que c’est impossible car une table ne peut pas avoir plus de 1600 colonnes.
ERREUR: les tables peuvent avoir au plus 1600 colonnes
Or cette table ne contient que 242 colonnes actuellement. Les types utilisés sont NUMERIC, TEXT, DATE. J’ai cru lire que la limite des 1600 colonnes pouvait descendre à 250 selon le type de colonnes de la table. Est-ce que tu me confirmes ce fonctionnement ? Le cas échéant est-ce qu’il y a un moyen de passer au-delà de cette limite avec une option quelconque ?"


Réponse :

1600 est la limite haute. Selon les types utilisés, cela peut être moins mais 242 alors que les colonnes sont de types text, numeric et date, cela ne semble pas la bonne explication. Cette table a-t-elle un fonctionnement particulier ?

Suivi :

Cette table a souvent des colonnes ajoutées et supprimées (add/drop column)

Réponse :

Que donne la commande :

Code : Tout sélectionner

SELECT DISTINCT
    a.attnum as num,
    a.attname as name,
    format_type(a.atttypid, a.atttypmod) as typ,
    a.attnotnull as notnull,
    com.description as comment,
    coalesce(i.indisprimary,false) as primary_key,
    def.adsrc as default
FROM pg_attribute a
JOIN pg_class pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_index i ON
    (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
LEFT JOIN pg_description com on
    (pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef def ON
    (a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid
AND pg_table_is_visible(pgc.oid)
-- AND NOT a.attisdropped
AND pgc.relname = 'cpi_temp'
ORDER BY a.attnum;
Suivi :
num | name | typ | notnull | comment | primary_key | default
------+---------------------------------+--------+---------+---------+-------------+---------
1 | idmatricule | bigint | f | | f |
2 | idtypepatient | bigint | f | | f |
3 | idtypepersonnel | bigint | f | | f |
4 | idtypesecteur | bigint | f | | f |
5 | idccam | bigint | f | | f |
6 | idproduit | bigint | f | | f |
7 | idmouvement | bigint | f | | f |
8 | idindicateur | bigint | f | | f |
9 | idconge | bigint | f | | f |
10 | idcompte | bigint | f | | f |
...
1585 | ........pg.dropped.1585........ | - | f | | f |
1586 | ........pg.dropped.1586........ | - | f | | f |
1587 | ........pg.dropped.1587........ | - | f | | f |
1588 | ........pg.dropped.1588........ | - | f | | f |
1589 | ........pg.dropped.1589........ | - | f | | f |
1590 | ........pg.dropped.1590........ | - | f | | f |
1591 | ........pg.dropped.1591........ | - | f | | f |
1592 | ........pg.dropped.1592........ | - | f | | f |
1593 | ........pg.dropped.1593........ | - | f | | f |
1594 | ........pg.dropped.1594........ | - | f | | f |
1595 | ........pg.dropped.1595........ | - | f | | f |
1596 | ........pg.dropped.1596........ | - | f | | f |
1597 | ........pg.dropped.1597........ | - | f | | f |
1598 | ........pg.dropped.1598........ | - | f | | f |
1599 | ........pg.dropped.1599........ | - | f | | f |
1600 | ........pg.dropped.1600........ | - | f | | f |
(1600 lignes)
Réponse :

Afin de gagner en performance, l'espace des colonnes supprimées n'est pas immédiatement restitué et les colonnes supprimées ne sont jamais supprimées de pg_attribute, elles sont marquées comme supprimées avec attisdropped à TRUE. L'espace peut être rendu avec un "VACUUM FULL" mais cela ne supprimera pour autant pas les informations dans pg_attribute.
La solution pour ajouter une colonne est ici de copier cette table "cpi_temp" sous un autre nom, par exemple cpi_temp_bis, de renommer la table cpi_temp en cpi_temp_old, de renommer cpi_temp_bis en cpi_temp puis de supprimer cpi_temp_old. Il sera alors possible d'ajouter une colonne à cpi_temp.
Cdlt. Phil - pgphil.ovh
Répondre