Messages de HadanMarv

HadanMarv · Général

Autant pour moi erreur de typo effectivement maquery est bien une variable de type character varying dont la variable est "calculé" à partir d'une requête analysant le catalogue (pg_tables et pg_attributes).

HadanMarv · Général

J'ai trouvé une solution toute simple :

maquery := INSERT INTO public.MATABLE (field1, field2) values ($1.field1, $1.field2);

Et ensuite :

EXECUTE maquery USING NEW;

C'était simple, fallait 'juste' savoir que c'était possible
Bonne journée,

HadanMarv

HadanMarv · Général

Bonjour,

Afin d'effectuer une "migration douce" terme à la mode il parait, j'essaie de mettre en place des triggers sur des tables pour que lors de l'insert ou la modification d'un enregistrement les données créer ou mises à jour soit recopier à l'identique dans une autre table ayant strictement la même structure mais dans un autre schéma.
Avec l'analyse du pg_tables et du pg_attribute j'arrive à généré la requête d'insert sans problème.
Le seul petit soucis, c'est que lors de l'appel du EXECUTE de ma requête généré il me dit à juste titre NEW.nomduchamp non reconnu car il voit çà comme un litteral et non comme une chaine que le moteur doit interprété.
l'erreur exacte est : missing FROM-clause entry for table "new"
J'arrive à générer un truc du style :

INSERT INTO public.MATABLE (field1, field2) values (NEW.field1, NEW.field2);

Je précise que je suis dans un trigger after insert or update.
D'avance merci de vos lumières

HadanMarv

HadanMarv · Général

Effectivement, en fait je me rends compte que c'est "juste" une limitation de PgAdmin.

Du coup pour ceux que çà pourrait intéresser voici ce que j'ai fait :
1. Création d'un vue des dépendances entre vues
2. Création d'une table basique avec deux champs, un id et un champ query (oui j'ai cherché longtemps celui-là )
3. Création d'une fonction (pl/sql) prenant en paramètre le nom du schéma et le nom de la vue que l'on souhaite modifier, cette fonction requête sur la vue créé plus haut et va créer les "drop" et les "create materialized view" dans le bon ordre
4. Modification à la main du script de la vue passé en étape 3 dans le champ query de la table mentionné en étape 2
5. Création d'une fonction (pl/sql), qui lie simplement les enregistrement de la table de l'étape 2 dans l'ordre (merci l'id) et qui fait un execute

Manque l'analyse du dico pour les index...
Mais globalement l'idée est là.

Si ce n'est pas clair -> dites le moi
Que vous pensez que je fais fausse route -> dites le moi
Que vous voulez les codes de mes fonctions -> dites le moi

Bonne journée
Anthony

HadanMarv · Général

Bonjour,

J'ai commencé le développement d’agrégation et de consolidation de données dans ma base de données Postgres.
En gros j'ai des centaines de tables, sur lesquelles je viens faire des pré-calculs via des consolidations et ensuite je les agrège entre elles.
Je me retrouve avec prêt de 200 vues matérialisé avec des performances corrects compte tenu de la volumétrie de données à traiter.

En cherchant sur le net, j'ai trouvé assez vite comment analyser le catalogue postgres pour créer un script me permettant de rafraîchir les vues dans le bonne ordre.

Mon problème :
Certaines des vues doivent être corrigés de temps en temps (mauvaise données en entrée, nouvelles règles de calcul, etc...)
Mais si la vue à des dépendances enfants, je suis d'abord obligé de droper les enfants avant de pouvoir droper la vue en question et de tout recréer.
çà se fait mais c'est pénible car tout à la main (avec l'arbre de dépendance, copier / coller des scripts dans une console dans le bon ordre, et exécution).

Ma question : existe-t-il une requête qui me permettrait de récupérer l'intégralité des définitions des vues matérialisés ? j'ai trouvé dans le catalogue le champ view_deifnition (information_schema.views) mais le champ est tronqué...
D'avance merci de vos retours.

HadanMarv

HadanMarv · Général

Impeccable, çà fonctionne.
Merci beaucoup

HadanMarv · Général

Le generate_series m'est utile pour faire un count du nombre d'enregistrement par jour.
Données initiales :

ID     DATE               HEURE    VALUE
1       01/09/2018      01:45:52  TEST0
2       01/09/2018      02:50:01  TEST1
3       02/09/2018      12:45:00  TEST2
4       02/09/2018      13:20:57  TEST3
5       02/09/2018      17:37:14  TEST4
6       04/09/2018      05:45:27  TEST5
7       04/09/2018      07:57:22  TEST6
8       05/09/2018      12:22:01  TEST7
9       09/09/2018      11:45:21  TEST8
10     09/09/2018      18:45:22  TEST9

avec un generate_series, un count et un group by j'obtiens :

DATE               COUNT_ROW
01/09/2018      2
02/09/2018      3
03/09/2018      0
04/09/2018      2
05/09/2018      1
06/09/2018      0
07/09/2018      0
08/09/2018      0
09/09/2018      2

Maintenant je voudrais écrire une requête qui me permettrait de détecter que je n'ai aucune données entre le 06/09/2018 et le 08/09/2018 car 3 jours sans rien.
Je ne sais pas si je suis plus clair

HadanMarv

HadanMarv · Général

Bonjour,

Je suis confronté a une petite problématique dans une requête.
J'ai une table contenu plusieurs enregistrement par jour.
Avec un count,un generate series et un group by j'arrive à trouver facilement le nombre d'enregistrement par jour même si je n'en ai pas.
Maintenant çà ce complique, je dois déterminer si j'ai trois jours consécutifs pour lesquelles je n'ai pas d'enregistrement.
Je pense qu'avec les fonctions de fenêtrage, il doit y avoir une solution, mais je n'ai pas encore trouvé laquelle.
Avez-vous une idée ?

D'avance merci de votre aide.

HadanMarv

HadanMarv · Général

Oui j'ai positionné des index sur la table facture et sur la table commande sur les deux champs en question séparément et groupés pour tester mais sans différence notable
Au commencement je voulais juste faire un select pour valider mes traitements, j'ai maintenant alimenter le nouveau champ mais les temps d’exécution sont très longs.
environ 16 secondes pour 100 facture, passant à 4min 30 pour 1000.
Les plans d'exécution montre chaque fois que les index non combinés sont utilisés (ce qui me parait cohérent).

HadanMarv · Général

Bonjour,

Tout d'abord merci de votre retour.
Malheureusement les temps de réponse sont horribles environ 4 min pour 10000 enregistrements de factures.
Personne n'aurait une autre idée ?

HadanMarv · Général

C'est si complexe que çà ? ou j'ai super mal expliqué mon problème ?

HadanMarv · Général

Bonjour à tous,

Voici un cas que je n'arrive pas à résoudre facilement que je vous expose ici :
J'ai une première table qui contient la liste des factures par client.
J'ai une autre table qui contient la liste des commandes par client.
Mais bien sûre je n'ai pas de lien direct entre les deux du style une facture est lié à une commande.
Je veux intégrer l'id de la commande de la facture, j'ai donc modifié les tables en conséquences et maintenant je veux faire la reprise de données.

