Arbitrary GROUP BY fields for partitioned table

Using Django
1

I have a partitioned table using pg-partitioning and it’s working great for SELECT queries but I bumped hard into a problem of GROUP BY queries. Described on the Django mailing list but decided to move my question here as it’s easier to give code snippets.

Django generates this query and it works fine in an un-partitioned table.

SELECT
  "page_titles"."id",
  "page_titles"."date",
  "page_titles"."name",
  "page_titles"."title",
  SUM("page_titles"."allow") AS "allow",
  SUM("page_titles"."block") AS "block",
  SUM("page_titles"."hit_count") AS "hits",
  "categories_category"."name" AS "category_name",
  "categories_rating"."name" AS "rating_name"
FROM
  "page_titles"
  LEFT OUTER JOIN "categories_category" ON ("page_titles"."category_id" = "categories_category"."id")
  LEFT OUTER JOIN "categories_rating" ON ("page_titles"."rating_id" = "categories_rating"."id")
GROUP BY
  "page_titles"."id",
  "categories_category"."name",
  "categories_rating"."name"
ORDER BY
  "page_titles"."date" DESC,
  "page_titles"."id" DESC;

But in a partitioned table, Postgres raises an error like so, presumably because partitioned tables can’t have globally unique indexes.

ERROR:  column "page_titles.date" must appear in the GROUP BY clause or be used in an aggregate function

LINE 3:     "page_titles"."date",

All we need to do is extend the GROUP BY clause to include the date, name and title columns, like so:

SELECT
  "page_titles"."id",
  "page_titles"."date",
  "page_titles"."name",
  "page_titles"."title",
  SUM("page_titles"."allow") AS "allow",
  SUM("page_titles"."block") AS "block",
  SUM("page_titles"."hit_count") AS "hits",
  "categories_category"."name" AS "category_name",
  "categories_rating"."name" AS "rating_name"
FROM
  "page_titles"
  LEFT OUTER JOIN "categories_category" ON ("page_titles"."category_id" = "categories_category"."id")
  LEFT OUTER JOIN "categories_rating" ON ("page_titles"."rating_id" = "categories_rating"."id")
GROUP BY
  "page_titles"."id",
  "page_titles"."date",
  "page_titles"."name",
  "page_titles"."title",
  "categories_category"."name",
  "categories_rating"."name"
ORDER BY
  "page_titles"."date" DESC,
  "page_titles"."id" DESC;

But Try As I Might, I can’t get Django to generate such a query, and I really, really need to stay in the ORM for dynamic filtering via rest_framework.

Here’s a reduced version of the model

@MonthPartitioning(partition_key='date')
class Titles(models.Model):

    name = TextField()
    title = TextField()
    category = ForeignKey(
        Category,
        null=True,
        blank=True,
        db_index=False,
        on_delete=SET_NULL,
    )
    rating = ForeignKey(
        Rating,
        null=True,
        blank=True,
        db_index=False,
        on_delete=SET_NULL,
    )
    hit_count = IntegerField(default=0)
    allow = IntegerField(default=0)
    block = IntegerField(default=0)

    objects = TitlesManager.from_queryset(TitlesQuerySet)()

    class Meta:
        app_label = 'page'
        ordering = ['-date', '-id']
        unique_together = [
            ('date', 'category', 'rating', 'name'),
        ]

    def __str__(self) -> str:
        return self.name

And here’s a reduced version of the custom manager:

class TitlesQuerySet(QuerySet):

    def title_stats(self):
        return self.values('pk', 'category').annotate(
            allow=Sum('allow'),
            block=Sum('block'),
            hits=Sum('hit_count'),
            category_pk=F('category_id'),
            category_name=F('category__name'),
            rating_name=F('rating__name'),
        ).values(
            'pk',
            'date',
            'title',
            'name',
            'category_name',
            'rating_name',
            'allow',
            'block',
            'hits',
        )

Is there some way I can simply add extra group_by fields so make the Postgres query planner happy? Or any way to generate the desired query within the ORM!?

2

Well, I got it working by changing ordering = ['-date', '-id'] to ordering = ['-date']. I don’t exactly understand why that fixed it because I tried various combos order_by queryset params. But hopefully this helps someone later.