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
Preorder columns are first transformed into SortGroupClauses first and then stored directly in pg_proc.
This functionality is a user case seen functionality, and is especially useful when SRF inputs are large and/or might be pipelined from another function (SRFs are used in pipelines in analytical systems many times, with large data).
The overhead of this patch is small. A new path is added for the preorder keys, and OrderCheck node's additional cost is pretty low, given that it only compares two rows and stores only a single row (previous row seen), hence the memory footprint is minuscule.
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.
I will add the patch to current commitfest.
Thoughts?
Regards,
Atri