Re: ORDER BY ALL

From: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
To: Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org, Peter Eisentraut <peter(at)eisentraut(dot)org>
Subject: Re: ORDER BY ALL
Date: 2026-11-16 21:50:53
Message-ID: CAN4CZFO7HvQevVDhHivfKYgv1qA=Vmu17ink337RHJADoUDXGA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello!

This seems like a good feature, thanks for working on it.

I have two questions:

* ALL is added to the shared sort_clause, wouldn't be a separate
select-level nonterminal (used only by the select_no_parens
productions and PLpgSQL_Expr) simpler?
* Is Query.orderByAll needed? Isn't the list printed by the existing
get_rule_orderby the same?

And some initial testing findings:

1. pg_dump can't restore an empty select list WITH TIES

create table tt(a int); insert into tt values (1),(2);
create view v_ties as select from tt order by all fetch first 1 row with ties;
select pg_get_viewdef('v_ties');
-- SELECT
-- FROM tt
-- FETCH FIRST (1) ROWS WITH TIES;

2. The deparse only looks at the first column's sort operator

create type pair as (x int, y int);
create view v1 as select row(1,2)::pair as p, 1 as n order by all;
create view v2 as select 1 as n, p from (values (row(1,2)::pair),
(row(2,1)::pair)) s(p) order by all;
-- later: CREATE OPERATOR CLASS ... DEFAULT FOR TYPE pair USING btree
(reversed ops)
select pg_get_viewdef('v1'); -> ORDER BY ALL USING < NULLS LAST;
-- syntax error on restore
select pg_get_viewdef('v2'); -> ORDER BY ALL; -- restores, but
returns (2,1),(1,2) instead of (1,2),(2,1)
-- same view with "order by n, p": ORDER BY 1::integer, p USING < NULLS LAST

3. errors can lose print error positions

(select 1 order by 1) order by all;
-- ERROR: multiple ORDER BY clauses not allowed
with recursive r(n) as (select 1 union all select n+1 from r where n<3
order by all) select * from r;
-- ERROR: ORDER BY in a recursive query is not implemented

4.

+ /*
+ * Ignore junk TLEs. These are system columns (like ctid, xmin)
+ * or internal working columns that shouldn't be part of the
+ * user-visible ordering.
+ */
+ if (tle->resjunk)
+ continue;

but

select ctid, a from t order by all; -- works, sorts by ctid
select xmin, a from t order by all; -- fails, no operator for xmin

so that comment seems wrong

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message surya poondla 2026-11-16 21:52:46 Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
Previous Message Bharath Rupireddy 2026-11-16 21:41:39 Re: pg_xmin_horizon: a system view of everything pinning the xmin horizon