Persistent temp tables in Postgres¶
Solution to implement persistent temp tables in Postgres
Problem¶
One of the problems that has to be resolved during Oracle 2 Postgres code migration is handling of temporary tables. Biggest difference is that in Oracle temporary tables are persistent schema level objects. You define the table structure once and it exists until you will explicitly drop it. The only difference comparing to ordinary table is that data in temporary table are stored either during current transaction only or until end of current session.
In contrast in Postgres temp tables are pure temporary objects and got automatically dropped after current session end. They also support data lifetime duration on the transaction or session level. But no temp table is available upon start of a new session.
In PL/SQL code you might have any SELECT or DML statement issued against temp table.
They will work because it will be present as a schema object.
In migrated plpgsql code such temp tables references will fail with relation "table_name" does not exist error.
We have to somehow create the table before executing any statement against it.
Solution¶
In order to simulate persistence of temp table and allow any SELECT or DML operations against it we have to define a set of objects.
- function that will create temp table if not exists yet
- function that will call function from p.1 and return all records from temp table
- view that will have the same name as temp table which will select from function defined on p.2
- this view is a main placeholder for a temp table.
- functions that will be used in triggers defined on view from p.3
- statement level and row level
INSTEAD OFtrigger(s) defined on a view from p.3 which will call functions from p.4
Dynamic table creation¶
Let's assume that we have such dict temp table defined in Oracle: create temp table dict(id int, n varchar2(10)).
First, we have to create a function that will dynamically create corresponding temp table in Postgres if not exists yet.
We will also add _tmp suffix to table name in order to have possibility to create dict view later on.
create or replace function create_tmp_table()
returns void as
$$ create temp table if not exists dict_tmp(id int, n varchar(10)) $$
language sql
set client_min_messages to warning;
This function will be executed before every statement issued against temp table name. There could be a lot of such calls.
We specify set client_min_messages to warning to avoid NOTICE messages sent to client for every such call.
Client messages level is configurable. But by default it sends NOTICE level messages.
=> select create_tmp_table();
-- create_tmp_table
-- ------------------
-- (1 row)
=> \d dict_tmp
-- Table "pg_temp_3.dict_tmp"
-- Column | Type | Collation | Nullable | Default
-- --------+-----------------------+-----------+----------+---------
-- id | integer | | |
-- n | character varying(10) | | |
=> select create_tmp_table();
-- create_tmp_table
-- ------------------
-- (1 row)
Function and wrapper view¶
Now we have to create a function that will call just created create_tmp_table function and will return dataset corresponding to dict table structure.
Postgres doesn't pick-up character column length defined in RETURNS TABLE clause.
Hence we specify unrestrictred VARCHAR during dict function definition
=> create or replace function dict()
returns table(id int, n varchar) as
$$
begin
perform create_tmp_table();
return query select * from dict_tmp;
end;
$$ language plpgsql;
=> \df dict
-- List of functions
-- Schema | Name | Result data type | Argument data types | Type
-- --------+------+----------------------------------------+---------------------+------
-- app | dict | TABLE(id integer, n character varying) | | func
At this point we are ready to create main substitution object.
It's a view with the same name and structure as an original dict temp table.
=> create or replace view dict as select * from dict();
=> \d dict
-- View "app.dict"
-- Column | Type | Collation | Nullable | Default
-- --------+-------------------+-----------+----------+---------
-- id | integer | | |
-- n | character varying | | |
If you need exact field length match you can specify it explicitly during view creation
=> create or replace view dict2 as select id, n::varchar(10) from dict();
=> \d dict2
-- View "app.dict2"
-- Column | Type | Collation | Nullable | Default
-- --------+-----------------------+-----------+----------+---------
-- id | integer | | |
-- n | character varying(10) | | |
=> drop view dict2;
We can select from this view as from usual table. But we cannot execute any DML against it yet.
=> select * from dict;
-- id | n
-- ----+---
-- (0 rows)
=> delete from dict;
-- ERROR: cannot delete from view "dict"
-- DETAIL: Views that do not select from a single table or view are not automatically updatable.
-- HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
According to the obtained error in order to enable DML against the view we have to introduce INSTEAD OF triggers or implement a rule.
Rules system is a Postgres powerful but quite specific feature. Its descrption could be a topic for a separate post.
We will use more standard INSTEAD OF triggers approach.
Trigger functions¶
In order to create trigger in Postges we must have created in advance a separate function that trigger will execute when fired.
Such funciton differs from ordinary one in a way that it must have returns trigger result datatype.
This is another markable difference from Oracle. Where instead of separate function trigger has a body defined as PL/SQL code.
Assuming that id attribute in dict table is a unique one we can create trigger function as below.
This is not in any way mandatory requirement. But usually we will have to know how to identify a row in a table from inside trigger function.
create or replace function dict_trg() returns trigger as $$ begin if TG_OP = 'INSERT' then insert into dict_tmp values (NEW.id, NEW.n) ; return NEW; elsif TG_OP = 'UPDATE' then update dict_tmp d set n = NEW.n where d.id = NEW.id -- OLD.id - depending on required logic ; return NEW; elsif TG_OP = 'DELETE' then delete from dict_tmp d where d.id = OLD.id ; return OLD; end if; end; $$ language plpgsql;
Created dict_trg function will be called from row level trigger. It will fail if dict_tmp table won't exist.
Thus we need another trigger function that will call created earlier create_tmp_table function before dict_trg will be called.
While we could add temp table creation as a first step right inside dict_trg function it would be non optimal approach.
We could have many rows affected within single DML operation.
And attempting to create temp table before processing each row in a trigger function could lead to performance penalties.
Instead we could attempt to create temp table just once. Before statement processing.
create or replace function dict_stmt_trg()
returns trigger as $$
begin
perform create_tmp_table();
return null;
end;
$$ language plpgsql;
Having trigger functions created we are ready for the final step - triggers creation.
Triggers¶
We need two triggers. One is before statement level and another one is instead of row level. Statement level will create temp table if not exists. It will be executed once per statement and thus we will not put additional workload on a database. In case of many affected by statement rows it could be a serious benefit.
Row level trigger will transfer per row processing against base dict_tmp table.
At that moment it will be already created by statement level trigger and we won't get any error.
create trigger dict_stmt_trg before insert or update or delete on dict for statement execute function dict_stmt_trg();
create trigger dict_trg instead of insert or update or delete on dict for each row execute function dict_trg();
=> \d dict
View "app.dict"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | |
n | character varying | | |
Triggers:
dict_stmt_trg BEFORE INSERT OR DELETE OR UPDATE ON dict FOR EACH STATEMENT EXECUTE FUNCTION dict_stmt_trg()
dict_trg INSTEAD OF INSERT OR DELETE OR UPDATE ON dict FOR EACH ROW EXECUTE FUNCTION dict_trg()
Testing¶
Let's test what we get. Whenever applicable test will start from fresh session to make sure temp table doesn't exist before command execution.
SELECT¶
After reconnect no actual dict_tmp temp table exists. But we can make a query from dict view as it would be a from persisten temp table in Oracle.
=> \c
-- You are now connected to database "dev" as user "app".
=> \d dict_tmp
-- Did not find any relation named "dict_tmp".
=> select * from dict;
-- id | n
-- ----+---
-- (0 rows)
-- After SELECT we already have "dict_tmp" temp table created.
dev=> \d dict_tmp
-- Table "pg_temp_4.dict_tmp"
-- Column | Type | Collation | Nullable | Default
-- --------+-----------------------+-----------+----------+---------
-- id | integer | | |
-- n | character varying(10) | | |
INSERT¶
After reconnect no actual dict_tmp temp table exists. But we can insert row into dict view as it would be with persisten temp table in Oracle.
=> \c
-- You are now connected to database "dev" as user "app".
=> \d dict_tmp
-- Did not find any relation named "dict_tmp".
=> insert into dict values (1, 'asdf');
-- INSERT 0 1
=> select * from dict;
-- id | n
-- ----+------
-- 1 | asdf
-- Behind the scene "dict_tmp" table is created.
=> \d dict_tmp
-- Table "pg_temp_3.dict_tmp"
-- Column | Type | Collation | Nullable | Default
-- --------+-----------------------+-----------+----------+---------
-- id | integer | | |
-- n | character varying(10) | | |
-- And it actually holds all the data accessible through "dict" view
=> select * from dict_tmp;
-- id | n
-- ----+------
-- 1 | asdf
UPDATE¶
Because temporary table losts data after session end we will continue test within the same session in which we inserted row.
=> select * from dict;
-- id | n
-- ----+------
-- 1 | asdf
=> update dict set n = 'qwer' where id = 1;
-- UPDATE 1
=> select * from dict;
-- id | n
-- ----+------
-- 1 | qwer
DELETE¶
For the same reason of data persistency we continue to test data removal within the same session after UPDATE statment above.
=> select * from dict;
-- id | n
-- ----+------
-- 1 | qwer
=> delete from dict where id = 1;
-- DELETE 1
=> select * from dict;
-- id | n
-- ----+---
-- (0 rows)
Let's reconnect and show that DELETE will also work on a fresh session where no temp table physically exists.
=> \c
-- You are now connected to database "dev" as user "app".
=> \d dict_tmp
-- Did not find any relation named "dict_tmp".
-- Obviously it will delete no rows because table is empty.
=> delete from dict;
-- DELETE 0
-- It works as usual by creating behind the scene "dict_tmp" temp table.
=> \d dict_tmp
-- Table "pg_temp_4.dict_tmp"
-- Column | Type | Collation | Nullable | Default
-- --------+-----------------------+-----------+----------+---------
-- id | integer | | |
-- n | character varying(10) | | |
Summary¶
We have created temporary table emulation in Postgres that works identically to Oracle one.
Top level view dict is a persistent schema level object. We have enabled SELECT and all basic DML operations against it.
Behind the scene there is a dict_tmp table is created dynamically.
It provides transactional and session data lifetime duration the same way as it happens in Oracle.