On Fri, Nov 29, 2019 at 8:40 AM Andrew Gierth <and...@tao11.riddles.org.uk> wrote:
> This patch is a rather hacky implementation of the basic idea for > implementing FETCH ... WITH TIES, and potentially also PERCENT, by using > a window function expression to compute a stopping point. > > Large chunks of this (the parser/ruleutils changes, docs, tests) are > taken from Surafel Temesgen's patch. The difference is that the executor > change in my version is minimal: Limit allows a boolean column in the > input to signal the point at which to stop. The planner inserts a > WindowAgg node to compute the necessary condition using the rank() > function. > > The way this is done in the planner isn't (IMO) the best and should > probably be improved; in particular it currently misses some possible > optimizations (most notably constant-folding of the offset+limit > subexpression). I also haven't tested it properly to see whether I broke > anything, though it does pass regression. > > > Unlike most other executor node limit node has implementation for handling backward scan that support cursor operation but your approach didn't do this inherently because it outsource limitNode functionality to window function and window function didn't do this eg. postgres=# begin; BEGIN postgres=# declare c cursor for select i from generate_series(1,1000000) s(i) order by i fetch first 2 rows with ties; DECLARE CURSOR postgres=# fetch all in c; i --- 1 2 (2 rows) postgres=# fetch backward all in c; ERROR: cursor can only scan forward HINT: Declare it with SCROLL option to enable backward scan. Even with SCROLL option it is not working as limitNode does. It store the result and return in backward scan that use more space than current limit and limit with ties implementation. If am not mistaken the patch also reevaluate limit every time returning row beside its not good for performance its will return incorrect result with limit involving volatile function regards Surafel
