Customers migrating from Oracle to PostgreSQL and SQL Server to PostgreSQL are often curious of Oracle compatibility and SQL Server compatibility in PostgreSQL. Best way to solve most of the Oracle or SQL Server compatibility is through some of the popular extensions in PostgreSQL. I have had a chance to contribute to multiple such tools or extensions supporting migrations, including Ora2Pg. In this article, I am going to talk about the support for Global Temporary Tables in PostgreSQL, using PGTT extension.
What is a Global Temporary Table (GTT) ?
A Global Temporary Table is a table that has a persistent DDL structure between sessions and database restart, but not persistent data. It doesn’t create WAL or REDO log with DML statements. It only needs to be created once in a database, like a normal non-journaled table, and can be used by any new session without having to be recreated. Whereas, standard temporary tables are dropped at the end of the session or transaction and must be recreated before being used in a new session. When using global or standard temporary tables, the data is visible only to the session that inserts the data into the table.
The main purpose of this feature, in the PostgreSQL point of view, might be to avoid bloating the catalog tables. If your application creates temporary tables and when there are a lot of connections, PostgreSQL will insert and delete a lot of entries in the system catalog tables for creating temporary tables. Due to PostgreSQL MVCC and the copy on write mechanism, autovacuum should clear the bloated space. If autovacuum is unable to clear the bloat, it will slow down any queries on the catalog tables related to relations. The problem is that it will not just slow down the current session using the temporary table, but it also slows down all queries because every query needs to access to the catalog tables.
Can this feature be implemented in PostgreSQL core ?
Historically, there have been several discussions about allowing temporary tables to exist as empty by default in all sessions. Here are some discussions retrieved from the PostgreSQL TODO list.
- What is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
- idea: global temp tables
- Re: idea: global temp tables
- global temporary tables
- Implementation of global temporary tables?
In 2016, Alexandr Alekseev, has implemented a patch which allows to create "fast temporary tables" without accessing the system catalog. This feature was not proposed to implement the global temporary table feature, but to skip catalog entries during temporary table creation and avoid catalog bloat. This patch proposes to store the Information about temporary tables in the shared memory. Unfortunately this patch was considered too invasive by the community and thus got rejected.
In 2019, Konstantin Knizhnik, came back with a more advanced patch, to implement Global Temporary Tables similar to Oracle. Konstantin has proposed several possible implementations including statements like : "create global temp table" and "create session table", addressing the problem related to disabling parallel plans when using temporary tables by making them accessible through shared buffers, etc. However, the discussion did not continue further since Feb, 2020.
At end of 2019, Zeng Wenjing, has also proposed an implementation of Global Temporary Table. The implementation stopped after being marked as returned with feedback in the last commitfest as of July, 2022. This was probably the most advanced work on this feature but it requires very fundamental design rework. No further work on this feature appeared in PostgreSQL core since July, 2022.
Others alternatives for Global Temporary Tables in PostgreSQL
pg_global_temp_tables
This pg_global_temp_tables tries to implement Oracle-style global temporary tables for PostgreSQL in SQL. It requires a function that is called through a view named like the temporary table you want to create. This function, when called, will create an underlying temporary table and return the content on this temporary table. It will also create an INSTEAD OF trigger on the view to INSERT/UPDATE/DELETE data on the underlying temporary table. This extension is not full featured and not maintained anymore since 2018.
Global-Temporary-Tables-For-PG
This extension is based on the previous extension and adds some of the missing features to have Oracle-style global temporary tables.
- Allow ON COMMIT DELETE ROWS in addition to the ON COMMIT DROP.
- Allow use of the temporary table outside a transaction.
- Supports indexes on the temporary table.
The main advantage of this SQL implementation is that it allows to be used on DBaaS platforms like AWS RDS, Google Cloud SQL and Azure Database for PostgreSQL, where some of the extensions like pgtt cannot be implemented.
pgtt
This is a C and SQL extension to use Oracle-style Global Temporary Tables in PostgreSQL. Its objective is to provide the Global Temporary Table feature as we do not see it being implemented in PostgreSQL core anytime soon. The main interest of the extension is to have the same syntax and behavior of Oracle Global Temporary Tables, which is very useful when you cannot or do not want to rewrite the application code when migrating to PostgreSQL.
Using the PGTT extension, you can simply create a Global Temporary table using the following syntax as an example:
CREATE GLOBAL TEMPORARY TABLE my_gtt (id integer, lbl text) ON COMMIT DROP;or if you want to get rid of the warning about the use of deprecated GLOBAL keyword, you may use the following syntax:
CREATE /*GLOBAL*/ TEMPORARY TABLE my_gtt (id integer, lbl text) ON COMMIT DROP;Since version 4.0 and thanks to Julien Rouhaud, the pgtt extension can be automatically loaded using the setting session_preload_libraries in your postgresql.conf file.
session_preload_libraries='pgtt'Prior to the version 4.0, your application always needed to execute a LOAD 'pgtt' in the session, before the first use of a GTT, and this is not a requirement anymore. This version also adds a port to the Windows operating system.
This extension is here to provide the full feature of Global temporary tables in PostgreSQL but do not expect solving the problem of catalog tables bloat. This is because, it still creates a temporary table on the fly, the first time it is used in a session.
Although this extension is stable and offers the full functionality, it is not yet distributed as binary package by the PostgreSQL Global Development Group (PGDG). The possibility offered in PGTT version 4.0 to preload it with setting session_preload_libraries will simplify the use and adoption.
Conclusion
The implementation of Global Temporary Tables functionality in PostgreSQL core will take time as all its design and implementation issues need to be addressed. The only reasonable alternative is still to use the pgtt extension or to rewrite your application to use PostgreSQL standard local temporary table.
Seeking specialized PostgreSQL support or Database Migrations assistance ? Get experts advice for effective data management, query tuning, database migrations and database optimization. Click here for personalized expertise to elevate your database performance and meet your unique business needs.
Stay tuned for more interesting topics.
Subscribe to our Newsletters and Stay tuned for more interesting topics.
