On Mon, Dec 23, 2019 at 7:51 AM Yugo Nagata <nagata@sraoss.co.jp> wrote:
>
> On Mon, 23 Dec 2019 02:26:09 +0000
> "tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com> wrote:
>
> > From: legrand legrand <legrand_legrand@hotmail.com>
> > > For each insert into a base table there are 3 statements:
> > > - ANALYZE pg_temp_3.pg_temp_81976
> > > - WITH updt AS ( UPDATE public.mv1 AS mv SET __ivm_count__ = ...
> > > - DROP TABLE pg_temp_3.pg_temp_81976
> >
> > Does it also include CREATE TEMPORARY TABLE, because there's DROP?
>
> CREATE TEMPRARY TABLE is not called because temptables are created
> by make_new_heap() instead of queries via SPI.
>
> > I remember that repeated CREATE and DROP of temporary tables should be avoided in PostgreSQL. Dropped temporary
tablesleave some unused memory in CacheMemoryContext. If creation and deletion of temporary tables are done per row in
asingle session, say loading of large amount of data, memory bloat could crash the OS. That actually happened at a
user'senvironment.
>
> > Plus, repeated create/drop may cause system catalog bloat as well even when they are performed in different
sessions. In a fortunate case, the garbage records gather at the end of the system tables, and autovacuum will free
thoseempty areas by truncating data files. However, if some valid entry persists after the long garbage area, the
systemtables would remain bloated.
>
> Thank you for explaining the problem. I understood that creating and
> dropping temprary tables is harmful more than I have thought. Although
> this is not a concrete plan, there are two ideas to reduce creating
> temporary tables:
For the pg_stat_statements point of view, utility command support is
already quite bad as with many workloads it's rather impossible to
activate track_utility as it'd otherwise pollute the hashtable with an
infinity of queries executed only once (random prepared transaction
name, random cursor names...). I'm wondering whether we should
normalize utility statements deparsing the utilityStmt, and also
normalizing some identifiers (maybe optionally with a GUC), eg.
"DECLARE ? AS CURSOR FOR normalized_query_here". However commands
like vacuum or drop would be better kept as-is.