Oracle-Style Global Temporary Tables for PostgreSQL

• Download source code - 11.7 KB
• Github project

Introduction

PostgreSQL semantic of temporary tables is substantially different from that of Oracle. Here is a brief summary:

• Oracle temporary tables are permanent, so their structure is static and visible to all users, and the content is temporary.
• PostgreSQL temporary tables are dropped either at the end of a session or at the end of a transaction. In PostgreSQL, both the structure and the content of a temp table is local for a database backend (a process) which created the table.
• Oracle temporary tables are always defined within a user-specified schema.
• PostgreSQL temporary tables cannot be defined within user's schema, they always use a special temporary schema instead.

Porting large Oracle application relying on many temporary tables can be difficult:

• Oracle queries may use schema.table notation for temporary tables, which is not allowed in Postgres. We can omit schema if it's the same as the current user, but we are still likely to have queries that reference other schemata.
• Postgres requires that each temporary table is created within the same session or transaction before it is accessed.

It gets worse if the application is supposed to work with both Postgres and Oracle, so we can't just fix the queries and litter the code with lots of create temporary table statements.

Enter pg_global_temp_tables

This library creates Oracle-style temporary tables in Postgres, so that Oracle queries work without any syntactic changes. Check it out:

-- Oracle application (1)
-- 
-- Temporary table is created like this:
-- create global temporary table temp_idlist(id number(18)) 

insert into myapp.temp_idlist(id) values(:p);

select u.login 
from myapp.users u
join myapp.temp_idlist t on u.id = t.id;

-- PostgreSQL application (2) using ordinary temporary tables
--
-- Temporary table is created in the same transaction 

create temporary table if not exists temp_idlist(id bigint);
insert into temp_idlist(id) values(:p);

select u.login 
from myapp.users u
join temp_idlist t on u.id = t.id;

-- PostgreSQL application (3) using pg_global_temp_tables
--
-- Temporary table is created like this:
-- create temporary table temp_idlist(id bigint);
-- create_permanent_temp_table('temp_idlist', 'myapp');
-- commit;

insert into myapp.temp_idlist(id) values(:p);

select u.login 
from myapp.users u
join myapp.temp_idlist t on u.id = t.id;

Note that the usage example in (1) and (3) is exactly the same.

Using the Code

The library consists of two functions:

• create_permanent_temp_table(p_table_name varchar, p_schema varchar default null)
• drop_permanent_temp_table(p_table_name varchar, p_schema varchar default null)

To install the library in your schema, execute the provided pg_global_temp_tables.sql script. To create a permanent temporary table, first create an ordinary temp table and then convert it to a persistent one using the create_permanent_temp_table function:

create temporary table if not exists another_temp_table
(
    first_name varchar,
    last_name varchar,
    date timestamp(0) with time zone,
    primary key(first_name, last_name)
)
on commit drop;

-- create my_schema.another_temp_table
select create_permanent_temp_table('another_temp_table', 'my_schema');

-- or create another_temp_table in the current schema
-- select create_permanent_temp_table('another_temp_table');

-- don't forget to commit: PostgreSQL DDL is transactional
commit;

To drop the emulated temporary table, use the drop_permanent_temp_table function:

-- drop my_schema.another_temp_table
select drop_permanent_temp_table('another_temp_table', 'my_schema');

-- or drop another_temp_table in the current schema
-- select drop_permanent_temp_table('another_temp_table');

commit;

How Does It Work

This library combines a few ideas to emulate Oracle-style temporary tables. First, let's define a view and use it instead of a temporary table. A view is a static object and it's defined within a schema, so it supports the schema.table notation used in our Oracle queries. A view can have instead of triggers which can create temporary table as needed. There are two problems, however:

• A view on a temporary table is automatically created as temporary, even if we omit the temporary keyword. Hence, the restrictions of temporary tables still apply, and we can't use schema-qualified names.
• There are no triggers on select, so we can't select from a view if the temporary table is not yet created.

Ok, we can't just create a view on a temporary table, so let's explore another option: we can define a function returning a table. A function is not temporary, it's defined within a schema, it can create the temporary table as needed and select and return rows from it. The function would look like this (note the returns table part of the definition):

-- let's do our experiments in a separate schema
create schema if not exists stage;

create or replace function stage.select_temp_idname() returns table(id bigint, name varchar) as $$
begin
    create temporary table if not exists test_temp_idname(id bigint, name varchar) on commit drop;
    return query select * from test_temp_idname;
end;
$$ language plpgsql;

This approach indeed works. We can select from a function, we can access it via schema-qualified name, and we don't have to create a temporary table before accessing it:

select * from stage.select_temp_idname()

-- id | name
-- ---+-----

Still, it's not quite usable:

• We have to add parentheses() after the function name, so we can't just leave Oracle queries as is, and
• Rows returned by a function are read-only.

