Обсуждение: 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

Regards
PAscal



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

> 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

Tatsuo Ishii <ishii@sraoss.co.jp> writes:
>> here is an unexpected error found while testing IVM v11 patches
>> ...
>> ERROR:  out of shared memory

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

I'm more than a little bit astonished that this proposed patch is
creating temp tables at all.  ISTM that that implies that it's
being implemented at the wrong level of abstraction, and it will be
full of security problems, as well as performance problems above
and beyond the one described here.

            regards, tom lane

On Sun, 29 Dec 2019 12:27:13 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> >> here is an unexpected error found while testing IVM v11 patches
> >> ...
> >> ERROR:  out of shared memory
> 
> > I think we could avoid such an error in IVM by reusing a temp table in
> > a session or a transaction.
> 
> I'm more than a little bit astonished that this proposed patch is
> creating temp tables at all.  ISTM that that implies that it's
> being implemented at the wrong level of abstraction, and it will be
> full of security problems, as well as performance problems above
> and beyond the one described here.

We realized that there is also other problems in using temp tables
as pointed out in another thread. So, we are now working on rewrite
our patch not to use temp tables.

Regards,
Yugo Nagata


-- 
Yugo NAGATA <nagata@sraoss.co.jp>

Hi,

On Fri, 17 Jan 2020 17:33:48 +0900
Yugo NAGATA <nagata@sraoss.co.jp> wrote:

> On Sun, 29 Dec 2019 12:27:13 -0500
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> > Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> > >> here is an unexpected error found while testing IVM v11 patches
> > >> ...
> > >> ERROR:  out of shared memory
> > 
> > > I think we could avoid such an error in IVM by reusing a temp table in
> > > a session or a transaction.
> > 
> > I'm more than a little bit astonished that this proposed patch is
> > creating temp tables at all.  ISTM that that implies that it's
> > being implemented at the wrong level of abstraction, and it will be
> > full of security problems, as well as performance problems above
> > and beyond the one described here.
> 
> We realized that there is also other problems in using temp tables
> as pointed out in another thread. So, we are now working on rewrite
> our patch not to use temp tables.

We fixed this problem in latest patches (v14) in the following thread.
https://www.postgresql.org/message-id/20200227150649.101ef342d0e7d7abee320159@sraoss.co.jp

We would appreciate it if you could review this.


Best Regards,

Takuma Hoshiai


> Regards,
> Yugo Nagata
> 
> 
> -- 
> Yugo NAGATA <nagata@sraoss.co.jp>
> 
> 
> 


-- 
Takuma Hoshiai <hoshiai@sraoss.co.jp>