Re: Incremental View Maintenance: ERROR: out of shared memory

> Hello 
> here is an unexpected error found while testing IVM v11 patches
> 
> 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"
> SQL statement "insert into b1 values (1,1)"
> PL/pgSQL function inline_code_block line 1 at SQL statement

Yeah, following code generates similar error as well even without IVM.

do $$ 
declare 
    i integer;
begin 
    for i in 1..10000
    loop 
        create temp table mytemp(i int);
        drop table mytemp;
    end loop; 
end;
$$
;

ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  SQL statement "create temp table mytemp(i int)"
PL/pgSQL function inline_code_block line 7 at SQL statement

I think we could avoid such an error in IVM by reusing a temp table in
a session or a transaction.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp