Traqueur, problème de verrouillage et parallélisme

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-19) 6.3.0 20170618, 64-bit \dt+ t1 Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-----+-------+--------------+---------+------------- public | t1 | table | postgres | 1740 MB | (1 ligne) -- SESSION A explain select count(*) from t1 a, t1 b; QUERY PLAN ------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=22002655250913.34..22002655250913.35 rows=1 width=8) -> Gather (cost=22002655250913.02..22002655250913.33 rows=3 width=8) Workers Planned: 3 -> Partial Aggregate (cost=22002655249913.02..22002655249913.03 rows=1 width=8) -> Nested Loop (cost=0.00..19959680668765.71 rows=817189832458921 width=0) -> Parallel Seq Scan on t1 a (cost=0.00..385067.59 rows=16236059 width=0) -> Seq Scan on t1 b (cost=0.00..726024.84 rows=50331784 width=0) (7 lignes) select count(*) from t1 a, t1 b; -- OUILLE ... -- SESSION B start transaction; START TRANSACTION truncate table t1; -- EN ATTENTE ./traqueur.sh -d 1 -p traqueur 0.08.02beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... busy_pc | distinct_exe | db | pid | pg_user | client_ip | application | query | wait_event_type | blockers | non_cpu_pc | mem_en_mo | swapped_en_mo ---------+--------------+----------+------+----------+-----------+-------------+------------------------------------------+-----------------+-----------------------+------------+-----------+--------------- 100 | 1 / 10 | postgres | 2838 | postgres | | psql | select count(*) from t1 a, t1 b; | | | 1 | 2 | 0 100 | 1 / 10 | postgres | 2855 | postgres | | psql | truncate table t1; | Lock | {2838,2860,2861,2862} | 100 | 0 | 0 100 | 1 / 10 | postgres | 2861 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | | | 0 | 0 | 0 100 | 1 / 10 | postgres | 2862 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | | | 0 | 0 | 0 100 | 1 / 10 | traqueur | 2691 | postgres | | traqueur | execute traqueur_sleep; | Timeout | | 100 | 11 | 0 80 | 1 / 8 | postgres | 2860 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | | | 2 | 0 | 0 10 | 1 / 1 | postgres | 2860 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | IO | | 2 | 0 | 0 10 | 1 / 1 | postgres | 2860 | postgres | | psql | select count(*) from t1 a, t1 b;postgres | LWLock | | 1 | 0 | 0 (8 lignes) Charge CPU moyennne globale ----------------------------- 100 (1 ligne) temps_cpu_user_en_s | temps_cpu_systeme_en_s | temps_cpu_idle_en_s | temps_io_en_s ---------------------+------------------------+---------------------+--------------- 4 | 0 | 0 | 0 (1 ligne) ./traqueur.sh -d 1 -p -o "pid, query, backend_type" traqueur 0.08.02beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... busy_pc | distinct_exe | pid | query | backend_type | non_cpu_pc | mem_en_mo | swapped_en_mo ---------+--------------+------+------------------------------------------+-------------------+------------+-----------+--------------- ... 100 | 1 / 10 | 2838 | select count(*) from t1 a, t1 b; | client backend | 0 | 2 | 0 100 | 1 / 10 | 2860 | select count(*) from t1 a, t1 b;postgres | background worker | 2 | 0 | 0 ...

select pg_terminate_backend(2861); pg_terminate_backend ---------------------- t -- SESSION A select count(*) from t1 a, t1 b; ERREUR: arrêt des connexions suite à la demande de l'administrateur CONTEXTE : processus parallèle select 1; ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction rollback; ROLLBACK select 1; ?column? ---------- 1 -- SESSION B TRUNCATE TABLE ...