PostgreSQL introspection's get_constraints() retrieves index fields in arbitrary order

[Mactory]

When trying to run the migrations from scratch for the django-blog-zinna app the first time, the migration command threw the following error when run under django 1.11 on a postgresql db:
ValueError: Found wrong number (0) of constraints for zinnia_entry(slug, creation_date)

After digging through the code, I found out, that the array_agg function used in the query at db.backends.postgresql.introspection line 196 aggregates the values in an arbitrary order.

[Mactory]

I have submitted a pull-request that adds a row number to the subquery which can then be used to order the field names in the correct order. Not sure if this is the best way to solve the problem, but it seems to be the one with the least changes necessary.

[Tim Graham]

Looks like a regression in f842d1011c1195aa26071a6ab6f96e0b8d907343. Can you add a regression test to the PR?

[Claude Paroz]

From the PostgreSQL docs:

The aggregate functions array_agg, json_agg, json_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will usually work.`

[Mactory]

I have extended the existing tests to also test a 4 field index. The test fails on my machine without the patch and passes with the patch.
Since the ordering of the fields was, as far as I could see it, arbitrary, it could happen though, that the problem exists but is not detected by the test. This can be if the ordering just happens to be correct. I don't see any possibility though to catch randomly correct orderings in a test. If you know any techniques to do so, please feel free to extend the tests further.

​PR

[Tim Graham <timograham@…>]

In 3a5299c:

Fixed #28197 -- Fixed introspection of index field ordering on PostgreSQL.

[Tim Graham <timograham@…>]

In 566726ff:

[1.11.x] Fixed #28197 -- Fixed introspection of index field ordering on PostgreSQL.

Backport of 3a5299c19cd5a38f7fa0f45ed2df7b10f0c9cf5d from master