Forums PostgreSQL.fr

HadanMarv

Membre

Select et ExclusiveLock

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

gleu

Administrateur

Re : Select et ExclusiveLock

OK, un ExclusiveLock, mais un ExclusiveLock sur quoi ? sur la table elle-même ou sur la transaction (virtuelle ou réelle ?) ?

Plus simplement, que voyez vous dans pg_locks pour votre processus ?

---

Guillaume.

HadanMarv

Membre

Re : Select et ExclusiveLock

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

gleu

Administrateur

Re : Select et ExclusiveLock

Donc c'est bien ce que je disais, vous n'avez aucun Exclusive Lock sur un objet. Vous l'avez sur l'identifiant de transaction virtuelle, ce qui n'a aucune conséquence particulière dans votre cas. Que la requête mette 30 secondes à s'exécuter dans pgAdmin vient peut-être du fait qu'elle ramène beaucoup de lignes et que le composant de grille de pgAdmin est assez lent. Rien à voir à priori avec la durée d'exécution de la requête. Maintenant, si vous avez 85 lignes pour un seul SELECT, cela sous-entend que ce n'est pas un SELECT simple. Ça doit être une requête avec beaucoup de jointures. Du coup, il est possible que les 30 secondes soient passées sur l'exécution de la requête, vu qu'elle est complexe. Tout ceci n'est évidemment que des conjectures, étant donné que vous ne donnez pratiquement aucune information

---

Guillaume.

HadanMarv

Membre

Re : Select et ExclusiveLock

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

Membre

Re : Select et ExclusiveLock

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

gleu

Administrateur

Re : Select et ExclusiveLock

Le serveur PostgreSQL a reçu un signal 9, ce qui revient à tuer le processus directement au niveau du noyau. Deux possibilités : soit quelqu'un (humain ou script) exécute un "kill -9" sur le serveur (ce qui serait extrêmement stupide), soit c'est le système d'exploitation qui a lancé le "kill -9" à cause d'une trop grosse consommation mémoire. Pour vérifier ce deuxième cas, il faut regarder dans les logs ou avec dmesg. Voici deux exemples de commandes pour trouver ça :

dmesg | egrep -i 'killed process'

et

egrep -i 'killed process' /var/log/messages

---

Guillaume.

HadanMarv

Membre

Re : Select et ExclusiveLock

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

gleu

Administrateur

Re : Select et ExclusiveLock

La requête n'apportera pas grand chose. Quel est la configuration de PostgreSQL et la configuration mémoire du serveur (physique ou virtuelle) ?

---

Guillaume.

HadanMarv

Membre

Re : Select et ExclusiveLock

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 ?

gleu

Administrateur

Re : Select et ExclusiveLock

Je ne vois pas très bien dans ces conditions comment PostgreSQL pourrait prendre autant de mémoire (les 2.6 Go dont vous parlez). Même avec une requête monstrueuse, pleine de tri, hachage et autres, je ne vois pas comment vous pourriez dépasser les 1.2 Go de RAM utilisés. Il serait intéressant de désactiver l'OOM killer, histoire de voir comment se comporte PostgreSQL dans ce cas. Il va manquer de mémoire, il va donc cracher dans les traces le détail de son occupation mémoire. Ce serait intéressant de voir ça.

---

Guillaume.

HadanMarv

Membre

Re : Select et ExclusiveLock

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

gleu

Administrateur

Re : Select et ExclusiveLock

Si vous voulez aller au fond du problème, commencez par désactiver l'OOM killer, et relancez votre requête, pour voir l'utilisation mémoire au moment du out of memory.

---

Guillaume.