Quantcast

[Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
               Reporter:             |          Owner:  nobody
  powderflask                        |
                   Type:             |         Status:  new
  Uncategorized                      |
              Component:  Database   |        Version:  1.11
  layer (models, ORM)                |
               Severity:  Normal     |       Keywords:  QuerySet.extra
           Triage Stage:             |      Has patch:  0
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 I'm working with a legacy DB (ArcSDE database -- really ugly) -- have been
 able to accommodate its oddities without resorting much to raw SQL, but
 django upgrade (1.8 --> 1.11) caused a previously working annotation to
 fail:
 {{{ psycopg2.ProgrammingError: :column ... must appear in the GROUP BY
 clause or be used in an aggregate function }}}

 **DB:  PostgreSQL 9.3.16 **   (i.e., this is not same issue as #26758 )
 Python3.6, Django1.11

 The annotation simply counts the number of related records from a related
 'attachments' table:
 {{{ qs.annotate(num_attachments=Count('attachments')) }}}

 The root cause appears to be that the relation between the model and
 attachments tables uses a unique field ''other than the main model's
 primary key'' (I know -- told you it was ugly - ArcSDE does not really
 support relations, except they implement attachments with this odd ball **
 sigh **).
 The change in behaviour seems to manifest from #19259 (I believe django1.8
 added all fields to the groupby clause).
 Since django now includes only the primary key in the groupby clause,
 postgresql won't do this aggregation across a relation that uses a non-pk
 field.

 I suspect there is a general issue here that aggregations on a related-
 table won't work in postgresql unless the relation is on the primary key
 field (so, yeah, basically this issue applies to almost no one, right...).

 Seems likely there is a better solution to this, but after a half-day of
 search / effort, I resorted to the following:
 {{{
 qs.extra(select={'num_attachments':
             'SELECT COUNT("attachmentid") FROM {attach_table} WHERE
 {attach_table}.rel_globalid = {model_table}.globalid'.format(
                     model_table  = qs.model._meta.db_table,
                     attach_table =
 qs.model.AttachmentModel._meta.db_table,
             )},)
 }}}

 This works and achieves my goal -- to annotate model with the number of
 related attachments.
 Since the {{{ model.attachments.count() }}} query works just fine, I'm
 considering eliminating this annotation and replacing with a property on
 the model class, what's one more query...  I'm sure there must be an
 smoother way, but it eludes me...

 Since the docs suggested to open a ticket for queries that could not be
 resolved without resorting to extra(), here it is, for whatever its worth.
 Hope this hasn't been a complete waste of time for you.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/054.468d29500601a42674d1df5c30c72119%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  new
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

 * cc: Simon Charette (added)


Comment:

 It's possible that's an edge case with the handling of `to_field`.

 Would it be possible for your to provide all the models involved in the
 queryset you're generating as it's really hard to figure out if the issue
 actually lies in the _GROUP BY_ logic without a set of models to reproduce
 the issue.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.d50b46520164c539e5c61f25492dc82d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  new
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by powderflask):

 The DB and relations are just nasty to set it all up.   I'm pretty sure I
 know what's needed to reproduce this -- I'll try to get something simpler
 to break in the same way, no sense in you banging your head against the
 ArcSDE wall.

 BTW - edge case is a very polite term for what this really is...   After
 writing this up, I decided to sacrifice the extra query to get the
 attachment.count() to buy a world of ponies.  My code base loves me for it

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.fe0692ce58ca679a69949688fa11b85d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  closed
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |               Resolution:
     Severity:  Normal               |  worksforme
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by powderflask):

 * status:  new => closed
 * resolution:   => worksforme


Comment:

 I tried.  I cannot reproduce this with a simpler test case.  My original
 suspicion is completely wrong - it works just fine.

 I can only reproduce this in the insanely complex DB -- I will continue to
 try to track down the cause, but for now I'd say this issue is too
 undefined to work on.  Sorry for the trouble, I'm closing this for now.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.15aa678edcbaa190730cdf40d750a76b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  closed
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |               Resolution:
     Severity:  Normal               |  worksforme
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by powderflask):

 This may have to do with one of the original models drawing data from a
 View - found this:
 " The feature of Postgres to be able to use the primary key of a table
 with GROUP BY and not need to add the other columns of that table in the
 GROUP BY clause is relatively new and works only for base tables. The
 optimizer is not (yet?) clever enough to identify primary keys for views,
 ctes or derived tables."
 https://dba.stackexchange.com/questions/88988/postgres-error-column-must-
 appear-in-the-group-by-clause-or-be-used-in-an-aggre

 I will try to reproduce this.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.40a9b8c735d3582d436a1674c4148c57%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  closed
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |               Resolution:
     Severity:  Normal               |  worksforme
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

 powderflask, if this happens to be the source of the issue and you are
 using un-managed models (`_meta.managed = False`) maybe we could branch on
 that to prevent the optimization.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:5>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.d56f077d01b17fc8edb16695b862937b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  closed
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |               Resolution:
     Severity:  Normal               |  worksforme
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by powderflask):

 * Attachment "issue28107.zip" added.

 small django app with models, migrations, and tests that illustrate the
 issue

