| 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
| 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 |