Vous n'êtes pas identifié(e).
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).
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
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
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
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
Impeccable, çà fonctionne.
Merci beaucoup
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 TEST9avec 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 2Maintenant 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
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
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).
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 ?
C'est si complexe que çà ? ou j'ai super mal expliqué mon problème ?
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 factureensuite je fais :
select max(id) from commande where date_commande <= date_facture and client = clientJ'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
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.
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
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
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 ?
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
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
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
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
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
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
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 :
Table Commande
ID |Etat |User |Prix
1 |Validé |Admin |50
Table Commande_historique
ID |ID_COMMANDE | User |Etat |Date
1 |1 |Client |Créé |2012-01-01 19:50:25
2 |1 |Admin |Accepté |2012-01-02 07:45:17
3 |1 |Client |Modifié |2012-01-02 10:37:01
4 |1 |Admin |Accepté |2012-01-02 14:28:13
5 |1 |Admin |Validé |2012-01-03 08:01:52
Avec ces informations je voudrais pouvoir en une requête produire le résultat suivant :
ID_COMMANDE |Etat |Durée
1 |Créé |0jour 11 heures 54 minutes 52 secondes
1 |Accepté |0jour 02 heures 51 minutes 44 secondes
1 |Modifié |0jour 03 heures 51 minutes 12 secondes
1 |Accepté |0jour 17 heures 33 minutes 39 secondes
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
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 ?
" -> Merge Right Join (cost=59401.53..64132.78 rows=3 width=204) (actual time=1409.023..334186.511 rows=9139 loops=1)"