Re: Temporary tables usage in functions

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

po 9. 11. 2020 v 18:19 odesílatel Michael Lewis <mlewis@xxxxxxxxxxx> napsal:

Also may I know if excessive use of temporary tables may cause locks?

Usually there are no problems with locks, but there is a problem with system tables bloating. Creating and dropping temp tables is expensive like creating or dropping normal tables.

Dropping a real table requires scanning all of shared_buffers, right? I wouldn't think dropping a temp table requires that kind of heavy operation because it should be stored within separate temp_buffers.

cleaning shared buffers probably is not a problem - when shared buffers are less than 20GB. The main problem is bloating pg_class, pg_attribute, maybe pg_depend. And VACUUM (autovacuum) is done after the transaction. So if somebody drop and recreate temp table when some function is starting, and this function is called 1M times inside the transaction, then there is brutal bloating of the system catalogue. And bloated system tables can do lot of other performance problems.

Against other databases, there can be used arrays instead temporary tables. This is significantly more effective. 

Can you expand on this point? What do you mean? Like using a values statement rather than temp table? I find that I often need to create a temp table and analyze it to allow the planner to make wise decisions.

This is correct usage of temp tables. When you need ANALYZE over some data, then there is no other possibility than using a temp table.

But a lot of people are coming from the MS SQL world, where temporary tables are used significantly often - for passing a list of ids between procedures, for creating multiline result, ...  The implementation of MS SQL temp tables or table variables is very different, and the usage is much more common - typically when we use arrays in Postgres. Different example is a Oracle. There are primary global temporary tables - again there is very low (zero) impact on system catalog, and some patterns that are working on Oracle well don't work well in Postgres (under longer higher load).

postgres=# \dt+ pg_attribute
                                          List of relations
┌────────────┬──────────────┬───────┬──────────┬─────────────┬───────────────┬────────┬─────────────┐
│   Schema   │     Name     │ Type  │  Owner   │ Persistence │ Access Method │  Size  │ Description │
╞════════════╪══════════════╪═══════╪══════════╪═════════════╪═══════════════╪════════╪═════════════╡
│ pg_catalog │ pg_attribute │ table │ postgres │ permanent   │ heap          │ 568 kB │             │
└────────────┴──────────────┴───────┴──────────┴─────────────┴───────────────┴────────┴─────────────┘
(1 row)

Timing is on.
postgres=# do $$
begin
  for i in 1..1000
  loop
    create temp table foo(a int);
    drop table foo;
  end loop;
end;
$$;
DO
Time: 2043,087 ms (00:02,043)
postgres=# do $$
begin
  for i in 1..1000
  loop
    create temp table foo(a int);
    drop table foo;
  end loop;
end;
$$;
DO
Time: 2082,437 ms (00:02,082)
postgres=# do $$
begin
  for i in 1..1000
  loop
    create temp table foo(a int);
    drop table foo;
  end loop;
end;
$$;
DO
Time: 2124,664 ms (00:02,125)
postgres=# do $$
begin
  for i in 1..1000
  loop
    create temp table foo(a int);
    drop table foo;
  end loop;
end;
$$;
DO
Time: 2137,486 ms (00:02,137)
postgres=# do $$
begin
  for i in 1..1000
  loop
    create temp table foo(a int);
    drop table foo;
  end loop;
end;
$$;
DO
Time: 2188,999 ms (00:02,189)
postgres=# do $$
begin
  for i in 1..1000
  loop
    create temp table foo(a int);
    drop table foo;
  end loop;
end;
$$;
DO
Time: 2209,167 ms (00:02,209)
postgres=# do $$
begin
  for i in 1..1000
  loop
    create temp table foo(a int);
    drop table foo;
  end loop;
end;
$$;
DO
Time: 2344,531 ms (00:02,345)

postgres=# \dt+ pg_attribute
                                          List of relations
┌────────────┬──────────────┬───────┬──────────┬─────────────┬───────────────┬─────────┬─────────────┐
│   Schema   │     Name     │ Type  │  Owner   │ Persistence │ Access Method │  Size   │ Description │
╞════════════╪══════════════╪═══════╪══════════╪═════════════╪═══════════════╪═════════╪═════════════╡
│ pg_catalog │ pg_attribute │ table │ postgres │ permanent   │ heap          │ 1592 kB │             │
└────────────┴──────────────┴───────┴──────────┴─────────────┴───────────────┴─────────┴─────────────┘
(1 row)

So some patterns that are usual with temporary tables on Oracle or on MSSQL are bad for Postgres. This is artificial example - the reality can be worse due too long transactions that can block vacuum.

On second hand - the advaise for Oracle is using temporary tables only when it is necessary and isn't possible to use collection too.

Regards

Pavel