Temp Table & Ignition Transactions / Sessions - "There is already an object named in the database"

Ignition
, ,
1

Database: MS Sql Server 2016
Ignition: 7.9.6

I have a query which uses a temporary table. If I try to run this query under different transactions repeatedly, I get an error that the object already exists.

Intuitively, I would have thought that temp tables would not be visible between different transactions, but this does not seem to be True.

I must run this same query in multiple clients and threads on each client. How can I do this?

Ex:

import threading
import sys
import time

import java

times = []

def do():
	try:
		q = 'create table #Results(num varchar(30))'
		s=time.time()
		t = system.db.beginTransaction(database='NDB',
			isolationLevel=system.db.READ_COMMITTED,
			timeout=5000
		)
		times.append(time.time()-s)
		_=system.db.runUpdateQuery(query=q, tx=t, database='NDB')
		system.db.closeTransaction(t)
	except java.lang.Exception, e:
		print(e.getCause().getMessage())

threads=[]
for i in range(20):
#	do()
	t=threading.Thread(target=do)
	t.start()
	threads.append(t)
for t in threads:
	t.join()

Output...

SQL error for "create table #Results(num varchar(30))": There is already an object named '#Results' in the database.

Note that this is inconsistent, hence the for loop. You may have to run a few times before the error pops up.

UPDATE:

After some research, it looks like SQL Server does things the following way: create a Connection, create zero or more Sessions using the Connection, create zero or more Transactions within each Session.

Temp tables are Session-scoped, which means that if Ignition is generating multiple Transactions within one Session and in each Transaction I am using the same temporary table name, I would end up creating the temp table in the first Transaction and then my create temp table statement in the next transaction would conflict with the existing object in the Session.

After a few runs of the code above, the error only seems to crop up in relation to some period of time between runs of the code which seems to correspond to the transaction timeout period. Might be something odd going on here...?

Table variables would be the next option, but they are batch-scoped and because I am using prepared statements with a ~2100 placeholder limit, I frequently have to cut up my queries into multiple batches and send them separately and a table variable would only be scoped for each of those batches.

2

Ignition creates a connection pool for each database connection you have. By default, the pool is fixed to 8 (JDBC) connections. Those JDBC connections likely map 1:1 with what MSSQL docs are calling ‘Sessions’ - so what’s probably happening is that you create a temp table on one session (by chance) then don’t borrow that same session from the pool for some period of time. Eventually you’ll get the same JDBC connection, and you’ll get the error.

I’m not an expert in MSSQL, but is there no way to explicitly discard the temporary table when you’re done with it? Or namespace it on whatever operation you’re doing - maybe name each table ${clientId}Results or something goofy like that?

3

To @PGriffith’s point, my typical flow for this type of thing would be:

1.) Create Temp Table
2.) Do Work requiring temp table
3.) Drop Temp Table
4.) Do any remaining work
5.) Return results

I am fully aware that when a session disconnects any Temporary tables created on that session are automatically dropped, but if your query is doing work following the need for the temp table this may free up the name for reuse.

I am unsure of the amount of data you are storing in the temp table but perhaps a table variable is a viable approach?

DECLARE @Results AS TABLE (num varchar(30))

Another option, might be to use a Global Temp Table and check if it exists or not, this is of course if the data format between sessions matches, which since you say it is the same query then perhaps this will work. You may need to restructure your query to use a MERGE statement so that you don’t end up with duplicated data or key conflicts. Once there are no sessions referencing the table it will be dropped.

IF OBJECT_ID('tempdb..##Results') IS NULL
     BEGIN
          CREATE TABLE ##Results(num varchar(30))
     END
4

Put these two statements together and you have a likely explanation. You are relying on SQL Server to automatically drop your temp tables on connection close. But Ignition re-uses connections in the pool, rarely closing them. You need to drop your temp tables yourself.

5

Thank you @pturmel and @PGriffith and @lrose. In my actual code, I do try to delete the temp table when I am done, but if an exception is thrown it is possible I never call the DROP TABLE statement.

If Ignition is doing the following every time I execute a query…

  • grab a connection from the pool
  • use my transaction id to “open” the transaction again and execute another batch of statements within it

…then my fear is that when the same temp table is referenced from multiple queries running at the same time, it is possible for one transaction to use a the temp table created by another transaction, because they happen to use the same Session.

Is this right / possible?

6

As I mentioned in my update, the table variable would be good but I have to spread out what I am doing across multiple requests to the database because of the ~2100 placeholder limit in JDBC prepared statements. Table variables are batch-scoped, so I would not be able to reference them in additional calls made for the same transaction.

The table name is currently the same, but the structure changes. I have a SQL builder and ORM-like interface, so that table objects in code can handle CRUD operations. To update the cache on the local client, I need to OUTPUT after inserts. I may have to link to databases I don’t control in the future or a new developer may take over the system and triggers may be added to some tables, so a separate SELECT statement is required following an INSERT…

CREATE TABLE #Results

INSERT INTO TargetTable
...VALUES...
OUTPUT INTO #Results

SELECT FROM #Results 
INNER JOIN TargetTable

Because of this, each #Results table needs to store columns specific to the table I am inserting into.

7

Your idea of using a random name looks like a good option.

One question I have about that is whether I will end up with a large number of temp tables sticking around if there are exceptions and the DROP TABLE statement cannot execute. Temp tables are destroyed when Sessions are destroyed, but does Ignition ever destroy these Sessions? Does it do it periodically, so that I would never have too many temp tables hanging around on SQL Server?

8

If you are worried about the Table not being dropped in case of an unexpected exception then wrap the query in a TRY CATCH.

CREATE TABLE #Results

BEGIN TRY
     INSERT INTO TargetTable
     ...VALUES...
     OUTPUT INTO #Results

     SELECT FROM #Results
     INNER JOIN TargetTable
END TRY
BEGIN CATCH
     --Any Error processing you want to do
     EXECUTE usp_GetErrorInfo
END CATCH

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
     DROP TABLE #Results

In this case if there is an exception the DROP will still be executed because you are “Handling” the error (with the exception of pretty specific cases such as compile errors). This would prevent accumulation of temp tables.

2 Likes
9

You could also put these two lines at the top of your existing script:

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

(:

10

Going with @PGriffith’s solution in combination with @lrose’s and @pturmel’s suggestion to check if the table exists and drop it. I am using a context manager in Python for all queries, so I use a regex to quickly extract any temp table names used for the transaction from all queries and when the transaction is being closed, check if each exists and delete it if it does.

The random UUID temp tables names will guarantee there is no funny business with the same temp table being used by multiple transactions sharing the same Session.