Cursor timeout in postgres

[Tim Tassonis]

Hi all

When examining strange behaviour in one of my programs I found out that
I must have somehow gotten into a timeout situation when fetching rows
from a cursor. My program read the first row, did some stuff for six
minutes and then tried to fetch the second row, which failed. The
connection however was still alive and further database access in the
program worked m'kay.

While I think the responsible component made a good decision to timeout
after 5 minutes of inactivity, I still wonder who bit me.


My program was written in php, using the odbc interface and the odbc
driver from postgres compiled against 8.1.5 libpg, accessing a 8.1.5
database (all on the same machine, on linux 32bit). I have not perfomed
any relevant tweaking in the config files (apart from php.ini).

Has anybody got the quick answer?

Tim

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Tom Lane]

Post by Tim Tassonis
When examining strange behaviour in one of my programs I found out that
I must have somehow gotten into a timeout situation when fetching rows
from a cursor. My program read the first row, did some stuff for six
minutes and then tried to fetch the second row, which failed. The
connection however was still alive and further database access in the
program worked m'kay.

Failed how, exactly? Did anything show up in the postmaster log?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

[Tim Tassonis]

Hi Tom

Post by Tom Lane

Post by Tim Tassonis
When examining strange behaviour in one of my programs I found out that
I must have somehow gotten into a timeout situation when fetching rows
from a cursor. My program read the first row, did some stuff for six
minutes and then tried to fetch the second row, which failed. The
connection however was still alive and further database access in the
program worked m'kay.

Failed how, exactly? Did anything show up in the postmaster log?

Forgot to check that, sorry. From my program's point of view, it failed
silently. When an odbc_exec() succeeds, one generally just loops over
the rows by odbc_fetch_row(), until it fails. The fetch might have
returned a message, but this was not checked, as odbc_fetch_row() is
bound to fail anyway when no more rows are present.

I try to reproduce the situation tomorrow and will also check on any
odbc_errmsg() messages and the postmaster log.

Bye
Tim


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by Tim Tassonis
I try to reproduce the situation tomorrow and will also check on any
odbc_errmsg() messages and the postmaster log.

OK. A couple of comments: the only timeout within Postgres itself is
statement_timeout, which I think wouldn't apply to your situation even
if you had it set (but I might be wrong). We have also heard reports
of firewalls dropping idle connections, but in that scenario you'd
not still have a working connection afterwards.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match