--
Ticket URL: <https://code.djangoproject.com/ticket/28107>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.40dcb932e934d9e0873e6d4d5a1d88d1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  new
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by powderflask):

 * status:  closed => new
 * resolution:  worksforme =>


Comment:

 OK - that's got it.  Yep -- this issues occurs when at least one of the
 models in the aggregation query is un-managed and backed by a DB view
 rather than a table.

 Attached is a zip of a simple django app that runs in a default django
 container,  demonstrates the issue:

 1) Demonstrate these weird relations work with models backed by DB tables:
  -  {{{ INSTALLED_APPS = [ 'issue28107.apps.Issue28107Config', ... ] }}}
  - configure / create postgre DB,
  - manage.py migrate
  - run the unit-test -- it should pass

 2) Replace table with view (created by migrations)
   - in models.py, remove 2 comments from Treatment.Meta:
 {{{
         managed = False
         db_table = 'issue28107_treatment_vw'

 }}}
  - re-run unit-test -- it should fail:
 {{{ column "issue28107_treatment_vw.globalid" must appear in the GROUP BY
 clause or be used in an aggregate function }}}

 This is a backwards-compatibility issue -- this worked at least up to
 django1.8

 I have no idea where to begin with this in terms of suggesting a patch --
 any pointers?
 thank you for the quick reply and suggestions -- awesome.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:6>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.0ae359029e269da6cad39aa26e7b0c0a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  new
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by powderflask):

 It occurs to me that if there were some way to force values into the
 group-by clause, that would serve as a reasonable workaround, given this
 is bound to be a fairly rare use-case.
 I did read some hacks that did this in 1.8, but it looks like
 query.group_by is now a boolean rather than a list of fields...  was
 pretty ugly anyhow.

 But I'm not missing something more obvious here am I -- like an extra()
 clause or something that could force the offending aggregate fields into
 the group_by clause?

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:7>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.c20d826ec8974a4422fbdf1a5bc74f55%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Bug                  |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

 * has_patch:  0 => 1
 * version:  1.11 => master
 * type:  Uncategorized => Bug
 * stage:  Unreviewed => Accepted


