Re: SSL timeout?

Jan-Peter Seifert wrote:
> Hello,

Hi,

Could you please try the drivers on testing for 8.4.0101 at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

regards,
Hiroshi Inoue

> there's a problem with running some of our update-scripts via a SSL-encrypted connection. Our application is submitting the sql queries within transactions via psqlODBC.
> Everything is fine as long as no encryption is used. However, as soon as ssl is being used the connection seems to close on transactions / commands taking longer than about 10 minutes:
>
> This is the application's log for the part that fails:
>
> [SQL 23.11.2009 14:41:01] [5300] BEGIN;
> [SQL 23.11.2009 14:41:01] [5300] SELECT pg_class.oid FROM pg_catalog.pg_class JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid WHERE relname='tabelle' AND pg_namespace.nspname = current_schema()
> [SQL 23.11.2009 14:41:01] [5300] SELECT a.atttypid, t.typname, d.adsrc FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_type t ON a.atttypid=t.oid LEFT OUTER JOIN pg_catalog.pg_attrdef d ON (a.attnum=d.adnum AND a.attrelid=d.adrelid) WHERE a.attrelid=109253095 AND a.attname='spalte';
> [SQL 23.11.2009 14:41:01] [5300] SELECT atttypmod FROM pg_catalog.pg_attribute WHERE attname='spalte' AND attrelid=109253095;
> [SQL 23.11.2009 14:41:01] [5300] SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attname='spalte' AND attrelid=109253095;
> [SQL 23.11.2009 14:41:01] [5300] SELECT COUNT(*) FROM tabelle;
> [SQL 23.11.2009 14:41:01] [5300] CREATE TABLE tmp_mc_NUXKDB WITH OIDS AS SELECT * FROM tabelle;
> [SQL 23.11.2009 14:41:54] [5300] DELETE FROM tabelle;
> [SQL 23.11.2009 14:43:25] [5300] UPDATE pg_catalog.pg_attribute SET atttypmod=18 WHERE attname='spalte' AND attrelid=109253095;
> [SQL 23.11.2009 14:43:25] [5300] INSERT INTO tabelle SELECT * FROM tmp_mc_NUXKDB;
> [SQL 23.11.2009 14:51:41] [5300] ROLLBACK WORK;
>
> The first error message is:
> ODBC-Error - SQLExecDirect/ExecuteSQL: SQLSTATE = 42883 Native Error = 26 Error Message: No response from the backend; No response from the backend
>
> The rollback fails because of the closed connection.
>
> During the transaction in question a pretty large table ( 1.500.000 rows,
> 67633 relpages ) is being written to a temporary table, dropped, recreated and filled with the data from the temporary table. It seems that the connection closes on the INSERT command from the temporary table. The pgAdmin status window still shows this insert after the error message.
>
> The app's code is pretty old, but it seems that this behaviour with ssl causes problems elsewhere as well.
>
> * your ODBC driver version
> 08.04.0100 on Windows (seems to happen on earlier versions as well: 8.2.300, 8.3.400 ...)
>
> * your PostgreSQL database version
> v8.3.8 Windows and Linux
>
> Thank you very much,
>
> Peter
>
> --------------------------------------------------