Je récupère toutes les factures pour un client, et ensuite je voudrais trouver dans la liste des commandes celle qui par date devrait correspondre (c'est un peu hasardeux mais bon).
Et là je bloque les temps de réponse sont horribles étant donnée les volumes de données à traiter.

select date_facture, client from facture

ensuite je fais :

select max(id) from commande where date_commande <= date_facture and client = client

J'ai donc mon id de commande.
Par contre je souhaiterai l'écrire en une seule requête histoire de gagner du temps car j'ai des millions d'enregistrements à traiter

D'avance merci de votre retour
HadanMarv

HadanMarv · Général

Effectivement l'index devait être corrompu, en mettant à jour l'index (reindex table A) tout est rentré dans l'ordre.
L'objet 522031 correspondait à la table en question.
Merci de votre aide.

HadanMarv · Général

Bonjour,

Je suis confronté à un problème que je n'avais jamais rencontré jusqu'alors et que j'espère vous allez pouvoir m'aider à résoudre.

J'ai une table A qui contient un champ a de type character varying(14).

J'ai été alerté par un utilisateur qu'une requête sur cette table lui retournait une erreur de ce type :
"n'a pas pu lire le bloc 5287 de la relation base/177867/522031 : a lu seulement 0 octets", semble indiqué que le disque dur doit en avoir un coup !

la requête est : select a from A where a = '34121227200056'
cependant en faisant select a from A where a like '3412122720005%' cela fonctionne correctement.

Je tente avec un autre jeu de donnée : 34121227200049 (avec la première requête) et là seconde surprise j'obtiens bien la ligne avec la chaîne 34121227200049 mais aussi une ligne avec la chaîne 06950303500011
qui vous en conviendrez avec moi n'a rien à voir. Encore plus étrange si je modifie un peu la requête avec :
select a from A where CAST(a as decimal) = cast('34121227200049' as decimal) et bien j’obtiens que ma ligne en question.

Merci d'avance de vos lumières

HadanMarv

HadanMarv · Général

Souhaitant vraiment comprendre la source de mon erreur afin de ne plus la commettre voici la requête exécuté par ma vue.
Elle devrait être exécutable partout.

Le but de cette requête et de réussir à ventiler la durée d'une matinée de cours et d'une après-midi en fonction de la date de début et de la date de fin et compte tenue de la durée en heures de la formation, de la durée en jours de la formation et également de la durée que j'ai appelé théorique.
Cette durée théorique représente le ratio de temps par jours obtenu par nb_beures_formation / nb_jours_formation.
Je souhaite que si la durée théorique est inférieure à 07h30 alors on prenne une répartition des horaires 3 heures le matin et 4 heures l'après-midi, au delà de 07h30 on prend une répartition 4 heures le matin et 4 heures l'après-midi, en calculant le reste sur la dernière demi-journée.

D'avance merci de vos lumières.

select DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique,
AM_JOUR_1, PM_JOUR_1, AM_JOUR_2, PM_JOUR_2, AM_JOUR_3, PM_JOUR_3, AM_JOUR_4, PM_JOUR_4, AM_JOUR_5, PM_JOUR_5, somme_jour_5,
nbJourReel
from (
select *, CASE WHEN (somme_jour_5 - duree_heures) != 0 THEN CAST(somme_jour_5 - duree_heures as VARCHAR) ELSE '' END as test,
CASE WHEN date_part('day', dateDebReel) > date_part('day', dateFinReel) THEN
CASE WHEN startInTheMorning AND date_part('hour', dateFinReel) > 13 THEN
1
ELSE
CASE WHEN startInTheMorning AND date_part('hour', dateFinReel) <= 13 THEN
0.5
ELSE
CASE WHEN startOnTheAfternoon AND date_part('hour', dateFinReel) > 13 THEN
0.5
ELSE
CASE WHEN startOnTheAfternoon AND date_part('hour', dateFinReel) <= 13 THEN
0
ELSE
1
END
END
END
END
+ date_part('day', dateDebReel) - date_part('day', dateFinReel)
ELSE
CASE WHEN date_part('day', dateDebReel) = date_part('day', dateFinReel) THEN
CASE WHEN (startInTheMorning AND date_part('hour', dateFinReel) <= 13) OR startOnTheAfternoon THEN
0.5
ELSE
1
END
ELSE
CASE WHEN startInTheMorning AND date_part('hour', dateFinReel) > 13 THEN
1
ELSE
CASE WHEN startInTheMorning AND date_part('hour', dateFinReel) <= 13 THEN
0.5
ELSE
CASE WHEN startOnTheAfternoon AND date_part('hour', dateFinReel) > 13 THEN
0.5
ELSE
0
END
END
END
+ date_part('day', dateFinReel) - date_part('day', dateDebReel)
END
END as nbJourReel
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique, startInTheMorning, startOnTheAfternoon,
AM_JOUR_1, PM_JOUR_1, /*somme_jour_1,*/
AM_JOUR_2, /*AM_JOUR_NUM_2, somme_jour_1_5,*/
PM_JOUR_2, /*PM_JOUR_NUM_2, somme_jour_2,*/
AM_JOUR_3, /*AM_JOUR_NUM_3, somme_jour_2_5,*/
PM_JOUR_3, /*PM_JOUR_NUM_3, somme_jour_3,*/
AM_JOUR_4, /*AM_JOUR_NUM_4, somme_jour_3_5,*/
PM_JOUR_4, /*PM_JOUR_NUM_4, somme_jour_4,*/
AM_JOUR_5, /*AM_JOUR_NUM_5, somme_jour_4_5,*/
CASE WHEN (dureeJours >= 5 AND duree_heures >= 35) OR (dureeJours > 4 AND startOnTheAfternoon) THEN
CASE WHEN duree_heures - somme_jour_4_5 != 4 THEN
SUBSTR(CAST(duree_heures - somme_jour_4_5 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_4_5 AS VARCHAR)) - 1) || 'h' ||
CASE WHEN SUBSTR(CAST(duree_heures - somme_jour_4_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_4_5 AS VARCHAR)) + 1) != '00' THEN
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_4_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_4_5 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
''
END
ELSE
'4h'
END
ELSE
''
END AS PM_JOUR_5,
/*CASE WHEN dureeJours = 5 OR ( dureeJours > 4 AND startOnTheAfternoon) THEN
4
ELSE
0
END AS PM_JOUR_NUM_5,*/
CASE WHEN (dureeJours >= 5 AND duree_heures >= 35) OR (dureeJours > 4 AND startOnTheAfternoon) THEN
CASE WHEN duree_heures - somme_jour_4_5 != 4 THEN
duree_heures - somme_jour_4_5
ELSE
4
END
ELSE
0
END + somme_jour_4_5 as somme_jour_5
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique, startInTheMorning, startOnTheAfternoon,
AM_JOUR_1, PM_JOUR_1, somme_jour_1,
AM_JOUR_2, AM_JOUR_NUM_2, somme_jour_1_5,
PM_JOUR_2, PM_JOUR_NUM_2, somme_jour_2,
AM_JOUR_3, AM_JOUR_NUM_3, somme_jour_2_5,
PM_JOUR_3, PM_JOUR_NUM_3, somme_jour_3,
AM_JOUR_4, AM_JOUR_NUM_4, somme_jour_3_5,
PM_JOUR_4, PM_JOUR_NUM_4, somme_jour_4,
CASE WHEN dureeJours >= 5 OR (dureeJours >= 4 AND startOnTheAfternoon) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
'3h'
ELSE
'4h'
END
ELSE
''
END AS AM_JOUR_5,
CASE WHEN dureeJours >= 5 OR (dureeJours >= 4 AND startOnTheAfternoon) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
3
ELSE
4
END
ELSE
0
END AS AM_JOUR_NUM_5,
CASE WHEN dureeJours >= 5 OR (dureeJours >= 4 AND startOnTheAfternoon) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
3
ELSE
4
END
ELSE
0
END + somme_jour_4 as somme_jour_4_5
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique, startInTheMorning, startOnTheAfternoon,
AM_JOUR_1, PM_JOUR_1, somme_jour_1,
AM_JOUR_2, AM_JOUR_NUM_2, somme_jour_1_5,
PM_JOUR_2, PM_JOUR_NUM_2, somme_jour_2,
AM_JOUR_3, AM_JOUR_NUM_3, somme_jour_2_5,
PM_JOUR_3, PM_JOUR_NUM_3, somme_jour_3,
AM_JOUR_4, AM_JOUR_NUM_4, somme_jour_3_5,
CASE WHEN dureeJours >= 4 THEN
CASE WHEN duree_heures - somme_jour_3_5 < 4 OR (duree_heures - somme_jour_3_5 > 4 AND duree_heures - somme_jour_3_5 <= 6) THEN
SUBSTR(CAST(duree_heures - somme_jour_3_5 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_3_5 AS VARCHAR)) - 1) || 'h' ||
CASE WHEN SUBSTR(CAST(duree_heures - somme_jour_3_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_3_5 AS VARCHAR)) + 1) != '00' THEN
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_3_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_3_5 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
''
END
ELSE
CASE WHEN dureeJours = 4 AND duree_heures > 32 THEN
SUBSTR(CAST(duree_heures - somme_jour_3_5 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_3_5 AS VARCHAR)) - 1) || 'h' ||
CASE WHEN SUBSTR(CAST(duree_heures - somme_jour_3_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_3_5 AS VARCHAR)) + 1) != '00' THEN
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_3_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_3_5 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
''
END
ELSE
'4h'
END
END
ELSE
CASE WHEN dureeJours > 3 AND startOnTheAfternoon THEN
CASE WHEN duree_heures - somme_jour_3_5 < 4 THEN
SUBSTR(CAST(duree_heures - somme_jour_3_5 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_3_5 AS VARCHAR)) - 1) || 'h' ||
CASE WHEN SUBSTR(CAST(duree_heures - somme_jour_3_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_3_5 AS VARCHAR)) + 1) != '00' THEN
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_3_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_3_5 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
''
END
ELSE
'4h'
END
ELSE
''
END
END AS PM_JOUR_4,
CASE WHEN dureeJours >= 4 THEN
CASE WHEN duree_heures - somme_jour_3_5 < 4 OR (duree_heures - somme_jour_3_5 > 4 AND duree_heures - somme_jour_3_5 <= 6) THEN
duree_heures - somme_jour_3_5
ELSE
CASE WHEN dureeJours = 4 AND duree_heures > 32 THEN
duree_heures - somme_jour_3_5
ELSE
4
END
END
ELSE
CASE WHEN dureeJours > 3 AND startOnTheAfternoon THEN
CASE WHEN duree_heures - somme_jour_3_5 < 4 THEN
duree_heures - somme_jour_3_5
ELSE
4
END
ELSE
0
END
END AS PM_JOUR_NUM_4,
CASE WHEN dureeJours >= 4 THEN
CASE WHEN duree_heures - somme_jour_3_5 < 4 OR (duree_heures - somme_jour_3_5 > 4 AND duree_heures - somme_jour_3_5 <= 6) THEN
duree_heures - somme_jour_3_5
ELSE
CASE WHEN dureeJours = 4 AND duree_heures > 32 THEN
duree_heures - somme_jour_3_5
ELSE
4
END
END
ELSE
CASE WHEN dureeJours > 3 AND startOnTheAfternoon THEN
CASE WHEN duree_heures - somme_jour_3_5 < 4 THEN
duree_heures - somme_jour_3_5
ELSE
4
END
ELSE
0
END
END + somme_jour_3_5 as somme_jour_4
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique, startInTheMorning, startOnTheAfternoon,
AM_JOUR_1, PM_JOUR_1, somme_jour_1,
AM_JOUR_2, AM_JOUR_NUM_2, somme_jour_1_5,
PM_JOUR_2, PM_JOUR_NUM_2, somme_jour_2,
AM_JOUR_3, AM_JOUR_NUM_3, somme_jour_2_5,
PM_JOUR_3, PM_JOUR_NUM_3, somme_jour_3,
CASE WHEN dureeJours >=4 OR (dureeJours = 3 AND startOnTheAfternoon AND (duree_heures - somme_jour_3) > 0) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
'3h'
ELSE
'4h'
END
ELSE
CASE WHEN dureeJours > 3 AND dureeJours < 4 THEN
CASE WHEN dureeTheorique <= 7.50 THEN
CASE WHEN duree_heures - somme_jour_3 <= 4 THEN
SUBSTR(CAST(duree_heures - somme_jour_3 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_3 AS VARCHAR)) - 1) || 'h' ||
CASE WHEN SUBSTR(CAST(duree_heures - somme_jour_3 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_3 AS VARCHAR)) + 1) != '00' THEN
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_3 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_3 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
''
END
ELSE
'3h'
END
ELSE
'4h'
END
ELSE
''
END
END AS AM_JOUR_4,
CASE WHEN dureeJours >=4 OR (dureeJours = 3 AND startOnTheAfternoon AND (duree_heures - somme_jour_3) > 0) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
3
ELSE
4
END
ELSE
CASE WHEN dureeJours > 3 AND dureeJours < 4 THEN
CASE WHEN dureeTheorique <= 7.50 THEN
CASE WHEN duree_heures - somme_jour_3 <= 4 THEN
duree_heures - somme_jour_3
ELSE
3
END
ELSE
4
END
ELSE
0
END
END AS AM_JOUR_NUM_4,
CASE WHEN dureeJours >=4 OR (dureeJours = 3 AND startOnTheAfternoon AND (duree_heures - somme_jour_3) > 0) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
3
ELSE
4
END
ELSE
CASE WHEN dureeJours > 3 AND dureeJours < 4 THEN
CASE WHEN dureeTheorique <= 7.50 THEN
CASE WHEN duree_heures - somme_jour_3 <= 4 THEN
duree_heures - somme_jour_3
ELSE
3
END
ELSE
4
END
ELSE
0
END
END + somme_jour_3 as somme_jour_3_5
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique, startInTheMorning, startOnTheAfternoon,
AM_JOUR_1, PM_JOUR_1, somme_jour_1,
AM_JOUR_2, AM_JOUR_NUM_2, somme_jour_1_5,
PM_JOUR_2, PM_JOUR_NUM_2, somme_jour_2,
AM_JOUR_3, AM_JOUR_NUM_3, somme_jour_2_5,
CASE WHEN dureeJours >= 3 THEN
CASE WHEN duree_heures - somme_jour_2_5 < 4 OR (duree_heures - somme_jour_2_5 > 4 AND duree_heures - somme_jour_2_5 < 6) THEN
SUBSTR(CAST(duree_heures - somme_jour_2_5 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_2_5 AS VARCHAR)) - 1) || 'h' ||
CASE WHEN SUBSTR(CAST(duree_heures - somme_jour_2_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_2_5 AS VARCHAR)) + 1) != '00' THEN
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_2_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_2_5 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
''
END
ELSE
'4h'
END
ELSE
CASE WHEN dureeJours > 2 AND startOnTheAfternoon THEN
CASE WHEN duree_heures - somme_jour_2_5 < 4 THEN
SUBSTR(CAST(duree_heures - somme_jour_2_5 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_2_5 AS VARCHAR)) - 1) || 'h' ||
CASE WHEN SUBSTR(CAST(duree_heures - somme_jour_2_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_2_5 AS VARCHAR)) + 1) != '00' THEN
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_2_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_2_5 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
''
END
ELSE
'4h'
END
ELSE
''
END
END AS PM_JOUR_3,
CASE WHEN dureeJours >= 3 THEN
CASE WHEN duree_heures - somme_jour_2_5 < 4 OR (duree_heures - somme_jour_2_5 > 4 AND duree_heures - somme_jour_2_5 < 6) THEN
duree_heures - somme_jour_2_5
ELSE
4
END
ELSE
CASE WHEN dureeJours > 2 AND startOnTheAfternoon THEN
CASE WHEN duree_heures - somme_jour_2_5 < 4 THEN
duree_heures - somme_jour_2_5
ELSE
4
END
ELSE
0
END
END AS PM_JOUR_NUM_3,
CASE WHEN dureeJours >= 3 THEN
CASE WHEN duree_heures - somme_jour_2_5 < 4 OR (duree_heures - somme_jour_2_5 > 4 AND duree_heures - somme_jour_2_5 < 6) THEN
duree_heures - somme_jour_2_5
ELSE
4
END
ELSE
CASE WHEN dureeJours > 2 AND startOnTheAfternoon THEN
CASE WHEN duree_heures - somme_jour_2_5 < 4 THEN
duree_heures - somme_jour_2_5
ELSE
4
END
ELSE
0
END
END + somme_jour_2_5 as somme_jour_3
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique, startInTheMorning, startOnTheAfternoon,
AM_JOUR_1, PM_JOUR_1, somme_jour_1,
AM_JOUR_2, AM_JOUR_NUM_2, somme_jour_1_5,
PM_JOUR_2, PM_JOUR_NUM_2, somme_jour_2,
CASE WHEN dureeJours >=3 OR (dureeJours = 2 AND startOnTheAfternoon) OR (dureeJours > 2 AND dureeJours < 3) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
CASE WHEN duree_heures - somme_jour_2 <= 4 THEN
SUBSTR(CAST(duree_heures - somme_jour_2 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_2 AS VARCHAR)) - 1) || 'h' ||
CASE WHEN SUBSTR(CAST(duree_heures - somme_jour_2 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_2 AS VARCHAR)) + 1) != '00' THEN
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_2 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_2 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
''
END
ELSE
'3h'
END
ELSE
'4h'
END
ELSE
''
END AS AM_JOUR_3,
CASE WHEN dureeJours >=3 OR (dureeJours = 2 AND startOnTheAfternoon) OR (dureeJours > 2 AND dureeJours < 3) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
CASE WHEN duree_heures - somme_jour_2 <= 4 THEN
duree_heures - somme_jour_2
ELSE
3
END
ELSE
4
END
ELSE
0
END AS AM_JOUR_NUM_3,
CASE WHEN dureeJours >=3 OR (dureeJours = 2 AND startOnTheAfternoon) OR (dureeJours > 2 AND dureeJours < 3) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
CASE WHEN duree_heures - somme_jour_2 <= 4 THEN
duree_heures - somme_jour_2
ELSE
3
END
ELSE
4
END
ELSE
0
END + somme_jour_2 as somme_jour_2_5
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique, startInTheMorning, startOnTheAfternoon, AM_JOUR_1, PM_JOUR_1, somme_jour_1, AM_JOUR_2, AM_JOUR_NUM_2, somme_jour_1_5,
CASE WHEN (dureeJours >=2 OR ( dureeJours > 1 AND startOnTheAfternoon)) AND duree_heures > somme_jour_1_5 THEN
CASE WHEN duree_heures - somme_jour_1_5 <= 5 THEN
SUBSTR(CAST(duree_heures - somme_jour_1_5 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_1_5 AS VARCHAR)) - 1) || 'h' ||
CASE WHEN SUBSTR(CAST(duree_heures - somme_jour_1_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_1_5 AS VARCHAR)) + 1) != '00' THEN
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_1_5 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_1_5 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
''
END
ELSE
'4h'
END
ELSE
''
END AS PM_JOUR_2,
CASE WHEN (dureeJours >=2 OR ( dureeJours > 1 AND startOnTheAfternoon)) AND duree_heures > somme_jour_1_5 THEN
CASE WHEN duree_heures - somme_jour_1_5 <= 5 THEN
duree_heures - somme_jour_1_5
ELSE
4
END
ELSE
0
END AS PM_JOUR_NUM_2,
CASE WHEN (dureeJours >=2 OR ( dureeJours > 1 AND startOnTheAfternoon)) AND duree_heures > somme_jour_1_5 THEN
CASE WHEN duree_heures - somme_jour_1_5 <= 5 THEN
duree_heures - somme_jour_1_5
ELSE
4
END
ELSE
0
END + somme_jour_1_5 as somme_jour_2
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique, startInTheMorning, startOnTheAfternoon, AM_JOUR_1, PM_JOUR_1, somme_jour_1,
CASE WHEN (dureeJours >=2 OR (dureeJours = 1 AND startOnTheAfternoon) OR (dureeJours > 1 AND duree_heures != 8)) AND duree_heures > somme_jour_1 THEN
CASE WHEN dureeTheorique <= 7.50 THEN
CASE WHEN (duree_heures - somme_jour_1) > 0 and (duree_heures - somme_jour_1) < 4 THEN
SUBSTR(CAST(duree_heures - somme_jour_1 AS VARCHAR), 1, position('.' in CAST(duree_heures - somme_jour_1 AS VARCHAR)) - 1) || 'h' ||
REPLACE(REPLACE(CAST(60 * CAST('0.' || SUBSTR(CAST(duree_heures - somme_jour_1 AS VARCHAR), position('.' in CAST(duree_heures - somme_jour_1 AS VARCHAR)) + 1) AS NUMERIC) AS VARCHAR),'00',''),'.','')
ELSE
'3h'
END
ELSE
'4h'
END
ELSE
''
END AS AM_JOUR_2,
CASE WHEN dureeJours >=2 OR (dureeJours = 1 AND startOnTheAfternoon) OR (dureeJours > 1 AND duree_heures != 8) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
CASE WHEN (duree_heures - somme_jour_1) < 4 THEN
duree_heures - somme_jour_1
ELSE
3
END
ELSE
4
END
ELSE
0
END AS AM_JOUR_NUM_2,
CASE WHEN dureeJours >=2 OR (dureeJours = 1 AND startOnTheAfternoon) OR (dureeJours > 1 AND duree_heures != 8) THEN
CASE WHEN dureeTheorique <= 7.50 THEN
CASE WHEN (duree_heures - somme_jour_1) < 4 THEN
duree_heures - somme_jour_1
ELSE
3
END
ELSE
4
END
ELSE
0
END + somme_jour_1 as somme_jour_1_5
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours, dureeTheorique, startInTheMorning, startOnTheAfternoon,
AM_JOUR_1, AM_JOUR_NUM_1,
CASE WHEN dureeJours >=1 AND duree_heures >= 7 THEN
'4h'
ELSE
CASE WHEN dureeJours =1 AND duree_heures < 7 THEN
SUBSTR(CAST(duree_heures - AM_JOUR_NUM_1 AS VARCHAR), 1, position('.' in CAST(duree_heures - AM_JOUR_NUM_1 AS VARCHAR)) - 1) || 'h' ||
REPLACE(
REPLACE(
CAST(CAST(
ROUNDOMEGA(
60 * CAST('0.' || SUBSTR(CAST(duree_heures - AM_JOUR_NUM_1 AS VARCHAR), position('.' in CAST(duree_heures - AM_JOUR_NUM_1 AS VARCHAR)) + 1) AS NUMERIC)
)
AS NUMERIC(2,0)) AS VARCHAR)
,'00','')
,'.','')
ELSE
CASE WHEN duree_heures > 4 AND dureeJours < 1 THEN
CASE WHEN ROUNDOMEGA(duree_heures / dureeJours) <= 7.50 THEN
REPLACE(REPLACE(CAST((duree_heures - 3) AS VARCHAR),'.','h'),'00','')
ELSE
REPLACE(REPLACE(CAST((duree_heures - 4) AS VARCHAR),'.','h'),'00','')
END
ELSE
CASE WHEN date_part('hour', dateDebReel) >= 13 THEN
REPLACE(REPLACE(CAST(duree_heures AS VARCHAR),'.','h'),'00','')
ELSE
''
END
END
END
END AS PM_JOUR_1,
CASE WHEN dureeJours >=1 AND duree_heures >= 7 THEN
4
ELSE
CASE WHEN dureeJours =1 AND duree_heures < 7 THEN
duree_heures - AM_JOUR_NUM_1
ELSE
CASE WHEN duree_heures > 4 AND dureeJours < 1 THEN
CASE WHEN ROUNDOMEGA(duree_heures / dureeJours) <= 7.50 THEN
duree_heures - 3
ELSE
duree_heures - 4
END
ELSE
CASE WHEN date_part('hour', dateDebReel) >= 13 THEN
duree_heures
ELSE
0
END
END
END
END AS PM_JOUR_NUM_1,
CASE WHEN dureeJours >=1 AND duree_heures >= 7 THEN
4
ELSE
CASE WHEN dureeJours =1 AND duree_heures < 7 THEN
duree_heures - AM_JOUR_NUM_1
ELSE
CASE WHEN duree_heures > 4 AND dureeJours < 1 THEN
CASE WHEN ROUNDOMEGA(duree_heures / dureeJours) <= 7.50 THEN
duree_heures - 3
ELSE
duree_heures - 4
END
ELSE
CASE WHEN date_part('hour', dateDebReel) >= 13 THEN
duree_heures
ELSE
0
END
END
END
END + AM_JOUR_NUM_1 as somme_jour_1
from (
select
DemId, StsId, dateDebReel, dateFinReel, duree_heures, dureeJours,ROUNDOMEGA(duree_heures / dureeJours) as dureeTheorique,(date_part('hour', dateDebReel) < 13) as startInTheMorning,
(date_part('hour', dateDebReel) >= 13) as startOnTheAfternoon,
CASE WHEN date_part('hour', dateDebReel) < 13 THEN
CASE WHEN dureeJours >=1 OR duree_heures > 4 THEN
CASE WHEN ROUNDOMEGA(duree_heures / dureeJours) <= 7.50 AND duree_heures != 8 THEN
'3h'
ELSE
'4h'
END
ELSE
REPLACE(REPLACE(CAST(duree_heures AS VARCHAR),'.','h'),'00','')
END
ELSE
''
END AS AM_JOUR_1,
CASE WHEN date_part('hour', dateDebReel) < 13 THEN
CASE WHEN dureeJours >=1 OR duree_heures > 4 THEN
CASE WHEN ROUNDOMEGA(duree_heures / dureeJours) <= 7.50 AND duree_heures != 8 THEN
3
ELSE
4
END
ELSE
duree_heures
END
ELSE
0
END AS AM_JOUR_NUM_1
from (
select *
from
(VALUES(170331,90612,CAST('2013-09-17 09:00:00' AS TIMESTAMP),CAST('2013-09-19 17:30:00' AS TIMESTAMP),21.00,3.00,7.00))
AS TMP (DEMID,STSID,datedebreel,datefinreel,duree_heures,dureejours)
) as t0
order by dateDebReel
) as t
) as t1
) as t2
) as t3
) as t4
) as t5
) as t6
) as t7
) as t8
) as t9
) as t10

