Re: inconsistent results querying table partitioned by date

Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> [ v2 patches ]

While this does seem to be fixing real bugs, it's failing to address my
point about stable vs. immutable operators.

Taking Alan's test case again (and using the v2 patch), consider:

regression=# explain select * from dataid where id=1 and datadatetime < '2018-01-01'::timestamp;
                                               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'::timestamp without 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'::timestamp without time zone))
(4 rows)

That's fine.  The given date is older than anything in the dataid_201902
partition, so we can prune.  But change it to this:

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.

            regards, tom lane