In the current implementation, temporary tables are used during maintenance of materialized views. Tow delta tables (as tuple sets to be removed and inserted) are created and dropped per a statement due to immediate maintenance.
This is problematic for following reasons:
Frequent create/drop may bloat system catalogue tables. Also it may take many locks and cause "out of shared memory" error.
create table b1 (id integer, x numeric(10,3));
create incremental materialized view mv1
as select id, count(*),sum(x) from b1 group by id;
do $$
declare
i integer;
begin
for i in 1..10000
loop
insert into b1 values (1,1);
end loop;
end;
$$
;
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
CONTEXT: SQL statement "DROP TABLE pg_temp_3.pg_temp_66154"
(https://www.postgresql.org/message-id/1577564109604-0.post%40n3.nabble.com )
- Temptables can not be used in prepared transactions.
- Creating new tables per statement prevents plan cache.
- It may be security problems, as well as performance problems
We plan to use alternative implementation in which tuplestore and ENRs are used rather than temptables.
In the current implementation, temporary tables are used during maintenance of materialized views. Tow delta tables (as tuple sets to be removed and inserted) are created and dropped per a statement due to immediate maintenance.
This is problematic for following reasons:
do $$ declare i integer; begin for i in 1..10000 loop insert into b1 values (1,1); end loop; end; $$ ;
ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. CONTEXT: SQL statement "DROP TABLE pg_temp_3.pg_temp_66154"