HadanMarv · Général

work_mem est en commentaire dans le fichier de conf
shared_buffers est à 1024MB

La RAM total du serveur est de 4Go et le swap à 5,8Go

Avez-vous besoin de plus d'éléments ?

HadanMarv · Général

Merci pour ces éléments de réponse. Cela nous à permis d'avancer et de constater que le kill du processus postgres et réalisé par OOM (processus unix tuant les process trop gourmand en mémoire).
Suite à investigation il s'avère que c'est belle et bien l'exécution de la vue qui pose le problème.
une requête sur la vue semble prendre 25ms (selon pg_stat_statements).
Deux choses se produisent :
1. le serveur de base prend très chère la mémoire du processus postgres monte jusque 2,6Go de mémoire...
2. Pour une raison inconnu le processus ne redescend pas à son utilisation mémoire initiale (semblant ainsi être la cause du kill par le oom).

Je peux vous fournir la requête de la vue si vous le souhaitez.
En attendant d'autre explication je me suis lancé dans le recodage de la vue en Java.

HadanMarv

HadanMarv · Général

de plus voidi les logs du moteur lors de l'exécution du select sur la vue :

2013-11-29 14:58:54 CET LOG: processus d'écriture en tâche de fond (PID 5824) a ?t? arrêt? par le signal 9 : Killed
2013-11-29 14:58:54 CET LOG: arrêt des autres processus serveur actifs
2013-11-29 14:58:55 CET ATTENTION: arrêt de la connexion à cause de l'arrêt brutal d'un autre processus serveur
2013-11-29 14:58:55 CET DETAIL: Le postmaster a commandé à ce processus serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté anormalement
et qu'il existe probablement de la mémoire partagée corrompue.
2013-11-29 14:58:55 CET ASTUCE : Dans un moment, vous devriez être capable de vous reconnecter à la base de
données et de relancer votre commande.
2013-11-29 14:58:55 CET ATTENTION: arrêt de la connexion à cause de l'arrêt brutal d'un autre processus serveur
2013-11-29 14:58:55 CET DETAIL: Le postmaster a commandé à ce processus serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté anormalement
et qu'il existe probablement de la mÃ©moire partagÃ©e corrompue.
2013-11-29 14:58:55 CET ASTUCE : Dans un moment, vous devriez être capable de vous reconnecter à la base de
données et de relancer votre commande.
2013-11-29 14:58:55 CET ATTENTION: arrêt de la connexion à cause de l'arrêt brutal d'un autre processus serveur
2013-11-29 14:58:56 CET DETAIL: Le postmaster a commandé à ce processus serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté anormalement
et qu'il existe probablement de la mémoire partagée corrompue.
2013-11-29 14:58:56 CET ASTUCE : Dans un moment, vous devriez être capable de vous reconnecter à la base de
donnÃ©es et de relancer votre commande.
2013-11-29 14:58:57 CET LOG: tous les processus serveur se sont arr?t?s, r?initialisation
2013-11-29 14:58:57 CET LOG: le système de bases de données a été interrompu ; dernier lancement connu ? 2013-11-29 14:50:51 CET
2013-11-29 14:58:57 CET LOG: le système de bases de données n'a pas été arrêté proprement ; restauration
automatique en cours
2013-11-29 14:58:57 CET LOG: enregistrement de longueur nulle ? 14/2F18F3F8
2013-11-29 14:58:57 CET LOG: la ré-exécution n'est pas nécessaire
2013-11-29 14:58:57 CET LOG: le système de bases de données est prêt pour accepter les connexions
2013-11-29 14:58:57 CET LOG: lancement du processus autovacuum

