Hi, On Sun, Jun 12, 2016 at 11:18 PM, Edmund Horner <ejr...@gmail.com> wrote: > Hi, > > I was trying out the beta download and noticed that the SQL definition for > some of my constraints had the columns in the wrong order. I traced this to > https://git.postgresql.org/gitweb/?p=pgadmin4.git;a=blob;f=web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/get_constraint_cols.sql;hb=702609517f23be7c14e2f9d655f66ea8ac43dd9c > which has the form of a SELECT ... UNION SELECT ... for each column in the > constraint. I was able to fix this one case by appending the loop index as > a new column and ordering by that: > > {% for n in range(colcnt|int) %} > {% if loop.index != 1 %} > UNION SELECT pg_get_indexdef({{ cid|string }}, {{ loop.index|string }}, > true) AS column, {{ loop.index|string }} AS idx > {% else %} > SELECT pg_get_indexdef({{ cid|string }} , {{ loop.index|string }} , > true) AS column, {{ loop.index|string }} AS idx > {% endif %} > {% endfor %} > ORDER BY idx > > There are a few other templates where it looks like the same pattern > happens. In a lot of them you use UNION ALL, which may coincidentally > return rows the same order as the individual SELECT statements. In the one > above you use UNION so hashing is used and rows are returned in a less > predictable order. I think that even in the first case an ORDER BY clause > is required for correctness -- or the rows need to be sorted in the client > code before generating the SQL. > > I have not attached a patch as I've not been involved on pgAdmin > development. But if the problem and the fix makes sense I would be happy to > work on one to try to add ordering to UNION-based query templates (e.g. the > ones on this list > https://git.postgresql.org/gitweb/?p=pgadmin4.git&a=search&h=HEAD&st=grep&s=UNION > ).
Good catch - a patch would be very welcome, thanks! -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers
