plpgsql и временные таблицы

Перевод plpgsql and temp. tables с Pavel Stehule's blog

Я провёл тестирование скорости трёх возможных стилей работы с временными таблицами в хранимых процедурах. Обычно я предпочитаю использовать проверку существования таблицы с помощью перехвата сообщения об ошибке. Я не ожидал этого, но перехват ошибок показал себя лучше всех.

create or replace function test1() 
returns void as $$
begin 
  drop table if exists omega; 
  create temp table omega(a integer); 
  insert into omega values(10); 
  if exists(select * from omega) then end if; 
end;
$$ language plpgsql;

create or replace function test2() 
returns void as $$
begin 
  if exists(select * from pg_class where relname='omega' and pg_table_is_visible(oid)) then 
    delete from omega; 
  else 
    create temp table omega(a integer); 
  end if; 
  insert into omega values(10); 
  if exists(select * from omega) then end if; 
end;
$$ language plpgsql;

create or replace function test3() 
returns void as $$
begin 
  begin 
    delete from omega; 
  exception 
    when others then 
      create temp table omega(a integer); 
  end; 
  insert into omega values(10); 
  if exists(select * from omega) then end if; 
end;
$$ language plpgsql;

тест с помощью pgbench

Test1:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 339.780441 (including connections establishing)
tps = 340.172513 (excluding connections establishing)

Test2:
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 1891.021562 (including connections establishing)
tps = 1907.533096 (excluding connections establishing)

Test3:
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 2664.756569 (including connections establishing)
tps = 2698.289177 (excluding connections establishing