IBM Support

Why does one run into temporary table already exists errors while using Netezza oledb driver through two different sessions

Question & Answer


Question

Why does one run into temporary table already exists errors while using Netezza oledb driver through two different sessions

Cause

If different sessions create the same temporary table at the same time or immediately after one another they should succeed. This does not happen when a customer runs two different sessions using the oledb driver with connection pooling turned on. The back end postgres continues to use the same postgres session until the connection times out due to no activity after 1 minute of idle time. The oledb driver does not automatically send a disconnectt to postgres though a close is issued by the application. Instead the oledb driver continues to use the previously opened connection to save connection resources. For more details on connection pooling as defined by the protocol:

http://dcx.sybase.com/1200/en/dbprogramming/pg-oledb-oledb-conn-pool.html .
This connection pooling feature is enabled by default.

Reproduction of issues in Aginity
----------------------------------------------

Scenario 1 using OLEDB ( waited between the two attempts in the same Aginity connection)
----------------------------------------------------------------------------------------------------------------
[nz@NZ80641-H1 postgres]$ grep "\[10052\]" pg.log
2012-10-31 17:18:57.090784 EDT [10052] DEBUG: connection: host=9.31.99.206 user=ADMIN database=RAGHU_TEST
2012-10-31 17:18:57.093521 EDT [10052] DEBUG: QUERY: set nz_encoding to 'utf8'
2012-10-31 17:18:57.094506 EDT [10052] DEBUG: QUERY: set DateStyle to 'ISO'
2012-10-31 17:18:57.095602 EDT [10052] DEBUG: QUERY: select version(), 'OLE-DB Client Version: Release 6.0.5 (P-10) [Build 24823]', '64-bit', 'OS Platform: Windows', 'OS Username: pbprakhy'
2012-10-31 17:18:57.097019 EDT [10052] DEBUG: QUERY: select feature from _v_oledb_feature
2012-10-31 17:18:57.102854 EDT [10052] DEBUG: QUERY: select identifier_case, current_catalog, current_user
2012-10-31 17:18:57.105171 EDT [10052] DEBUG: QUERY: select current_sid
2012-10-31 17:18:57.116743 EDT [10052] DEBUG: QUERY: create temporary table temp_test1 as select * from test1
2012-10-31 17:18:57.130082 EDT [10052] DEBUG: UpdateStatsInsertDummy called on table 'TEMP_TEST1'
2012-10-31 17:20:03.945255 EDT [10052] DEBUG: disconnect: host=9.31.99.206 user=ADMIN database=RAGHU_TEST

2012-10-31 17:20:25.050414 EDT [10520] DEBUG: connection: host=9.31.99.206 user=ADMIN database=RAGHU_TEST
2012-10-31 17:20:25.053099 EDT [10520] DEBUG: QUERY: set nz_encoding to 'utf8'
2012-10-31 17:20:25.054173 EDT [10520] DEBUG: QUERY: set DateStyle to 'ISO'
2012-10-31 17:20:25.055357 EDT [10520] DEBUG: QUERY: select version(), 'OLE-DB Client Version: Release 6.0.5 (P-10) [Build 24823]', '64-bit', 'OS Platform: Windows', 'OS Username: pbprakhy'
2012-10-31 17:20:25.057005 EDT [10520] DEBUG: QUERY: select feature from _v_oledb_feature
2012-10-31 17:20:25.063025 EDT [10520] DEBUG: QUERY: select identifier_case, current_catalog, current_user
2012-10-31 17:20:25.065194 EDT [10520] DEBUG: QUERY: select current_sid
2012-10-31 17:20:25.069440 EDT [10520] DEBUG: QUERY: create temporary table temp_test1 as select * from test1
2012-10-31 17:20:25.079642 EDT [10520] DEBUG: UpdateStatsInsertDummy called on table 'TEMP_TEST1'
2012-10-31 17:21:33.950268 EDT [10520] DEBUG: disconnect: host=9.31.99.206 user=ADMIN database=RAGHU_TEST

Scenario 2 using OLEDB ( Did not wait in the same Aginity connection)
-----------------------------------------------------------------------------
2012-10-31 17:26:20.181734 EDT [12612] DEBUG: connection: host=9.31.99.206 user=ADMIN database=RAGHU_TEST
2012-10-31 17:26:20.185514 EDT [12612] DEBUG: QUERY: set nz_encoding to 'utf8'
2012-10-31 17:26:20.186929 EDT [12612] DEBUG: QUERY: set DateStyle to 'ISO'
2012-10-31 17:26:20.188497 EDT [12612] DEBUG: QUERY: select version(), 'OLE-DB Client Version: Release 6.0.5 (P-10) [Build 24823]', '64-bit', 'OS Platform: Windows', 'OS Username: pbprakhy'
2012-10-31 17:26:20.190257 EDT [12612] DEBUG: QUERY: select feature from _v_oledb_feature
2012-10-31 17:26:20.197276 EDT [12612] DEBUG: QUERY: select identifier_case, current_catalog, current_user
2012-10-31 17:26:20.199926 EDT [12612] DEBUG: QUERY: select current_sid
2012-10-31 17:26:20.210340 EDT [12612] DEBUG: QUERY: create temporary table temp_test1 as select * from test1
2012-10-31 17:26:20.225582 EDT [12612] DEBUG: UpdateStatsInsertDummy called on table 'TEMP_TEST1'
2012-10-31 17:26:21.483784 EDT [12612] DEBUG: QUERY: select current_sid
2012-10-31 17:26:21.487111 EDT [12612] DEBUG: QUERY: create temporary table temp_test1 as select * from test1
2012-10-31 17:26:21.487802 EDT [12612] ERROR: Relation 'TEMP_TEST1' already exists
2012-10-31 17:26:48.479944 EDT [12612] DEBUG: QUERY: select current_sid
2012-10-31 17:26:48.484107 EDT [12612] DEBUG: QUERY: create temporary table temp_test1 as select * from test1
2012-10-31 17:26:48.484614 EDT [12612] ERROR: Relation 'TEMP_TEST1' already exists

Answer

Disable the connection pooling feature by using the option "DB Services=-4" (which means to disable connection pooling and transaction enlistment) at the time the connection is opened in the application.
This can be done in Aginity by selecting Tools -> Options, and changing the default services for OLE-DB. Select "All services except pooling and auto enlistment", Disconnect and reconnect..

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
17 October 2019

UID

swg21620434

Page Feedback