Businesses Adapting to Babelfish for Aurora PostgreSQL Face T-SQL Limits

When customers migrate from commercial databases like SQL Server to Babelfish for Aurora PostgreSQL, they encounter a notable limitation: the lack of support for T-SQL global temporary tables (##table_name). Babelfish for Aurora PostgreSQL currently supports only local temporary tables, which are dropped after the session ends and do not allow cross-session data access. To adapt, you may need to review your code to see if local temporary tables can be used instead. If not, this post covers how to implement T-SQL global temporary table behavior in Babelfish for Aurora PostgreSQL using permanent tables.

This workaround involves modifying existing T-SQL code that utilizes global temporary tables. However, it does not replicate all the capabilities of global temporary tables, particularly the automatic drop behavior, which must be handled manually, for example by adding explicit drop statements.

Overview of T-SQL Global Temporary Tables

Before diving 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, while 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 (Column1 DataType1, Column2 DataType2...);

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;
```

Here are a 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 using the 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 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)
);