1

I have the following simple Postgres stored procedure created that just sleeps a number of minutes as passed in to call:

create procedure sleep_test(delay_minutes numeric)
    language plpgsql
as
$$
BEGIN
    RAISE NOTICE 'Starting procedure. Sleeping for % minutes...', delay_minutes;
    PERFORM pg_sleep(delay_minutes * 60); -- Function call is in seconds
    RAISE NOTICE 'Finished sleeping.';

END;
$$;

Running the procedure for four minutes:

call sleep_test(4)

If I run the the procedure for four minutes in a worksheet, all works fine. The procedure runs (has state=='active' in pg_stat_activity) for 4 minutes and returns back to the calling worksheet:

Running the procedure for five minutes:

call sleep_test(5)

If I run it for 5 minutes or more, though, the process runs for 5 minutes (has state=='active' in pg_stat_activity), but after five minutes it goes into an "idle" state in pg_stat_activity and does not return back to the calling worksheet. From the worksheet's perspective it thinks the procedure is still running (indefinitely).

DB Settings (in case they are useful):

server_version == 12.22

statement_timeout == 0

idle_in_transaction_session_timeout==1d

Clients used to test - same issue with both:

Datagrip 2025.1 Query Console

Python 3.9 script using psycopg2 2.9.9

Database server config:

It is an AWS Postgres RDS database. Sorry... I am not familiar with where to exactly find OS and version in AWS.

Improve this question
14
  • Can you tag the software that hosts/handles these worksheets, along with its timeout settings? My guess would be that it defines its own limits and enforces them independently from the db. Commented Sep 23 at 17:49
  • Also add the OS and it's version to the question text. FYI, idle_in_transaction_session_timeout==1d is not a good idea. Commented Sep 23 at 18:04
  • @Zegarek: I updated the question. Basically ran into the error within my Python script on a more complex procedure that sometimes takes under 5 minutes to run and sometimes over 5 minutes to run. Wrote the "sleep_test" function to prove that it's not locking or getting locked on anything and it just seems to be a timeout of some sort. Commented Sep 23 at 19:06
  • @AdrianKlaver : Added the database server config section in my question. Not sure where to exactly find these details since it's an AWS Postgres database instance. And I agree that idle_in_transaction_session_timeout==1d is not the best, but this is a database I inherited. Commented Sep 23 at 19:07
  • 1
    I don't think that this is a server problem. The server runs its course, then goes idle. It seems pretty clear that the client just stopped waiting for the server, but didn't cancel the query (else you would find an error in the PostgreSQL log). This is a client software bug. Commented Sep 23 at 21:02
Related questions
1869
Insert results of a stored procedure into a temporary table
1160
Search text in stored procedure in SQL Server
1023
Function vs. Stored Procedure in SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.