From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: pavol.sekeres8@gmail.com
Subject: BUG #17755: database queries get stuck for certain IDs
Date: Fri, 20 Jan 2023 10:26:48 +0000
Message-ID: <17755-e7333e310e686ae4@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17755
Logged by: Pavol Sekeres
Email address: pavol.sekeres8@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: CentOS Linux 7 (Core)
Description:
Hello,
we are using PostgreSQL version 9.6.8 on our production environment which we
operate ourselves. We are facing issue with the PostgreSQL database.
When we use select/insert query on a specific table it runs fine 99.99% of
the time. But we are doing many inserts per day from our app (around
100.000) and it happens to us almost every week that some
query to this table gets stuck. Then you can list it using pg_stat_activity
and see there that it is running long(hours, days, even weeks). From this
point the only option we have is to kill this process in which the query
runs.
Unless we do this, the database gets busy by this process infinitely running
query and the cpu usage rises dramatically causing database drop-outs.
The problem is just with this table and we are using many in our database.
Some further information is: this problematic table is called request_datas
and has just two columns : 1. request_id - is unlimited varchar containing
the string representation of uuid, 2. request_data - is again unlimited
varchar containing string representation of JSON object
We noticed that the insert/select queries which get stuck and run infinitely
have specific request_id - starting with 'a6bda' for example
'a6bda748-0e31-4da6-8fed-331c6c4a7cdb'.
Also we are not always querying the database directly but we are using java
app with JDBC connections. However the select query gets stuck even when
triggered from the psql console.
From our point of view we are using the database normally and there is no
reason why simple select query where id is equal to some value should get
stuck and run infinitely.
Do you know what could be the cause for our problems and possibly suggest
any solution?
Thank you,
Best regards,
Pavol
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, pavol.sekeres8@gmail.com
Subject: Re: BUG #17755: database queries get stuck for certain IDs
In-Reply-To: <17755-e7333e310e686ae4@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox