Subject: Re: FETCH FIRST clause WITH TIES option
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Surafel Temesgen <surafel3000@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: 
 <CALAY4q9ky7rD_A4vf=FVQvCGngm3LOes-ky0J6euMrg=_Se+ag@mail.gmail.com>
 <40c9e6c5-4fc7-1589-0cc5-8a857405b91a@2ndquadrant.com>
 <87r2g87py9.fsf@news-spur.riddles.org.uk>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <942526de-1854-2bdc-ad17-e276c1efeb1d@2ndquadrant.com>
Date: Mon, 29 Oct 2018 17:08:38 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: <87r2g87py9.fsf@news-spur.riddles.org.uk>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 10/29/2018 04:17 PM, Andrew Gierth wrote:
>>>>>> "Tomas" == Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> 
>   > On 10/26/2018 12:28 PM, Surafel Temesgen wrote:
>   >> hello ,
>   >>
>   >> The WITH TIES keyword is sql standard that specifies any peers of
>   >> retained rows to retained in the result set too .which means
>   >> according to ordering key the result set can includes additional rows
>   >> which have ties on the last position, if there are any and It work
>   >> with ORDER BY query.
> 
>   Tomas> Thanks for the patch. I've looked at it today, and it seems
>   Tomas> mostly OK, with a couple of minor issues. Most of it is code
>   Tomas> formatting and comment wording issues, so I'm not going to go
>   Tomas> through them here - see the attached 0002 patch (0001 is your
>   Tomas> patch, rebased to current master).
> 
> I still think that this is the wrong approach. Implementing WITH TIES
> and PERCENT together using an implicit window function call kills two
> birds with one very small stone (the only executor change needed would
> be teaching LIMIT to be able to stop on a boolean condition), with
> maximum reuse of existing facilities.
> 

Hmmm, maybe. How would that work, exactly? Wouldn't that mean extra 
overhead (the window functions are hardly free) and limitations? Perhaps 
that was discussed in some other thread in the past?

FWIW, I doubt the patch can be much smaller/simpler - a significant part 
of the new stuff is in gram.y and node read/out infrastructure, the 
changes to LIMIT node are fairly minimal.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services