Forums PostgreSQL.fr

Une recherche dans les mailing-lists trouve une réponse de Thomas Munro sur un cas comparable:

https://www.postgresql.org/message-id/f … utlook.com

Lorsque la table dont il faut verrouiller les lignes est référencée une deuxième fois dans la requête pour jointure, il dit que les deux côtés de la jointure n'ont pas le même niveau de visibilité, ce qui produit des résultats incohérents par rapport aux attentes.

Est-ce qu'il y a une solution avec la forme actuelle de la requête? Il dit:

et effectivement avec la requête mentionnée en #1

UPDATE message SET is_dequeued = true
WHERE id IN (SELECT f2.id FROM message f2 WHERE f2.id_queue = 5 AND f2.is_dequeued = false ORDER BY f2.id ASC FOR UPDATE SKIP LOCKED  LIMIT 1)
returning *

on n'est pas dans la bonne situation parce que le WHERE id... s'applique à la table message au niveau supérieur alors que le FOR UPDATE s'applique à la table message aliasée en "f2" dans la sous-requête.

Pour éviter le problème, on peut j'imagine soit couper la requête en deux comme suggère Guillaume en #13, soit utiliser un niveau d'isolation supérieur, REPEATABLE READ ou SERIALIZED. Dans ces niveaux d'isolation, ce genre d'anomalie liés aux accès concurrents devrait être remonté en erreur à l'utilisateur avec annulation de la transaction, au lieu de passer en force en mode "advienne que pourra" (en READ COMMITTED).

Je n'ai jamais essayé pg_createcluster sur Mac OS (qui est un projet perso de Peter Eisentraut si je comprends bien) , mais si c'est pareil que celui utilisé par les packages Debian, cette ligne de commande a un problème:

pg_createcluster -p 5442 -l "$dbpath_parentdir/$instance_name" -u $username -e UTF-8 --locale=fr_BE -d $dbpath -- initdb -A -X $dbpath_parentdir/wals

Le souci est que derrière le -- il ne doit pas y avoir le mot initdb. Dans la doc il est dit "initdb options", mais c'est à interpréter comme "les options destinées à initdb", et non pas comme "le mot initdb suivi d'une liste d'options".

Ce qui n'empêche pas que la valeur du $dbpath n'est peut-être pas bonne par ailleurs, comme Sébastien le suggère.

Dans la fonction il faut se référer au champ insee par new.insee et non insee tout seul, de même pour les autres champs de la table.

Oui, le parallélisme intra-requête est apparu avec la version 9.6.
https://www.postgresql.org/docs/9.6/parallel-query.html

Sinon la version 11 date de 2018 et sera obsolète en 2023, il serait préférable de migrer sur la dernière version qui est la 14.

Non la simple présence du WITH ne contraint pas les lignes touchées par l'UPDATE. Si la sous-requête du WITH était utilisée pour corréler dans l'UPDATE alors OK, mais là ce n'est pas le cas (d'ailleurs dans aucune des requêtes du message #4 me semble-t-il).

Regardez dans votre client SQL combien de lignes sont affectées. Normalement cette info est affichée quelque part. Avec psql, ça affiche UPDATE N où N est le nombre de lignes, et de plus ça arrive dans la variable ROW_COUNT.

Au pire, ajoutez RETURNING * à la fin de la requête UPDATE et vous verrez aussi que ça affecte toute la table, par opposition à seulement les lignes correspondant au course_id en paramètre.

Ca enlève celle qui répond et ça garde celle qui ne répond pas (enfin pas tout de suite) donc effectivement ça ne peut pas marcher.

Encore une fois, si le serveur est protégé de l'extérieur,  listen_addresses=* serait OK.

Concernant systemd, je viens d'essayer et comme prévu c'est moins simple. Effectivement  faire le changement dans postgresql.service ne semble pas marcher pour moi non plus.

Sur Debian/Ubuntu l'unité systemd postgresql est en fait un lanceur et pas le service lui-même.
La vraie unité est générée pour chaque instance suivant le modèle dans /lib/systemd/postgresql@.service

Si dans la section [Unit] de ce fichier j'ajoute

After=network-online.target

ça semble marcher pour moi avec une interface DHCP.  Ceci sur un portable Ubuntu 18.04 qui a les mêmes packages Postgres que Debian à peu de choses près, donc que je pense que ce serait pareil sur Debian. A confirmer.

Le plus probable est que l'adresse réseau ne soit pas encore montée quand postgres démarre dans le contexte d'un boot, et donc il n'écoute pas dessus. Il faudrait regarder le log serveur (/var/log/postgresql/postgresql-13-main.log juste après un boot, si c'est ça il y aura des messages d'erreur à ce sujet.

Vous avez sans doute fait une erreur de manip. C'est facile à tester, créer une table vide avec juste les 2 colonnes requêtées

CREATE TABLE film(film_id int, length int);

Lancer la  requête copié-collée. Effectivement elle sort l'erreur mentionnée.

select case
        when length between 0 and 59 then 'Less then 1 hour'
        when length between 60 and 119 then '1 to 2 hours'
        when length between 120 and 179 then '2 to 3 hours'
        else 'over 3 hours'
        end as FilmLengthCategory
    , count (film_id) as CountofFilms
