Mentat

Date: Fri, 25 May 2018 10:45:45 +0200
From: Radko Krkoš <krkos@cesnet.cz>
Subject: Visiaci dotaz

Ahoj, všimol som si že krátko po štarte Mentatu sa spustí dotaz:

SELECT groups.name AS groups_name, groups.source AS groups_source,
groups.description AS groups_description, groups.enabled AS
groups_enabled, groups.parent_id AS groups_parent_id, groups.id AS
groups_id, groups.createtime AS groups_createtime FROM groups WHERE
groups.id = 7747;

ktorý nikdy (20h+) nedobehne. Má niekto tušenie čo to je a ako to že to
ničomu v Mentate nevadí?

Na neskôr:
Zaujímavé je, že keď ten dotaz spustím ručne, prebehne normálne a okamžite.

Mek:

Smrdí mi to zapomenutým commitem...někde. Dotaz proběhne v pořádku, ale pak nezavolám commit a na straně serveru to asi zůstane viset.

Radko:

To asi nebude ono, po SELECTe to netreba.

Radko:

Už asi rozumiem čo si myslel. Zdá sa že to nejako blokuje iné spojenie.
Vyzerá to na COMMIT, ale nie nezavolaný ale zrejme naviac. Je ho tiež
vidieť v zozname bežiacich transakcií ale bez doby behu. Nemám ale
tušenie ako to debugovať. Rozmýšľal som či niečo nevolá COMMIT v slučke
aj bez dát ale čo som pozeral kód, tak by to tak byť nemalo.

Příklad výseku z pg_stat_activity:

  datid  |    datname    |  pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type |     wait_event      |        state        | backend_xid | backend_xmin |                                                                                                                        query                                                                                                                         |    backend_type     
---------+---------------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+---------------------+-------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------
 7655235 | mentat_main   |  1671 |    16384 | mentat   |                  | ::1         |                 |       47378 | 2018-08-09 09:55:24.522505+02 |                               | 2018-08-09 09:55:32.502753+02 | 2018-08-09 09:55:32.502889+02 | Client          | ClientRead          | idle                |             |              | ROLLBACK                                                                                                                                                                                                                                             | client backend
   16385 | mentat_events | 67217 |    16384 | mentat   |                  | ::1         |                 |       42290 | 2018-08-07 10:48:28.172669+02 |                               | 2018-08-09 15:19:05.295189+02 | 2018-08-09 15:19:05.295292+02 | Client          | ClientRead          | idle                |             |              | COMMIT                                                                                                                                                                                                                                               | client backend
 7655235 | mentat_main   | 67226 |    16384 | mentat   |                  | ::1         |                 |       42300 | 2018-08-07 10:48:29.268503+02 | 2018-08-07 10:48:29.277103+02 | 2018-08-09 15:14:49.378458+02 | 2018-08-09 15:14:49.378649+02 | Client          | ClientRead          | idle in transaction |             |              | SELECT groups.name AS groups_name, groups.source AS groups_source, groups.description AS groups_description, groups.enabled AS groups_enabled, groups.parent_id AS groups_parent_id, groups.createtime AS groups_createtime, groups.id AS groups_id +| client backend
         |               |       |          |          |                  |             |                 |             |                               |                               |                               |                               |                 |                     |                     |             |              | FROM groups                                                                                                                                                                                                                                         +| 
         |               |       |          |          |                  |             |                 |             |                               |                               |                               |                               |                 |                     |                     |             |              | WHERE groups.id = 297                                                                                                                                                                                                                                | 
(11 rows)

Nevíme, co to způsobuje, proč to visí, nebo zda je to z nějakého důvodu korektní.