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

On 06-01-2015 PM 04:26, Atri Sharma wrote:
> On Tue, Jan 6, 2015 at 12:43 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp
>> wrote:
>> Though, I have no strong opinion on whether one thing is good or the
>> other or whether they cover some particular use case all the same.
>> Perhaps you can say that better.
>>
>>
> Personally, I think returning non ordered rows when ORDER BY clause is
> specifically specified by user is a gross violation of security and could
> lead to major user application breakdowns, since the application will trust
> that postgres will return the rows in order since ORDER BY was specified.
> Of course, what Ashutosh suggested makes the patch much simpler, but I
> would rather not go down that road.
> 

I think the same thing applies to IMMUTABLE declarations for example.
Planner trusts (or take as a hint) such declarations during, say,
constraint exclusion where quals involving non-immutable functions are
kept out of the exclusion proof. If a miscreant declares a non-immutable
function IMMUTABLE, then constraint violations may ensue simply because
planner trusted the miscreant. That is, such unsafe restrict clauses
would wrongly prove a partition as being unnecessary to scan. I am sure
there are other sites where such bets are made. In that light, I might
as well call them hints than anything.

<manual>
The volatility category is a *promise* to the optimizer about the
behavior of the function
</manual>

Though, as I said ordering behavior *may not be* a good candidate to
make such promises.

On the other hand, what such a thing might help with, are the situations
where a developer is frustrated because planner would ignore (or is
uninformed about) the order that the developer *knows* his function
produces.

But, if the node you propose to enforce the order is good enough, then
it may be worthwhile to go that route, :)

Thanks,
Amit