Subject: during call SQLFreeHandle, the connection is broken. client can't be notified.

From: "LiChong" <[email-masked]>
Date: Tue, 5 Dec 2023 20:37:19 +0800

你好

事务场景下，SQLFreeHandle调用进行中，连接断开，但是SQLFreeHandle返回SQL_SUCCESS。由于SQLFreeHandle调用连接断开，触发cc_on_abort，将事务取消，在客户端执行commit时，没有与服务端交互并且返回成功，导致客户端以为数据已提交，导致丢数据。

In a transaction scenario, the connection is disconnected while the SQLFreeHandle call is in progress, but SQLFreeHandle returns SQL_SUCCESS. Because the connection is disconnected by the SQLFreeHandle call, the cc_on_abort is triggered, the transaction is canceled, and the client performs a commit without interacting with the server and returning success, causing the client to think that the data has been submitted, resulting in data loss.

call step:
SQLAllocHandle1
sql1
SQLFreeHandle1
SQLAllocHandle2
sql2
SQLFreeHandle2
commit

during&nbsp;call SQLFreeHandle2， the connection&nbsp;is broken. client can't&nbsp;be&nbsp;notified.
we modify the code, in statement.c&nbsp;SC_Destructor and PGAPI_FreeStmt,&nbsp;&nbsp;Is there any other better solution to this problem? Please share your thoughts and suggestions, thanks.

below is psqlodbc log, debug=2
--------------
[7f5843a3a700]&nbsp; &nbsp; bind.c[PDATA_free_params]689: leaving
[7f5843a3a700] results.c[PGAPI_MoreResults]2035: leaving 100
[7f5843a3a700]odbcapi30.[SQLFreeHandle]250: Entering
[7f5843a3a700]statement.[PGAPI_FreeStmt]248: entering...hstmt=0x7f583c044600, fOption=1
[7f5843a3a700] qresult.c[QR_Destructor]344: entering
[7f5843a3a700]statement.[SC_init_Result]536: leaving(0x7f583c044600)
[7f5843a3a700]statement.[SC_Destructor]483: entering self=0x7f583c044600, self-&gt;result=(nil), self-&gt;hdbc=0xe27cc0
[7f5843a3a700]connection[CC_send_query_append]1809: conn=0xe27cc0, query='DEALLOCATE "_PLAN0x7f583c044600"'
[7f5843a3a700]connection[CC_send_query_append]1891: query_len=32
[7f5843a3a700]connection[CC_send_query_append]1935: [QLOG]PQsendQuery: 0xdf4990 'SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x7f583c044600";RELEASE _per_query_svp_'
[7f5843a3a700] qresult.c[QR_Constructor]173: entering
[7f5843a3a700] qresult.c[QR_Constructor]242: leaving 0x7f583c030380
[7f5843a3a700]connection[handle_pgres_error]864: PG_DIAG_SEVERITY_NONLOCALIZED=(null)
[7f5843a3a700]connection[handle_pgres_error]883: [QLOG]	((null)) (null) '(null)'
[7f5843a3a700]connection[handle_pgres_error]950: error message=server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
(137)
[7f5843a3a700]connection[CC_on_abort]1582: entering opt=2
[7f5843a3a700]connection[CC_on_abort]1604: [QLOG]PQfinish: 0xdf4990
[7f5843a3a700] qresult.c[QR_Destructor]344: entering
[7f5843a3a700] qresult.c[QR_close_result]255: entering
[7f5843a3a700] qresult.c[QR_free_memory]479: entering fcount=0
[7f5843a3a700] qresult.c[QR_free_memory]577: leaving
[7f5843a3a700] qresult.c[QR_close_result]319: leaving
[7f5843a3a700] qresult.c[QR_Destructor]348: leaving
[7f5843a3a700]&nbsp; &nbsp; bind.c[APD_free_params]643: entering self=0x7f583c0447e0
[7f5843a3a700]&nbsp; &nbsp; bind.c[APD_free_params]655: leaving
[7f5843a3a700]&nbsp; &nbsp; bind.c[IPD_free_params]698: entering self=0x7f583c044850
[7f5843a3a700]&nbsp; &nbsp; bind.c[IPD_free_params]709: leaving
[7f5843a3a700]&nbsp; &nbsp; bind.c[PDATA_free_params]663: entering self=0x7f583c044948
[7f5843a3a700]&nbsp; &nbsp; bind.c[PDATA_free_params]689: leaving
[7f5843a3a700]statement.[SC_Destructor]526: leaving
[7f5843a3a700]odbcapi30.[SQLEndTran]171: Entering
[7f5843a3a700] execute.c[PGAPI_Transact]1208: entering hdbc=0xe27cc0, henv=(nil)
[7f5843a3a700]odbcapi30.[SQLAllocHandle]40: Entering
[7f5843a3a700]statement.[PGAPI_AllocStmt]189: entering...

