Re: inconsistent results querying table partitioned by date

On 2019/05/10 8:22, Tom Lane wrote:
> Alan Jackson <ajax@tvsquared.com> writes:
>> Im having a problem with querying a table partitioned by date.
>> Depending on the sequence of operations on a date parameter used in a where clause, I either get the expected
results,or no results.
 
> 
> Yeah, this is pretty clearly broken.

Indeed it is.

> It looks to me like the partition
> pruning code is making insupportable assumptions about a comparison to
> a stable expression.  Using your example table:
> 
> regression=# explain select * from dataid where id=1 and datadatetime < localtimestamp;
      
 
>                                     QUERY PLAN                                    
> ----------------------------------------------------------------------------------
>  Bitmap Heap Scan on dataid_default  (cost=4.19..11.31 rows=3 width=12)
>    Recheck Cond: ((id = 1) AND (datadatetime < LOCALTIMESTAMP))
>    ->  Bitmap Index Scan on dataid_default_pkey  (cost=0.00..4.19 rows=3 width=0)
>          Index Cond: ((id = 1) AND (datadatetime < LOCALTIMESTAMP))
> (4 rows)
> 
> It should absolutely not have pruned away the dataid_201902 partition,
> but it did.
> 
> While I'm on about it, this behavior is also insupportable:
> 
> regression=# explain select * from dataid where id=1 and datadatetime < '2018-05-09'::timestamptz ;

(likely a typo in the condition: s/2018/2019)

>                                                QUERY PLAN                                               
> --------------------------------------------------------------------------------------------------------
>  Bitmap Heap Scan on dataid_default  (cost=4.18..11.30 rows=3 width=12)
>    Recheck Cond: ((id = 1) AND (datadatetime < '2018-05-09 00:00:00-04'::timestamp with time zone))
>    ->  Bitmap Index Scan on dataid_default_pkey  (cost=0.00..4.18 rows=3 width=0)
>          Index Cond: ((id = 1) AND (datadatetime < '2018-05-09 00:00:00-04'::timestamp with time zone))
> (4 rows)
> 
> because timestamp-against-timestamptz comparison is inherently only
> stable; the pruning code is way exceeding its authority by supposing
> that a comparison that holds at plan time will hold at runtime,
> even with a constant comparison value.

I looked into it and the problem is not really that plan-time pruning is
comparing stable expressions against partition bounds.  If it had, it
wouldn't have pruned dataid_201902 anyway, because its bounding range for
datadatetime is '2019-02-01' to '2019-03-01', which is clearly less than
'2019-05-09' (Tom's localtimestamp).

The real problem seems to be with the way range partition pruning assumes
an operator strategy to perform pruning with.  Quoted query's WHERE clause
looks something like this: (firstkey = CONSTANT AND secondkey <
STABLE_EXPRESSION).  From this set of clauses, a (CONSTANT,
STABLE_EXPRESSION) tuple is formed to be compared against partition bounds
using row-comparison-like semantics.  As things stand today, the actual
comparison function (partprune.c: get_matching_range_bounds()) receives
only the strategy of the last expression, which in this case is that of a
LESS operator.  When the tuple (CONSTANT, STABLE_EXPRESSION) passes
through the last step to extract Datum values to be passed to
get_matching_range_bounds, it's correctly determined that
STABLE_EXPRESSION cannot be computed during planning and so the tuple is
truncated to just (CONSTANT-Datum), but the strategy to assume during
pruning is still that of the LESS operator, whereas now it should really
be EQUAL.  With LESS semantics, get_matching_range_bounds() concludes that
no partition bounds are smaller than 1 (extracted from id=1 in the above
query), except the default partition, so it prunes 'dataid_201902'.

I've attached a patch to fix that.  Actually, I've attached two patches --
the 1st one adds a test for the misbehaving case with *wrong* output
wherein a partition is incorrectly pruned, and the 2nd actually fixes the
bug and updates the output of the test added by the 1st patch.  Divided
the patch this way just to show the bug clearly.

> Seems to be equally broken in v11 and HEAD.  I didn't try v10.

v10 is fine, as it uses constraint exclusion.

Attached patches apply to both v11 and HEAD.

Thanks,
Amit