BUG #17596: "invalid attribute number 11" when updating partitioned table with a MULTIEXPR_SUBLINK
От | PG Bug reporting form |
---|---|
Тема | BUG #17596: "invalid attribute number 11" when updating partitioned table with a MULTIEXPR_SUBLINK |
Дата | |
Msg-id | 17596-c5357f61427a81dc@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17596: "invalid attribute number 11" when updating partitioned table with a MULTIEXPR_SUBLINK
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17596 Logged by: Andre Lin Email address: 857348270@qq.com PostgreSQL version: 12.12 Operating system: Linux x86_64 GNU/Linux Description: Steps to reproduce this ERROR: drop procedure if exists insert_data_start(starts int,table_num int, table_name text); create or REPLACE procedure insert_data_start(starts int,table_num int, table_name text) as $$ declare v_sql text; BEGIN v_sql := 'insert into '||table_name||' select i, repeat((i%365)::text,5)::bytea, (i%10)::char, int8(i%550), int2(i%127), int4(i%789),round(i%20*2.5866,3)::numeric, bpchar(md5((i%300)::text)), (''aaaaa:''||(i%360))::varchar, ''bbbbb''||(i%400)||''a''::text, md5((i%300)::TEXT), (i%666)::bigint::oid, ((i%200)*2.35)::float4, float8((i%300)*2.215), ''dsa ''||(i%765), (i%35),''tinterval''||(i%44), concat(concat_ws(''.'',(i+100)%200, i%100,i%100,(i+100)%200),''/25'')::inet, date((''2022-02-17 19:12:40+08''::timestamp)), ''00:00:00'' + (i%400+1) * interval ''1 minute'', (date(''2022-02-18'')+(i%24) * interval ''1 day''+(i%24) * interval ''1 minute''+(i%24) * interval ''1 second'')::timestamp,timestamptz(date(date(''2022-02-18'')+(i%24) * interval ''1 day'')),(i%500) * interval ''1 day'',timetz(''2022-02-16 17:29:13.9+08''::timestamp + (i%500)*interval ''1 second''),concat(concat_ws(''.'',(i+100)%200, i%100,i%100,(i+100)%200),''/25'')::inet,int4(i%10)::bit(5), (i%100+1)::int::bit(5)::varbit(5), bool(i%2), concat_ws(''-'',to_hex((i%192)+1),to_hex((i%168)+1),to_hex((i%200)+1),to_hex((i%100)+1),to_hex((i%98)+1),to_hex((i%255)+1))::macaddr, point(ceil((i%68)+1),ceil((i%77)+1)),(i*987)::varchar::oidvector,polygon(box(point(i%550,i%360),point(i%50,i%22))),((i*3.156)%9999)::money,(i%798,i%666)::varchar::tid,circle(point(i%250,i%160),i%76),concat_ws(''-'',to_hex((i%192)+16),to_hex((i%168)+16),to_hex((i%200)+16),to_hex((i%100)+16),to_hex((i%98)+16),to_hex((i%239)+16))::macaddr8,line(point(i%93+1,i%120),point(i%450+2,i%36)),path(polygon(box(point(i%192,i%120),point(i%913,i%120)))),box(point(i%292+1,i%20),point(i%92+2,i%24)),lseg(point(i%92+1,i%220),point(i%238+2,i%120)),name(''ccc''||(i%12)),(''dfa in ddd''||(i%900))::text,((i*3.1123)%998)::numeric(10,2),''sdfwadsf''||(i%120),''dfewvew''||(i%30),(i%50)/13, (i%20)/17, to_hex(i*900%500),''dfjwedfv''||(i%40) from generate_series('||starts||','||table_num||') i'; execute v_sql; end; $$ language plpgsql; drop table if exists s; create table s(c0 serial,c1 BYTEA default '',c2 CHAR default 'a',c3 INT8 default 1999,c4 INT2 default -127,c5 INT4 default -1,c6 numeric default 3.14156,c7 BPCHAR default '',c8 VARCHAR default '',c9 text default 'text',c10 TEXT default '',c11 OID default '2',c12 FLOAT4 default 10.8,c13 FLOAT8 default 0.999,c14 text default 'tgose',c15 varchar default '00:05:04',c16 varchar default 'tinterval(abstime(now()), abstime(now()))',c17 INET default '148.85.65.189/25',c18 DATE default '2022-07-26 14:25:25',c19 TIME default '14:25:03',c20 TIMESTAMP default '2022-07-26 12:12:12',c21 TIMESTAMPTZ default '2022-07-26 12:12:12+08',c22 INTERVAL default interval '1 day',c23 TIMETZ default '14:36:04+08',c24 CIDR default '198.24.10.0/24',c25 bit varying(5) default '',c26 VARBIT default '00011',c27 bool default false,c28 macaddr default 'bf:77:bf:5b:5b:92',c29 point default '(10,10)',c30 oidvector default '',c31 polygon default '((2,2),(2,2),(2,2),(2,2))',c32 money default 10,c33 tid default '(2,2)',c34 circle default '<(2,2),2>',c35 macaddr8 default '12:12:12:ff:fe:12:12:12',c36 line default '{0,-1,2}',c37 path default '((2,2),(2,2),(2,2),(2,2))',c38 box default '(4,2),(3,2)',c39 lseg default '[(3,2),(4,2)]',c40 name default '',c41 text default '',c42 numeric default 1.99,c43 text default 'text', c44 text default 'text',c45 numeric default 3.141596, c46 numeric default 1.963489, c47 text default '',c48 text default 'text',c49 serial,c50 bigserial,c51 int[] default '{1,2,3}',c52 text default ''); call insert_data_start(1, 5000, 's'); analyze s; drop table if exists r; create table r(c0 serial,c1 BYTEA default '',c2 CHAR default 'a',c3 INT8 default 1999,c4 INT2 default -127,c5 INT4 default -1,c6 numeric default 3.14156,c7 BPCHAR default '',c8 VARCHAR default '',c9 text default 'text',c10 TEXT default '',c11 OID default '2',c12 FLOAT4 default 10.8,c13 FLOAT8 default 0.999,c14 text default 'tgose',c15 varchar default '00:05:04',c16 varchar default 'tinterval(abstime(now()), abstime(now()))',c17 INET default '148.85.65.189/25',c18 DATE default '2022-07-26 14:25:25',c19 TIME default '14:25:03',c20 TIMESTAMP default '2022-07-26 12:12:12',c21 TIMESTAMPTZ default '2022-07-26 12:12:12+08',c22 INTERVAL default interval '1 day',c23 TIMETZ default '14:36:04+08',c24 CIDR default '198.24.10.0/24',c25 bit varying(5) default '',c26 VARBIT default '00011',c27 bool default false,c28 macaddr default 'bf:77:bf:5b:5b:92',c29 point default '(10,10)',c30 oidvector default '',c31 polygon default '((2,2),(2,2),(2,2),(2,2))',c32 money default 10,c33 tid default '(2,2)',c34 circle default '<(2,2),2>',c35 macaddr8 default '12:12:12:ff:fe:12:12:12',c36 line default '{0,-1,2}',c37 path default '((2,2),(2,2),(2,2),(2,2))',c38 box default '(4,2),(3,2)',c39 lseg default '[(3,2),(4,2)]',c40 name default '',c41 text default '',c42 numeric default 1.99,c43 text default 'text', c44 text default 'text',c45 numeric default 3.141596, c46 numeric default 1.963489, c47 text default '',c48 text default 'text',c49 serial,c50 bigserial,c51 int[] default '{1,2,3}',c52 text default ''); call insert_data_start(1, 5000, 'r'); analyze r; drop table if exists t1; create table t1(c0 serial,c1 BYTEA default '',c2 CHAR default 'a',c3 INT8 default 1999,c4 INT2 default -127,c5 INT4 default -1,c6 numeric default 3.14156,c7 BPCHAR default '',c8 VARCHAR default '',c9 text default 'text',c10 TEXT default '',c11 OID default '2',c12 FLOAT4 default 10.8,c13 FLOAT8 default 0.999,c14 text default 'tgose',c15 varchar default '00:05:04',c16 varchar default 'tinterval(abstime(now()), abstime(now()))',c17 INET default '148.85.65.189/25',c18 DATE default '2022-07-26 14:25:25',c19 TIME default '14:25:03',c20 TIMESTAMP default '2022-07-26 12:12:12',c21 TIMESTAMPTZ default '2022-07-26 12:12:12+08',c22 INTERVAL default interval '1 day',c23 TIMETZ default '14:36:04+08',c24 CIDR default '198.24.10.0/24',c25 bit varying(5) default '',c26 VARBIT default '00011',c27 bool default false,c28 macaddr default 'bf:77:bf:5b:5b:92',c29 point default '(10,10)',c30 oidvector default '',c31 polygon default '((2,2),(2,2),(2,2),(2,2))',c32 money default 10,c33 tid default '(2,2)',c34 circle default '<(2,2),2>',c35 macaddr8 default '12:12:12:ff:fe:12:12:12',c36 line default '{0,-1,2}',c37 path default '((2,2),(2,2),(2,2),(2,2))',c38 box default '(4,2),(3,2)',c39 lseg default '[(3,2),(4,2)]',c40 name default '',c41 text default '',c42 numeric default 1.99,c43 text default 'text', c44 text default 'text',c45 numeric default 3.141596, c46 numeric default 1.963489, c47 text default '',c48 text default 'text',c49 serial,c50 bigserial,c51 int[] default '{1,2,3}',c52 text default '') partition by hash (c20); create table t1_part_0 partition of t1 for values with(modulus 5,remainder 0) partition by range (c18); create table t1_part_1 partition of t1 for values with(modulus 5,remainder 1) partition by range (c18); create table t1_part_2 partition of t1 for values with(modulus 5,remainder 2) partition by range (c18); create table t1_part_3 partition of t1 for values with(modulus 5,remainder 3) partition by range (c18); create table t1_part_4 partition of t1 for values with(modulus 5,remainder 4) partition by range (c18); create table t1_part_0_0 partition of t1_part_0 for values from ('2022-02-19 12:12:12') to ('2022-12-19 12:12:12'); create table t1_part_0_1 partition of t1_part_0 for values from ('2023-02-19 12:12:12') to ('2023-12-19 12:12:12'); create table t1_part_0_2 partition of t1_part_0 default; create table t1_part_1_0 partition of t1_part_1 for values from ('2022-02-19 12:12:12') to ('2022-12-19 12:12:12'); create table t1_part_1_1 partition of t1_part_1 for values from ('2023-02-19 12:12:12') to ('2023-12-19 12:12:12'); create table t1_part_1_2 partition of t1_part_1 default; create table t1_part_2_0 partition of t1_part_2 for values from ('2022-02-19 12:12:12') to ('2022-12-19 12:12:12'); create table t1_part_2_1 partition of t1_part_2 for values from ('2023-02-19 12:12:12') to ('2023-12-19 12:12:12'); create table t1_part_2_2 partition of t1_part_2 default; create table t1_part_3_0 partition of t1_part_3 for values from ('2022-02-19 12:12:12') to ('2022-12-19 12:12:12'); create table t1_part_3_1 partition of t1_part_3 for values from ('2023-02-19 12:12:12') to ('2023-12-19 12:12:12'); create table t1_part_3_2 partition of t1_part_3 default; create table t1_part_4_0 partition of t1_part_4 for values from ('2022-02-19 12:12:12') to ('2022-12-19 12:12:12'); create table t1_part_4_1 partition of t1_part_4 for values from ('2023-02-19 12:12:12') to ('2099-12-19 12:12:12'); create table t1_part_4_2 partition of t1_part_4 default; call insert_data_start(1, 5000, 't1'); analyze t1; update t1 set (c14) = (select t1.c10 from r limit 1) from r t2, s t3, (select c3,c12 from s t4 group by t4.c12, t4.c3) t4 where t1.c3=t2.c3 and t1.c12=t2.c12 and t2.c3=t3.c3 and t2.c12=t3.c12 and t3.c3=t4.c3 and t3.c12=t4.c12 and t1.c0 between 900 and 1500; The result is ERROR: XX000: invalid attribute number 11 LOCATION: slot_getsomeattrs_int, execTuples.c:1909 I did some investigations. The current implementation, the update plans of different partitions use the same param id for the subplan param, but they correspond to different SubPlanStates during execution, SubPlanState are generated during ExecInitSubPlan and linked to ParamExecData->execPlan. Since the plans of multiple partitions share one Param, according to the execution order, the final execPlan will be set to the SubPlanState of the last partition in the InitPlan phase. And because the order of joins in different partition plans is different, the final SubPlanState->args does not match the expectation (some are OUTER_VAR, some are INNER_VAR), and an error is occurred (failed assertion if "--enable-cassert" and coredump)
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Amit KapilaДата:
Сообщение: Re: Excessive number of replication slots for 12->14 logical replication
Следующее
От: Maxim BogukДата:
Сообщение: Re: BUG #17594: conditional hash indexes size (hash index ignore WHERE condition during CREATE INDEX?)