PostgreSQL function , query disappear after long time

Question

We are facing an issue in production environment using PostgreSQL v14, we have a function which is selecting datas from a table then aggregate these data and then insert these aggregated data in another table. At the end of the function we are updating a table with the time of execution of this function (thus we know that the function went to the end of the process).

We are executing this function or from dbeaver or from a java client

The problem : When having a large set of data to aggregate, it can take more than 1 hours, we noticed that if that take more than 1 hours, the query seems to disappear from the pg_stat_activity and we dont get any return on the client side (dbeaver or java) and nothing is commited .

If the set of data is small (taking less than one hour), then we see the query becoming "idle" and we can see that our table has been updated.

We speaked with the database administrator and he said that there is no timeout configured on the database and that we should check the network (all these activites are done under a private network of our client using a VPN).

If anyone already had a similar issue and can help it would be very helpful :). Thanks.

Answer 1

SQL sessions and database connections don't just vanish without a trace. You will get a response on the client side — if nothing else, at least a "server closed the connection unexpectedly". So the problem must be that your code doesn't check the result or the error message.

If there was an error (for example, because the statement ran into a timeout or was canceled by the client), you can find that error message in the PostgreSQL server log. So look there, and you might get a clue what is going on.

• Nadir Hafsaoui: I found this on the internet : cybertec-postgresql.com/en/… which seems to be yours :d (thanks for sharing). Do you think my problem can be related to the "Connections closed by a network component" part? Thanks
• Laurenz Albe: Sure, that's an option. But even then you'd get an error on the client side.