Opened 2 months ago

Closed 2 months ago

Last modified 2 months ago

#28197 closed Bug (fixed)

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

Reported by: Mactory Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Release blocker Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (6)

comment:1 Changed 2 months ago by 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.

comment:2 Changed 2 months ago by Tim Graham

Has patch: set
Needs tests: set
Severity: NormalRelease blocker
Summary: db.backends.postgresql.introspection.get_constraints retrieves index fields in arbitrary orderPostgreSQL introspection's get_constraints() retrieves index fields in arbitrary order
Triage Stage: UnreviewedAccepted

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

comment:3 Changed 2 months ago by 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.`

comment:4 Changed 2 months ago by Mactory

Needs tests: unset

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

comment:5 Changed 2 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: newclosed

In 3a5299c:

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

comment:6 Changed 2 months ago by Tim Graham <timograham@…>

In 566726ff:

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

Backport of 3a5299c19cd5a38f7fa0f45ed2df7b10f0c9cf5d from master

Note: See TracTickets for help on using tickets.
Back to Top