Hi,
On 2019-10-24 11:10:34 -0700, Andres Freund wrote:
> On 2019-10-15 13:05:32 +0100, Peter Geoghegan wrote:
> > > - aborted abbreviated keys (lines 1522, 1608, 1774, 3620, 3739, 3867,
> > > 4266)
> >
> > Also hard to test -- there was a bug here when abbreviated keys first
> > went in -- that was detected by amcheck.
> >
> > All of the places where we abort are essentially the same, though.
>
> Why is it that hard? Seems fairly easy to create cases that reliably
> abort.
>
> I really don't think it's ok to have as many abbrev abort related paths
> without any coverage - the relevant code isn't that trivial. And
> something like amcheck really doesn't strike me as sufficient. For one,
> it doesn't provide any coverage either. For another, plenty sorts don't
> end up in a form that amcheck sees.
>
> Tests aren't just there to verify that the current behaviour isn't
> broken, they're also there to allow to develop with some confidence. And
> I don't think tuplesort as is really allows that, and e.g. abbreviated
> keys made that substantially worse. That happens, but I think it'd be
> good if you could help improving the situation.
>
> E.g.
> SELECT * FROM (SELECT ('00000000-0000-0000-0000-'||to_char(g.i,
> '000000000000FM'))::uuid uuid FROM generate_series(15000, 0, -1) g(i)) d
> ORDER BY uuid
> reliably triggers abbreviated keys, and it looks to me like that should
> be portable. With a few tweaks it'd be fairly easy to use that to
> provide some OK coverage for most the abbrev key cases.
Here's a first stab at getting the coverage of tuplesort.c to a
satisfying level. There's still bits uncovered, but that's largely
either a) trace_sort related b) hopefully unreachable stuff c) explain
related. The largest actually missing thing is a disk-based
mark/restore, which probably ought be covered.
I think the the test time of this would still be OK, but if not we could
also work a bit more on that angle.
I'm pretty sure there's some minor copy & paste mistakes in the test,
but I want to get this out there and get some reactions before investing
further time.
Peter, Tom?
- Andres
>From 3c987fa79d1d2341869de226879a7201e035040a Mon Sep 17 00:00:00 2001
From: Andres Freund <and...@anarazel.de>
Date: Thu, 24 Oct 2019 13:58:40 -0700
Subject: [PATCH v1 1/2] Add tests for tuplesort.c.
Author:
Reviewed-By:
Discussion: https://postgr.es/m/
Backpatch:
---
src/test/regress/expected/tuplesort.out | 591 ++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/tuplesort.sql | 258 +++++++++++
3 files changed, 850 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/tuplesort.out
create mode 100644 src/test/regress/sql/tuplesort.sql
diff --git a/src/test/regress/expected/tuplesort.out b/src/test/regress/expected/tuplesort.out
new file mode 100644
index 00000000000..d3b99df73ae
--- /dev/null
+++ b/src/test/regress/expected/tuplesort.out
@@ -0,0 +1,591 @@
+-- only use parallelism when explicitly intending to do so
+SET max_parallel_maintenance_workers = 0;
+SET max_parallel_workers = 0;
+-- A table with with contents that, when sorted, triggers abbreviated
+-- key aborts. One easy way to achieve that is to use uuids that all
+-- have the same prefix, as abbreviated keys for uuids just use the
+-- first sizeof(Datum) bytes.
+DROP TABLE IF EXISTS abbrev_abort_uuids;
+NOTICE: table "abbrev_abort_uuids" does not exist, skipping
+CREATE TABLE abbrev_abort_uuids (
+ id serial not null,
+ abort_increasing uuid,
+ abort_decreasing uuid,
+ noabort_increasing uuid,
+ noabort_decreasing uuid);
+INSERT INTO abbrev_abort_uuids (abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing)
+ SELECT
+ ('00000000-0000-0000-0000-'||to_char(g.i, '000000000000FM'))::uuid abort_increasing,
+ ('00000000-0000-0000-0000-'||to_char(20000 - g.i, '000000000000FM'))::uuid abort_decreasing,
+ (to_char(g.i % 10009, '00000000FM')||'-0000-0000-0000-'||to_char(g.i, '000000000000FM'))::uuid noabort_increasing,
+ (to_char(((20000 - g.i) % 10009), '00000000FM')||'-0000-0000-0000-'||to_char(20000 - g.i, '000000000000FM'))::uuid noabort_decreasing
+ FROM generate_series(0, 20000, 1) g(i);
+-- and a few NULLs
+INSERT INTO abbrev_abort_uuids(id) VALUES(0);
+INSERT INTO abbrev_abort_uuids DEFAULT VALUES;
+INSERT INTO abbrev_abort_uuids DEFAULT VALUES;
+-- add just a few duplicates
+INSERT INTO abbrev_abort_uuids (abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing)
+ SELECT abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+ FROM abbrev_abort_uuids
+ WHERE (id < 10 OR id > 19990) AND id % 3 = 0 AND abort_increasing is not null;
+----
+-- Check sort node uses of tuplesort wrt. abbreviated keys
+----
+-- plain sort triggering abbreviated abort
+SELECT abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing OFFSET 20000 - 4;
+ abort_increasing | abort_decreasing
+--------------------------------------+--------------------------------------
+ 00000000-0000-0000-0000-000000019992 | 00000000-0000-0000-0000-000000000008
+ 00000000-0000-0000-0000-000000019993 | 00000000-0000-0000-0000-000000000007
+ 00000000-0000-0000-0000-000000019994 | 00000000-0000-0000-0000-000000000006
+ 00000000-0000-0000-0000-000000019994 | 00000000-0000-0000-0000-000000000006
+ 00000000-0000-0000-0000-000000019995 | 00000000-0000-0000-0000-000000000005
+ 00000000-0000-0000-0000-000000019996 | 00000000-0000-0000-0000-000000000004
+ 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003
+ 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003
+ 00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002
+ 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001
+ 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ |
+ |
+ |
+(15 rows)
+
+SELECT abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing NULLS FIRST OFFSET 20000 - 4;
+ abort_increasing | abort_decreasing
+--------------------------------------+--------------------------------------
+ 00000000-0000-0000-0000-000000000011 | 00000000-0000-0000-0000-000000019989
+ 00000000-0000-0000-0000-000000000010 | 00000000-0000-0000-0000-000000019990
+ 00000000-0000-0000-0000-000000000009 | 00000000-0000-0000-0000-000000019991
+ 00000000-0000-0000-0000-000000000008 | 00000000-0000-0000-0000-000000019992
+ 00000000-0000-0000-0000-000000000008 | 00000000-0000-0000-0000-000000019992
+ 00000000-0000-0000-0000-000000000007 | 00000000-0000-0000-0000-000000019993
+ 00000000-0000-0000-0000-000000000006 | 00000000-0000-0000-0000-000000019994
+ 00000000-0000-0000-0000-000000000005 | 00000000-0000-0000-0000-000000019995
+ 00000000-0000-0000-0000-000000000005 | 00000000-0000-0000-0000-000000019995
+ 00000000-0000-0000-0000-000000000004 | 00000000-0000-0000-0000-000000019996
+ 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997
+ 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998
+ 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998
+ 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999
+ 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000
+(15 rows)
+
+-- plain sort not triggering abbreviated abort
+SELECT noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing OFFSET 20000 - 4;
+ noabort_increasing | noabort_decreasing
+--------------------------------------+--------------------------------------
+ 00009997-0000-0000-0000-000000009997 | 00010003-0000-0000-0000-000000010003
+ 00009998-0000-0000-0000-000000009998 | 00010002-0000-0000-0000-000000010002
+ 00009999-0000-0000-0000-000000009999 | 00010001-0000-0000-0000-000000010001
+ 00010000-0000-0000-0000-000000010000 | 00010000-0000-0000-0000-000000010000
+ 00010001-0000-0000-0000-000000010001 | 00009999-0000-0000-0000-000000009999
+ 00010002-0000-0000-0000-000000010002 | 00009998-0000-0000-0000-000000009998
+ 00010003-0000-0000-0000-000000010003 | 00009997-0000-0000-0000-000000009997
+ 00010004-0000-0000-0000-000000010004 | 00009996-0000-0000-0000-000000009996
+ 00010005-0000-0000-0000-000000010005 | 00009995-0000-0000-0000-000000009995
+ 00010006-0000-0000-0000-000000010006 | 00009994-0000-0000-0000-000000009994
+ 00010007-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993
+ 00010008-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992
+ |
+ |
+ |
+(15 rows)
+
+SELECT noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing NULLS FIRST OFFSET 20000 - 4;
+ noabort_increasing | noabort_decreasing
+--------------------------------------+--------------------------------------
+ 00010006-0000-0000-0000-000000010006 | 00009994-0000-0000-0000-000000009994
+ 00010005-0000-0000-0000-000000010005 | 00009995-0000-0000-0000-000000009995
+ 00010004-0000-0000-0000-000000010004 | 00009996-0000-0000-0000-000000009996
+ 00010003-0000-0000-0000-000000010003 | 00009997-0000-0000-0000-000000009997
+ 00010002-0000-0000-0000-000000010002 | 00009998-0000-0000-0000-000000009998
+ 00010001-0000-0000-0000-000000010001 | 00009999-0000-0000-0000-000000009999
+ 00010000-0000-0000-0000-000000010000 | 00010000-0000-0000-0000-000000010000
+ 00009999-0000-0000-0000-000000009999 | 00010001-0000-0000-0000-000000010001
+ 00009998-0000-0000-0000-000000009998 | 00010002-0000-0000-0000-000000010002
+ 00009997-0000-0000-0000-000000009997 | 00010003-0000-0000-0000-000000010003
+ 00009996-0000-0000-0000-000000009996 | 00010004-0000-0000-0000-000000010004
+ 00009995-0000-0000-0000-000000009995 | 00010005-0000-0000-0000-000000010005
+ 00009994-0000-0000-0000-000000009994 | 00010006-0000-0000-0000-000000010006
+ 00009993-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007
+ 00009992-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008
+(15 rows)
+
+-- bounded sort (disables abbreviated keys)
+SELECT abort_increasing, noabort_increasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5;
+ abort_increasing | noabort_increasing
+--------------------------------------+--------------------------------------
+ 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000000000
+ 00000000-0000-0000-0000-000000000001 | 00000001-0000-0000-0000-000000000001
+ 00000000-0000-0000-0000-000000000002 | 00000002-0000-0000-0000-000000000002
+ 00000000-0000-0000-0000-000000000002 | 00000002-0000-0000-0000-000000000002
+ 00000000-0000-0000-0000-000000000003 | 00000003-0000-0000-0000-000000000003
+(5 rows)
+
+SELECT abort_increasing, noabort_increasing FROM abbrev_abort_uuids ORDER BY noabort_increasing NULLS FIRST LIMIT 5;
+ abort_increasing | noabort_increasing
+--------------------------------------+--------------------------------------
+ |
+ |
+ |
+ 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000000000
+ 00000000-0000-0000-0000-000000010009 | 00000000-0000-0000-0000-000000010009
+(5 rows)
+
+----
+-- Check index creation uses of tuplesort wrt. abbreviated keys
+----
+-- index creation using abbreviated keys successfully
+CREATE INDEX abbrev_abort_uuids__noabort_increasing_idx ON abbrev_abort_uuids (noabort_increasing);
+CREATE INDEX abbrev_abort_uuids__noabort_decreasing_idx ON abbrev_abort_uuids (noabort_decreasing);
+-- verify
+EXPLAIN SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing LIMIT 5;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
+ Limit (cost=0.29..0.69 rows=5 width=36)
+ -> Index Scan using abbrev_abort_uuids__noabort_increasing_idx on abbrev_abort_uuids (cost=0.29..1608.45 rows=20011 width=36)
+(2 rows)
+
+SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing LIMIT 5;
+ id | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------
+ 1 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
+ 10010 | 00000000-0000-0000-0000-000000010009 | 00009991-0000-0000-0000-000000009991
+ 2 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
+ 10011 | 00000001-0000-0000-0000-000000010010 | 00009990-0000-0000-0000-000000009990
+ 3 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
+(5 rows)
+
+EXPLAIN SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing LIMIT 5;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
+ Limit (cost=0.29..0.69 rows=5 width=36)
+ -> Index Scan using abbrev_abort_uuids__noabort_decreasing_idx on abbrev_abort_uuids (cost=0.29..1608.45 rows=20011 width=36)
+(2 rows)
+
+SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing LIMIT 5;
+ id | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------
+ 20001 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20010 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 9992 | 00009991-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009
+ 20000 | 00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001
+ 9991 | 00009990-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010
+(5 rows)
+
+-- index creation using abbreviated keys, hitting abort
+CREATE INDEX abbrev_abort_uuids__abort_increasing_idx ON abbrev_abort_uuids (abort_increasing);
+CREATE INDEX abbrev_abort_uuids__abort_decreasing_idx ON abbrev_abort_uuids (abort_decreasing);
+-- verify
+EXPLAIN SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
+ Limit (cost=0.29..0.69 rows=5 width=36)
+ -> Index Scan using abbrev_abort_uuids__abort_increasing_idx on abbrev_abort_uuids (cost=0.29..1608.45 rows=20011 width=36)
+(2 rows)
+
+SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5;
+ id | abort_increasing | abort_decreasing
+-------+--------------------------------------+--------------------------------------
+ 1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000
+ 2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999
+ 3 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998
+ 20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998
+ 4 | 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997
+(5 rows)
+
+EXPLAIN SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing LIMIT 5;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
+ Limit (cost=0.29..0.69 rows=5 width=36)
+ -> Index Scan using abbrev_abort_uuids__abort_decreasing_idx on abbrev_abort_uuids (cost=0.29..1608.45 rows=20011 width=36)
+(2 rows)
+
+SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing LIMIT 5;
+ id | abort_increasing | abort_decreasing
+-------+--------------------------------------+--------------------------------------
+ 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20000 | 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001
+ 19999 | 00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002
+ 19998 | 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003
+(5 rows)
+
+----
+-- Check CLUSTER uses of tuplesort wrt. abbreviated keys
+----
+-- when aborting, increasing order
+BEGIN;
+SET LOCAL enable_indexscan = false;
+CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__abort_increasing_idx;
+-- head
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid LIMIT 5;
+ id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
+ 2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
+ 3 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
+ 20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
+ 4 | 00000000-0000-0000-0000-000000000003 | 00000000-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003 | 00009988-0000-0000-0000-000000019997
+(5 rows)
+
+-- tail
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid DESC LIMIT 5;
+ id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 0 | | | |
+ 20002 | | | |
+ 20003 | | | |
+ 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+(5 rows)
+
+ROLLBACK;
+-- when aborting, decreasing order
+BEGIN;
+SET LOCAL enable_indexscan = false;
+CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__abort_decreasing_idx;
+-- head
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid LIMIT 5;
+ id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20000 | 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001
+ 19999 | 00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002
+ 20009 | 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003 | 00009988-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003
+(5 rows)
+
+-- tail
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid DESC LIMIT 5;
+ id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 0 | | | |
+ 20002 | | | |
+ 20003 | | | |
+ 1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
+ 2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
+(5 rows)
+
+ROLLBACK;
+-- when not aborting, increasing order
+BEGIN;
+SET LOCAL enable_indexscan = false;
+CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__noabort_increasing_idx;
+-- head
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid LIMIT 5;
+ id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000
+ 10010 | 00000000-0000-0000-0000-000000010009 | 00000000-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 | 00009991-0000-0000-0000-000000009991
+ 2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999
+ 10011 | 00000000-0000-0000-0000-000000010010 | 00000000-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010 | 00009990-0000-0000-0000-000000009990
+ 20004 | 00000000-0000-0000-0000-000000000002 | 00000000-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998
+(5 rows)
+
+-- tail
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid DESC LIMIT 5;
+ id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 0 | | | |
+ 20002 | | | |
+ 20003 | | | |
+ 10009 | 00000000-0000-0000-0000-000000010008 | 00000000-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992
+ 10008 | 00000000-0000-0000-0000-000000010007 | 00000000-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993
+(5 rows)
+
+ROLLBACK;
+-- when no aborting, decreasing order
+BEGIN;
+SET LOCAL enable_indexscan = false;
+CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__noabort_decreasing_idx;
+-- head
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid LIMIT 5;
+ id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000
+ 9992 | 00000000-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 | 00009991-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009
+ 20000 | 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001
+ 9991 | 00000000-0000-0000-0000-000000009990 | 00000000-0000-0000-0000-000000010010 | 00009990-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010
+(5 rows)
+
+-- tail
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid DESC LIMIT 5;
+ id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
+-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
+ 0 | | | |
+ 20003 | | | |
+ 20002 | | | |
+ 9993 | 00000000-0000-0000-0000-000000009992 | 00000000-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008
+ 9994 | 00000000-0000-0000-0000-000000009993 | 00000000-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007
+(5 rows)
+
+ROLLBACK;
+----
+-- test forward and backward scans for in-memory and disk based tuplesort
+----
+-- in-memory
+BEGIN;
+SET LOCAL enable_indexscan = false;
+-- unfortunately can't show analyze output confirming sort method,
+-- the memory used output wouldn't be stable
+EXPLAIN (COSTS OFF) DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
+ QUERY PLAN
+--------------------------------------
+ Sort
+ Sort Key: noabort_decreasing
+ -> Seq Scan on abbrev_abort_uuids
+(3 rows)
+
+DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
+-- first and second
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------------------------
+ 00000000-0000-0000-0000-000000000000
+(1 row)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------------------------
+ 00000000-0000-0000-0000-000000000000
+(1 row)
+
+-- scroll beyond beginning
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------------------------
+ 00000000-0000-0000-0000-000000000000
+(1 row)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------------------------
+ 00000000-0000-0000-0000-000000000000
+(1 row)
+
+-- scroll beyond end end
+FETCH LAST FROM c;
+ noabort_decreasing
+--------------------
+
+(1 row)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+
+(1 row)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------
+
+(1 row)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+
+(1 row)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+COMMIT;
+-- disk based
+BEGIN;
+SET LOCAL enable_indexscan = false;
+SET LOCAL work_mem = '100kB';
+-- unfortunately can't show analyze output confirming sort method,
+-- the memory used output wouldn't be stable
+EXPLAIN (COSTS OFF) DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
+ QUERY PLAN
+--------------------------------------
+ Sort
+ Sort Key: noabort_decreasing
+ -> Seq Scan on abbrev_abort_uuids
+(3 rows)
+
+DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
+-- first and second
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------------------------
+ 00000000-0000-0000-0000-000000000000
+(1 row)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------------------------
+ 00000000-0000-0000-0000-000000000000
+(1 row)
+
+-- scroll beyond beginning
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------------------------
+ 00000000-0000-0000-0000-000000000000
+(1 row)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------------------------
+ 00000000-0000-0000-0000-000000000000
+(1 row)
+
+-- scroll beyond end end
+FETCH LAST FROM c;
+ noabort_decreasing
+--------------------
+
+(1 row)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+
+(1 row)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------
+
+(1 row)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+FETCH BACKWARD FROM c;
+ noabort_decreasing
+--------------------
+
+(1 row)
+
+FETCH NEXT FROM c;
+ noabort_decreasing
+--------------------
+(0 rows)
+
+COMMIT;
+----
+-- test tuplesort using both in-memory and disk sort
+---
+-- memory based
+SELECT
+ -- fixed-width by-value datum
+ (array_agg(id ORDER BY id DESC NULLS FIRST))[0:5],
+ -- fixed-width by-ref datum
+ (array_agg(abort_increasing ORDER BY abort_increasing DESC NULLS LAST))[0:5],
+ -- variable-width datum
+ (array_agg(id::text ORDER BY id::text DESC NULLS LAST))[0:5],
+ -- fixed width by-value datum tuplesort
+ percentile_disc(0.99) WITHIN GROUP (ORDER BY id),
+ -- ensure state is shared
+ percentile_disc(0.01) WITHIN GROUP (ORDER BY id),
+ -- fixed width by-ref datum tuplesort
+ percentile_disc(0.8) WITHIN GROUP (ORDER BY abort_increasing),
+ -- variable width by-ref datum tuplesort
+ percentile_disc(0.2) WITHIN GROUP (ORDER BY id::text),
+ -- multi-column tuplesort
+ rank('00000000-0000-0000-0000-000000000000', '2', '2') WITHIN GROUP (ORDER BY noabort_increasing, id, id::text)
+FROM (
+ SELECT * FROM abbrev_abort_uuids
+ UNION ALL
+ SELECT NULL, NULL, NULL, NULL, NULL) s;
+ array_agg | array_agg | array_agg | percentile_disc | percentile_disc | percentile_disc | percentile_disc | rank
+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------------+-----------------+--------------------------------------+-----------------+------
+ {NULL,20010,20009,20008,20007} | {00000000-0000-0000-0000-000000020000,00000000-0000-0000-0000-000000020000,00000000-0000-0000-0000-000000019999,00000000-0000-0000-0000-000000019998,00000000-0000-0000-0000-000000019997} | {9999,9998,9997,9996,9995} | 19810 | 200 | 00000000-0000-0000-0000-000000016003 | 136 | 2
+(1 row)
+
+-- disk based (see also above)
+BEGIN;
+SET LOCAL work_mem = '100kB';
+SELECT
+ (array_agg(id ORDER BY id DESC NULLS FIRST))[0:5],
+ (array_agg(abort_increasing ORDER BY abort_increasing DESC NULLS LAST))[0:5],
+ (array_agg(id::text ORDER BY id::text DESC NULLS LAST))[0:5],
+ percentile_disc(0.99) WITHIN GROUP (ORDER BY id),
+ percentile_disc(0.01) WITHIN GROUP (ORDER BY id),
+ percentile_disc(0.8) WITHIN GROUP (ORDER BY abort_increasing),
+ percentile_disc(0.2) WITHIN GROUP (ORDER BY id::text),
+ rank('00000000-0000-0000-0000-000000000000', '2', '2') WITHIN GROUP (ORDER BY noabort_increasing, id, id::text)
+FROM (
+ SELECT * FROM abbrev_abort_uuids
+ UNION ALL
+ SELECT NULL, NULL, NULL, NULL, NULL) s;
+ array_agg | array_agg | array_agg | percentile_disc | percentile_disc | percentile_disc | percentile_disc | rank
+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------------+-----------------+--------------------------------------+-----------------+------
+ {NULL,20010,20009,20008,20007} | {00000000-0000-0000-0000-000000020000,00000000-0000-0000-0000-000000020000,00000000-0000-0000-0000-000000019999,00000000-0000-0000-0000-000000019998,00000000-0000-0000-0000-000000019997} | {9999,9998,9997,9996,9995} | 19810 | 200 | 00000000-0000-0000-0000-000000016003 | 136 | 2
+(1 row)
+
+ROLLBACK;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index fc0f14122bb..d33a4e143dc 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -112,7 +112,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr
# ----------
# Another group of parallel tests
# ----------
-test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info
+test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/sql/tuplesort.sql b/src/test/regress/sql/tuplesort.sql
new file mode 100644
index 00000000000..ad47dd3168f
--- /dev/null
+++ b/src/test/regress/sql/tuplesort.sql
@@ -0,0 +1,258 @@
+-- only use parallelism when explicitly intending to do so
+SET max_parallel_maintenance_workers = 0;
+SET max_parallel_workers = 0;
+
+-- A table with with contents that, when sorted, triggers abbreviated
+-- key aborts. One easy way to achieve that is to use uuids that all
+-- have the same prefix, as abbreviated keys for uuids just use the
+-- first sizeof(Datum) bytes.
+DROP TABLE IF EXISTS abbrev_abort_uuids;
+
+CREATE TABLE abbrev_abort_uuids (
+ id serial not null,
+ abort_increasing uuid,
+ abort_decreasing uuid,
+ noabort_increasing uuid,
+ noabort_decreasing uuid);
+
+INSERT INTO abbrev_abort_uuids (abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing)
+ SELECT
+ ('00000000-0000-0000-0000-'||to_char(g.i, '000000000000FM'))::uuid abort_increasing,
+ ('00000000-0000-0000-0000-'||to_char(20000 - g.i, '000000000000FM'))::uuid abort_decreasing,
+ (to_char(g.i % 10009, '00000000FM')||'-0000-0000-0000-'||to_char(g.i, '000000000000FM'))::uuid noabort_increasing,
+ (to_char(((20000 - g.i) % 10009), '00000000FM')||'-0000-0000-0000-'||to_char(20000 - g.i, '000000000000FM'))::uuid noabort_decreasing
+ FROM generate_series(0, 20000, 1) g(i);
+
+-- and a few NULLs
+INSERT INTO abbrev_abort_uuids(id) VALUES(0);
+INSERT INTO abbrev_abort_uuids DEFAULT VALUES;
+INSERT INTO abbrev_abort_uuids DEFAULT VALUES;
+
+-- add just a few duplicates
+INSERT INTO abbrev_abort_uuids (abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing)
+ SELECT abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+ FROM abbrev_abort_uuids
+ WHERE (id < 10 OR id > 19990) AND id % 3 = 0 AND abort_increasing is not null;
+
+----
+-- Check sort node uses of tuplesort wrt. abbreviated keys
+----
+
+-- plain sort triggering abbreviated abort
+SELECT abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing OFFSET 20000 - 4;
+SELECT abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing NULLS FIRST OFFSET 20000 - 4;
+
+-- plain sort not triggering abbreviated abort
+SELECT noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing OFFSET 20000 - 4;
+SELECT noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing NULLS FIRST OFFSET 20000 - 4;
+
+-- bounded sort (disables abbreviated keys)
+SELECT abort_increasing, noabort_increasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5;
+SELECT abort_increasing, noabort_increasing FROM abbrev_abort_uuids ORDER BY noabort_increasing NULLS FIRST LIMIT 5;
+
+
+----
+-- Check index creation uses of tuplesort wrt. abbreviated keys
+----
+
+-- index creation using abbreviated keys successfully
+CREATE INDEX abbrev_abort_uuids__noabort_increasing_idx ON abbrev_abort_uuids (noabort_increasing);
+CREATE INDEX abbrev_abort_uuids__noabort_decreasing_idx ON abbrev_abort_uuids (noabort_decreasing);
+
+-- verify
+EXPLAIN SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing LIMIT 5;
+SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_increasing LIMIT 5;
+EXPLAIN SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing LIMIT 5;
+SELECT id, noabort_increasing, noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing LIMIT 5;
+
+-- index creation using abbreviated keys, hitting abort
+CREATE INDEX abbrev_abort_uuids__abort_increasing_idx ON abbrev_abort_uuids (abort_increasing);
+CREATE INDEX abbrev_abort_uuids__abort_decreasing_idx ON abbrev_abort_uuids (abort_decreasing);
+
+-- verify
+EXPLAIN SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5;
+SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_increasing LIMIT 5;
+EXPLAIN SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing LIMIT 5;
+SELECT id, abort_increasing, abort_decreasing FROM abbrev_abort_uuids ORDER BY abort_decreasing LIMIT 5;
+
+
+----
+-- Check CLUSTER uses of tuplesort wrt. abbreviated keys
+----
+
+-- when aborting, increasing order
+BEGIN;
+SET LOCAL enable_indexscan = false;
+CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__abort_increasing_idx;
+
+-- head
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid LIMIT 5;
+
+-- tail
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid DESC LIMIT 5;
+ROLLBACK;
+
+-- when aborting, decreasing order
+BEGIN;
+SET LOCAL enable_indexscan = false;
+CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__abort_decreasing_idx;
+
+-- head
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid LIMIT 5;
+
+-- tail
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid DESC LIMIT 5;
+ROLLBACK;
+
+-- when not aborting, increasing order
+BEGIN;
+SET LOCAL enable_indexscan = false;
+CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__noabort_increasing_idx;
+
+-- head
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid LIMIT 5;
+
+-- tail
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid DESC LIMIT 5;
+ROLLBACK;
+
+-- when no aborting, decreasing order
+BEGIN;
+SET LOCAL enable_indexscan = false;
+CLUSTER abbrev_abort_uuids USING abbrev_abort_uuids__noabort_decreasing_idx;
+
+-- head
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid LIMIT 5;
+
+-- tail
+SELECT id, abort_increasing, abort_decreasing, noabort_increasing, noabort_decreasing
+FROM abbrev_abort_uuids
+ORDER BY ctid DESC LIMIT 5;
+ROLLBACK;
+
+----
+-- test forward and backward scans for in-memory and disk based tuplesort
+----
+
+-- in-memory
+BEGIN;
+SET LOCAL enable_indexscan = false;
+-- unfortunately can't show analyze output confirming sort method,
+-- the memory used output wouldn't be stable
+EXPLAIN (COSTS OFF) DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
+DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
+
+-- first and second
+FETCH NEXT FROM c;
+FETCH NEXT FROM c;
+
+-- scroll beyond beginning
+FETCH BACKWARD FROM c;
+FETCH BACKWARD FROM c;
+FETCH BACKWARD FROM c;
+FETCH BACKWARD FROM c;
+FETCH NEXT FROM c;
+
+-- scroll beyond end end
+FETCH LAST FROM c;
+FETCH BACKWARD FROM c;
+FETCH NEXT FROM c;
+FETCH NEXT FROM c;
+FETCH NEXT FROM c;
+FETCH BACKWARD FROM c;
+FETCH NEXT FROM c;
+
+COMMIT;
+
+-- disk based
+BEGIN;
+SET LOCAL enable_indexscan = false;
+SET LOCAL work_mem = '100kB';
+-- unfortunately can't show analyze output confirming sort method,
+-- the memory used output wouldn't be stable
+EXPLAIN (COSTS OFF) DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
+DECLARE c SCROLL CURSOR FOR SELECT noabort_decreasing FROM abbrev_abort_uuids ORDER BY noabort_decreasing;
+
+-- first and second
+FETCH NEXT FROM c;
+FETCH NEXT FROM c;
+
+-- scroll beyond beginning
+FETCH BACKWARD FROM c;
+FETCH BACKWARD FROM c;
+FETCH BACKWARD FROM c;
+FETCH BACKWARD FROM c;
+FETCH NEXT FROM c;
+
+-- scroll beyond end end
+FETCH LAST FROM c;
+FETCH BACKWARD FROM c;
+FETCH NEXT FROM c;
+FETCH NEXT FROM c;
+FETCH NEXT FROM c;
+FETCH BACKWARD FROM c;
+FETCH NEXT FROM c;
+
+COMMIT;
+
+
+----
+-- test tuplesort using both in-memory and disk sort
+---
+
+-- memory based
+SELECT
+ -- fixed-width by-value datum
+ (array_agg(id ORDER BY id DESC NULLS FIRST))[0:5],
+ -- fixed-width by-ref datum
+ (array_agg(abort_increasing ORDER BY abort_increasing DESC NULLS LAST))[0:5],
+ -- variable-width datum
+ (array_agg(id::text ORDER BY id::text DESC NULLS LAST))[0:5],
+ -- fixed width by-value datum tuplesort
+ percentile_disc(0.99) WITHIN GROUP (ORDER BY id),
+ -- ensure state is shared
+ percentile_disc(0.01) WITHIN GROUP (ORDER BY id),
+ -- fixed width by-ref datum tuplesort
+ percentile_disc(0.8) WITHIN GROUP (ORDER BY abort_increasing),
+ -- variable width by-ref datum tuplesort
+ percentile_disc(0.2) WITHIN GROUP (ORDER BY id::text),
+ -- multi-column tuplesort
+ rank('00000000-0000-0000-0000-000000000000', '2', '2') WITHIN GROUP (ORDER BY noabort_increasing, id, id::text)
+FROM (
+ SELECT * FROM abbrev_abort_uuids
+ UNION ALL
+ SELECT NULL, NULL, NULL, NULL, NULL) s;
+
+-- disk based (see also above)
+BEGIN;
+SET LOCAL work_mem = '100kB';
+
+SELECT
+ (array_agg(id ORDER BY id DESC NULLS FIRST))[0:5],
+ (array_agg(abort_increasing ORDER BY abort_increasing DESC NULLS LAST))[0:5],
+ (array_agg(id::text ORDER BY id::text DESC NULLS LAST))[0:5],
+ percentile_disc(0.99) WITHIN GROUP (ORDER BY id),
+ percentile_disc(0.01) WITHIN GROUP (ORDER BY id),
+ percentile_disc(0.8) WITHIN GROUP (ORDER BY abort_increasing),
+ percentile_disc(0.2) WITHIN GROUP (ORDER BY id::text),
+ rank('00000000-0000-0000-0000-000000000000', '2', '2') WITHIN GROUP (ORDER BY noabort_increasing, id, id::text)
+FROM (
+ SELECT * FROM abbrev_abort_uuids
+ UNION ALL
+ SELECT NULL, NULL, NULL, NULL, NULL) s;
+
+ROLLBACK;
--
2.23.0.385.gbc12974a89
>From c4d4548149d87e8b29ad9f958a5776e09a9f9077 Mon Sep 17 00:00:00 2001
From: Andres Freund <and...@anarazel.de>
Date: Thu, 24 Oct 2019 13:58:53 -0700
Subject: [PATCH v1 2/2] Remove unused code from tuplesort.
Author:
Reviewed-By:
Discussion: https://postgr.es/m/
Backpatch:
---
src/backend/utils/sort/tuplesort.c | 63 +-----------------------------
1 file changed, 2 insertions(+), 61 deletions(-)
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index a507bd24984..7947d2bca0a 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -4141,67 +4141,8 @@ comparetup_index_hash(const SortTuple *a, const SortTuple *b,
static void
copytup_index(Tuplesortstate *state, SortTuple *stup, void *tup)
{
- IndexTuple tuple = (IndexTuple) tup;
- unsigned int tuplen = IndexTupleSize(tuple);
- IndexTuple newtuple;
- Datum original;
-
- /* copy the tuple into sort storage */
- newtuple = (IndexTuple) MemoryContextAlloc(state->tuplecontext, tuplen);
- memcpy(newtuple, tuple, tuplen);
- USEMEM(state, GetMemoryChunkSpace(newtuple));
- stup->tuple = (void *) newtuple;
- /* set up first-column key value */
- original = index_getattr(newtuple,
- 1,
- RelationGetDescr(state->indexRel),
- &stup->isnull1);
-
- if (!state->sortKeys->abbrev_converter || stup->isnull1)
- {
- /*
- * Store ordinary Datum representation, or NULL value. If there is a
- * converter it won't expect NULL values, and cost model is not
- * required to account for NULL, so in that case we avoid calling
- * converter and just set datum1 to zeroed representation (to be
- * consistent, and to support cheap inequality tests for NULL
- * abbreviated keys).
- */
- stup->datum1 = original;
- }
- else if (!consider_abort_common(state))
- {
- /* Store abbreviated key representation */
- stup->datum1 = state->sortKeys->abbrev_converter(original,
- state->sortKeys);
- }
- else
- {
- /* Abort abbreviation */
- int i;
-
- stup->datum1 = original;
-
- /*
- * Set state to be consistent with never trying abbreviation.
- *
- * Alter datum1 representation in already-copied tuples, so as to
- * ensure a consistent representation (current tuple was just
- * handled). It does not matter if some dumped tuples are already
- * sorted on tape, since serialized tuples lack abbreviated keys
- * (TSS_BUILDRUNS state prevents control reaching here in any case).
- */
- for (i = 0; i < state->memtupcount; i++)
- {
- SortTuple *mtup = &state->memtuples[i];
-
- tuple = (IndexTuple) mtup->tuple;
- mtup->datum1 = index_getattr(tuple,
- 1,
- RelationGetDescr(state->indexRel),
- &mtup->isnull1);
- }
- }
+ /* Not currently needed */
+ elog(ERROR, "copytup_index() should not be called");
}
static void
--
2.23.0.385.gbc12974a89
