HI Zsolt, Thanks for the review.
On Tue, Nov 17, 2026 at 3:20 AM Zsolt Parragi <[email protected]> wrote: > 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? > Yes, a separate select-level nonterminal would be simpler. I went with the shared sort_clause because it lets us control the error message. Currently, misuse produces “ORDER BY ALL is only allowed in a query’s ORDER BY clause”, which is much clearer than a generic “syntax error at or near “all””. That said, I don’t have a strong opinion here and I’m happy to switch if that’s what everyone prefers. * Is Query.orderByAll needed? Isn't the list printed by the existing > get_rule_orderby the same? > > Good question. The existing get_rule_orderby would print a correct list, but I think the flag is worth keeping because the expanded form loses information in the deparsed output. It isn’t what the user wrote: pg_get_viewdef and pg_dump would show ORDER BY a DESC, b DESC instead of ORDER BY ALL DESC, which for a wide view gets long and hides the intent that every output column is sorted. It is also noisier to read, since set-operation views would show something like ORDER BY 1 DESC NULLS LAST, 2 DESC NULLS LAST rather than the much clearer ORDER BY ALL DESC NULLS LAST. But looking further in the code, I see that currently there are planty of example where pg_get_viewdef() would not return the orginal view query defination. Few examples, are SELECT * is expanded to the column list at creation time, ORDER BY / GROUP BY ordinals and aliases are resolved to the expression or column, etc. Considering this, I think removing Query.orderByAll will make code much simpler and clear. 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; > This is a nice catch. This is problem when there is empty select list, for the non-empty select list output is already correct. With no columns, ORDER BY ALL expands to an empty list, so there is nothing to print. This needs the flag ( Query.orderByAll) we just removed, with deparse emitting ORDER BY ALL when sortClause is non-NIL or orderByAll is set. The alternative is to reject the combination. I am still keeping this as open for the opinion. > 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 > > This is fixed, with the implementation of removing Query.orderByAll. 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 > > Fixed in the latest version. > 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 > This is fixed, with the implementation of removing Query.orderByAll. Attaching the v2 patch for further reviews. Thanks, Rushabh Lathia www.EnterpriseDB.com
v2-0001-Add-ORDER-BY-ALL.patch
Description: Binary data
