On 12/01/2026 7:51 AM, Pavel Stehule wrote:
Hi,
last week there was a discussion on linkedin related to port Oracle's
application to Postgres.
I am sure so lot of usage of temporary tables in application is
useless, based on long history of ported applications - Sybase (MSSQL)
-> Oracle -> Postgres, but still global temporary tables are
interesting feature - and impossibility to use GTT is a real problem
for lot of users.
One of the issues of this port are probably temporary tables. It is
probably a common issue - because PostgreSQL doesn't support global
temporary tables and any workarounds have a significant problem with
bloating of some system catalog tables - pg_attribute, pg_class,
pg_depends, pg_shdepends.
The implementation has two parts - one can be "simple" - using a local
storage for a persistent table.
Second is almost impossible - storing some metadata that cannot be
shared - like relpages, reltuples, pg_statistic. We also want to
support some views like pg_stats for global temp tables too, and if
possibly without bigger changes.
Some years ago there was a some implementations based on using some
memory caches. It doesn't work well, because Postgres has not concept
of session persistent caches of catalog data, that should live across
cache invalidation signal.
I think so this problem can be reduced just on implementation of
pg_statistic table. If we can support GTT for pg_statistic we can
support GTT generally.
pg_statistic can be (in future) partitioned table - one partition can
for common tables, one partition can be global temporary tables. The
partition for global temporary tables can be GTT by self. There can be
a GTT partition for currently used local temporary tables too (this
pattern can fix a bloating related to usage of local temporary tables).
I am not sure if proposed design is implementable - it requires
partitioning of system tables on some very low level.
Has somebody some ideas to this topic?
Regards
Pavel
Hi,
7 years ago I proposed Oracle-like solution for temp tables (shared
metadata, private data):
https://www.postgresql.org/message-id/flat/73954ab7-44d3-b37b-81a3-69bdcbb446f7%40postgrespro.ru
and you also participated in discussion and one of the concerns were
this problems with statistics.
I do not completely understand how partitioning of system tables can
solve this problem.
Do you propose that each backend has its own (private) partition?
It seems to be impossible and can cause even worse catalog bloating
(instead of one temp table we will have to create temp partitions for
multiple system tables).
