BUG #15337: partition modify bug? cann't sync relcache in the samesession immediate?
От | PG Bug reporting form |
---|---|
Тема | BUG #15337: partition modify bug? cann't sync relcache in the samesession immediate? |
Дата | |
Msg-id | 153450643179.1303.7733825857009150633@wrigleys.postgresql.org обсуждение исходный текст |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15337 Logged by: Zhou Digoal Email address: digoal@126.com PostgreSQL version: 10.5 Operating system: CentOS 7.x x64 Description: 1、execute this sql file in the same session: ``` DROP TABLE IF EXISTS test CASCADE; CREATE TABLE test ( id int8 NOT NULL, create_date timestamp, title varchar(255), type varchar(20) NOT NULL ) PARTITION BY LIST (type); CREATE TABLE test_a PARTITION OF test FOR VALUES IN ('a') PARTITION BY RANGE (create_date); CREATE TABLE test_b PARTITION OF test FOR VALUES IN ('b') PARTITION BY RANGE (create_date); CREATE TABLE test_c PARTITION OF test FOR VALUES IN ('c') PARTITION BY RANGE (create_date); CREATE OR REPLACE FUNCTION test_2ndlevel_partitions_and_constraints() RETURNS void AS $$ DECLARE types varchar[] := array['a', 'b', 'c']; dates varchar[] := array['2014-01-01', '2015-01-01', '2016-01-01', '2017-01-01', '2018-01-01', '2019-01-01']; type text; date text; nextDate text; tableCode text; BEGIN FOREACH type IN array types LOOP FOR i IN 1..5 LOOP --- date := dates[i]; nextDate := dates[i + 1]; --- tableCode := type || '_' || split_part(date, '-', 1); EXECUTE format('CREATE TABLE test_%s PARTITION OF test_%s FOR VALUES FROM (''%s'') TO (''%s'');', tableCode, type, date, nextDate); EXECUTE format('ALTER TABLE test_%s ADD PRIMARY KEY (id);', tableCode); END LOOP; END LOOP; END; $$ LANGUAGE plpgsql; SELECT test_2ndlevel_partitions_and_constraints(); DROP FUNCTION IF EXISTS test_2ndlevel_partitions_and_constraints; -- -- --- INSERT INTO public.test(id, create_date, title, type) SELECT id, timestamp '2014-01-01 00:00:00' + random() * (timestamp '2018-12-31 00:00:00' - timestamp '2014-01-01 00:00:00'), md5(random()::text), -- (random() * 4 + 1)::int ('{a,b,c}'::text[])[ceil(random() * 3)] FROM generate_series(1, 300000) id; -- select count(*) from test; select count(*) from test where type = 'c'; select count(*) from test_c; --- ALTER TABLE test DETACH PARTITION test_c; --- ALTER TABLE test ATTACH PARTITION test_c FOR VALUES IN ('c', 'd'); --- insert into public.test values (0,'2014-01-01','test','d'); ``` 2、raise error ``` postgres=# insert into public.test values (0,'2014-01-01', 'test','d'); ERROR: new row for relation "test_c_2014" violates partition constraint DETAIL: Failing row contains (0, 2014-01-01 00:00:00, test, d). ``` 3、in this session ,test table's partition already modified correct. ``` postgres=# \d+ test Table "public.test" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+-----------------------------+-----------+----------+---------+----------+--------------+------------- id | bigint | | not null | | plain | | create_date | timestamp without time zone | | | | plain | | title | character varying(255) | | | | extended | | type | character varying(20) | | not null | | extended | | Partition key: LIST (type) Partitions: test_a FOR VALUES IN ('a'), test_b FOR VALUES IN ('b'), test_c FOR VALUES IN ('c', 'd') ``` but why raise the error? 4、open another session execute the same query , it's ok/. ``` postgres=# insert into public.test values (0,'2014-01-01', 'test','d'); INSERT 0 1 ``` 5、and read the modified partition direct , then will ok also. ``` postgres=# discard all; DISCARD ALL postgres=# insert into public.test values (0,'2014-01-01', 'test','d'); ERROR: new row for relation "test_c_2014" violates partition constraint DETAIL: Failing row contains (0, 2014-01-01 00:00:00, test, d). postgres=# select * from test limit 1; id | create_date | title | type ----+----------------------------+----------------------------------+------ 27 | 2014-01-06 08:18:58.660182 | 1a87819edc130e6754d7848e138075bc | a (1 row) postgres=# insert into public.test values (0,'2014-01-01', 'test','d'); ERROR: new row for relation "test_c_2014" violates partition constraint DETAIL: Failing row contains (0, 2014-01-01 00:00:00, test, d). postgres=# select * from test_c limit 1; id | create_date | title | type ----+----------------------------+----------------------------------+------ 22 | 2014-06-08 11:36:47.342778 | 1657c0a4de29d653568a9c6564461378 | c (1 row) postgres=# insert into public.test_c values (0,'2014-01-01', 'test','d'); INSERT 0 1 ``` best regards, digoal.
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Andrew GierthДата:
Сообщение: Re: BUG #15334: Partition elimination not working as expected when using enum as partition key
Следующее
От: Andrew GierthДата:
Сообщение: Re: BUG #15336: Wrong cursor's bacward fetch results in select with ALL(subquery)