Re: Reverse collations (initially for making keyset pagination cover more cases)

>>>>> "David" == David Fetter <david(at)fetter(dot)org> writes:

First, in testing the patch I found there were indeed some missing
cases: the sortsupport version of the comparator needs to be fixed too.
I attach a draft addition to your patch, you should probably look at
adding test cases that need this to work.

David> (a, b, c) < ($1, $2 COLLATE "C_backwards", $3)
David> ...
David> ORDER BY a, b DESC, c

That would have to be:

WHERE (a, b COLLATE "C_backwards", c) < ($1, $2, $3)
...
ORDER BY a, b COLLATE "C_backwards", c

Adding the below patch to yours, I can get this on the regression test
db (note that this is a -O0 asserts build, timings may be slow relative
to a production build):

create collation "C_rev" ( LOCALE = "C", REVERSE = true );
create index on tenk1 (hundred, (stringu1::text collate "C_rev"), string4);

explain analyze
select hundred, stringu1::text, string4
from tenk1
where (hundred, stringu1::text COLLATE "C_rev", string4)
> (10, 'WKAAAA', 'VVVVxx')
order by hundred, (stringu1::text collate "C_rev"), string4
limit 5;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..1.28 rows=5 width=132) (actual time=0.029..0.038 rows=5 loops=1)
-> Index Scan using tenk1_hundred_stringu1_string4_idx on tenk1 (cost=0.29..1768.49 rows=8900 width=132) (actual time=0.028..0.036 rows=5 loops=1)
Index Cond: (ROW(hundred, ((stringu1)::text)::text, string4) > ROW(10, 'WKAAAA'::text, 'VVVVxx'::name))
Planning Time: 0.225 ms
Execution Time: 0.072 ms
(5 rows)

and I checked the results, and they look correct now.

--
Andrew (irc:RhodiumToad)