| From: | Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> |
|---|---|
| To: | Zsolt Parragi <zsolt(dot)parragi(at)percona(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-19 08:49:23 |
| Message-ID: | CAGPqQf28MES9Ow1b6Nr4c2rvQf7UopYjHPCDq5ED0u39EHSKbQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
HI Zsolt,
Thanks for the review.
On Tue, Nov 17, 2026 at 3:20 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
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 | Content-Type | Size |
|---|---|---|
| v2-0001-Add-ORDER-BY-ALL.patch | application/octet-stream | 43.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Maxim Orlov | 2026-11-19 08:56:21 | Re: Fix race with LLVM and bison. |
| Previous Message | Chao Li | 2026-11-19 08:33:01 | Re: A latent pointer increment bug in pgtypes_defmt_scan() |