MIME-Version: 1.0
From: Winner Lohas <lohaswinner@gmail.com>
Date: Wed, 13 Mar 2024 05:04:35 +0900
Message-ID: 
 <CAED_kjmh0qyCnPwmOg5f285uH8bDahPg42sHDu=vqOWmzKyH6w@mail.gmail.com>
Subject: Does ODBC driver connection pooling have a bug?
To: pgsql-odbc@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007c5dfd06137c2b52"
Archived-At: 
 <https://www.postgresql.org/message-id/CAED_kjmh0qyCnPwmOg5f285uH8bDahPg42sHDu%3DvqOWmzKyH6w%40mail.gmail.com>
Precedence: bulk

--0000000000007c5dfd06137c2b52
Content-Type: text/plain; charset="UTF-8"

Hello.

I was using the ODBC driver and found that the problem occurred in a
temporary table.

However, when I worked with C# with Npgsql (PostgreSQL .NET driver), there
were no problems even though connection pooling was working. When a
connection is reused internally, DISCARD ALL is done first. So the
temporary table is also automatically dropped.

I also tested MySQL, and the MySQL ODBC driver worked without problem.
However, looking at the network protocol, it seems like the authentication
process is almost done again rather than a connection reset. The process
took quite a long time because it seemed that only the TCP connection was
being reused rather than FIN. What's interesting is that the ODBC data
source manager is installed with pooling turned off by default.

The default setting of MS SQL Server's ODBC driver is to use pooling, and
it works well with or without pooling. Internally, sp_reset_connection is
executed to initialize reused connections.

Regards.
Wooseung Kim

--0000000000007c5dfd06137c2b52
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div>Hello.</div><div><br></div><div>I wa=
s using the ODBC driver and found that the problem occurred in a temporary =
table.</div><div><br></div><div>However, when I worked with C# with Npgsql =
(PostgreSQL .NET driver), there were no problems even though connection poo=
ling was working. When a connection is reused internally, DISCARD ALL is do=
ne first. So the temporary table is also automatically dropped.</div><div><=
br></div><div>I also tested MySQL, and the MySQL ODBC driver worked without=
 problem. However, looking at the network protocol, it seems like the authe=
ntication process is almost done again rather than a connection reset. The =
process took quite a long time because it seemed that only the TCP connecti=
on was being reused rather than FIN. What&#39;s interesting is that the ODB=
C data source manager is installed with pooling turned off by default.</div=
><div><br></div><div>The default setting of MS SQL Server&#39;s ODBC driver=
 is to use pooling, and it works well with or without pooling. Internally, =
sp_reset_connection is executed to initialize reused connections.<br><br>Re=
gards.</div><div>Wooseung Kim</div></div></div>

--0000000000007c5dfd06137c2b52--