"relation ... deleted while still in use" error when using immutable lookup functions in create table as #83
Description
Continuing #82
We must rely on
create table tbl as select ... in an important scenario.
There is regression in comparison to XL which causes some immutable lookup functions to cause the error:
ERROR: XX000 - node:datanode1, backend_pid:39522, nodename:datanode1,backend_pid:39522,message:relation 523409537 deleted while still in use
Ie. this works:
create table r distribute by replication as select i i, i || '_val'::text itxt from generate_series(1,5) i;
create table r2 distribute by replication as select i i, i || '_val'::text itxt from generate_series(1,5) i;
CREATE OR REPLACE FUNCTION getr_text(id integer)
RETURNS text
LANGUAGE sql
IMMUTABLE
parallel safe
AS $function$
select itxt from r2 where i = id;
$function$;
create table s (id int primary key, val text) distribute by shard(id) ;
insert into s select id, id|| '_val' idval from generate_series(1,10000000) id;
-- this works
create table mv_r_s_1 as
select s.*,r.*, getr_text(r.i), getr_text(s.id) d2 from
r right outer join s on (getr_text(r.i) = s.val
limit 0;
--- However if we use a function that does lookup on some well established table, we get the above error:
create table mv_r_s_2 as
select s.*,r.*, getr_text(r.i), getr_text(s.id) d2 from
r right outer join s on (getr_text(r.i) = s.val or
dr3_ops_cs36_tmp.getmaincatalog_tst() = i % 4) --<---- this immutable function causes error
;The function is very similar to getr_text - except is run on an older, replicated table.
You can see immutable function query to be shipped to datanode in the log at DEBUG5 level attached here.