Re: percentile value check can be slow

On Sat, Nov 18, 2017 at 10:44:36AM -0500, Tom Lane wrote:
> jotpe <jotpe(at)posteo(dot)de> writes:
> > I tried to enter invalid percentile fractions, and was astonished
> > that it seems to be checked after many work is done?
>
> IIRC, only the aggregate's final-function is concerned with direct
> arguments, so it's not all that astonishing.

It may not be surprising from the point of view of a systems
programmer, but it's pretty surprising that this check is deferred to
many seconds in when the system has all the information it need in
order to establish this before execution begins.

I'm not sure I see an easy way to do this check early, but it's worth
trying on grounds of POLA violation. I have a couple of ideas on how
to do this, one less invasive but hinky, the other a lot more invasive
but better overall.

Ugly Hack With Ugly Performance Consequences:
Inject a subtransaction at the start of execution that supplies an
empty input to the final function with the supplied direct
arguments.

Bigger Lift:
Require a separate recognizer function direct arguments and fire
it during post-parse analysis. Perhaps this could be called
recognizer along with the corresponding mrecognizer. It's not
clear that it's sane to have separate ones, but I thought I'd
bring it up for completeness.

Way Bigger Lift, As Far As I Can Tell, But More Fun For Users:
Allow optional CHECK constraints in CREATE AGGREGATE for direct
arguments.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate