Content-Type: multipart/alternative;
 boundary="------------SmTrZjW8ALyglbZIXzDUa2xI"
Message-ID: <c1d1e271-9a71-4469-85da-da168fc6440e@cloud.gatewaynet.com>
Date: Sat, 31 May 2025 23:43:52 +0300
MIME-Version: 1.0
Content-Language: en-US
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
Subject: PostgreSQL 16.6 , query stuck with STAT Ssl, wait_event_type : IPC ,
 wait_event : ParallelFinish
Archived-At: 
 <https://www.postgresql.org/message-id/c1d1e271-9a71-4469-85da-da168fc6440e%40cloud.gatewaynet.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------SmTrZjW8ALyglbZIXzDUa2xI
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi

a query is stuck with the above, it seems it waits for parallel worker 
to finish, however , there are no parallel works running :

postgres@[local]/dynacom=# SELECT application_name, backend_type, 
backend_start,xact_start,query_start,wait_event_type, wait_event ,state 
FROM pg_stat_activity;
application_name |         backend_type         |         backend_start 
         |          xact_start           |          query_start 
          | wait_event_type |     wait_event      | state
------------------+------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+-------- 

                  | autovacuum launcher          | 2024-11-29 
17:48:50.92935+02  |                               | 
                               | Activity        | AutoVacuumMain      |
                  | logical replication launcher | 2024-11-29 
17:48:50.929496+02 |                               | 
                               | Activity        | LogicalLauncherMain |
DBMIRROR         | client backend               | 2025-05-31 
19:04:16.724305+03 | 2025-05-31 19:05:21.686093+03 | 2025-05-31 
19:05:21.909936+03 | IPC             | ParallelFinish      | active
                  | client backend               | 2025-05-31 
23:31:30.030806+03 |                               | 2025-05-31 
23:35:05.045573+03 | Client          | ClientRead          | idle
psql             | client backend               | 2025-05-31 
23:29:33.863485+03 | 2025-05-31 23:35:09.322972+03 | 2025-05-31 
23:35:09.322972+03 |                 |                     | active
RXMLFVSLS        | client backend               | 2025-05-31 
23:32:37.351131+03 |                               | 2025-05-31 
23:35:09.295221+03 | Client          | ClientRead          | idle
psql             | client backend               | 2025-04-28 
16:59:55.968442+03 |                               | 2025-05-27 
16:43:56.338228+03 | Client          | ClientRead          | idle
                  | background writer            | 2024-11-29 
17:48:50.916876+02 |                               | 
                               | Activity        | BgWriterMain        |
                  | archiver                     | 2024-12-03 
18:57:36.447067+02 |                               | 
                               | Activity        | ArchiverMain        |
                  | checkpointer                 | 2024-11-29 
17:48:50.916648+02 |                               | 
                               | Activity        | CheckpointerMain    |
                  | walwriter                    | 2024-11-29 
17:48:50.928789+02 |                               | 
                               | Activity        | WalWriterMain       |
(11 rows)

postgres@[local]/dynacom=#

So, I will terminate this backend now to get the system working again, 
we are curious why this happened, our system serves daily 22M+ 
transactions, this is Saturday night hence the low traffic.

postgres@smadb:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 12 (bookworm)
Release:        12
Codename:       bookworm
postgres@smadb:~$ psql -Aqt -c 'select version()'
PostgreSQL 16.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 
12.2.0-14) 12.2.0, 64-bit
postgres@smadb:~$


--------------SmTrZjW8ALyglbZIXzDUa2xI
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi</p>
    <p>a query is stuck with the above, it seems it waits for parallel
      worker to finish, however , there are no parallel works running :</p>
    <p><span style="font-family:monospace"><span
          style="color:#000000;background-color:#ffffff;">postgres@[local]/dynacom=#
          SELECT application_name, backend_type,
          backend_start,xact_start,query_start,wait_event_type,
          wait_event ,state FROM pg_stat_activity;
        </span><br>
        application_name |         backend_type         |
                backend_start         |          xact_start           |
                 query_start          | wait_event_type |     wait_event
             | state   <br>
------------------+------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------
        <br>
                         | autovacuum launcher          | 2024-11-29
        17:48:50.92935+02  |                               |
                                      | Activity        | AutoVacuumMain
             |  <br>
                         | logical replication launcher | 2024-11-29
        17:48:50.929496+02 |                               |
                                      | Activity        |
        LogicalLauncherMain |  <br>
        DBMIRROR         | client backend               | 2025-05-31
        19:04:16.724305+03 | 2025-05-31 19:05:21.686093+03 | 2025-05-31
        19:05:21.909936+03 | IPC             | ParallelFinish      |
        active
        <br>
                         | client backend               | 2025-05-31
        23:31:30.030806+03 |                               | 2025-05-31
        23:35:05.045573+03 | Client          | ClientRead          |
        idle
        <br>
        psql             | client backend               | 2025-05-31
        23:29:33.863485+03 | 2025-05-31 23:35:09.322972+03 | 2025-05-31
        23:35:09.322972+03 |                 |                     |
        active
        <br>
        RXMLFVSLS        | client backend               | 2025-05-31
        23:32:37.351131+03 |                               | 2025-05-31
        23:35:09.295221+03 | Client          | ClientRead          |
        idle
        <br>
        psql             | client backend               | 2025-04-28
        16:59:55.968442+03 |                               | 2025-05-27
        16:43:56.338228+03 | Client          | ClientRead          |
        idle
        <br>
                         | background writer            | 2024-11-29
        17:48:50.916876+02 |                               |
                                      | Activity        | BgWriterMain
               |  <br>
                         | archiver                     | 2024-12-03
        18:57:36.447067+02 |                               |
                                      | Activity        | ArchiverMain
               |  <br>
                         | checkpointer                 | 2024-11-29
        17:48:50.916648+02 |                               |
                                      | Activity        |
        CheckpointerMain    |  <br>
                         | walwriter                    | 2024-11-29
        17:48:50.928789+02 |                               |
                                      | Activity        | WalWriterMain
              |  <br>
        (11 rows)
        <br>
        <br>
        postgres@[local]/dynacom=# <br>
        <br>
      </span></p>
    <p>So, I will terminate this backend now to get the system working
      again, we are curious why this happened, our system serves daily
      22M+ transactions, this is Saturday night hence the low traffic.</p>
    <p><span style="font-family:monospace"><span
style="font-weight:bold;color:#54ff54;background-color:#ffffff;">postgres@smadb</span><span
          style="color:#000000;background-color:#ffffff;">:</span><span
style="font-weight:bold;color:#5454ff;background-color:#ffffff;">~</span><span
          style="color:#000000;background-color:#ffffff;">$ lsb_release
          -a
        </span><br>
        No LSB modules are available.
        <br>
        Distributor ID: Debian
        <br>
        Description:    Debian GNU/Linux 12 (bookworm)
        <br>
        Release:        12
        <br>
        Codename:       bookworm
        <br>
        <span
style="font-weight:bold;color:#54ff54;background-color:#ffffff;">postgres@smadb</span><span
          style="color:#000000;background-color:#ffffff;">:</span><span
style="font-weight:bold;color:#5454ff;background-color:#ffffff;">~</span><span
          style="color:#000000;background-color:#ffffff;">$ psql -Aqt -c
          'select version()'
        </span><br>
        PostgreSQL 16.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian
        12.2.0-14) 12.2.0, 64-bit
        <br>
        <span
style="font-weight:bold;color:#54ff54;background-color:#ffffff;">postgres@smadb</span><span
          style="color:#000000;background-color:#ffffff;">:</span><span
style="font-weight:bold;color:#5454ff;background-color:#ffffff;">~</span><span
          style="color:#000000;background-color:#ffffff;">$ </span><br>
        <br>
        <br>
      </span></p>
  </body>
</html>

--------------SmTrZjW8ALyglbZIXzDUa2xI--