Si cela peut donner des informations supplémentaires

HadanMarv · Général

Très bien merci pour ces informations.

Concernant le second sujet voici ce que j'obtiens à l'exécution de la vue (plantage du moteur postgres) :

ATTENTION: arrêt de la connexion à cause de l'arrêt brutal d'un autre processus serveur
DETAIL: Le postmaster a commandé à ce processus serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté anormalement
et qu'il existe probablement de la mémoire partagée corrompue.
HINT: Dans un moment, vous devriez être capable de vous reconnecter à la base de
données et de relancer votre commande.
********** Erreur **********

J'ai à ma disposition deux moteurs en 8.4 et cela plante sur l'un et fonctionne sur l'autre.

D'avance merci de vos lumières (vue sur demande parce qu'elle est énorme).

HadanMarv

HadanMarv · Général

Effectivement ce n'est probablement pas suffisant. Donc voici quelques éléments complémentaires.
85 lignes se rajoute dans la table pg_lock pendant l'exécution de ma requête. 84 sont en mode "AccessShareLock", locktype = relation et 1 en "ExclusiveLock" avec un locktype = "virtualxid".
Merci de me dire si vous avez besoin d'autres éléments.

D'autre part, j'ai installé pg_stat_statements, qui me donne des résultat que je ne sais interpréter pour le moment (beaucou plus complet en 9.1 que en 8.4 du reste).
en 8.4 j'ai que la requête avec un nombre d'appel, un temps d'exécution que je suppose * en fonciton du nombre d'appel et un nombre retourné je pense à moins que ce ne soit parcourus.

