Re: Patch to add functionality to specify ORDER BY in CREATE FUNCTION for SRFs

Atri Sharma wrote
> If order of result rows is not the same as required, an error is raised:
>
> SELECT * FROM incorrect_order_nulls() ORDER BY e NULLS LAST;
> ERROR: Order not same as specified

First reaction for the error was unfavorable but (see below) it likely is
the best option and does adequately cover the reason for failure -
programmer error. It is not data specific (other than by accident) so any
runtime attempt to correct an error is going to be wasted effort and
overhead.

> In the inner joins thread, Tom mentioned having a new node which has
> multiple plans and executor can decide which plan to execute given runtime
> conditions. I played around with the idea, and am open to experiment
> having
> a new node which has a Sort based plan and is executed in case OrderCheck
> node sees that the inherent order of result tuples is not correct.
> Feedback
> here would be very welcome.

Could SQL functions be explored such that if the planner sees an order by it
omits the post-function sort node otherwise it adds an explicit one?

How expensive is sorting an already sorted result?

Runtime conditional sorting seems worse case, depending on implementation,
since a large result with an error in the last bit will end up nearly double
processing. I'd rather deem unsorted output programmer error and raise the
error so it is likely to be discovered during testing and not have any
meaningful runtime overhead.

David J.

--
View this message in context: http://postgresql.nabble.com/Patch-to-add-functionality-to-specify-ORDER-BY-in-CREATE-FUNCTION-for-SRFs-tp5832876p5832885.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.