Project

General

Profile

Anomalie #10524

Requête extrêmement longue sur le serveur lors d'une mise à jour

Added by Henri QUENEAU about 3 years ago. Updated 12 months ago.

Status:
Développé / Analysé (S)
Priority:
2-Sérieux
Assignee:
Target version:
Start date:
05/07/2019
Due date:
Tags Courrier:
19.04.2

Description

En tant qu'administrateur technique, lorsque je fais une mise à jour, il y a la requête ci-dessous qui est extrêmement longue car j'ai énormément de courrier en base.

DO $$ BEGIN
  IF (SELECT count(attname) FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mlb_coll_ext') AND attname = 'recommendation_limit_date') = 1 THEN
    ALTER TABLE res_letterbox ADD COLUMN opinion_limit_date TIMESTAMP without TIME ZONE DEFAULT NULL;
    UPDATE res_letterbox SET opinion_limit_date =
    (
      SELECT recommendation_limit_date FROM mlb_coll_ext
      WHERE res_letterbox.res_id = mlb_coll_ext.res_id
    );
    ALTER TABLE mlb_coll_ext DROP COLUMN IF EXISTS recommendation_limit_date;
  END IF;
END$$;

Il faut ajouter une condition pour que ça marche mieux

DO $$ BEGIN
  IF (SELECT count(attname) FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mlb_coll_ext') AND attname = 'recommendation_limit_date') = 1 THEN
    ALTER TABLE res_letterbox ADD COLUMN opinion_limit_date TIMESTAMP without TIME ZONE DEFAULT NULL;

UPDATE res_letterbox SET opinion_limit_date = mlb_coll_ext.recommendation_limit_date 
FROM mlb_coll_ext
WHERE res_letterbox.res_id = mlb_coll_ext.res_id
AND recommendation_limit_date IS NOT NULL;

    ALTER TABLE mlb_coll_ext DROP COLUMN IF EXISTS recommendation_limit_date;
  END IF;
END$$;

History

#1 Updated by Henri QUENEAU about 3 years ago

  • Assignee deleted (Henri QUENEAU)

#2 Updated by Emmanuel DILLARD about 3 years ago

  • Status changed from A traiter to A étudier
  • Tags Courrier 19.04.2 added

#3 Updated by Florian AZIZIAN about 3 years ago

  • Tracker changed from Fonctionnalité to Anomalie
  • Project changed from Backlog to CURRENT SPRINT
  • Subject changed from requête extrêmement longue sur le serveur lors d'une mise à jour to Requête extrêmement longue sur le serveur lors d'une mise à jour
  • Status changed from A étudier to En cours de dev (S)
  • Assignee set to EDI PO
  • Target version changed from 19.04 (Support sécurité) to 20.03 (Support restreint)

#4 Updated by Emmanuel DILLARD about 3 years ago

  • Project changed from CURRENT SPRINT to Backlog
  • Status changed from En cours de dev (S) to Développé / Analysé (S)

#8 Updated by Emmanuel DILLARD 12 months ago

  • Project changed from Backlog to Backlog Courrier
  • Target version changed from 20.03 (Support restreint) to 20.03 (Restreint)

Also available in: Atom PDF