En 9.1 j'ai plein de colonne en plus comme indiqué ici : Documentation Postgres
mais je n'arrive pas à interpréter correctement les résultats.

De plus, nous avons constaté que dans certains cas l'arrêt brutal du moteur.
Je pense que du tuning est à faire mais j'avoue ne rien y connaitre.

D'avance merci de vos lumières.

HadanMarv

HadanMarv · Général

Bonjour,

Suite à un problème de plantage du moteur postgres avec le message 'Le système est en cours de restauration', j'ai pu constaté que certaines des applications qui accèdent à ma base, lance des select en exclusiveLock.
Je n'arrive pas à comprendre comment en select peut poser un verrou de ce type sur une table.
Dans un premier temps, pour palier au problème, j'ai créer un compte avec seulement les droits select sur la table en question. a ma grande surprise, l'exclusivelock persiste.
Le problème c'est que le traitement retourne 25000 lignes de cette même table et que cela donne un délais au traitement suivant. la requête exécuté dans pgadmin retourne un résultat en plus de 30 secondes. la table posséde 53 colonnes et utilise un espace de stockage d'un peu plus de 130M.
Au total cette table contient 500 000 enregistrements.
Le moteur postgres installé est le 8.4

D'avance merci de vos lumières.

HadanMarv

HadanMarv · Général

Et bien quoi dire mise à part merci beaucoup.
J'avoue honnêtement ne rien comprendre à la requête fourni, mais cela fonctionne parfaitement.
Pourriez-vous m'éclairer sur son fonctionnement (partition, lead, over) ?
D'avance merci

HadanMarv

HadanMarv · Général

Bonjour,

J'ai une table commande et une table commande_historique.
Lors de la création de ma commande, j'insère un enregistrement dans ma table commande_historique.
Je fait de même à chaque fois que je change l'état de ma commande.
J'obtiens ainsi :

Les calculs correspondent au temps écoulés entre deux dates ici toujours moins de 1 jour, mais çà peut parfois être plus.
Si quelqu'un à une solution j'suis preneur.
J'avoue que je me fais des noeuds au cerveau en essayant de résoudre ce cas.

D'avance merci de vos réponses.
HadanMarv

HadanMarv · Optimisation

Malheureusement pas d'amélioration significative, il continue à me retourner le même résultat...
Dois-je redémarrer la base à l'issue de l'analyse / Vaccum ?

HadanMarv · Optimisation

"              ->  Merge Right Join  (cost=59401.53..64132.78 rows=3 width=204) (actual time=1409.023..334186.511 rows=9139 loops=1)"

#1 Re : Général » Interprétation de requête » 08/11/2019 15:51:54

