Re: inconsistent results querying table partitioned by date

On Thu, 16 May 2019 at 12:28, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> > Thinking about this more, if we're now making it so a forplanner =
> > true set of steps requires all values being compared to the partition
> > key to be Const, then anything that's not Const must require run-time
> > pruning. That means the:
>
> > else if (func_volatile(fnoid) != PROVOLATILE_IMMUTABLE)
>
> > in analyze_partkey_exprs() can simply become "else".
>
> Um ... what about a merely-stable comparison operator with a Const?

Oops. It seems I got my wires crossed there. Wasn't quite fully awake
when I started thinking about this earlier.

> The loop over steps, per se, isn't that expensive --- but extra syscache
> lookups are. Or at least that's my gut feeling about it. If we just had
> match_clause_to_partition_key mark the steps as being plan-time executable
> or not, we could avoid the repeat lookup.

okay, on re-think. I'm a little unsure of if you're mixing up "extra"
and "repeat", we do need an "extra" lookup because we've discovered
that strict ops are not safe to use during planning. I can't see
around having this extra call. If you mean it's a repeat call, then
it's really not, as we only do the op_volatile() check with
forplanner=true. With forplanner = false we only call
contain_var_clause() and contain_volatile_functions().

> >> * Teach match_clause_to_partition_key to not emit plan-time-usable
> >> quals at all if it is called with forplanner = false, or
>
> > That's not really possible. Imagine a table partitioned by HASH(a,b)
> > and WHERE a = $1 and b = 10; we can't do any pruning during planning
> > here, and if we only generate steps containing "a = $1" for run-time,
> > then we can't do anything there either.
>
> That's an interesting example, but how does it work now? I did not
> observe any code in there that seems to be aware of cross-dependencies
> between steps. Presumably somewhere we are combining those hash quals,
> so couldn't we mark the combined step properly?

In current HEAD we always generate steps for both quals when
generating steps for planning and execution.
partkey_datum_from_expr() just can't eval the qual when we're
executing the steps during planning. My patch skips the step
generation when the Expr is non-Const, so we save a few cycles and
some memory allocation there. Remember that we don't reuse steps
created for plan-time pruning in run-time pruning. A fresh set is
created.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services