BUG #15337: partition modify bug? cann't sync relcache in the samesession immediate? - Mailing list 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.