Re: BUG #16840: Rows not found in table partitioned by hash when not all partitions exists

Eh, too much work today. Please ignore my second email. All examples from
the first message are ok. Sorry.

Michał Albrycht

śr., 27 sty 2021 o 18:10 Michał Albrycht <michalalbrycht(at)gmail(dot)com>
napisał(a):

> Argh! I pasted the wrong value of volume_id in my examples. Number 1
> should be replaced with 2 so correct insert should look like this:
>
> INSERT INTO dir (volume_id, path) VALUES
> (2, 'abc'),
> (2, 'def'),
> (2, 'ghi');
>
> And queries:
> SELECT * FROM dir WHERE volume_id=2 AND path='abc';
> SELECT * FROM dir WHERE volume_id=2 AND (path='abc' OR path='def');
> SELECT * FROM dir_part_2 WHERE volume_id=2 AND (path='abc' or path='def');
>
> Sorry for creating unnecessary confusion.
>
> Michał Albrycht
>
> śr., 27 sty 2021 o 18:03 PG Bug reporting form <noreply(at)postgresql(dot)org>
> napisał(a):
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 16840
>> Logged by: Michał Albrycht
>> Email address: michalalbrycht(at)gmail(dot)com
>> PostgreSQL version: 13.1
>> Operating system: Ubuntu 18
>> Description:
>>
>> Lets create a table partitioned by hash:
>>
>> CREATE TABLE dir (
>> id SERIAL,
>> volume_id BIGINT,
>> path TEXT
>> ) PARTITION BY HASH (volume_id);
>>
>>
>> Now let's create just one partition:
>>
>> CREATE TABLE dir_part_2 PARTITION OF dir FOR VALUES WITH (modulus 3,
>> remainder 2);
>>
>> Insert sample value:
>>
>> INSERT INTO dir (volume_id, path) VALUES
>> (1, 'abc'),
>> (1, 'def'),
>> (1, 'ghi');
>>
>> Queries to verify db state:
>> SELECT * FROM dir WHERE volume_id=1 AND path='abc'; -- returns 1 row - OK
>> SELECT * FROM dir WHERE volume_id=1 AND (path='abc' OR path='def') --
>> returns 0 rows - NOT OK!
>> SELECT * FROM dir_part_2 WHERE volume_id=1 AND (path='abc' or path='def')
>> --
>> returns 2 rows - OK
>>
>> Now lets add missing partitions:
>> CREATE TABLE dir_part_0 PARTITION OF dir FOR VALUES WITH (modulus 3,
>> remainder 0);
>> CREATE TABLE dir_part_1 PARTITION OF dir FOR VALUES WITH (modulus 3,
>> remainder 1);
>>
>> And run problematic query one more time:
>> SELECT * FROM dir WHERE volume_id=1 AND (path='abc' OR path='def') --
>> returns 2 rows - OK
>>
>>
>> So Postgres silently fails to find a rows, and does not throw any error
>> when
>> not all hash partitions are present.
>>
>>