Workaround for T-SQL global temporary tables in Babelfish for Aurora PostgreSQL

When customers migrate from commercial databases SQL Server to Babelfish for Aurora PostgreSQL, currently the T-SQL global temporary tables (##table_name) are not supported. Babelfish for Aurora PostgreSQL currently only supports local temporary tables, which are dropped after the session ends and do not allow cross-session data access. Review your code to see if you can use local temporary tables instead of global temporary tables. If not, refer to this post where we cover how to implement T-SQL global temporary table behavior in Babelfish for Aurora PostgreSQL using permanent table.

This workaround involves changing existing T-SQL code that utilizes global temporary tables. However, it would not provide all the capabilities of global temporary tables. In particular, the automatic drop behavior of global temporary tables is not replicated. This would need to be handled manually, for example by adding explicit drop statements.

Overview of T-SQL global temporary tables

Before we dive into the solution, let’s first discuss what a T-SQL global temporary table is. In SQL Server, temporary tables are used to store and process intermediate data. There are two types of temp tables: local and global. A local temporary table (#local_table) is only visible to the connection that created it; a global temp table is visible across all connections and can even be used in other sessions. T-SQL Global temporary tables are automatically dropped when the session that created the table ends and the last active T-SQL statement (not session) referencing the table in other sessions ends.

A T-SQL global temporary table has a table name prefixed with a double hash sign (##table_name). There are two ways to create a global temporary table:

Use CREATE statement:
```
CREATE TABLE ##tmp (<column names>);
```
Use SELECT INTO, where you create a temporary table from an existing permanent table:
```
SELECT Column1,Column2...ColumnN INTO ##tmp FROM Source_Table WHERE Condition ;
```

The following are few examples of use cases for global temporary tables:

If you have multiple sessions performing processing on the same dataset, you can use global temporary tables for maintaining status as well as sharing the data between sessions
Global temporary tables can be used for debugging long-running database sessions without permanently storing data. For example, one session could process business logic while a separate debugging session manipulates data temporarily for analysis, without affecting permanent tables.

Prerequisites

To follow along with this post, make sure you have the following prerequisites in place:

A SQL Server database with the Northwind sample database
An Aurora PostgreSQL cluster that supports Babelfish, Babelfish is an option available with Aurora PostgreSQL version 13.4 and higher releases. We recommend to go with latest supported version of Aurora PostgreSQL.

SQL Server implementation of T-SQL global temporary tables

This section shows an example of how a global temporary table works in T-SQL. The process requires two active sessions (session 1 and session 2) with different user IDs.

Session 1

Use session 1 to perform the following steps:

Create a global temporary table:

-- Create global temporary table 
DROP TABLE ##temp_customers
GO
CREATE TABLE ##temp_customers (
       id                    VARCHAR(100), 
       cust_name     VARCHAR(100), 
       city          VARCHAR(100), 
       country              VARCHAR(100), 
       phone         VARCHAR(100),
       insert_time   DATETIME,
       session_id    INT, 
       login_name    VARCHAR(200),  
       login_time    DATETIME)
GO

Insert data into this global temporary table. For this post, we use the table Customers from the publicly available Northwind schema:

-- Insert data into global temporary table
INSERT INTO ##temp_customers (id, cust_name, city, country, phone, insert_time, 
session_id, login_name, login_time)
SELECT customerId, CompanyName, city, country, phone, getdate(), session_id, 
login_name, login_time
FROM Customers
cross join sys.dm_exec_sessions 
WHERE country = 'USA'
and session_id = @@SPID;

Verify that the data is in the global temporary table:
```
SELECT * FROM ##temp_customers
```

It should fetch 13 rows.

Update a row:

-- update row from session 1 
update ##temp_customers set cust_name = 'TEST' where id = 'GREAL' -- 1 row update

Session 2

Connect to session 2 while the session 1 is still active and complete the following steps:

Query the global temporary table to see the results:
```
SELECT * FROM ##temp_customers
```
Disconnect from session 1 and try to query from session 2 again.

Upon verifying the table, you will discover that it no longer exists as expected, with an error message confirming that the object name is invalid.

Babelfish for Aurora PostgreSQL cluster workaround

We’ve observed some customers use global temporary tables for debugging purposes within their workloads. For example, one long-running user session might be processing business logic while simultaneously, from a separate session, a user is carrying out live debugging or data manipulation activities without storing the data permanently in the database. In this section, we demonstrate how you can implement a similar solution in Babelfish for Aurora PostgreSQL.

The solution consists of the following steps:

Before you create the table, make sure you don’t already have a table with the same name. If it exists, delete the table:

-- Check if temp table exists for the same spid
DECLARE @sess VARCHAR(50)
DECLARE @name VARCHAR(1000)
DECLARE @sql1 VARCHAR(1000)
SELECT @sess = session_id  from sys.dm_exec_connections where session_id = @@spid
SELECT @name = name FROM sys.tables WHERE name like '%'+@sess+''
 
SET @sql1 =  'DROP TABLE ' + @name
select @sql1
 
if @name IS not NULL
       exec sp_executesql  @sql1
GO

The following is a reference dynamic SQL code to generate a table with a session ID appended in the name. Change the logic according to your business requirement.

-- Dynamic SQL statement to create table
DECLARE @sql NVARCHAR(2000)
DECLARE @sid VARCHAR(50)
 
SET @sid = @@SPID
SET  @sql = '
       CREATE TABLE global_temp_customers_' + @sid + ' (
              id VARCHAR(100),
              cust_name VARCHAR(100),
              city VARCHAR(100),
              country VARCHAR(100),
              phone VARCHAR(100),
              insert_time DATETIME,
              session_id INT,
              login_name VARCHAR(200),
              login_time DATETIME)
       '
SELECT @sql
EXEC(@sql);

Start the application workload (session 1). During this step, a table with the suffix session ID is created in the database and populated according to the business logic.
Use the following query to get the list of all the sessions in the database (session 2). Then identify the relevant session based on the login_name, host_name and login_time.
```
-- Get details of session / login
select session_id, login_name, login_time, host_name  
from sys.dm_exec_sessions
```
After you identify the session ID, use the following query to find the table. Replace the session ID with the one you obtained from the previous query:
```
-- Find table based on session id
SELECT * FROM sys.tables WHERE name like 'global_temp_<session_id>%'
```
Query the table and perform the necessary debugging and capturing of statistics for the problematic workloads.

To perform cleanup, drop the table that you created for debugging by using one of the following methods. Note that if another session is currently accessing the table, dropping may be delayed until exclusive access can be obtained.

Add the drop statement in the application logic as shown. Replace the session ID with the one you used to create the table:
```
drop table global_temp_customers_<session_id>
go;
```

Alternatively, periodically use the following code to clean up the tables from the database. This procedure looks into Babelfish catalog tables and active sessions and deletes the tables that belong to inactive sessions:

CREATE PROCEDURE cleanup_temporary_tables
AS
BEGIN
	-- Declare local variables for the processing
	DECLARE @l_count INT = 0 
	DECLARE @l_count1 INT = 1 
	DECLARE @sess VARCHAR(1000)  
	DECLARE @sql NVARCHAR(2000)
	
	-- create temporary table for storing sessions which created temp tables
	CREATE TABLE #tmp_sessions (cnt INT IDENTITY, session_id INT)
	INSERT INTO #tmp_sessions (session_id) 
	-- Check system catalog based on table name
	SELECT LTRIM(name, 'global_temp_customers_') FROM sys.tables WHERE name like 'global_temp_customers_%'

	SELECT @l_count = count(*) FROM #tmp_sessions

	-- If we have un-used tables, then proceed with below processing
	IF @l_count > 0
	BEGIN 
		WHILE @l_count1 <= @l_count
		BEGIN 
			SELECT @sess = session_id FROM #tmp_sessions WHERE cnt = @l_count1
	
			-- delete sessions for the active session
			DELETE FROM #tmp_sessions
			WHERE session_id in (
				SELECT session_id FROM sys.dm_exec_sessions WHERE status = 'active'
			)

			-- create drop statement and drop it
			SET  @sql = '
				DROP TABLE global_temp_customers_' + @sess + ''
			EXEC(@sql);
		
			SET @l_count1 = @l_count1 +1 
			IF @l_count1 = @l_count
				BREAK;
		END 
	END 

END
GO

-- execute the cleanup procedure
EXEC cleanup_temporary_tables

Conclusion

Global temporary tables are useful when multiple sessions share the same temporary data, for example in collaborative tasks. To mimic SQL Server global temporary tables in Babelfish, you can create a permanent table with a session ID and later use a stored procedure for cleanup as shown in this post.

The AWS Babelfish team is making continuous improvements to the product and adding new features on a regular basis. Check out the Babelfish for Aurora PostgreSQL updates with each quarterly release for the latest improvements.

If you have any questions or suggestions, leave your feedback in the comments section.

About the Authors

Vanshika Nigam is a Solutions Architect on the Database Migration Accelerator team at Amazon Web Services. With over 6 years of experience working with Amazon RDS, she serves as an Amazon DMA Advisor helping AWS customers accelerate the migrations of their on-premises, commercial databases to AWS Cloud database solutions.

Amit Arora is a Solutions Architect with a focus on database and analytics at AWS. He works with our financial technology and global energy customers and AWS certified partners to provide technical assistance and design customer solutions on cloud migration projects, helping customers migrate and modernize their existing databases to the AWS Cloud.