To finally fix this, we combine both approaches, a view and a function. The view selects rows from the function, and we can make it updatable by means of the instead of triggers.

The Complete Sample Code of a Permanent Temp Table

Here is a working sample:

create or replace function stage.select_temp_idname() returns table(id bigint, name varchar) as $$
begin
    create temporary table if not exists test_temp_idname(id bigint, name varchar) on commit drop;
    return query select * from test_temp_idname;
end;
$$ language plpgsql;

create or replace view stage.temp_idname as 
    select * from stage.select_temp_idname();

create or replace function stage.temp_idname_insert() returns trigger as $$
begin
    create temporary table if not exists test_temp_idname(id bigint, name varchar) on commit drop;
    insert into test_temp_idname(id, name) values (new.id, new.name);
    return new;
end;
$$ language plpgsql;

drop trigger if exists temp_idname_insert on stage.temp_idname;
create trigger temp_idname_insert 
    instead of insert on stage.temp_idname
    for each row
    execute procedure stage.temp_idname_insert();

Finally, we can use the table just like Oracle:

select * from stage.temp_idname

-- NOTICE: 42P07: relation "test_temp_idname" already exists, skipping
-- id | name
-- ---+-----

insert into stage.temp_idname(id, name) values (1, 'one'), (2, 'two')

-- NOTICE: 42P07: relation "test_temp_idname" already exists, skipping
-- (2 rows affected)

select * from stage.temp_idname

-- NOTICE: 42P07: relation "test_temp_idname" already exists, skipping
-- id | name
-- ---+-----
-- 1  | one
-- 2  | two

One minor thing that annoys me is that pesky notice: relation already exists, skipping. We get the notice every time we access the emulated temporary table via select or insert statements. Notices can be suppressed using the client_min_messages setting:

set client_min_messages = error

But that affects all notices, even meaningful ones. Luckily, Postgres allows specifying settings per function, so that when we enter a function, Postgres applies these settings reverting them back on exit. This way, we suppress our notices without affecting the client's session-level setting:

create or replace function stage.select_temp_idname() returns table(id bigint, name varchar) as $$
begin
    create temporary table if not exists test_temp_idname(id bigint, name varchar) on commit drop;
    return query select * from test_temp_idname;
end;
$$ language plpgsql set client_min_messages = error;

Creating Permanent Temporary Tables

Let's recap what's needed to create a permanent temporary table residing in a schema:

1. A function returning the contents of a temporary table
2. A view on the function
3. Instead of insert/update/delete trigger on the view
4. Trigger function that does the job of updating the table

To delete the temporary table, we just drop the (1) and (4) functions with cascade options, and the rest is cleaned up automatically.

It's a bit cumbersome to create these each time we need a temporary table, so let's create a function that does the job. Here, we have a new challenge: specifying the table structure can be quite tricky. Suppose we have a function like this:

select create_permanent_temp_table(
    p_schema => 'stage', 
    p_table_name => 'complex_temp_table', 
    p_table_structure => '
        id bigint,
        name character varying (256),
        date timestamp(0) with time zone
    ',
    p_table_pk => ...
    p_table_pk_columns => ...
    p_table_indexes => ...
    etc.
);

The function have to parse table structure, list of primary key columns, indexes, etc. If the function doesn't validate the provided code, it's vulnerable to SQL injection, but validating the code turns out to require a full-blown SQL parser (for example, columns can have default values specified by arbitrary expressions). Worse, the table specification can change in the future, the syntax will evolve over time, etc. I'd like to avoid that kind of complexity in my utility code, so is there a better way?

The alternative approach that came to my mind is to convert an ordinal temporary table into a permanent one. We start with creating a temporary table using native PostgreSQL syntax, then we inspect the structure of the table and recreate it as a permanent object:

-- create a table as usual
create temporary table if not exists complex_temp_table
(
    id bigint,
    name character varying (256),
    date timestamp(0) with time zone,
    constraint complex_temp_table_pk primary key(id)
    -- or just: primary key (id)
)
on commit drop;

-- convert temp table into permanent one
select create_permanent_temp_table(p_schema => 'stage', p_table_name => 'complex_temp_table');

So what the library does is basically this:

1. Reverse engineer the given temporary table
2. Generate the code to re-create the temporary table
3. Format the template code using temp table name, schema and the above code
4. Execute the generated code to create a view and a trigger.

The detailed review of these steps is a bit too much for a single article, so I'll better write a follow-up in case anyone is interested. Please let me know what do you think, dear reader.

Resources

• Excellent, detailed and accurate PostgreSQL documentation
• Stackoverflow question related to reverse engineering the existing tables
• PGUnit, a tiny-footprint unit testing framework for the PostgreSQL
• CodeProject article describing the differences between Oracle and PostgreSQL temporary tables, by Patrycja Dybka

History

• 13^th March, 2017 — Initial posting
• 20^th March, 2017 — Fixed the problem with temp tables without the primary key