Обсуждение: [GENERAL] ERROR: canceling statement due to statement timeout

On 01/11/2017 04:08 PM, Patrick B wrote:
> Hi guys,
>
> I'm using PostgreSQL 9.2 in two different servers.
>
> server1 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) -
> RAID 10 Magnetic disks
> server2 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) -
> EBS (AWS) io2 10k IOPS
>
> When I run a query, I get this error:
>
> ERROR: canceling statement due to statement timeout
>
> statement_timeout is 0 in both servers.
>
> However, on server1 I am able to run the query. Only on server2 that I
> get that error.
>
> Why? If it is same DB???

It is not the same DB if it is on two different servers not connected by
replication. More to the point statement_timeout is a client connection
setting, so is the client you use to connect to server2 the same as the
one you use for server1?

Is AWS being 'helpful' and setting a timeout?

Is there anything in the log before the ERROR shown above that indicates
something is setting statement_timeout?

>
> Patrick


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/11/2017 04:31 PM, Patrick B wrote:
> 2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
>     On 01/11/2017 04:08 PM, Patrick B wrote:
>
>         Hi guys,
>
>         I'm using PostgreSQL 9.2 in two different servers.
>
>         server1 (Master Postgres DB server, running Postgres 9.2 / 128GB
>         ram) -
>         RAID 10 Magnetic disks
>         server2 (Master Postgres DB server, running Postgres 9.2 / 128GB
>         ram) -
>         EBS (AWS) io2 10k IOPS
>
>         When I run a query, I get this error:
>
>         ERROR: canceling statement due to statement timeout
>
>         statement_timeout is 0 in both servers.
>
>         However, on server1 I am able to run the query. Only on server2
>         that I
>         get that error.
>
>         Why? If it is same DB???
>
>
>     It is not the same DB if it is on two different servers not
>     connected by replication. More to the point statement_timeout is a
>     client connection setting, so is the client you use to connect to
>     server2 the same as the one you use for server1?
>
>     Is AWS being 'helpful' and setting a timeout?
>
>     Is there anything in the log before the ERROR shown above that
>     indicates something is setting statement_timeout?
>
>         <mailto:adrian.klaver@aklaver.com>
>
>
> Same database, different database servers; server1 is the old Master
> server and I'm using it to compare.
>
> It is not the client, because if I run the query manually using explain
> analyze i get the error:

Well that is a client also.

Are you sure there is not something in your AWS setup that is doing this?


>
>         live_db=> explain analyze
>
>         SELECT DISTINCT id0
>         FROM
>         (SELECT
>
>         [...]
>
>         ERROR:  canceling statement due to statement timeout
>
>
> just a remind that on server1 works, but on server2 it doesn't.

Server1 is not on AWS and server2 is, see above.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/11/2017 04:31 PM, Patrick B wrote:
> 2017-01-12 13:23 GMT+13:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>

>
>     It is not the same DB if it is on two different servers not
>     connected by replication. More to the point statement_timeout is a
>     client connection setting, so is the client you use to connect to
>     server2 the same as the one you use for server1?
>
>     Is AWS being 'helpful' and setting a timeout?
>
>     Is there anything in the log before the ERROR shown above that
>     indicates something is setting statement_timeout?
>
>         <mailto:adrian.klaver@aklaver.com>
>
>
> Same database, different database servers; server1 is the old Master
> server and I'm using it to compare.

Are you actually running 9.2 on AWS, as the lowest version I see is 9.3.

Might be helpful to run the below on server2:

select * from pg_settings where name='statement_timeout';

and see what source says:

https://www.postgresql.org/docs/9.2/static/view-pg-settings.html
>
> It is not the client, because if I run the query manually using explain
> analyze i get the error:
>
>         live_db=> explain analyze
>
>         SELECT DISTINCT id0
>         FROM
>         (SELECT
>
>         [...]
>
>         ERROR:  canceling statement due to statement timeout
>
>
> just a remind that on server1 works, but on server2 it doesn't.


--
Adrian Klaver
adrian.klaver@aklaver.com