Forums PostgreSQL.fr

Alain V.

Membre

[Débutant] Réutilisation de numéros de séquence

Bonjour le forum.

Suite à un TRUNCATE TABLE qui ne détruit pas le compteur je m'attendais à ce que la progression de la séquence continue en tenant compte de la valeur "last_value" du catalogue.
Lors du repeuplement de cette table devenue vide j'ai constaté que la valeur "last_value" était bien prise en compte mais qu'il était quand même possible de réutiliser des valeurs détruites.

Est-ce normal qu'un INSERT INTO des valeurs de séquences ayant été déjà utilisées soient acceptées quand même avec le message d'erreur "clés primaires multiples ne sont pas autorisées"?

Voici un extrait de la table. Pour la lisibilité, j'ai supprimé les ALTER TABLE... OWNER TO.

CREATE TABLE t_00_dic_medical (
    t_00_noid bigint NOT NULL,
    t_00_tri numeric(6,0) DEFAULT 0,
    t_00_jp character varying(100),
    [...]

La totalité de la séquence avec une valeur 6 pour "last_value" :

CREATE SEQUENCE t_00_dic_medical_t_00_noid_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER SEQUENCE t_00_dic_medical_t_00_noid_seq OWNED BY t_00_dic_medical.t_00_noid;
SELECT pg_catalog.setval('t_00_dic_medical_t_00_noid_seq', 6, true);
ALTER TABLE t_00_dic_medical ALTER COLUMN t_00_noid SET DEFAULT nextval('t_00_dic_medical_t_00_noid_seq'::regclass);

Je peuple un peu la table pour tester. Le compteur monte à 6. Je la "truncate" puis je la repeuple à nouveau avec ce dump :

INSERT INTO t_00_dic_medical (t_00_noid, t_00_tri, t_00_jp, [...]) VALUES (1, 2, 'いぼ（疣）', [...]); (notez la valeur 1 entrée en première colonne)
INSERT INTO t_00_dic_medical (t_00_noid, t_00_tri, t_00_jp, [...]) VALUES (2, 11, '傷を焼灼する',[...]); (notez la valeur 2 entrée en première colonne)
ALTER TABLE ONLY t_00_dic_medical
    ADD CONSTRAINT t_00_dic_medical_pkey PRIMARY KEY (t_00_noid);

L'erreur :

$psql labonnebase < dump.txt
[...]
ERREUR:  la relation « t_00_dic_medical » existe déjà (Ce message me semble normal car la description de la table est aussi dans le dump)
ALTER TABLE
ERREUR:  la relation « t_00_dic_medical_t_00_noid_seq » existe déjà (Ce message me semble normal puisque je n'ai fait que "truncaté" la table et non "droppée")
ALTER TABLE
ALTER SEQUENCE
 setval
--------
      6
(1 ligne)

ALTER TABLE
INSERT 0 1
INSERT 0 1
ERREUR:  les clés primaires multiples ne sont pas autorisées pour la table « t_00_dic_medical »

Le résultat :

 t_00_noid | t_00_tri |   t_00_jp    |
-----------+----------+--------------+
         1 |        2 | いぼ（疣）   |
         2 |       11 | 傷を焼灼する |
         7 |        0 |              |

Pour la première colonne, je m'attendais à ce qu'on m'interdise le INSERT INTO 1, 2 et que la séquence reprenne à partir de 7. Je m'attendais donc à cette seule séquence autorisée : 7, 8, 9, et non 1, 2, 7 que j'ai forcé.

La valeur de la séquence dans le catalogue :

select * from t_00_dic_medical_t_00_noid_seq ;
         sequence_name          | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
--------------------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 t_00_dic_medical_t_00_noid_seq |          7 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t

Où est-ce que j'ai fait une erreur?
Qu'est-ce que je n'ai pas compris?

Merci beaucoup d'avance pour le temps que vous accorderez à mes interrogations.

flo

Membre

Re : [Débutant] Réutilisation de numéros de séquence

Bonjour,

Pourriez-vous copier ici l'ensemble du fichier ou des ordres passés, en un seul bloc?

Alain V.

Membre

Re : [Débutant] Réutilisation de numéros de séquence

Merci pour votre intérêt.

Voici ce que j'ai fait avec le dump qui génère le message d'erreur :

bd_archimede=> SELECT * FROM t_00_dic_medical;
 t_00_noid | t_00_tri |   t_00_jp    | t_00_class |       t_00_fr        | t_00_fr_gramm | t_00_uk | t_00_uk_gramm |                          t_00_explication
-----------+----------+--------------+------------+----------------------+---------------+---------+---------------+--------------------------------------------------------------------
         1 |        2 | いぼ（疣）   |            | verrue               | nf            |         |               |
         2 |       11 | 傷を焼灼する |            | cautériser une plaie |               |         |               | en brûler superficiellement les tissus afin d'éviter son infection
         7 |        0 |              |            | français             |               |         |               |
(3 lignes)

bd_archimede=> TRUNCATE t_00_dic_medical ;
TRUNCATE TABLE
bd_archimede=>

En ligne de commande :

$ psql -h 192.168.3.105 -U archimede -W bd_archimede < dump_t_00_dic_medical.txt
Mot de passe pour l'utilisateur archimede :
SET
SET
SET
SET
SET
SET
SET
SET
ERREUR:  la relation « t_00_dic_medical » existe déjà
ALTER TABLE
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
ERREUR:  la relation « t_00_dic_medical_t_00_noid_seq » existe déjà
ALTER TABLE
ALTER SEQUENCE
 setval
--------
      6
(1 ligne)

ALTER TABLE
INSERT 0 1
INSERT 0 1
ERREUR:  les clés primaires multiples ne sont pas autorisées pour la table « t_00_dic_medical »
ATTENTION:  aucun droit n'a pu être révoqué pour « public »
REVOKE
ATTENTION:  aucun droit n'a pu être révoqué pour « public »
REVOKE
ATTENTION:  aucun droit n'a été accordé pour « public »
GRANT
ATTENTION:  aucun droit n'a été accordé pour « public »
GRANT
$

Le dump :

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: t_00_dic_medical; Type: TABLE; Schema: public; Owner: archimede; Tablespace: 
--

CREATE TABLE t_00_dic_medical (
    t_00_noid bigint NOT NULL,
    t_00_tri numeric(6,0) DEFAULT 0,
    t_00_jp character varying(100),
    t_00_class character varying(10),
    t_00_fr character varying(100),
    t_00_fr_gramm character varying(10),
    t_00_uk character varying(100),
    t_00_uk_gramm character varying(10),
    t_00_explication text
);


ALTER TABLE public.t_00_dic_medical OWNER TO archimede;

--
-- Name: COLUMN t_00_dic_medical.t_00_noid; Type: COMMENT; Schema: public; Owner: archimede
--

COMMENT ON COLUMN t_00_dic_medical.t_00_noid IS 'Numérotation auto-incrémentée. Maximum = ???? Avec clef primaire';


--
-- Name: COLUMN t_00_dic_medical.t_00_tri; Type: COMMENT; Schema: public; Owner: archimede
--

COMMENT ON COLUMN t_00_dic_medical.t_00_tri IS 'Numéro de tri. Maximum = 1.000.000 de lignes dans ce dictionnaire';


--
-- Name: COLUMN t_00_dic_medical.t_00_jp; Type: COMMENT; Schema: public; Owner: archimede
--

COMMENT ON COLUMN t_00_dic_medical.t_00_jp IS 'Japonais. Limite 100 caractères. A voir les doubles octets';


--
-- Name: COLUMN t_00_dic_medical.t_00_class; Type: COMMENT; Schema: public; Owner: archimede
--

COMMENT ON COLUMN t_00_dic_medical.t_00_class IS 'Japonais pour classement. Limite 10 caractères. A voir les doubles octets';


--
-- Name: COLUMN t_00_dic_medical.t_00_fr; Type: COMMENT; Schema: public; Owner: archimede
--

COMMENT ON COLUMN t_00_dic_medical.t_00_fr IS 'Français. Limite 100 caractères.';


--
-- Name: COLUMN t_00_dic_medical.t_00_fr_gramm; Type: COMMENT; Schema: public; Owner: archimede
--

COMMENT ON COLUMN t_00_dic_medical.t_00_fr_gramm IS 'Français. Grammaire. Limite 10 caractères.';


--
-- Name: COLUMN t_00_dic_medical.t_00_uk; Type: COMMENT; Schema: public; Owner: archimede
--

COMMENT ON COLUMN t_00_dic_medical.t_00_uk IS 'Anglais. Limite 100 caractères.';


--
-- Name: COLUMN t_00_dic_medical.t_00_uk_gramm; Type: COMMENT; Schema: public; Owner: archimede
--

COMMENT ON COLUMN t_00_dic_medical.t_00_uk_gramm IS 'Anglais. Grammaire. Limite 10 caractères.';


--
-- Name: COLUMN t_00_dic_medical.t_00_explication; Type: COMMENT; Schema: public; Owner: archimede
--

COMMENT ON COLUMN t_00_dic_medical.t_00_explication IS 'Explication. Type de champs texte';


--
-- Name: t_00_dic_medical_t_00_noid_seq; Type: SEQUENCE; Schema: public; Owner: archimede
--

CREATE SEQUENCE t_00_dic_medical_t_00_noid_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.t_00_dic_medical_t_00_noid_seq OWNER TO archimede;

--
-- Name: t_00_dic_medical_t_00_noid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: archimede
--

ALTER SEQUENCE t_00_dic_medical_t_00_noid_seq OWNED BY t_00_dic_medical.t_00_noid;


--
-- Name: t_00_dic_medical_t_00_noid_seq; Type: SEQUENCE SET; Schema: public; Owner: archimede
--

SELECT pg_catalog.setval('t_00_dic_medical_t_00_noid_seq', 6, true);


--
-- Name: t_00_noid; Type: DEFAULT; Schema: public; Owner: archimede
--

ALTER TABLE t_00_dic_medical ALTER COLUMN t_00_noid SET DEFAULT nextval('t_00_dic_medical_t_00_noid_seq'::regclass);


--
-- Data for Name: t_00_dic_medical; Type: TABLE DATA; Schema: public; Owner: archimede
--

INSERT INTO t_00_dic_medical (t_00_noid, t_00_tri, t_00_jp, t_00_class, t_00_fr, t_00_fr_gramm, t_00_uk, t_00_uk_gramm, t_00_explication) VALUES (1, 2, 'いぼ（疣）', '', 'verrue', 'nf', '', '', NULL);
INSERT INTO t_00_dic_medical (t_00_noid, t_00_tri, t_00_jp, t_00_class, t_00_fr, t_00_fr_gramm, t_00_uk, t_00_uk_gramm, t_00_explication) VALUES (2, 11, '傷を焼灼する', '', 'cautériser une plaie', '', NULL, '', 'en brûler superficiellement les tissus afin d''éviter son infection');


--
-- Name: t_00_dic_medical_pkey; Type: CONSTRAINT; Schema: public; Owner: archimede; Tablespace: 
--

ALTER TABLE ONLY t_00_dic_medical
    ADD CONSTRAINT t_00_dic_medical_pkey PRIMARY KEY (t_00_noid);


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

Et le nouveau remplissage de la table :

bd_archimede=> SELECT * FROM t_00_dic_medical;
 t_00_noid | t_00_tri |   t_00_jp    | t_00_class |       t_00_fr        | t_00_fr_gramm | t_00_uk | t_00_uk_gramm |                          t_00_explication
-----------+----------+--------------+------------+----------------------+---------------+---------+---------------+--------------------------------------------------------------------
         1 |        2 | いぼ（疣）   |            | verrue               | nf            |         |               |
         2 |       11 | 傷を焼灼する |            | cautériser une plaie |               |         |               | en brûler superficiellement les tissus afin d'éviter son infection
(2 lignes)

bd_archimede=>

Je n'ai passé aucune autre commande, ni côté serveur, ni sur ma machine. Vous avez ici dans l'ordre la totalité de ce que je vois et de ce que j'ai exécuté.

gleu

Administrateur

Re : [Débutant] Réutilisation de numéros de séquence

Pourquoi le INSERT des valeurs 1 et 2 vous seraient interdits ? il n'y a strictement aucune contrainte sur la table et/ou les colonnes qui le permette. Pas plus que de triggers. Sans clé primaire, vous pouvez même avoir des valeurs en double ou NULL. La séquence ici est liée à la table en tant que valeur par défaut. Donc la séquence n'est prise en compte que quand vous faites un INSERT sans valeur pour la colonne t_00_noid (ou avec la valeur DEFAULT).

---

Guillaume.

Alain V.

Membre

Re : [Débutant] Réutilisation de numéros de séquence

Je n'avais pas compris qu'une séquence n'était prise en compte que sur un INSERT sans valeur pour la colonne t_00_noid (ou avec la valeur DEFAULT).

Je pensais aussi que la clef primaire que j'avais mise dessus permettait de préserver les valeurs détruites de toute réutilisation future :
ALTER TABLE ONLY t_00_dic_medical
    ADD CONSTRAINT t_00_dic_medical_pkey PRIMARY KEY (t_00_noid);

Il faut donc que je revoie la contrainte sur la colonne.

Encore merci pour le coup de pouce.

gleu

Administrateur

Re : [Débutant] Réutilisation de numéros de séquence

Une clé primaire empêche qu'il y ait des valeurs doublées ou NULL dans la table. Autrement dit, elle assure de l'unicité des valeurs et de leur non NULLité. Les anciennes valeurs ne sont donc pas prises en compte.

---

Guillaume.

Alain V.

Membre

Re : [Débutant] Réutilisation de numéros de séquence

Oui, c'est plus clair maintenant.