Best Wishes

LiChong
[email-masked]

&nbsp;

---

From: Dave Cramer <[email-masked]>
Date: Tue, 5 Dec 2023 16:17:48 -0500

Hi LiChong,

Attached is a patch which is the diff between your file and the current
master

There are some differences at the bottom of the file that seem to indicate
that your version of the source is older than the current master. Can you
confirm?

Also can you send back your changes in a patch file.

I used git diff to produce the attached patch
Dave Cramer
www.postgres.rocks

On Tue, 5 Dec 2023 at 10:05, LiChong <[email-masked]> wrote:

> In a transaction scenario, the connection is disconnected while the
> SQLFreeHandle call is in progress, but SQLFreeHandle returns SQL_SUCCESS.
> Because the connection is disconnected by the SQLFreeHandle call, the
> cc_on_abort is triggered, the transaction is canceled, and the client
> performs a commit without interacting with the server and returning
> success, causing the client to think that the data has been submitted,
> resulting in data loss.
>
> call step:
> SQLAllocHandle1
> sql1
> SQLFreeHandle1
> SQLAllocHandle2
> sql2
> SQLFreeHandle2
> commit
>
> during call SQLFreeHandle2， the connection is broken. client can't be
> notified.
> we modify the code, in statement.c SC_Destructor and PGAPI_FreeStmt,  Is
> there any other better solution to this problem? Please share your thoughts
> and suggestions, thanks.
>
>
> [7f5843a3a700]    bind.c[PDATA_free_params]689: leaving
> [7f5843a3a700]statement.[PGAPI_FreeStmt]248:
> entering...hstmt=0x7f583c044600, fOption=1
> [7f5843a3a700]statement.[SC_Destructor]483: entering self=0x7f583c044600,
> self->result=(nil), self->hdbc=0xe27cc0
> [7f5843a3a700]connection[CC_send_query_append]1935: [QLOG]PQsendQuery:
> 0xdf4990 'SAVEPOINT _per_query_svp_;DEALLOCATE
> "_PLAN0x7f583c044600";RELEASE _per_query_svp_'
> [7f5843a3a700]    bind.c[APD_free_params]643: entering self=0x7f583c0447e0
> [7f5843a3a700]    bind.c[APD_free_params]655: leaving
> [7f5843a3a700]    bind.c[IPD_free_params]698: entering self=0x7f583c044850
> [7f5843a3a700]    bind.c[IPD_free_params]709: leaving
> [7f5843a3a700]    bind.c[PDATA_free_params]663: entering
> self=0x7f583c044948
> [7f5843a3a700]    bind.c[PDATA_free_params]689: leaving
> ------------------------------
> LiChong
> [email-masked]
>
> <https://wx.mail.qq.com/home/index?t=readmail_businesscard_midpage&nocheck=true&name=LiChong&icon=http%3A%2F%2Fthirdqq.qlogo.cn%2Fg%3Fb%3Doidb%26k%3DibNo29G3ctrZB4g6CruEbPQ%26kti%3DZJFUfQAAAAI%26s%3D100%26t%3D1495360545&mail=527474735%40qq.com&code=cQgX3D3WeDCWQvmPtMFPlLDHUxugkIV4ZJL13Kp6JfYG7YRIxonT-I3oXnDYS0pagV-5F-_UWTWE_F7HuTKQjg>
>
>