timeout failing

[dant4z]

Hi, i have some trouble.

I have query that returns 789 rows:

SELECT "brand_id", "brand_name" FROM "brands" GROUP BY "brand_id", "brand_name" ORDER BY "brand_name" ASC;
Execution Time: 2.558 ms

But sometimes i have suspension this query and failing by timeout.
In postgres in pg_stat_activity i see:

=# select state, query, now() - query_start as duration from pg_stat_activity order by duration desc limit 1;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------
state | idle
query | SELECT "brand_id", "brand_name" FROM "brands" GROUP BY "brand_id", "brand_name" ORDER BY "brand_name" ASC
duration | 00:00:57.017424

According to http://www.postgresql.org/docs/9.4/static/monitoring-stats.html
query: "Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed."
state = idle: The backend is waiting for a new client command.

That means that query was executed and postgres is waiting for a new nginx command. Then timeout (1 minute) expires and nginx returns 500 with timeout.

config:
upstream database {
postgres_server xxxxxxxxxxx;
postgres_keepalive off;
}
..................
.................
server {
location = /query {
internal;
postgres_pass database;
postgres_connect_timeout 30s;
postgres_result_timeout 1m;
postgres_query $echo_request_body;
}
}

Also i have similarly query (returns 928 rows) that also sometimes suspending.
I noticed that hang queries that return many rows.

Please help me to understand, that's going on.
Thanks.

[agentzh]

@dant4z What versions of ngx_postgres and libpq are you using? If you're not using the latest, please try upgrading first.

[agentzh]

@dant4z Also you may want to look at this ticket: #12

[dant4z]

I'm using libpq 9.4.4-1, and ngx_postgres 1.0rc6. Also i saw this problem at 1.0rc5.

I'm using latest openresty 1.7.10.2, that includes ngx_postgres 1.0rc6

[agentzh]

@dant4z Will you try the patch for libpq in #12 ? Not sure if it is it. Still worth trying anyway.

[istr]

I have the same issue with openresty 1.9.7.3 (includes ngx_postgres-1.0rc7) and libpq-9.3.11.

Will try the mentioned patch, too.