from film
group by case
        when length between 0 and 59 then 'Less then 1 hour'
        when length between 60 and 119 then '1 to 2 hours'
        when length between 120 and 179 then '2 to 3 hours'
        else 'Over 3 hours'
        end
order by CountOfFilms desc

ERREUR:  la colonne « film.length » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat
LIGNE 2 :         when length between 0 and 59 then 'Less then 1 hour'

Changer le 'over' en 'Over', relancer, résultat: plus d'erreur:

select case
        when length between 0 and 59 then 'Less then 1 hour'
        when length between 60 and 119 then '1 to 2 hours'
        when length between 120 and 179 then '2 to 3 hours'
        else 'Over 3 hours'
        end as FilmLengthCategory
    , count (film_id) as CountofFilms
from film
group by case
        when length between 0 and 59 then 'Less then 1 hour'
        when length between 60 and 119 then '1 to 2 hours'
        when length between 120 and 179 then '2 to 3 hours'
        else 'Over 3 hours'
        end
order by CountOfFilms desc

filmlengthcategory | countoffilms 
--------------------+--------------
(0 ligne)

PS: on peut aussi écrire GROUP BY 1 avec PostgreSQL pour grouper par la première colonne sans répéter toute l'expression mais c'est non-standard.

Ca n'a pas forcément à voir avec "bloque" mais dans le code 4) il y a une erreur classique de débutant:

WHEN "SampleType" = 'Quarter core' AND "DupSampleID"= NULL

Cette condition sera toujours fausse. Il faudrait utiliser "DupSampleID" IS NULL au lieu de "DupSampleID"=NULL.

L'erreur est normale parce que l'expression CASE est  un mix incorrect des deux formes possibles de CASE

UPDATE "DHCollar_Planned"
        SET "Xto"=
            CASE New."HoleID"
            WHEN "Azimuth" >=0 AND "Azimuth"<180
                THEN New."Xplanned"+New."FinalDepth"
            ELSE
                New."Xplanned"-New."FinalDepth"
            END;

Si on veut exprimer que si "Azimuth" >=0 AND "Azimuth"<180 alors "Xto" doit prendre la valeur New."Xplanned"+New."FinalDepth" et sinon la valeur New."Xplanned"-New."FinalDepth", alors c'est la forme CASE WHEN condition1 THEN valeur1 WHEN condition2 THEN valeur2 etc... et le New."HoleID" n'a rien à faire là.

L'autre forme possible est CASE expression WHEN valeur1 THEN autre valeur ELSE ... END qui teste les égalités entre l'expression et les valeurs successives.

Un autre problème est que dans un trigger on ne met pas à jour les champs avec un nouvel UPDATE de la table qui est train d'être modifiée mais avec une assignation directe des champs: NEW.colonne := valeur;

Apparemment les champs à mettre à jour sont NEW."Xto" et NEW."Yto", mais la présence de New."HoleID" et New."YHoleID" dans ce code n'a pas de raison apparente.

Si vous avez besoin de plus d'aide sur ce code, essayez de décrire ce qu'il doit faire.

En ce qui concerne "casser le lien d'héritage".
Il faut savoir qu'avec l'héritage, sortir une table de la hiérarchie et la remettre n'est pas une opération neutre.
Une fois que la table fille est remise dans la hiérarchie, les modifications de colonnes existantes du parent ne sont plus propagées dans la table fille comme avant.
Plusieurs utilisateurs comme moi ont signalé ça comme un bug tant c'est surprenant, mais il s'avère que ce n'est pas un bug, c'est conçu comme ça.

Exemple:

DROP TABLE  IF EXISTS parent_tbl CASCADE; 

CREATE TABLE parent_tbl(f1 int, f2 int, f3 int); 

CREATE TABLE child1_tbl() INHERITS(parent_tbl); 
CREATE TABLE child2_tbl() INHERITS(parent_tbl); 
CREATE TABLE child3_tbl() INHERITS(parent_tbl); 

ALTER TABLE child2_tbl NO INHERIT parent_tbl; 
ALTER TABLE child2_tbl INHERIT parent_tbl; 

ALTER TABLE parent_tbl DROP COLUMN f2; 

\d child*_tbl 

            Table "public.child1_tbl" 
Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+--------- 
f1     | integer |           |          | 
f3     | integer |           |          | 
Inherits: parent_tbl 

            Table "public.child2_tbl" 
Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+--------- 
f1     | integer |           |          | 
f2     | integer |           |          | 
f3     | integer |           |          | 
Inherits: parent_tbl 

            Table "public.child3_tbl" 
Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+--------- 
f1     | integer |           |          | 
f3     | integer |           |          | 
Inherits: parent_tbl 

Il est aussi possible que le mot de passe soit hashé en md5 alors que le serveur requiert scram-sha-256 via pg_hba.conf.
Il me semble que c'est le message d'erreur générique de mot de passe incorrect qui sort dans ce cas-là.

Pour faire la transition md5 -> scram-sha-256, il faut vérifier que password_encryption est bien à "scram-sha-256" et regénérer le mot de passe avec la commande \password de psql, pour chaque compte ayant un "vieux" mot de passe.