Vous n'êtes pas identifié(e).
Bonjour à tous.
Je suis utilisateur en entreprise de PostGreSql et je rencontre un problème bloquant que je n'arrive pas à résoudre.
J'ai un traitement Java qui créé 10.000 threads en parallèle et chacun de ces threads effectue un INSERT similaire dans une même table (je fais simple).
J'utilise "PostgreSQL 9.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit".
Et j'utilise le "Etat du serveur "de PgAdmin 1.20.0 (je n'ai pas d'autres outils à ma disposition).
A un moment, dans la zone "Verrous" j'ai les lignes suivantes, et c'est bloqué indéfiniment :
(sur la 1ère ligne, il y a un "ExclusiveLock" qui semble tout bloquer)
PID XID TX Mode
5716 bdd_dev 20/3339 20/3339 ExclusiveLock Oui 2017-04-04 16:45:40+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 postgres pg_class_relname_nsp_index bdd_dev 20/3339 AccessShareLock Oui 2017-04-04 16:45:40+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 postgres pg_class_oid_index bdd_dev 20/3339 AccessShareLock Oui 2017-04-04 16:45:40+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 postgres pg_class bdd_dev 20/3339 AccessShareLock Oui 2017-04-04 16:45:40+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 postgres pg_locks bdd_dev 20/3339 AccessShareLock Oui 2017-04-04 16:45:40+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 pg_database_datname_index bdd_dev 20/3339 AccessShareLock Oui 2017-04-04 16:45:40+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 pg_database_oid_index bdd_dev 20/3339 AccessShareLock Oui 2017-04-04 16:45:40+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 postgres pg_class_tblspc_relfilenode_index bdd_dev 20/3339 AccessShareLock Oui 2017-04-04 16:45:40+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 pg_database bdd_dev 20/3339 AccessShareLock Oui 2017-04-04 16:45:40+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text, J'ai un peu tout essayé (mais je n'ai pas une compréhension fine de la BDD) et aussi fait changer pas mal de paramètres comme "lock_timeout". Rien n'y fait.
Y'a-t-il un paramètre responsable de ça ? Un élément quelque part qui pourrait aider à trouver l'origine du problème ? Bref, une piste...
Merci à tous pour votre aide.
Hors ligne
Ces requêtes n'ont pas l'air d'avoir un rapport avec des INSERTs. Il est normal d'avoir plein de verrous dans une base active, tant qu'ils ne sont pas pris indéfiniment.
Il faudrait commencer par
select * from pg_stat_activity;pour avoir une vue d'ensemble des sessions en cours.
A part ça, si les 10000 INSERTs sont réellement simultanés, c'est excessif. Très schématiquement, diviser ça par le nombre de threads effectifs des CPUs du serveur et ça donne une idée du ratio de surcharge.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Bien d'accord avec Daniel. Sans compter que toutes les lignes affichées ici concernent un seul processus, le 5716, qui ne peut donc pas se bloquer.
Guillaume.
Hors ligne
Merci de votre aide.
En fait, j'ai fait une FIFO pour éviter les 10.000 requêtes simultanées et maîtriser l'envoi effectif des requêtes. J'ai mis la FIFO à 32 (qui me paraissait correct pour un max_connexions à 100).
Voilà mon "select * from pg_stat_activity;" :
10091 bdd_ru 17/1121 17/1121 ExclusiveLock Oui <insufficient privilege>
5716 bdd_dev 20/25118 20/25118 ExclusiveLock Oui 2017-04-05 07:53:53+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
10103 bdd_ru 29/937 29/937 ExclusiveLock Oui <insufficient privilege>
10103 bdd_ru 32982 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
10103 bdd_ru 33103 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
10103 bdd_ru 33103 bdd_ru 29/937 RowExclusiveLock Oui <insufficient privilege>
10103 bdd_ru 33101 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
10103 bdd_ru 33101 bdd_ru 29/937 RowExclusiveLock Oui <insufficient privilege>
10103 bdd_ru 33099 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
10103 bdd_ru 33097 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
10103 bdd_ru 33095 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
10103 bdd_ru 32971 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
5716 postgres pg_class_tblspc_relfilenode_index bdd_dev 20/25118 AccessShareLock Oui 2017-04-05 07:53:53+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 postgres pg_class_relname_nsp_index bdd_dev 20/25118 AccessShareLock Oui 2017-04-05 07:53:53+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 postgres pg_class_oid_index bdd_dev 20/25118 AccessShareLock Oui 2017-04-05 07:53:53+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 postgres pg_class bdd_dev 20/25118 AccessShareLock Oui 2017-04-05 07:53:53+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 postgres pg_locks bdd_dev 20/25118 AccessShareLock Oui 2017-04-05 07:53:53+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
10091 bdd_ru 33034 bdd_ru 17/1121 AccessShareLock Oui <insufficient privilege>
10091 bdd_ru 33027 bdd_ru 17/1121 RowExclusiveLock Oui <insufficient privilege>
10091 bdd_ru 33109 bdd_ru 17/1121 AccessShareLock Oui <insufficient privilege>
10091 bdd_ru 33107 bdd_ru 17/1121 AccessShareLock Oui <insufficient privilege>
10091 bdd_ru 33105 bdd_ru 17/1121 AccessShareLock Oui <insufficient privilege>
10091 bdd_ru 32982 bdd_ru 17/1121 AccessShareLock Oui <insufficient privilege>
10091 bdd_ru 32982 bdd_ru 17/1121 RowShareLock Oui <insufficient privilege>
5716 pg_database_datname_index bdd_dev 20/25118 AccessShareLock Oui 2017-04-05 07:53:53+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
5716 pg_database_oid_index bdd_dev 20/25118 AccessShareLock Oui 2017-04-05 07:53:53+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
10103 bdd_ru 29/937 ExclusiveLock Oui <insufficient privilege>
10103 bdd_ru 32977 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
10103 bdd_ru 32977 bdd_ru 29/937 RowExclusiveLock Oui <insufficient privilege>
5716 pg_database bdd_dev 20/25118 AccessShareLock Oui 2017-04-05 07:53:53+02 SELECT pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user, pgl.virtualxid::text,
10103 bdd_ru 33109 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
10091 bdd_ru 17/1121 ExclusiveLock Oui <insufficient privilege>
10103 bdd_ru 33107 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege>
10103 bdd_ru 33105 bdd_ru 29/937 AccessShareLock Oui <insufficient privilege> Hors ligne
Bon, plusieurs choses :
1. Le résultat affiché là n'est pas le résultat de la requête demandée.
2. Il serait mieux de l'exécuter en tant que superutilisateur pour voir toutes les requêtes et non pas des "<insufficient privilege>" qui apparaissent parce que vous avez utilisé un rôle qui n'a pas les droits suffisants pour ça.
3. Pour les requêtes affichées, on ne voit aucun INSERT.
4. Il serait bon à la base d'indiquer le problème constaté (lenteur ? INSERT qui ne se termine pas ?) qu'une raison (ExclusiveLock infini) qui a des chances d'être fausse. Quel est le problème au départ ?
Guillaume.
Hors ligne
Je recommence en étant plus précis et plus factuel.
J'ai une FIFO Java qui envoie 10.000 INSERT dans une table en s'assurant qu'il n'y ait jamais plus de 32 INSERT simultanés.
Une fois le traitement terminé, tout paraît s'être passé correctement.
J'attends 10 bonnes minutes :
select * from pg_stat_activity where usename='bdd_dev';
(les anciennes lignes "<insufficient privilege>" étaient des lignes sur d'autres schémas auxquels je n'ai pas les droits)
25112;"bdd_dev";12061;25108;"bdd_dev";"";"221.11.150.146";"";53601;"2017-04-05 08:08:17.504797+02";"";"2017-04-05 08:08:25.746717+02";"2017-04-05 08:08:25.747112+02";f;"idle";;;"ROLLBACK"
25112;"bdd_dev";14804;25108;"bdd_dev";"";"221.11.150.146";"";54510;"2017-04-05 13:43:17.848359+02";"";"2017-04-05 13:43:17.90626+02";"2017-04-05 13:43:17.906321+02";f;"idle";;;"SET extra_float_digits = 3"
25112;"bdd_dev";14805;25108;"bdd_dev";"";"221.11.150.146";"";54511;"2017-04-05 13:43:17.910749+02";"";"2017-04-05 13:43:17.91703+02";"2017-04-05 13:43:17.917057+02";f;"idle";;;"SET extra_float_digits = 3"
25112;"bdd_dev";14803;25108;"bdd_dev";"";"221.11.150.146";"";54509;"2017-04-05 13:43:17.747922+02";"";"2017-04-05 13:43:17.787366+02";"2017-04-05 13:43:17.787411+02";f;"idle";;;"SET extra_float_digits = 3"
25112;"bdd_dev";14806;25108;"bdd_dev";"";"221.11.150.146";"";54512;"2017-04-05 13:43:17.919858+02";"";"2017-04-05 13:43:17.923054+02";"2017-04-05 13:43:17.923076+02";f;"idle";;;"SET extra_float_digits = 3"
25112;"bdd_dev";14807;25108;"bdd_dev";"";"221.11.150.146";"";54513;"2017-04-05 13:43:17.925574+02";"";"2017-04-05 13:43:17.929223+02";"2017-04-05 13:43:17.929263+02";f;"idle";;;"SET extra_float_digits = 3"
25112;"bdd_dev";14808;25108;"bdd_dev";"";"221.11.150.146";"";54514;"2017-04-05 13:43:17.931912+02";"";"2017-04-05 13:43:17.935031+02";"2017-04-05 13:43:17.935055+02";f;"idle";;;"SET extra_float_digits = 3"
25112;"bdd_dev";14809;25108;"bdd_dev";"";"221.11.150.146";"";54515;"2017-04-05 13:43:17.937725+02";"";"2017-04-05 13:43:17.940813+02";"2017-04-05 13:43:17.940836+02";f;"idle";;;"SET extra_float_digits = 3"
12141;"postgres";12410;25108;"bdd_dev";"pgAdmin III - Navigateur";"mon-adresse-ip";"";59790;"2017-04-05 08:50:32.965062+02";"";"2017-04-05 08:50:34.532303+02";"2017-04-05 08:50:34.533729+02";f;"idle";;;"SELECT setting FROM pg_settings WHERE name IN ('autovacuum', 'track_counts')"
25112;"bdd_dev";12412;25108;"bdd_dev";"pgAdmin III - Navigateur";"mon-adresse-ip";"";59791;"2017-04-05 08:50:34.591344+02";"";"2017-04-05 08:50:42.978575+02";"2017-04-05 08:50:42.979811+02";f;"idle";;;"SELECT t.oid, t.xmin, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable, nspname, des.description, l.lanname, p.prosrc,
COALESCE(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE'), substring(pg_get_trigger (...)"
25112;"bdd_dev";14623;25108;"bdd_dev";"pgAdmin III - ??diteur de requ??tes";"mon-adresse-ip";"";51844;"2017-04-05 13:17:37.124107+02";"2017-04-05 13:44:20.775775+02";"2017-04-05 13:44:20.775775+02";"2017-04-05 13:44:20.775779+02";f;"active";;2600020;"select * from pg_stat_activity where usename='bdd_dev';"SELECT * FROM pg_locks;
"relation";25112;11187;;;"";;;;;"50/520";14623;"AccessShareLock";t;t
"virtualxid";;;;;"50/520";;;;;"50/520";14623;"ExclusiveLock";t;tHors ligne
Il reste donc à savoir quel est le problème. Moi, ces deux sorties (pg_stat_activity et pg_locks) sont normales.
Guillaume.
Hors ligne
Déjà, je pensais que j'ai un lock là-dessus.
Donc je n'ai pas dû aller assez loin dans mon exemple pour le capturer.
Je vais donc revoir ma copie et je reviendrai éventuellement avec un exemple plus significatif.
En tous cas, merci, ces explications et remarques, ça me permet d'avancer (même si ça ne se voit pas forcément :mrgreen ).
Hors ligne
Bonjour à tous.
Je me permets de revenir à nouveau demander conseil sur mon problème.
De mon "select * from pg_stat_activity", on voit qu'il y a des requêtes "coincées" depuis hier (les "2017-04-19 16:13").
Y'a-t-il une configuration côté postgresql applicable pour libérer automatiquement ces locks au bout de X secondes/minutes ?
Merci.
Voici mon "select * from pg_stat_activity" :
31291 sonar_application 2440 22077 sonar_application <insufficient privilege>
31291 sonar_application 2503 22077 sonar_application <insufficient privilege>
25113 application_ru 28346 25110 application_ru @IP 54817 2017-04-19 15:36:23.206116+02 2017-04-19 15:36:31.056691+02 2017-04-19 15:36:31.057021+02 f idle ROLLBACK
25113 application_ru 28796 25110 application_ru @IP 56626 2017-04-19 16:12:12.300004+02 2017-04-19 16:13:30.466847+02 2017-04-19 16:13:30.466867+02 f idle SET application_name = ''
25113 application_ru 28673 25110 application_ru @IP 56435 2017-04-19 16:06:23.358773+02 2017-04-19 16:36:27.968358+02 2017-04-19 16:36:27.968957+02 f idle COMMIT
25113 application_ru 28784 25110 application_ru @IP 56614 2017-04-19 16:12:11.437838+02 2017-04-19 16:13:23.351358+02 2017-04-19 16:13:23.351376+02 f idle SET application_name = ''
25113 application_ru 28675 25110 application_ru @IP 56437 2017-04-19 16:06:23.368882+02 2017-04-19 16:31:25.234878+02 2017-04-19 16:31:25.235567+02 f idle COMMIT
25113 application_ru 28785 25110 application_ru @IP 56615 2017-04-19 16:12:11.508069+02 2017-04-19 16:13:25.402835+02 2017-04-19 16:13:25.40286+02 f idle SET application_name = ''
25113 application_ru 28708 25110 application_ru @IP 56539 2017-04-19 16:12:09.115635+02 2017-04-19 16:13:15.209504+02 2017-04-19 16:13:15.209522+02 f idle SET application_name = ''
25113 application_ru 28781 25110 application_ru @IP 56611 2017-04-19 16:12:10.837079+02 2017-04-19 16:13:33.482875+02 2017-04-19 16:13:33.554897+02 2017-04-19 16:13:33.554905+02 f idle in transaction SET application_name = ''
25113 application_ru 28786 25110 application_ru @IP 56616 2017-04-19 16:12:11.699986+02 2017-04-19 16:36:27.170896+02 2017-04-19 16:36:27.171562+02 f idle COMMIT
25113 application_ru 28782 25110 application_ru @IP 56612 2017-04-19 16:12:10.841735+02 2017-04-19 16:36:27.800646+02 2017-04-19 16:36:27.800763+02 f idle COMMIT
25113 application_ru 28797 25110 application_ru @IP 56627 2017-04-19 16:12:12.304627+02 2017-04-19 16:36:28.02497+02 2017-04-19 16:36:28.025547+02 f idle COMMIT
25113 application_ru 28798 25110 application_ru @IP 56628 2017-04-19 16:12:12.356057+02 2017-04-19 16:31:23.951092+02 2017-04-19 16:31:23.951717+02 f idle COMMIT
25113 application_ru 28800 25110 application_ru @IP 56630 2017-04-19 16:12:12.587392+02 2017-04-19 16:13:37.555668+02 2017-04-19 16:13:37.555695+02 f idle SET application_name = ''
25113 application_ru 28801 25110 application_ru @IP 56643 2017-04-19 16:12:12.70953+02 2017-04-19 16:36:27.985085+02 2017-04-19 16:36:27.985648+02 f idle COMMIT
25113 application_ru 28716 25110 application_ru @IP 56547 2017-04-19 16:12:09.216328+02 2017-04-20 03:01:05.928534+02 2017-04-20 03:01:05.928761+02 f idle ROLLBACK
25113 application_ru 28787 25110 application_ru @IP 56617 2017-04-19 16:12:12.001022+02 2017-04-19 16:13:30.445042+02 2017-04-19 16:13:30.445061+02 f idle SET application_name = ''
25113 application_ru 28718 25110 application_ru @IP 56549 2017-04-19 16:12:09.233936+02 2017-04-19 16:39:28.12618+02 2017-04-19 16:39:28.12685+02 f idle COMMIT
25113 application_ru 28719 25110 application_ru @IP 56550 2017-04-19 16:12:09.247048+02 2017-04-19 16:13:19.265682+02 2017-04-19 16:13:19.265701+02 f idle SET application_name = ''
25113 application_ru 28799 25110 application_ru @IP 56629 2017-04-19 16:12:12.385876+02 2017-04-19 16:13:30.454688+02 2017-04-19 16:13:30.454706+02 f idle SET application_name = ''
25113 application_ru 28720 25110 application_ru @IP 56551 2017-04-19 16:12:09.254461+02 2017-04-19 16:36:27.946329+02 2017-04-19 16:36:27.946927+02 f idle COMMIT
25113 application_ru 28802 25110 application_ru @IP 56653 2017-04-19 16:12:12.732014+02 2017-04-19 16:13:31.469815+02 2017-04-19 16:13:31.469834+02 f idle SET application_name = ''
25113 application_ru 28803 25110 application_ru @IP 56655 2017-04-19 16:12:12.762342+02 2017-04-19 16:13:35.583056+02 2017-04-19 16:13:35.583076+02 f idle SET application_name = ''
25113 application_ru 28723 25110 application_ru @IP 56554 2017-04-19 16:12:09.284433+02 2017-04-19 16:13:26.360818+02 2017-04-19 16:13:26.36084+02 f idle SET application_name = ''
25113 application_ru 28383 25110 application_ru @IP 54943 2017-04-19 15:39:11.734454+02 2017-04-19 15:43:26.630369+02 2017-04-19 15:43:26.631539+02 f idle COMMIT
25113 application_ru 28783 25110 application_ru @IP 56613 2017-04-19 16:12:10.919845+02 2017-04-19 16:36:28.059978+02 2017-04-19 16:36:28.060652+02 f idle COMMIT
25113 application_ru 28725 25110 application_ru @IP 56556 2017-04-19 16:12:09.301346+02 2017-04-19 16:36:28.033173+02 2017-04-19 16:36:28.033898+02 f idle COMMIT
25113 application_ru 28726 25110 application_ru @IP 56557 2017-04-19 16:12:09.308391+02 2017-04-19 16:13:18.251517+02 2017-04-19 16:13:18.251536+02 f idle SET application_name = ''
25113 application_ru 28727 25110 application_ru @IP 56558 2017-04-19 16:12:09.316531+02 2017-04-19 16:13:22.305681+02 2017-04-19 16:13:22.322066+02 2017-04-19 16:13:22.322075+02 f idle in transaction SET application_name = ''
25113 application_ru 28728 25110 application_ru @IP 56559 2017-04-19 16:12:09.322599+02 2017-04-19 16:13:35.502415+02 2017-04-19 16:13:35.504006+02 2017-04-19 16:13:35.504012+02 f idle in transaction SET application_name = ''
25113 application_ru 28804 25110 application_ru @IP 56656 2017-04-19 16:12:12.766971+02 2017-04-19 16:13:25.402983+02 2017-04-19 16:13:25.402999+02 f idle SET application_name = ''
25113 application_ru 28730 25110 application_ru @IP 56561 2017-04-19 16:12:09.335491+02 2017-04-19 16:42:33.786153+02 2017-04-19 16:42:33.787624+02 f idle COMMIT
25113 application_ru 28731 25110 application_ru @IP 56562 2017-04-19 16:12:09.344289+02 2017-04-19 16:13:18.25649+02 2017-04-19 16:13:18.256508+02 f idle SET application_name = ''
25113 application_ru 28732 25110 application_ru @IP 56563 2017-04-19 16:12:09.353086+02 2017-04-19 16:36:28.010933+02 2017-04-19 16:36:28.011792+02 f idle COMMIT
25113 application_ru 28815 25110 application_ru @IP 56828 2017-04-19 16:14:09.063283+02 2017-04-19 16:36:28.018321+02 2017-04-19 16:36:28.018881+02 f idle COMMIT
25113 application_ru 28734 25110 application_ru @IP 56565 2017-04-19 16:12:09.372957+02 2017-04-19 16:13:26.365472+02 2017-04-19 16:13:26.365489+02 f idle SET application_name = ''
25113 application_ru 28735 25110 application_ru @IP 56566 2017-04-19 16:12:09.380326+02 2017-04-19 16:36:27.70929+02 2017-04-19 16:36:27.709883+02 f idle COMMIT
25113 application_ru 28736 25110 application_ru @IP 56567 2017-04-19 16:12:09.391501+02 2017-04-19 16:36:27.231899+02 2017-04-19 16:36:27.232516+02 f idle COMMIT
25113 application_ru 28737 25110 application_ru @IP 56568 2017-04-19 16:12:09.40697+02 2017-04-19 16:31:23.899905+02 2017-04-19 16:31:23.900514+02 f idle COMMIT
25113 application_ru 28738 25110 application_ru @IP 56569 2017-04-19 16:12:09.415068+02 2017-04-19 16:13:31.436896+02 2017-04-19 16:13:31.452284+02 2017-04-19 16:13:31.452292+02 f idle in transaction SET application_name = ''
25113 application_ru 28788 25110 application_ru @IP 56618 2017-04-19 16:12:12.085267+02 2017-04-19 16:39:28.149274+02 2017-04-19 16:39:28.149861+02 f idle COMMIT
25113 application_ru 28741 25110 application_ru @IP 56572 2017-04-19 16:12:09.460198+02 2017-04-19 16:13:22.317279+02 2017-04-19 16:13:22.3173+02 f idle SET application_name = ''
25113 application_ru 28742 25110 application_ru @IP 56573 2017-04-19 16:12:09.469126+02 2017-04-19 16:13:35.508389+02 2017-04-19 16:13:35.508531+02 2017-04-19 16:13:35.508544+02 f idle in transaction 3621401 SELECT ID FROM entite WHERE (ID = $1)
25113 application_ru 28743 25110 application_ru @IP 56574 2017-04-19 16:12:09.480635+02 2017-04-19 16:13:19.280655+02 2017-04-19 16:13:19.280673+02 f idle SET application_name = ''
25113 application_ru 28744 25110 application_ru @IP 56575 2017-04-19 16:12:09.489709+02 2017-04-19 16:13:37.535828+02 2017-04-19 16:13:37.535846+02 f idle SET application_name = ''
25113 application_ru 28790 25110 application_ru @IP 56620 2017-04-19 16:12:12.153983+02 2017-04-19 16:13:29.438648+02 2017-04-19 16:13:29.470937+02 2017-04-19 16:13:29.470947+02 f idle in transaction SET application_name = ''
25113 application_ru 28746 25110 application_ru @IP 56577 2017-04-19 16:12:09.515061+02 2017-04-19 16:13:26.410793+02 2017-04-19 16:13:26.410814+02 f idle SET application_name = ''
25113 application_ru 28747 25110 application_ru @IP 56578 2017-04-19 16:12:09.523441+02 2017-04-19 16:46:59.392052+02 2017-04-19 16:46:59.392118+02 f idle ROLLBACK
25113 application_ru 28748 25110 application_ru @IP 56579 2017-04-19 16:12:09.538529+02 2017-04-19 16:13:19.28965+02 2017-04-19 16:13:19.289667+02 f idle SET application_name = ''
25113 application_ru 28749 25110 application_ru @IP 56580 2017-04-19 16:12:09.659386+02 2017-04-19 16:13:30.450561+02 2017-04-19 16:13:30.450581+02 f idle SET application_name = ''
25113 application_ru 28751 25110 application_ru @IP 56581 2017-04-19 16:12:10.163491+02 2017-04-19 16:36:27.918467+02 2017-04-19 16:36:27.91931+02 f idle COMMIT
25113 application_ru 28791 25110 application_ru @IP 56621 2017-04-19 16:12:12.165035+02 2017-04-19 16:13:31.503379+02 2017-04-19 16:13:31.503402+02 f idle SET application_name = ''
25113 application_ru 28792 25110 application_ru @IP 56622 2017-04-19 16:12:12.17066+02 2017-04-19 16:13:25.386412+02 2017-04-19 16:13:25.38643+02 f idle SET application_name = ''
25113 application_ru 28793 25110 application_ru @IP 56623 2017-04-19 16:12:12.26737+02 2017-04-19 16:31:24.016792+02 2017-04-19 16:31:24.017748+02 f idle COMMIT
25113 application_ru 28755 25110 application_ru @IP 56585 2017-04-19 16:12:10.337108+02 2017-04-19 16:36:27.901824+02 2017-04-19 16:36:27.902427+02 f idle COMMIT
25113 application_ru 28794 25110 application_ru @IP 56624 2017-04-19 16:12:12.276537+02 2017-04-19 16:13:29.471072+02 2017-04-19 16:13:29.471136+02 f idle SET application_name = ''
25113 application_ru 28795 25110 application_ru @IP 56625 2017-04-19 16:12:12.282006+02 2017-04-19 16:13:38.562048+02 2017-04-19 16:13:38.562066+02 f idle SET application_name = ''
25113 application_ru 28758 25110 application_ru @IP 56588 2017-04-19 16:12:10.374721+02 2017-04-19 16:13:27.465016+02 2017-04-19 16:13:27.465037+02 f idle SET application_name = ''
25113 application_ru 28759 25110 application_ru @IP 56589 2017-04-19 16:12:10.385124+02 2017-04-19 16:13:22.3223+02 2017-04-19 16:13:22.322317+02 f idle SET application_name = ''
25113 application_ru 28760 25110 application_ru @IP 56590 2017-04-19 16:12:10.397664+02 2017-04-19 16:13:21.311688+02 2017-04-19 16:13:21.311707+02 f idle SET application_name = ''
25113 application_ru 28761 25110 application_ru @IP 56591 2017-04-19 16:12:10.404931+02 2017-04-19 16:13:26.394118+02 2017-04-19 16:13:26.394144+02 f idle SET application_name = ''
25113 application_ru 28762 25110 application_ru @IP 56592 2017-04-19 16:12:10.418222+02 2017-04-19 16:13:35.584132+02 2017-04-19 16:13:35.584152+02 f idle SET application_name = ''
25113 application_ru 28763 25110 application_ru @IP 56593 2017-04-19 16:12:10.428541+02 2017-04-19 16:13:30.45086+02 2017-04-19 16:13:30.450879+02 f idle SET application_name = ''
25113 application_ru 28764 25110 application_ru @IP 56594 2017-04-19 16:12:10.437921+02 2017-04-19 16:14:31.537502+02 2017-04-19 16:14:31.538207+02 f idle COMMIT
25113 application_ru 28765 25110 application_ru @IP 56595 2017-04-19 16:12:10.449559+02 2017-04-19 16:36:27.332857+02 2017-04-19 16:36:27.333461+02 f idle COMMIT
25113 application_ru 28766 25110 application_ru @IP 56596 2017-04-19 16:12:10.461488+02 2017-04-19 16:39:28.116043+02 2017-04-19 16:39:28.116659+02 f idle COMMIT
25113 application_ru 28767 25110 application_ru @IP 56597 2017-04-19 16:12:10.469026+02 2017-04-19 16:13:26.380102+02 2017-04-19 16:13:26.380119+02 f idle SET application_name = ''
25113 application_ru 28814 25110 application_ru @IP 56827 2017-04-19 16:14:09.057832+02 2017-04-19 16:36:27.374108+02 2017-04-19 16:36:27.374798+02 f idle COMMIT
25113 application_ru 28769 25110 application_ru @IP 56599 2017-04-19 16:12:10.48764+02 2017-04-19 16:36:27.815437+02 2017-04-19 16:36:27.816038+02 f idle COMMIT
25113 application_ru 28770 25110 application_ru @IP 56600 2017-04-19 16:12:10.497406+02 2017-04-19 16:36:27.991652+02 2017-04-19 16:36:27.992342+02 f idle COMMIT
25113 application_ru 28771 25110 application_ru @IP 56601 2017-04-19 16:12:10.508009+02 2017-04-19 16:13:31.693369+02 2017-04-19 16:13:33.554604+02 2017-04-19 16:13:33.554611+02 f idle in transaction SET application_name = ''
25113 application_ru 28772 25110 application_ru @IP 56602 2017-04-19 16:12:10.519918+02 2017-04-19 16:13:30.434314+02 2017-04-19 16:13:30.452299+02 2017-04-19 16:13:30.452306+02 f idle in transaction SET application_name = ''
25113 application_ru 28773 25110 application_ru @IP 56603 2017-04-19 16:12:10.528599+02 2017-04-19 16:36:27.737733+02 2017-04-19 16:36:27.738331+02 f idle COMMIT
25113 application_ru 28774 25110 application_ru @IP 56604 2017-04-19 16:12:10.535573+02 2017-04-19 16:13:33.518344+02 2017-04-19 16:13:33.518431+02 f idle SET application_name = ''
25113 application_ru 28775 25110 application_ru @IP 56605 2017-04-19 16:12:10.543604+02 2017-04-19 16:13:34.566578+02 2017-04-19 16:13:35.523592+02 2017-04-19 16:13:35.5236+02 f idle in transaction 3621950 SET application_name = ''
25113 application_ru 28776 25110 application_ru @IP 56606 2017-04-19 16:12:10.554973+02 2017-04-19 16:14:31.910779+02 2017-04-19 16:14:31.911477+02 f idle COMMIT
25113 application_ru 28777 25110 application_ru @IP 56607 2017-04-19 16:12:10.563418+02 2017-04-19 16:13:19.303911+02 2017-04-19 16:13:19.306557+02 2017-04-19 16:13:19.306565+02 f idle in transaction SET application_name = ''
25113 application_ru 28778 25110 application_ru @IP 56608 2017-04-19 16:12:10.571919+02 2017-04-19 16:13:25.432152+02 2017-04-19 16:13:27.397355+02 2017-04-19 16:13:27.39737+02 f idle in transaction 3621401 3621401 SELECT ID FROM entite WHERE (ID = $1)
25113 application_ru 28779 25110 application_ru @IP 56609 2017-04-19 16:12:10.579365+02 2017-04-19 16:36:27.892211+02 2017-04-19 16:36:27.893005+02 f idle COMMIT
25113 application_ru 28780 25110 application_ru @IP 56610 2017-04-19 16:12:10.586249+02 2017-04-19 16:36:28.052815+02 2017-04-19 16:36:28.053432+02 f idle COMMIT
12141 postgres 29053 25110 application_ru pgAdmin III - ??tat du &serveur @IP 52607 2017-04-19 16:50:47.746554+02 2017-04-20 08:12:57.990491+02 2017-04-20 08:12:57.990601+02 f idle SELECT 1
12141 postgres 28965 25110 application_ru pgAdmin III - Navigateur @IP 52060 2017-04-19 16:37:45.140964+02 2017-04-19 16:37:46.682888+02 2017-04-19 16:37:46.684221+02 f idle SELECT setting FROM pg_settings WHERE name IN ('autovacuum', 'track_counts')
25113 application_ru 28967 25110 application_ru pgAdmin III - Navigateur @IP 52062 2017-04-19 16:37:46.741158+02 2017-04-19 16:37:56.44218+02 2017-04-19 16:37:56.443296+02 f idle SELECT t.oid, t.xmin, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable, nspname, des.description, l.lanname, p.prosrc,
COALESCE(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE'), substring(pg_get_trigger (...)
25113 application_ru 28972 25110 application_ru pgAdmin III - ??diteur de requ??tes @IP 52078 2017-04-19 16:38:10.859407+02 2017-04-20 08:12:59.674586+02 2017-04-20 08:12:59.674586+02 2017-04-20 08:12:59.674592+02 f active 3621401 SELECT
pg_stat_activity.pid,
pg_class.relname,
pg_locks.transactionid,
pg_locks.granted,
waiting,
mode,
query,
age(now(),pg_stat_activity.query_start) AS age
FROM pg_stat_activity,pg_locks left
OUTER JOIN pg_class
ON (pg_locks.relation (...)
12141 postgres 29032 25110 application_ru pgAdmin III - ??tat du &serveur @IP 52452 2017-04-19 16:47:24.120869+02 2017-04-20 08:12:58.044782+02 2017-04-20 08:12:58.045222+02 f idle SELECT transaction::text, gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASCVoici mon "show all" :
allow_system_table_mods off Allows modifications of the structure of system tables.
application_name pgAdmin III - ??diteur de requ??tes Sets the application name to be reported in statistics and logs.
archive_command (disabled) Sets the shell command that will be called to archive a WAL file.
archive_mode off Allows archiving of WAL files using archive_command.
archive_timeout 0 Forces a switch to the next xlog file if a new file has not been started within N seconds.
array_nulls on Enable input of NULL elements in arrays.
authentication_timeout 1min Sets the maximum allowed time to complete client authentication.
autovacuum on Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor 0.1 Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold 50 Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age 200000000 Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers 3 Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age 400000000 Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime 1min Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay 20ms Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit -1 Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor 0.2 Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold 50 Minimum number of tuple updates or deletes prior to vacuum.
autovacuum_work_mem -1 Sets the maximum memory to be used by each autovacuum worker process.
backslash_quote safe_encoding Sets whether \' is allowed in string literals.
bgwriter_delay 200ms Background writer sleep time between rounds.
bgwriter_lru_maxpages 100 Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier 2 Multiple of the average buffer usage to free per round.
block_size 8192 Shows the size of a disk block.
bonjour off Enables advertising the server via Bonjour.
bonjour_name Sets the Bonjour service name.
bytea_output escape Sets the output format for bytea.
check_function_bodies on Check function bodies during CREATE FUNCTION.
checkpoint_completion_target 0.5 Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
checkpoint_segments 3 Sets the maximum distance in log segments between automatic WAL checkpoints.
checkpoint_timeout 5min Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning 30s Enables warnings if checkpoint segments are filled more frequently than this.
client_encoding UNICODE Sets the client's character set encoding.
client_min_messages notice Sets the message levels that are sent to the client.
commit_delay 0 Sets the delay in microseconds between transaction commit and flushing WAL to disk.
commit_siblings 5 Sets the minimum concurrent open transactions before performing commit_delay.
constraint_exclusion partition Enables the planner to use constraints to optimize queries.
cpu_index_tuple_cost 0.005 Sets the planner's estimate of the cost of processing each index entry during an index scan.
cpu_operator_cost 0.0025 Sets the planner's estimate of the cost of processing each operator or function call.
cpu_tuple_cost 0.01 Sets the planner's estimate of the cost of processing each tuple (row).
cursor_tuple_fraction 0.1 Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
data_checksums off Shows whether data checksums are turned on for this cluster.
DateStyle ISO, DMY Sets the display format for date and time values.
db_user_namespace off Enables per-database user names.
deadlock_timeout 1s Sets the time to wait on a lock before checking for deadlock.
debug_assertions off Turns on various assertion checks.
debug_pretty_print on Indents parse and plan tree displays.
debug_print_parse off Logs each query's parse tree.
debug_print_plan off Logs each query's execution plan.
debug_print_rewritten off Logs each query's rewritten parse tree.
default_statistics_target 100 Sets the default statistics target.
default_tablespace Sets the default tablespace to create tables and indexes in.
default_text_search_config pg_catalog.french Sets default text search configuration.
default_transaction_deferrable off Sets the default deferrable status of new transactions.
default_transaction_isolation read committed Sets the transaction isolation level of each new transaction.
default_transaction_read_only off Sets the default read-only status of new transactions.
default_with_oids off Create new tables with OIDs by default.
dynamic_shared_memory_type posix Selects the dynamic shared memory implementation used.
effective_cache_size 4GB Sets the planner's assumption about the size of the disk cache.
effective_io_concurrency 1 Number of simultaneous requests that can be handled efficiently by the disk subsystem.
enable_bitmapscan on Enables the planner's use of bitmap-scan plans.
enable_hashagg on Enables the planner's use of hashed aggregation plans.
enable_hashjoin on Enables the planner's use of hash join plans.
enable_indexonlyscan on Enables the planner's use of index-only-scan plans.
enable_indexscan on Enables the planner's use of index-scan plans.
enable_material on Enables the planner's use of materialization.
enable_mergejoin on Enables the planner's use of merge join plans.
enable_nestloop on Enables the planner's use of nested-loop join plans.
enable_seqscan on Enables the planner's use of sequential-scan plans.
enable_sort on Enables the planner's use of explicit sort steps.
enable_tidscan on Enables the planner's use of TID scan plans.
escape_string_warning on Warn about backslash escapes in ordinary string literals.
event_source PostgreSQL Sets the application name used to identify PostgreSQL messages in the event log.
exit_on_error off Terminate session on any error.
extra_float_digits 0 Sets the number of digits displayed for floating-point values.
from_collapse_limit 8 Sets the FROM-list size beyond which subqueries are not collapsed.
fsync on Forces synchronization of updates to disk.
full_page_writes on Writes full pages to WAL when first modified after a checkpoint.
geqo on Enables genetic query optimization.
geqo_effort 5 GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations 0 GEQO: number of iterations of the algorithm.
geqo_pool_size 0 GEQO: number of individuals in the population.
geqo_seed 0 GEQO: seed for random path selection.
geqo_selection_bias 2 GEQO: selective pressure within the population.
geqo_threshold 12 Sets the threshold of FROM items beyond which GEQO is used.
gin_fuzzy_search_limit 0 Sets the maximum allowed result for exact search by GIN.
hot_standby off Allows connections and queries during recovery.
hot_standby_feedback off Allows feedback from a hot standby to the primary that will avoid query conflicts.
huge_pages try Use of huge pages on Linux.
ignore_checksum_failure off Continues processing after a checksum failure.
ignore_system_indexes off Disables reading from system indexes.
integer_datetimes on Datetimes are integer based.
IntervalStyle postgres Sets the display format for interval values.
join_collapse_limit 8 Sets the FROM-list size beyond which JOIN constructs are not flattened.
krb_caseins_users off Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
lc_collate fr_FR.UTF-8 Shows the collation order locale.
lc_ctype fr_FR.UTF-8 Shows the character classification and case conversion locale.
lc_messages fr_FR.UTF-8 Sets the language in which messages are displayed.
lc_monetary fr_FR.UTF-8 Sets the locale for formatting monetary amounts.
lc_numeric fr_FR.UTF-8 Sets the locale for formatting numbers.
lc_time fr_FR.UTF-8 Sets the locale for formatting date and time values.
listen_addresses * Sets the host name or IP address(es) to listen to.
lo_compat_privileges off Enables backward compatibility mode for privilege checks on large objects.
local_preload_libraries Lists unprivileged shared libraries to preload into each backend.
lock_timeout 3min Sets the maximum allowed duration of any wait for a lock.
log_autovacuum_min_duration -1 Sets the minimum execution time above which autovacuum actions will be logged.
log_checkpoints off Logs each checkpoint.
log_connections off Logs each successful connection.
log_destination stderr Sets the destination for server log output.
log_disconnections off Logs end of a session, including duration.
log_duration off Logs the duration of each completed SQL statement.
log_error_verbosity default Sets the verbosity of logged messages.
log_executor_stats off Writes executor performance statistics to the server log.
log_file_mode 0600 Sets the file permissions for log files.
log_hostname off Logs the host name in the connection logs.
log_line_prefix %t [%p-%l] %q%u@%d Controls information prefixed to each log line.
log_lock_waits off Logs long lock waits.
log_min_duration_statement -1 Sets the minimum execution time above which statements will be logged.
log_min_error_statement error Causes all statements generating error at or above this level to be logged.
log_min_messages warning Sets the message levels that are logged.
log_parser_stats off Writes parser performance statistics to the server log.
log_planner_stats off Writes planner performance statistics to the server log.
log_rotation_age 1d Automatic log file rotation will occur after N minutes.
log_rotation_size 10MB Automatic log file rotation will occur after N kilobytes.
log_statement none Sets the type of statements logged.
log_statement_stats off Writes cumulative performance statistics to the server log.
log_temp_files -1 Log the use of temporary files larger than this number of kilobytes.
log_timezone localtime Sets the time zone to use in log messages.
log_truncate_on_rotation off Truncate existing log files of same name during log rotation.
logging_collector off Start a subprocess to capture stderr output and/or csvlogs into log files.
maintenance_work_mem 64MB Sets the maximum memory to be used for maintenance operations.
max_connections 200 Sets the maximum number of concurrent connections.
max_files_per_process 1000 Sets the maximum number of simultaneously open files for each server process.
max_function_args 100 Shows the maximum number of function arguments.
max_identifier_length 63 Shows the maximum identifier length.
max_index_keys 32 Shows the maximum number of index keys.
max_locks_per_transaction 64 Sets the maximum number of locks per transaction.
max_pred_locks_per_transaction 64 Sets the maximum number of predicate locks per transaction.
max_prepared_transactions 0 Sets the maximum number of simultaneously prepared transactions.
max_replication_slots 0 Sets the maximum number of simultaneously defined replication slots.
max_stack_depth 2MB Sets the maximum stack depth, in kilobytes.
max_standby_archive_delay 30s Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.
max_standby_streaming_delay 30s Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data.
max_wal_senders 0 Sets the maximum number of simultaneously running WAL sender processes.
max_worker_processes 8 Maximum number of concurrent worker processes.
password_encryption on Encrypt passwords.
port 5432 Sets the TCP port the server listens on.
post_auth_delay 0 Waits N seconds on connection startup after authentication.
pre_auth_delay 0 Waits N seconds on connection startup before authentication.
quote_all_identifiers off When generating SQL fragments, quote all identifiers.
random_page_cost 4 Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
restart_after_crash on Reinitialize server after backend crash.
search_path $user,public Sets the schema search order for names that are not schema-qualified.
segment_size 1GB Shows the number of pages per disk file.
seq_page_cost 1 Sets the planner's estimate of the cost of a sequentially fetched disk page.
server_encoding UTF8 Sets the server (database) character set encoding.
server_version 9.4.8 Shows the server version.
server_version_num 90408 Shows the server version as an integer.
session_replication_role origin Sets the session's behavior for triggers and rewrite rules.
shared_buffers 256MB Sets the number of shared memory buffers used by the server.
sql_inheritance on Causes subtables to be included by default in various commands.
ssl on Enables SSL connections.
ssl_ca_file Location of the SSL certificate authority file.
ssl_cert_file /etc/ssl/certs/ssl-cert-snakeoil.pem Location of the SSL server certificate file.
ssl_crl_file Location of the SSL certificate revocation list file.
ssl_key_file /etc/ssl/private/ssl-cert-snakeoil.key Location of the SSL server private key file.
ssl_prefer_server_ciphers on Give priority to server ciphersuite order.
ssl_renegotiation_limit 0 Set the amount of traffic to send and receive before renegotiating the encryption keys.
standard_conforming_strings on Causes '...' strings to treat backslashes literally.
statement_timeout 0 Sets the maximum allowed duration of any statement.
superuser_reserved_connections 3 Sets the number of connection slots reserved for superusers.
synchronize_seqscans on Enable synchronized sequential scans.
synchronous_commit on Sets the current transaction's synchronization level.
synchronous_standby_names List of names of potential synchronous standbys.
syslog_facility local0 Sets the syslog facility to be used when syslog enabled.
syslog_ident postgres Sets the program name used to identify PostgreSQL messages in syslog.
tcp_keepalives_count 9 Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle 7200 Time between issuing TCP keepalives.
tcp_keepalives_interval 75 Time between TCP keepalive retransmits.
temp_buffers 8MB Sets the maximum number of temporary buffers used by each session.
temp_file_limit -1 Limits the total size of all temporary files used by each session.
temp_tablespaces Sets the tablespace(s) to use for temporary tables and sort files.
TimeZone localtime Sets the time zone for displaying and interpreting time stamps.
timezone_abbreviations Default Selects a file of time zone abbreviations.
trace_notify off Generates debugging output for LISTEN and NOTIFY.
trace_recovery_messages log Enables logging of recovery-related debugging information.
trace_sort off Emit information about resource usage in sorting.
track_activities on Collects information about executing commands.
track_activity_query_size 1024 Sets the size reserved for pg_stat_activity.query, in bytes.
track_counts on Collects statistics on database activity.
track_functions none Collects function-level statistics on database activity.
track_io_timing off Collects timing statistics for database I/O activity.
transaction_deferrable off Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures.
transaction_isolation read committed Sets the current transaction's isolation level.
transaction_read_only off Sets the current transaction's read-only status.
transform_null_equals off Treats expr=NULL as expr IS NULL.
unix_socket_group Sets the owning group of the Unix-domain socket.
unix_socket_permissions 0777 Sets the access permissions of the Unix-domain socket.
update_process_title on Updates the process title to show the active SQL command.
vacuum_cost_delay 0 Vacuum cost delay in milliseconds.
vacuum_cost_limit 200 Vacuum cost amount available before napping.
vacuum_cost_page_dirty 20 Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit 1 Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss 10 Vacuum cost for a page not found in the buffer cache.
vacuum_defer_cleanup_age 0 Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.
vacuum_freeze_min_age 50000000 Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age 150000000 Age at which VACUUM should scan whole table to freeze tuples.
vacuum_multixact_freeze_min_age 5000000 Minimum age at which VACUUM should freeze a MultiXactId in a table row.
vacuum_multixact_freeze_table_age 150000000 Multixact age at which VACUUM should scan whole table to freeze tuples.
wal_block_size 8192 Shows the block size in the write ahead log.
wal_buffers 8MB Sets the number of disk-page buffers in shared memory for WAL.
wal_keep_segments 0 Sets the number of WAL files held for standby servers.
wal_level minimal Set the level of information written to the WAL.
wal_log_hints off Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modifications.
wal_receiver_status_interval 10s Sets the maximum interval between WAL receiver status reports to the primary.
wal_receiver_timeout 1min Sets the maximum wait time to receive data from the primary.
wal_segment_size 16MB Shows the number of pages per write ahead log segment.
wal_sender_timeout 1min Sets the maximum time to wait for WAL replication.
wal_sync_method fdatasync Selects the method used for forcing WAL updates to disk.
wal_writer_delay 200ms WAL writer sleep time between WAL flushes.
work_mem 4MB Sets the maximum memory to be used for query workspaces.
xmlbinary base64 Sets how binary values are to be encoded in XML.
xmloption content Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.
zero_damaged_pages off Continues processing past damaged page headers.Hors ligne
Qu'entendez-vous par "coincés" ?
Si c'est "bloqués en attente d'un verrou", non, aucune n'est bloquée en attente d'un verrou. Toutes ces connexions ne font rien. Elles sont en attente d'une requête à exécuter (en dehors de la connexion qui a donné ce résultat sur pg_stat_activity).
Guillaume.
Hors ligne
Merci.
Mais ça a quel sens qu'il y ait des requêtes SET ou COMMIT dans pg_stat_activity qui ont un QUERY_START ou un STATE_CHANGE qui a plus de 15 heures d'ancienneté ?
Hors ligne
Ça permet de connaître la dernière requête exécutée. Ça aurait moins de sens de supprimer une information potentiellement intéressante. À partir du moment où la colonne state indique "idle" ou "idle in transaction", on sait que le processus serveur ne fait rien (à part attendre la prochaine requête à exécutée), et on sait que le contenu de "query" correspond à la dernière requête exécutée.
Guillaume.
Hors ligne
OK ! Pour moi, c'était des requêtes en cours d'exécution. Donc, un problème que j'avais quelquepart. Merci pour ton explication.
Je me permets une autre question alors. Cette requête, qui pour moi indiquait des "locks" en cours (depuis 19 heures), elle signifie quoi ?
requete :
SELECT
pg_stat_activity.pid,
pg_class.relname,
pg_locks.transactionid,
pg_locks.granted,
waiting,
mode,
query,
age(now(),pg_stat_activity.query_start) AS "age"
FROM pg_stat_activity,pg_locks left
OUTER JOIN pg_class
ON (pg_locks.relation = pg_class.oid)
WHERE pg_locks.pid = pg_stat_activity.pid
AND pg_stat_activity.pid <> pg_backend_pid()
ORDER BY age DESC LIMIT 10; résultat :
28777 entite_ukey t f AccessShareLock SET application_name = '' 19:00:27.42496
28777 entite_pkey t f AccessShareLock SET application_name = '' 19:00:27.42496
28777 t f ExclusiveLock SET application_name = '' 19:00:27.42496
28777 entite t f AccessShareLock SET application_name = '' 19:00:27.42496
28777 entite_ukey_libelle t f AccessShareLock SET application_name = '' 19:00:27.42496
28727 entite t f AccessShareLock SET application_name = '' 19:00:24.409451
28727 entite_ukey t f AccessShareLock SET application_name = '' 19:00:24.409451
28727 entite_ukey_libelle t f AccessShareLock SET application_name = '' 19:00:24.409451
28727 entite_pkey t f AccessShareLock SET application_name = '' 19:00:24.409451
28727 t f ExclusiveLock SET application_name = '' 19:00:24.409451Hors ligne
Cette requête liste les verrous pris ou en attente pour chaque session ouverte. La colonne granted (qui ne contient que des t pour true) indique que les verrous ont bien été acceptés (parce que granted est à true). Donc pas d'attente de verrous. Ce que confirme la colonne waiting qui ne contient que des f (pour false). Le type de verrou est indiqué par la colonne mode.
Guillaume.
Hors ligne
Merci.
Donc des verrous posés et pas levés depuis 19 heures ?
Si j'enlève le "LIMIT 10", ça me donne aussi (notamment) les lignes suivantes :
28778 entite_ukey_libelle t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 entite_ukey t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 entite t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 entite t f RowShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 entite_pkey t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 trace t f RowExclusiveLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 batiment_ukey_libelle t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 batiment_ukey t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 batiment_pkey t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 batiment t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 client_ukey t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 client_ukey t f RowExclusiveLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 client_pkey t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 client_pkey t f RowExclusiveLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 trace_id_seq t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28742 entite_ukey_libelle t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:09.361716
28742 entite_ukey t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:09.361716
28742 entite_pkey t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:09.361716
28742 entite t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:09.361716
28742 t f ExclusiveLock SELECT ID FROM entite WHERE (ID = $1) 19:14:09.361716
28778 t f ExclusiveLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 client t f AccessShareLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 client t f RowExclusiveLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892
28778 3621401 t f ExclusiveLock SELECT ID FROM entite WHERE (ID = $1) 19:14:17.472892Hors ligne
> Donc des verrous posés et pas levés depuis 19 heures ?
Si la connexion est dans une transaction non terminée, ça ne paraît pas anormale (n dehors du fait qu'une transaction ne devrait pas durer autant de temps).
> Si j'enlève le "LIMIT 10", ça me donne aussi (notamment) les lignes suivantes :
Même état, pas de blocage, des verrous acceptés.
Guillaume.
Hors ligne
OK. Je comprends. Merci.
Et y-a-til une solution au niveau conf de la base de données, pour rollbacker toutes les transactions en cours depuis x minutes ?
Merci encore pour toutes ces explications, j'y vois plus clair.
Hors ligne
Il y a une solution depuis la version 9.6 avec le paramètre idle_in_transaction_session_timeout. Cependant, si vous le configurez globalement, cela impactera toutes les transactions. Attention à ne pas le configurer trop bas, auquel cas des transactions légitimes seraient annulées. (désolé, j'ai du modifier mon message après coup, j'avais fait enter trop tôt 
)
Guillaume.
Hors ligne
J'avais vu ce paramètre mais je suis hélas encore en 9.4. Du coup, se pose la question d'une éventuelle migration...
Encore merci pour toutes ces explications qui m'ont permis de bien comprendre mon problème (qui semble être au final une dépassement de mon pool JDBC qui empêche l'exécution (notamment) d'un ordre de fin de transaction et laisse donc une ou des transactions inachevées).
Hors ligne
Le fait de compter sur le serveur pour couper ces connexions, c'est prendre le problème à l'envers. C'est le client (en l'occurrence le pooler) qui est à l'initiative de l'ouverture de connexion, c'est à lui de savoir quand il n'en a plus besoin et de la fermer.
Même si une connexion est ouverte depuis longtemps et ne fait rien, le serveur ne peut pas deviner si une requête arrivera la milliseconde d'après ou pas.
En principe libérer des connexions inutilisées fait partie des fonctions de base d'un pooler.
Des connexions "idle in transaction" sur de longues durées, c'est problématique, parce qu'en fonction de ce qu'a fait la transaction avant d'arriver à cet état, il est possible que des ressources restent verrouillées, ce qui empêche les autres transactions de progresser. Un problème bien connu est aussi que ça empêche VACUUM de faire son travail.
Les connexions simplement "idle" ne posent pas ce problème. Il est quand même bon de les éliminer si elles ne servent plus, mais ce n'est pas du tout la même criticité.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Je vais aussi effectivement regarder ce pool de plus près de toutes façons.
Merci pour tes explications.
Hors ligne