Re: Async queries pretending to be synchronous

On Fri, Mar 1, 2019 at 10:01 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:

> In investigating #3656 I found the initial problem to be that when running
> in a container, Gunicorn will kill the worker process if a thread doesn't
> respond for 30 seconds by default. I fixed that by making the timeout match
> the application session timeout, but it revealed another issue.
>
> Given the function below (from the ticket), if you open the query tool and
> run:
>
> SELECT 1; SELECT fails_after(30);
>
> the async query actually blocks for 30 seconds in the cur.execute() call
> in execute_async() in connection.py (line 968). This causes the entire app
> to hang (watch the dashboard requests pile up in pending state in the
> network tab of the browser dev tools).
>
> If you run just the second SELECT, it works as expected, as does running
> something like:
>
> SELECT 1; SELECT pg_sleep(30);
>
> Anyone have any idea what's going on?
>

Connection.poll() blocking the call here. ( connection.py - _wait_timeout
function line #1378 state = conn.poll() )
In the asynchronous connection, after executing the query, the conn.poll()
is being called to fetch the connection status.
It gives the status accordingly but in this case, it is blocking and not
giving the status.

> CREATE OR REPLACE FUNCTION fails_after(s integer)
> RETURNS text AS $$
> begin
> raise notice 'Entered';
> perform pg_sleep(s);
> raise '!!!';
> end;
> $$ LANGUAGE plpgsql;
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>