Comment:

 Thanks to your detailed report writing a patch was easy.

 [https://github.com/django/django/pull/8441 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:8>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.667653d30090e084d781353e5c2da3b9%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Bug                  |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by powderflask):

 Should I change the title of this issue to reflect the root cause was a
 model backed by a View?

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:9>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.049677c4e2cb3d6ee402b214eb39aecb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Bug                  |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

 Sure go ahead!

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:10>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.dee3997c1631d10e3c98f7d6bd5f4ddb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when un-managed model is backed by a DB view (was: Can't perform annotation on related table when relation between tables not on primary key)

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when un-managed model is backed
by a DB view
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Bug                  |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Description changed by powderflask:

Old description:

> I'm working with a legacy DB (ArcSDE database -- really ugly) -- have
> been able to accommodate its oddities without resorting much to raw SQL,
> but  django upgrade (1.8 --> 1.11) caused a previously working annotation
> to fail:
> {{{ psycopg2.ProgrammingError: :column ... must appear in the GROUP BY
> clause or be used in an aggregate function }}}
>
> **DB:  PostgreSQL 9.3.16 **   (i.e., this is not same issue as #26758 )
> Python3.6, Django1.11
>
> The annotation simply counts the number of related records from a related
> 'attachments' table:
> {{{ qs.annotate(num_attachments=Count('attachments')) }}}
>
> The root cause appears to be that the relation between the model and
> attachments tables uses a unique field ''other than the main model's
> primary key'' (I know -- told you it was ugly - ArcSDE does not really
> support relations, except they implement attachments with this odd ball
> ** sigh **).
> The change in behaviour seems to manifest from #19259 (I believe
> django1.8 added all fields to the groupby clause).
> Since django now includes only the primary key in the groupby clause,
> postgresql won't do this aggregation across a relation that uses a non-pk
> field.
>
> I suspect there is a general issue here that aggregations on a related-
> table won't work in postgresql unless the relation is on the primary key
> field (so, yeah, basically this issue applies to almost no one,
> right...).
>
> Seems likely there is a better solution to this, but after a half-day of
> search / effort, I resorted to the following:
> {{{
> qs.extra(select={'num_attachments':
>             'SELECT COUNT("attachmentid") FROM {attach_table} WHERE
> {attach_table}.rel_globalid = {model_table}.globalid'.format(
>                     model_table  = qs.model._meta.db_table,
>                     attach_table =
> qs.model.AttachmentModel._meta.db_table,
>             )},)
> }}}
>
> This works and achieves my goal -- to annotate model with the number of
> related attachments.
> Since the {{{ model.attachments.count() }}} query works just fine, I'm
> considering eliminating this annotation and replacing with a property on
> the model class, what's one more query...  I'm sure there must be an
> smoother way, but it eludes me...
>
> Since the docs suggested to open a ticket for queries that could not be
> resolved without resorting to extra(), here it is, for whatever its
> worth.  Hope this hasn't been a complete waste of time for you.
New description:

 I'm working with a legacy DB (ArcSDE database -- really ugly) -- have been
 able to accommodate its oddities without resorting much to raw SQL, but
 django upgrade (1.8 --> 1.11) caused a previously working annotation to
 fail:
 {{{ psycopg2.ProgrammingError: :column ... must appear in the GROUP BY
 clause or be used in an aggregate function }}}

 **DB:  PostgreSQL 9.3.16 **   (i.e., this is not same issue as #26758 )
 Python3.6, Django1.11

 The annotation simply counts the number of related records from a related
 'attachments' table:
 {{{ qs.annotate(num_attachments=Count('attachments')) }}}

 The root cause appears to be that the relation between an unmanaged model
 (backed by a DB View) and attachments tables uses a unique field ''other
 than the main model's primary key'' (I know -- told you it was ugly -
 ArcSDE does not really support relations, except they implement
 attachments with this odd ball ** sigh **).
 The change in behaviour seems to manifest from #19259 (I believe django1.8
 added all fields to the groupby clause).
 Since django now includes only the primary key in the groupby clause,
 postgresql won't do this aggregation across a relation that uses a non-pk
 field.

 I suspect there is a general issue here that aggregations on a related-
 table won't work in postgresql unless the relation is on the primary key
 field (so, yeah, basically this issue applies to almost no one, right...).
 UPDATE:  The root cause is actually that Postgresql treats Views
 differently than Tables w.r.t. what is required in the group by clause.

 Seems likely there is a better solution to this, but after a half-day of
 search / effort, I resorted to the following:
 {{{
 qs.extra(select={'num_attachments':
             'SELECT COUNT("attachmentid") FROM {attach_table} WHERE
 {attach_table}.rel_globalid = {model_table}.globalid'.format(
                     model_table  = qs.model._meta.db_table,
                     attach_table =
 qs.model.AttachmentModel._meta.db_table,
             )},)
 }}}

 This works and achieves my goal -- to annotate model with the number of
 related attachments.
 Since the {{{ model.attachments.count() }}} query works just fine, I'm
 considering eliminating this annotation and replacing with a property on
 the model class, what's one more query...  I'm sure there must be an
 smoother way, but it eludes me...

 Since the docs suggested to open a ticket for queries that could not be
 resolved without resorting to extra(), here it is, for whatever its worth.
 Hope this hasn't been a complete waste of time for you.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:11>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.3288c5c19768c0aab950351ee3ab7409%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when un-managed model is backed by a DB view

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when un-managed model is backed
by a DB view
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Bug                  |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

 From what I understood you are still on 1.8 but this patch will only be
 available in a couple of months when 2.0 is released.

 To get this fixed on your side in the mean time you'll have to define your
 own `django.db.backends.postgresql.base.DatabaseWrapper` subclass but it's
 not as intimidating as it sounds.

 Simply define a `DatabaseWrapper` subclass in a `chosen_module_name.base`
 module that extends the aforementioned class and set it's `features_class`
 attribute to a subclass of
 `django.db.backends.postgresql.features.DatabaseFeatures` with
 `allows_group_by_selected_pks = False`

 {{{
 django.db.backends.postgresql import base, features

 class DatabaseWrapper(base.DatabaseWrapper):
     class features_class(features.DatabaseFeatures):
         allows_group_by_selected_pks = False
 }}}

 Note that this disable the optimization all together. If you want to keep
 the optimization for managed models you'll have to define your own
 `SQLCompiler` subclass and override the `collapse_group_by` method which
 is a bit harder. I suggest you have a look
 [https://github.com/django/django/blob/e7afef13f594eb667f2709c0ef7bca98452ab32b/django/db/backends/mysql/operations.py#L10
 how it's done for the MySQL backend] to figure it out.

 I suggest you have a look at how [https://github.com/carljm/django-
 transaction-hooks django-transaction-hooks] did it if you need a more
 concrete example.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:12>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.c1151c4ecb8046625f5c0a1563c9cf50%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when un-managed model is backed by a DB view

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when un-managed model is backed
by a DB view
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Bug                  |                   Status:  new
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by powderflask):

 Thank you so much Simon - really above the call.

 I am migrating to django1.11, which caused me to stumble into this -- I
 can certainly implement a workaround until we move to 2.0

 thanks again for all your help -- much appreciated.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:13>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.0b228eb9b3445f6f79a9600030350693%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Loading...