select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 10beta3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
show max_prepared_transactions;
max_prepared_transactions
---------------------------
100
create table t1(c1 int);
CREATE TABLE
insert into t1 values(1),(2),(3);
INSERT 0 3
-- SESSION 1
start transaction;
START TRANSACTION
select pg_backend_pid();
pg_backend_pid
----------------
12459
update t1 set c1 = 11 where c1 = 1;
UPDATE 1
-- SESSION B
start transaction;
START TRANSACTION
select pg_backend_pid();
pg_backend_pid
----------------
12494
update t1 set c1 = 22 where c1 = 2;
UPDATE 1
prepare transaction 'patanok';
PREPARE TRANSACTION
-- SESSION C
start transaction;
START TRANSACTION
select pg_backend_pid();
pg_backend_pid
----------------
12521
update t1 set c1 = 33 where c1 = 3;
UPDATE 1
prepare transaction 'nouka';
PREPARE TRANSACTION
\q
-- SESSION D
select pg_backend_pid();
pg_backend_pid
----------------
12565
truncate table t1;
-- EN ATTENTE
-- SESSION E
select pg_backend_pid();
pg_backend_pid
----------------
12590
select * from t1;
-- EN ATTENTE
./traqueur.sh -d 1
traqueur 0.10.03beta - 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
---------+--------------+----------+-------+----------+-----------+-------------+--------------------+-----------------+-----------
100 | 1 / 10 | postgres | 12565 | postgres | | psql | truncate table t1; | Lock | {0,12459}
100 | 1 / 10 | postgres | 12590 | postgres | | psql | select * from t1; | Lock | {0,12459}
-- SESSION admin
select pg_terminate_backend(12459);
pg_terminate_backend
----------------------
t
-- les sessions D et E attendent toujours
./traqueur.sh -d 1
traqueur 0.10.03beta - 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
---------+--------------+----------+-------+----------+-----------+-------------+--------------------+-----------------+----------
100 | 1 / 10 | postgres | 12565 | postgres | | psql | truncate table t1; | Lock | {0}
100 | 1 / 10 | postgres | 12590 | postgres | | psql | select * from t1; | Lock | {0}
-- SESSION admin
select pg_terminate_backend(12494);
pg_terminate_backend
----------------------
t
-- les sessions D et E attendent toujours
./traqueur.sh -d 1
traqueur 0.10.03beta - 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
---------+--------------+----------+-------+----------+-----------+-------------+--------------------+-----------------+----------
100 | 1 / 10 | postgres | 12565 | postgres | | psql | truncate table t1; | Lock | {0}
100 | 1 / 10 | postgres | 12590 | postgres | | psql | select * from t1; | Lock | {0}
-- SESSION admin
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+---------+-------------------------------+----------+----------
2452 | patanok | 2017-08-28 14:32:46.015907+02 | postgres | postgres
2453 | nouka | 2017-08-28 14:34:16.135918+02 | postgres | postgres
rollback prepared 'patanok';
ROLLBACK PREPARED
-- les sessions D et E attendent toujours
-- SESSION admin
commit prepared 'nouka';
COMMIT PREPARED
-- SESSION D
...
TRUNCATE TABLE
-- SESSION E
...
c1
----
(0 ligne)
./traqueur.sh -d 1
traqueur 0.10.03beta - 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
---------+--------------+----+-----+---------+-----------+-------------+-------+-----------------+----------
(0 ligne)