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

Attachment: v2-0001-Add-ORDER-BY-ALL.patch
Description: Binary data

Reply via email to