Thread: 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>