RE: Implementing Incremental View Maintenance

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?

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.

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

Regards
Takayuki Tsunakawa