PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15337
PG Version10.5
OSCentOS 7.x x64
Opened2018-08-17 11:47:11+00
Reported byZhou Digoal
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15337
Logged by:          Zhou Digoal
Email address:      (redacted)
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.

Messages

DateAuthorSubject
2018-08-17 11:47:11+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15337: partition modify bug? cann't sync relcache in the same session immediate?