#2 Re : Général » Interprétation de requête » 07/11/2019 13:05:05

#3 Général » Interprétation de requête » 07/11/2019 12:23:55

#4 Re : Général » Script avec la définition complète des vues matérialisées » 06/11/2019 10:32:54

#5 Général » Script avec la définition complète des vues matérialisées » 05/11/2019 10:59:41

#6 Re : Général » Fonction de fenêtrage » 19/09/2018 16:40:11

#7 Re : Général » Fonction de fenêtrage » 19/09/2018 09:25:32

#8 Général » Fonction de fenêtrage » 18/09/2018 17:17:58

#9 Re : Général » [SQL] Question algo de requête » 16/02/2018 16:28:49

#10 Re : Général » [SQL] Question algo de requête » 16/02/2018 15:56:18

#11 Re : Général » [SQL] Question algo de requête » 15/02/2018 14:57:57

#12 Général » [SQL] Question algo de requête » 15/02/2018 10:47:21

#13 Re : Général » [PROBLEME DE DONNEES] La requête au résultat incompréhensible » 24/06/2016 16:38:13

#14 Général » [PROBLEME DE DONNEES] La requête au résultat incompréhensible » 24/06/2016 15:20:37

#15 Re : Général » Select et ExclusiveLock » 05/12/2013 11:30:57

#16 Re : Général » Select et ExclusiveLock » 03/12/2013 16:24:03

#17 Re : Général » Select et ExclusiveLock » 02/12/2013 15:17:20

#18 Re : Général » Select et ExclusiveLock » 29/11/2013 15:59:22

#19 Re : Général » Select et ExclusiveLock » 29/11/2013 15:51:37

#20 Re : Général » Select et ExclusiveLock » 27/11/2013 12:06:22

#21 Général » Select et ExclusiveLock » 26/11/2013 14:46:03

#22 Re : Général » Conception requête complexe » 17/02/2012 12:45:36

#23 Général » Conception requête complexe » 17/02/2012 12:10:02

#24 Re : Optimisation » Ordre des jointures et performances » 02/12/2011 18:10:54

#25 Re : Optimisation » Ordre des jointures et performances » 02/12/2011 14:47:40

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux