#35028: psycopg3: distinct query iteration causes
psycopg.errors.InvalidColumnReference: for SELECT DISTINCT, ORDER BY
expressions must appear in select list
-------------------------------------+-------------------------------------
Reporter: Richard Ebeling | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
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, Florian Apolloner, Daniele Varrazzo (added)
* version: 5.0 => 4.2
* stage: Unreviewed => Accepted
Comment:
It appears that `psycopg3` will
[https://github.com/psycopg/psycopg/blob/52ed68a9d699c1e43e07a4d04441534197552de5/psycopg/psycopg/connection.py#L255-L262
always use server-side cursors] and ignores when using a named cursor and
we know they are causing issues with how the ORM generates SQL
(`DISTINCT`, `GROUP BY`, `ORDER BY`) as it doesn't have proper prepared
statement support yet (#20516).
I see a few paths forward here
1. Adapt `psycopg3` to allow the use of named client cursors or have
Django directly create cursors instead of calling `create_cursor` so it
can issue the proper `DECLARE`, `FETCH`, `CLOSE` commands. I'm not sure
this is possible but I assume it is given it was the case of `psycopg2`.
2. On the Django side, when on `psycopg>=3`, make it so the
`DISABLE_SERVER_SIDE_CURSORS`
[https://docs.djangoproject.com/en/5.0/ref/settings/#disable-server-side-
cursors setting] defaults to `not db_settings.get("server_side_binding",
False)` and consider merging both settings under a single
`server_side_cursors: bool` setting instead. This would disable server
side cursors on `psycopg>=3` entirely until #20516 is fixed which I think
is the right thing to do until we've demonstrated that we actually support
this configuration properly.
3. Invest significant efforts in getting #20516 fixed so we can enable
server side cursors which require the ORM to generate SQL that can be
prepared.
Until a solution lands Richard you have two choices
1. Keep using `psycopg2`
2. Use `psycopg>=3` but
[https://docs.djangoproject.com/en/5.0/ref/settings/#std-setting-DATABASE-
DISABLE_SERVER_SIDE_CURSORS disable server side cursors]
--
Ticket URL: <https://code.djangoproject.com/ticket/35028#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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018c5ef090aa-c8bd4805-f455-4319-b7bd-3c2e67f0a189-000000%40eu-central-1.amazonses.com.
