Thread: BUG #17755: database queries get stuck for certain IDs

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

PG Bug reporting form <noreply@postgresql.org> writes:
> we are using PostgreSQL version 9.6.8 on our production environment which we
> operate ourselves.

You realize, I hope, that you are running a very old minor release
of a branch that's been out of support entirely for more than a year.
You might consider consulting our release note archives

https://www.postgresql.org/docs/release/

to see if your problem matches up with any of the large number of
post-9.6.8 fixes.

> 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'.

Smells a little bit like index corruption, perhaps.  I'd try reindexing
whatever index(es) exist on that column.  But if that doesn't fix it ...

> Do you know what could be the cause for our problems and possibly suggest
> any solution?

Running ancient minor releases is fundamentally unsound database
administration practice.  At minimum you should be running the last
9.6.x minor release (9.6.24); but really now would be a good time
to be thinking about upgrading to some still-in-support branch.
If there is an actual Postgres problem here, and it wasn't fixed
by 9.6.24, we aren't ever going to fix it in that branch.

            regards, tom lane