From: | Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> |
---|---|
To: | legrand_legrand(at)hotmail(dot)com |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Incremental View Maintenance: ERROR: out of shared memory |
Date: | 2019-12-29 11:24:04 |
Message-ID: | 20191229.202404.1435334044751365605.t-ishii@sraoss.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexey Kondratov | 2019-12-29 12:12:16 | Re: Physical replication slot advance is not persistent |
Previous Message | Michael Paquier | 2019-12-29 11:11:03 | Re: pgbench - use pg logging capabilities |