Vous n'êtes pas identifié(e).

Pages : 1

#1 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 24/04/2017 08:44:01

cricri

Je vais aussi effectivement regarder ce pool de plus près  de toutes façons.
Merci pour tes explications.

#2 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 20/04/2017 15:45:04

cricri

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).

#3 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 20/04/2017 12:02:48

cricri

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.

#4 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 20/04/2017 11:34:31

cricri

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.472892

#5 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 20/04/2017 11:15:23

cricri

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.409451

#6 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 20/04/2017 09:36:01

cricri

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é ?

#7 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 20/04/2017 08:26:03

cricri

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 ASC

Voici 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.

#8 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 05/04/2017 17:30:27

cricri

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 ).

#9 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 05/04/2017 14:42:16

cricri

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;t

#10 Re : Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 05/04/2017 08:02:12

cricri

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>

#11 Général » ExclusiveLock infini sur un SELECT pg_stat_get_backend_pid » 04/04/2017 17:01:54

cricri
Réponses : 20

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.

Pages : 1

Pied de page des forums

Propulsé par FluxBB