Обсуждение: Time-Out Issue
Hi,
In PostgreSQL 9.3.2, is there a default time-out for running processes (specifically a view via a data connection) that take a long time? If so, how do I bump it up? My data connection (Devart) keeps returning time-out errors from PostgreSQL for views that must filter and return massive datasets. Smaller datasets it has no problem with.
The data connection I’m using has a timeout property but that deals only with how long it waits in trying to “establish” a connection to the database.
I’m thinking that there is a default time-out value in PostgreSQL that determines how long a process is allowed to run before it’s considered a runaway process and is terminated by PostgreSQL.
Thanks,
Chris
Chris Campbell <ccampbell@cascadeds.com> wrote: > I’m thinking that there is a default time-out value in PostgreSQL > that determines how long a process is allowed to run before it’s > considered a runaway process and is terminated by PostgreSQL. There is the statement_timeout setting, but the default is zero, which means "no limit". Perhaps your configuration or software is setting to something else? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
-----Original Message----- From: Kevin Grittner [mailto:kgrittn@ymail.com] Sent: Wednesday, January 08, 2014 11:44 AM To: Chris Campbell; pgsql-novice@postgresql.org Subject: Re: [NOVICE] Time-Out Issue Chris Campbell <ccampbell@cascadeds.com> wrote: > I’m thinking that there is a default time-out value in PostgreSQL that > determines how long a process is allowed to run before it’s considered > a runaway process and is terminated by PostgreSQL. There is the statement_timeout setting, but the default is zero, which means "no limit". Perhaps your configuration or softwareis setting to something else? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company Okay I will look into that. Thank you for your reply. Chris
Chris Campbell <ccampbell@cascadeds.com> writes:
> In PostgreSQL 9.3.2, is there a default time-out for running processes (specifically a view via a data connection)
thattake a long time?
No. There is the statement_timeout parameter, but it's not enabled by
default; and anyway if you were hitting a statement timeout you'd be
getting a pretty specific error message, like this:
ERROR: canceling statement due to statement timeout
If so, how do I bump it up? My data connection (Devart) keeps returning time-out errors from PostgreSQL for views
thatmust filter and return massive datasets. Smaller datasets it has no problem with.
Why do you say the error is "from PostgreSQL"? It seems rather more
likely that this is some kind of client-side timeout.
If the query requires a great deal of server think-time before it sends
any rows, it's possible that you are hitting a network connection timeout.
NAT routers in particular often drop "idle" connections after a few
minutes. That breaks all sorts of RFC rules, but they do it anyway.
You can usually work around network issues by setting aggressive TCP
keepalive parameters, either at the server or client end, to keep some
traffic flowing on the network link.
I don't know what Devart is, but another possibility is that it has got
some idea of a query timeout.
regards, tom lane
-----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, January 08, 2014 12:02 PM To: Chris Campbell Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Time-Out Issue Chris Campbell <ccampbell@cascadeds.com> writes: >> In PostgreSQL 9.3.2, is there a default time-out for running processes (specifically a view via a data connection) thattake a long time? >Why do you say the error is "from PostgreSQL"? It seems rather more likely that this is some kind of client-side timeout. >If the query requires a great deal of server think-time before it sends any rows, it's possible that you are hitting a networkconnection timeout. >NAT routers in particular often drop "idle" connections after a few minutes. That breaks all sorts of RFC rules, but theydo it anyway. >You can usually work around network issues by setting aggressive TCP keepalive parameters, either at the server or clientend, to keep some traffic flowing on the network link. >I don't know what Devart is, but another possibility is that it has got some idea of a query timeout. Hey, Tom. Thanks for your reply. I don't actually "know" that the message is being returned from PostgreSQL, technically speaking the data connection is raisingthe message but the data connection is getting its information from somewhere. I'm simply going through a processof elimination to get to the bottom of this issue. I've already confirmed that the "statement_timeout" value = 0per Kevin's suggestion. Devart has a 3rd party control (dotConnect) that I use in my .net environment. As I mentioned, the Devart control has nosetting for determining how long a process can execute, only how long it tries to establish a connect before giving up. I will have the local Network Admin. look into the networking settings you described. Thanks so much for your help. I really appreciate it. Chris.