Re: inconsistent results querying table partitioned by date

On 2019/05/11 6:05, Tom Lane wrote:
> regression=# explain select * from dataid where id=1 and datadatetime < '2018-01-01'::timestamptz;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on dataid_default (cost=4.18..11.30 rows=3 width=12)
> Recheck Cond: ((id = 1) AND (datadatetime < '2018-01-01 00:00:00-05'::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-01-01 00:00:00-05'::timestamp with time zone))
> (4 rows)
>
> That's not fine. What we have here is a "timestamp < timestamptz"
> operator, which is only stable, therefore it might give different
> results at runtime than at plan time. You can't make plan-time
> pruning decisions with that. What we should have gotten here was
> an Append node that could do run-time pruning.

You're right. It seems that prune_append_rel_partitions() is forgetting
to filter mutable clauses from rel->baserestrictinfo, like
relation_excluded_by_constraints() does. I fixed that in the attached
0003 patch, which also adds a test for this scenario. I needed to also
tweak run-time pruning support code a bit so that it considers the cases
involving mutable functions as requiring (startup) run-time pruning, in
addition to the cases with mutable expressions. Adding David if he wants
to comment.

0003 patch cannot be applied as-is to both REL_11_STABLE and HEAD
branches, so I've attached two files, one for each branch.

BTW, while looking at this, I came across this comment in parse_coerce.c:
coerce_type():

* XXX if the typinput function is not immutable, we really ought to
* postpone evaluation of the function call until runtime. But there
* is no way to represent a typinput function call as an expression
* tree, because C-string values are not Datums. (XXX This *is*
* possible as of 7.3, do we want to do it?)

Should something be done about that?

Thanks,
Amit