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

Hello,

I would like to add one more info. We are using database replication and
this issue is happening only on primary server. If we try select on our
stand by copy (exact replica of our database) the query doesn't get stuck
and runs quickly.

Thank you,
Best regards,
Pavol

On Fri, Jan 20, 2023 at 11:27 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17755
> Logged by: Pavol Sekeres
> Email address: pavol(dot)sekeres8(at)gmail(dot)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
>
>