Add psycopg3 backend

[Paolo Melchiorre]

A stable version of Psycopg3 has been released for some time and sub-versions are being released which fix minor problems.
​https://github.com/psycopg/psycopg

The author of Psycopg3 himself has already created some demonstration branches with the integration test of Django and Psycopg3.
​https://www.psycopg.org/articles/2021/08/02/psycopg3-django-driver/

I propose to add a new database backend for PostgreSQL based on Psycopg3 in parallel with the one based on Psycopg2, we can start from the work already done.
​https://github.com/dvarrazzo/django-psycopg3-backend

P.S. In the past I believe there was a similar situation when Psycpg2 support was added in parallel with version 1.

[Daniele Varrazzo]

Hello Paolo, thank you for looking into this.

Happy to give you my support, with explanations and eventual changes on the Psycopg side if needed.

[Paolo Melchiorre]

Hi Daniele, I think the first step for you can be to rebase your Django fork [1] with the official Django [2] repository and then open a PR from your psycopg3-support branch to the Django main branch.

[1] ​https://github.com/dvarrazzo/django/tree/psycopg3-support
[2] ​https://github.com/django/django

[Arsalan Ghassemi]

Adding support for psycopg3 seems relevant since it has a stable release, so I'm tagging it as accepted.

[Daniele Varrazzo]

Hello Paolo,

I have rebased my Django branch on current main in a new ​psycopg3-4.1 branch and I have made the ​django-psycopg3-backend almost compatible with it: there are a few issues described ​in this commit message, together with how to run tests.

[Paolo Melchiorre]

Hi Daniele

Thank, I'll take a look at both branches and let you know.

[Florian Apolloner]

Given the CCs on this ticket I'd highly appreciate review and tests of the PR in ​https://github.com/django/django/pull/15687 -- remember the more reviews and tests we get the sooner this will get in :)

[Mariusz Felisiak <felisiak.mariusz@…>]

In 04518e31:

Refs #33308 -- Enabled explicit GROUP BY and ORDER BY aliases.

This ensures explicit grouping from using values() before annotating an
aggregate function groups by selected aliases if supported.

The GROUP BY feature is disabled on Oracle because it doesn't support it.

[Paolo Melchiorre]

Assign to @apollo13 because he's working on this issue.

[GitHub <noreply@…>]

In 7990d25:

Refs #33308 -- Improved adapting DecimalField values to decimal.

[Mariusz Felisiak <felisiak.mariusz@…>]

In d87a7b9:

Refs #33308 -- Stopped inheriting from FieldGetDbPrepValueMixin by PostgresOperatorLookup.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 5c23d9f:

Refs #33308 -- Used get_db_prep_value() to adapt JSONFields.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 3cafb783:

Refs #33308 -- Used psycopg's errors instead of errorcodes.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 149b55f:

Refs #33308 -- Ensured type handlers are registered for all PostgreSQL specific tests.

Co-authored-by: Mariusz Felisiak <felisiak.mariusz@…>

[Mariusz Felisiak <felisiak.mariusz@…>]

In d3e746ac:

Refs #33308 -- Added get_type_oids() hook and simplified registering type handlers on PostgreSQL.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 0ff4659:

Refs #33308 -- Deprecated support for passing encoded JSON string literals to JSONField & co.

JSON should be provided as literal Python objects an not in their
encoded string literal forms.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 2fecf99:

Refs #33308 -- Made PostGISAdapter do not use psycopg2's Binary().

[Mariusz Felisiak <felisiak.mariusz@…>]

In 3e3b7f6:

Refs #33308 -- Avoided passing None to RawSQL's params.

Passing None to params causes errors in determining the data type on
psycopg3.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 6a216581:

Refs #33308 -- Modernized database wrapper in the PostgreSQL backend.

• Used connection.info instead of connection.get_parameter_status() and connection.server_info which don't exist in psycopg 3.
• Set encoding using the client_encoding connection parameter instead of connection.set_client_encoding() that doesn't exist in psycopg 3.
• Used the dbname connection parameter instead of deprecated alias - database.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 2ebfbd89:

Refs #33308 -- Moved psycopg2 imports to the psycopg_any module.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 2f38f7b:

Refs #33308 -- Added psycopg_any.sql.quote() hook.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 1d90c9b:

Refs #33308 -- Added psycopg_any.IsolationLevel.

[Mariusz Felisiak <felisiak.mariusz@…>]

In db7bb3b6:

Refs #33308 -- Added DatabaseOperations.compose_sql() on PostgreSQL.

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 09ffc5c:

Fixed #33308 -- Added support for psycopg version 3.

Thanks Simon Charette, Tim Graham, and Adam Johnson for reviews.

Co-authored-by: Florian Apolloner <florian@…>
Co-authored-by: Mariusz Felisiak <felisiak.mariusz@…>

[Mariusz Felisiak <felisiak.mariusz@…>]

In a6511bc:

Refs #33308 -- Added tests for queryset ordered by annotation with nulls_first/nulls_last.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 312d0f88:

[4.2.x] Refs #33308 -- Added tests for queryset ordered by annotation with nulls_first/nulls_last.

Backport of a6511bc23329f1d3939571ad01d3176f8f6cb786 from main

[GitHub <noreply@…>]

In 0c1518e:

Fixed #34590 -- Reverted "Refs #33308 -- Improved adapting DecimalField values to decimal."

This reverts 7990d254b0af158baf827fafbd90fe8e890f23bd.

Thanks Marc Odermatt for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 91f8df5c:

[4.2.x] Fixed #34590 -- Reverted "Refs #33308 -- Improved adapting DecimalField values to decimal."

This reverts 7990d254b0af158baf827fafbd90fe8e890f23bd.

Thanks Marc Odermatt for the report.
Backport of 0c1518ee429b01c145cf5b34eab01b0b92f8c246 from main

[Mariusz Felisiak <felisiak.mariusz@…>]

In 5e4c179:

Refs #33308 -- Removed support for passing encoded JSON string literals to JSONField & co.

Per deprecation timeline.