Re: Implementing Incremental View Maintenance

On Mon, 23 Dec 2019 02:26:09 +0000
"tsunakawa(dot)takay(at)fujitsu(dot)com" <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:

> From: legrand legrand <legrand_legrand(at)hotmail(dot)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 tables leave some unused memory in CacheMemoryContext. If creation and deletion of temporary tables are done per row in a single session, say loading of large amount of data, memory bloat could crash the OS. That actually happened at a user's environment.

> 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 those empty areas by truncating data files. However, if some valid entry persists after the long garbage area, the system tables 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:

1. Create a temporary table only once at the first view maintenance in
this session. This is possible if we store names or oid of temporary
tables used for each materialized view in memory. However, users may
access to these temptables whenever during the session.

2. Use tuplestores instead of temprary tables. Tuplestores can be
converted to Ephemeral Name Relation (ENR) and used in queries.
It doesn't need updating system catalogs, but indexes can not be
used to access.

>
> What kind of workload and data are you targeting with IVM?

IVM (with immediate maintenance approach) would be efficient
in situations where modifications on base tables are not frequent.
In such situations, create and drop of temptalbes is not so
frequent either, but it would be still possible that the problem
you concern occurs. So, it seems worth to consider the way to
reduce use of temptable.

Regards,
Yugo Nagata

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>