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