Forums PostgreSQL.fr

J'imagine que PostgreSQL est installé sur un poste Windows et que pgAdmin est lancé sur le même PC (pgAdmin et PostgreSQL sont deux logiciels distincts: pgAdmin est un client et PostgreSQL un serveur).

Par rapport à la copie d'écran dans le champ en haut Hostname/address if faut mettre localhost comme valeur.

Il y a des services web ouverts au public qui permettent de saisir du SQL dans une page web pour s'entraîner à faire des requêtes, mais généralement pas de s'authentifier via un client SQL. Par exemple sqlfiddle.com est assez connu mais il y en a d'autres.

Concernant les bases opendata, il y en a plein mais sous forme téléchargeable de fichiers SQL ou de fichiers CSV ou JSON, il faut les
charger dans son propre serveur.

Si vous avez installé PostgreSQL sur votre PC vous pouvez vous authentifier sur votre propre serveur et faire ce que vous voulez dessus.

Dans la requête du message #1 il y  a un S1.troop_id impliqué dans le GROUP BY qui potentiellement change le nombre de lignes du résultat. Et autant pour les autres colonnes on comprend intuitivement à quoi elles correspondent, autant pour celle-là il faudrait des explications pour savoir quoi faire avec et comprendre en quoi elle influe sur le résultat.

Pour l'absence possible de 2eme date d'observation, en effet c'est un LEFT JOIN entre S1 et S2 donc S2 va avoir des colonnes correspondantes à NULL dans ces cas. C'est gérable avec le fenêtrage, pour ces cas la colonne first_date va être égale à last_date pour le même individu. Les colonnes de la 2nde observation inexistante pourraient être mises à NULL dans un étage supérieur de la requête avec des expressions du style CASE WHEN date2=date1 THEN null else date2 END.

Avec Debian chaque instance postgres a son répertoire de config , par exemple /etc/postgresql/14/main si c'est Postgres version 14 et que le nom du cluster est "main" (le nom par défaut).

Dans le doute, utiliser la commande pg_lsclusters pour avoir la liste des instances.

Dans ce répertoire il y a un fichier start.conf avec ce contenu:

# Automatic startup configuration
#   auto: automatically start the cluster
#   manual: manual startup with pg_ctlcluster/postgresql@.service only
#   disabled: refuse to start cluster
# See pg_createcluster(1) for details. When running from systemd,
# invoke 'systemctl daemon-reload' after editing this file.

auto

Remplacer auto par manual pour ne pas démarrer automatiquement.

Source: https://manpages.debian.org/buster/post … .1.en.html

Pour avoir la liste des colonnes changées quelque soit la structure de la table, on peut utiliser une requête de ce style, qui fait l'intersection de OLD et NEW pour ne garder que ce qui change:

    select array_agg(k2)
     from (select * from jsonb_each_text(row_to_json(OLD)::jsonb)) as r1(k1,v1)
       join (select * from jsonb_each_text(row_to_json(NEW)::jsonb)) as r2(k2,v2)
       on k1=k2
     where v1 is distinct from v2;

Il y a une version avec hstore aussi, où on peut faire encore plus simplement

hstore(NEW.*) - hstore(OLD.*)

Mais hstore étant rendu obsolète par jsonb, ce n'est pas forcément une bonne idée de partir là dessus aujourd'hui.

La version "toutes options" du trigger d'audit avec hstore est maintenue ici:
https://github.com/2ndQuadrant/audit-tr … /audit.sql

La redistribution de la clef primaire échoue via l'UPDATE du fait que Postgres ne teste pas l'unicité à la fin de l'instruction, comme indiqué ici dans la doc:

https://doc.postgresql.fr/14/sql-set-constraints.html

Pour que l'UPDATE passe, il faudrait que la contrainte soit DEFERRABLE INITIALLY DEFERRED ou DEFERRABLE tout court et que la transaction la passe en DEFERRED.

L'autre solution est de ne pas faire un UPDATE mais de réinsérer les lignes avec DELETE+INSERT:

with d as (delete from table returning *)
 insert into table select row_number() over (order by Nom), Nom from d;

Je suggère row_number() au lieu de rank() parce que rank() créé des doublons en cas d'égalité

En vrac, il y a plusieurs soucis visibles dans le code montré

- BEGIN MAJ

Pourquoi MAJ? La syntaxe valide c'est BEGIN tout seul.

- FETCH ALL FROM cLecture;

La syntaxe est FETCH [ direction { FROM | IN } ] curseur INTO cible;

ALL n'est pas possible. La cible devrait probablement être rt la variable déclarée mais pas utilisée. S'il n'y a aucune cible à avoir, un MOVE cLecture ferait mieux l'affaire.

Attention au fait que la syntaxe du FETCH en plpgsql diffère du FETCH du SQL. C'est piégeux mais c'est une règle générale. Le plpgsql a sa propre syntaxe qui ressemble superficiellement au SQL mais qui diffère à cause des variables et du côté procédural.

- COMMIT à supprimer

- la requête du curseur n'a pas d'ORDER BY donc il faut voir que les valeurs successives de NB vont arriver dans les lignes dans un ordre indéterminé.

En principe on ne change pas des paramètres sans avoir au moins des hypothèses sur la cause des lenteurs, sauf si les paramètres basiques de tuning comme shared_buffers ou work_mem ne sont cohérents avec les capacités du serveur.

Très souvent on installe l'extension pg_stat_statements pour collecter les requêtes normalisées avec leurs temps d'exécution mix/max/moyen, qui  donnent des éléments concrets d'analyse.

Une autre méthode très utilisée est de mettre log_min_duration_statement au seuil de durée d'exécution que vous souhaiteriez que vos requêtes ne dépassent pas. Toutes les requêtes plus longues sortiront dans le log. Ensuite on peut analyser le log avec pgBadger.

Il est aussi recommandé d'activer log_lock_waits, qui permettra de savoir si des requêtes sont lentes parce qu'elles attendent  des verrous. Les logs produits sont aussi visibles de manière assez claire dans les rapports pgBadger. Pendant qu'on y est on ajoute souvent log_checkpoints et les logs d'autovacuum, ainsi qu'éventuellement log_temp_files.

Il y a aussi des outils de monitoring qui capturent à fréquence régulière la vue pg_stat_activity (entre autres) pour voir combien de connexions sont occupées à faire quoi. Généralement on regarde ces résultats en même temps que les métriques système d'activité CPU et disque.

Le fait que French_France.28605 ne soit pas proposé dans les listes préremplies de l'installeur ou de pgadmin est une limitation de ces programmes. Idéalement on devrait pouvoir ajouter une valeur non préremplie. Si on ne peut pas le faire, après que Postgres soit installé il faut contourner ça en créant la base avec CREATE DATABASE en SQL.

Dans psql ou dans l'outil de requête de pgadmin, on peut toujours exécuter un ordre SQL de ce style:

CREATE DATABASE nomdelabase
ENCODING = 'LATIN9'
TEMPLATE template0
LC_COLLATE = 'French_France.28605'
LC_CTYPE = 'French_France.28605';

Effectivement, il semble qu'on ne puisse pas dire à pg_dump de ne pas lire les séquences liées quand on extrait le contenu d'une table, mais on peut utiliser plus simplement COPY pour exporter le contenu de la table (ce que fait d'ailleurs pg_dump, avec plein d'autres choses qui ne vous intéressent pas dans le cas présent), sans avoir aucun droit sur les séquences liées.

psql [options] -c 'COPY nomtable TO STDOUT' > export-table.sql

Si la table est déjà créée dans la base cible, le contenu peut se recharger dans cette base avec la commande en sens inverse:

psql [options] -c '\copy nomtable FROM export-table.sql'

Si la structure de la table n'existe pas déjà dans la base cible et qui faut l'extraire au préalable de la base source, c'est faisable avec

pg_dump [options] -s -t nomtable  > structure-table.sql

qui est utilisable avec un compte en lecture seule sans droit d'accès aux séquences liées à la table.

https://www.pgadmin.org/docs/pgadmin4/l … _diff.html

Dans le 3eme champ de .pgpass, il faut mettre replication au lieu d'un nom de base de données.

Si ça échoue toujours après ça, consulter le fichier de log du serveur pour avoir des détails sur l'erreur.