Order by in a sub query when aggregating the main query

Hi all,

I have a question related to the order by clause used in a subquery of
a main query that uses one or more aggregation functions with a group
by.
A basic example of the type of query in question is the following (see
below for the actual query):

select w, array_agg(x)
from (
select v, v / 10 as w
from pg_catalog.generate_series(25, 0, -1) as t(v)
order by v
) as t(x)
group by w

This query will return an ordered array as specified by the order by
clause.in the subquery.
Can this behaviour be relied upon?
From what I could find from searching in SQL the order by in a
subquery could be ignored by the engines, but I've found that
postgresql will always respect it.

The context of the question is the updated reflection logic that will
be introduced in version 2 of SQLAlchemy, that makes use of orderby in
subqueries to, for example, match column index of a constraint with
the column name of a table. This query and other similar one return
the correct result, and they seem stable in their output (ie the CI is
not randomly failing because the order has changed). For more
information this potential issue with the current query is traket in
the issue https://github.com/sqlalchemy/sqlalchemy/issues/8561
Below is the full query that will be used in sqlalchemy to reflect
constraints given the constraint type and on a list of table oids:

select
attr.conrelid,
array_agg(attr.attname) as cols,
attr.conname,
min(attr.description) as description
from (
select
con.conrelid as conrelid,
con.conname as conname,
con.description as description,
pg_catalog.pg_attribute.attname as attname
from pg_catalog.pg_attribute
join (
select
pg_catalog.pg_constraint.conrelid as conrelid,
pg_catalog.pg_constraint.conname as conname,
unnest(pg_catalog.pg_constraint.conkey) as attnum,
generate_subscripts(pg_catalog.pg_constraint.conkey,
%(generate_subscripts_1)s) as ord,
pg_catalog.pg_description.description as description
from pg_catalog.pg_constraint
left outer join pg_catalog.pg_description on
pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid
where
pg_catalog.pg_constraint.contype = :contype
and pg_catalog.pg_constraint.conrelid in (:oids)
) as con on
pg_catalog.pg_attribute.attnum = con.attnum
and pg_catalog.pg_attribute.attrelid = con.conrelid
order by con.conname, con.ord
) as attr
group by attr.conrelid, attr.conname
order by attr.conrelid, attr.conname

The other reflection queries that use order by in subqueries are
similar to the above, I can post them here if they may prove useful.

Thank you
Federico