[Django] #36107: PostgresSQL ArrayField with size crashes with bulk_create

[Django]

#36107: PostgresSQL ArrayField with size crashes with bulk_create
-------------------------------------+-------------------------------------
Reporter: Claude Paroz | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Release
| blocker
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
When trying to `bulk_create` model instances with an ArrayField with a
specified `size` (and triggering the UNNEST instruction), the query
crashes at database level (column "<field>" is of type double precision[]
but expression is of type double precision).

Probably a regression in a16eedcf9c69d8a11d94cac1811018c5b996d491

A possible reproducing test:
{{{
diff --git a/tests/postgres_tests/migrations/0002_create_test_models.py
b/tests/postgres_tests/migrations/0002_create_test_models.py
index 188f79607d..31705ae21a 100644
--- a/tests/postgres_tests/migrations/0002_create_test_models.py
+++ b/tests/postgres_tests/migrations/0002_create_test_models.py
@@ -167,6 +167,28 @@ class Migration(migrations.Migration):
},
bases=(models.Model,),
),
+ migrations.CreateModel(
+ name="WithSizeArrayModel",
+ fields=[
+ (
+ "id",
+ models.AutoField(
+ verbose_name="ID",
+ serialize=False,
+ auto_created=True,
+ primary_key=True,
+ ),
+ ),
+ (
+ "field",
+ ArrayField(models.FloatField(), size=2, null=True,
blank=True),
+ ),
+ ],
+ options={
+ "required_db_vendor": "postgresql",
+ },
+ bases=(models.Model,),
+ ),
migrations.CreateModel(
name="NullableIntegerArrayModel",
fields=[
diff --git a/tests/postgres_tests/models.py
b/tests/postgres_tests/models.py
index e3118bc590..1563f6a35d 100644
--- a/tests/postgres_tests/models.py
+++ b/tests/postgres_tests/models.py
@@ -64,6 +64,10 @@ class DateTimeArrayModel(PostgreSQLModel):
times = ArrayField(models.TimeField())


+class WithSizeArrayModel(PostgreSQLModel):
+ field = ArrayField(models.FloatField(), size=3)
+
+
class NestedIntegerArrayModel(PostgreSQLModel):
field = ArrayField(ArrayField(models.IntegerField()))

diff --git a/tests/postgres_tests/test_array.py
b/tests/postgres_tests/test_array.py
index d930a01a1d..c2b0ebcd31 100644
--- a/tests/postgres_tests/test_array.py
+++ b/tests/postgres_tests/test_array.py
@@ -28,6 +28,7 @@ from .models import (
OtherTypesArrayModel,
PostgreSQLModel,
Tag,
+ WithSizeArrayModel,
)

try:
@@ -216,6 +217,12 @@ class TestQuerying(PostgreSQLTestCase):
]
)

+ def test_bulk_create_with_sized_arrayfield(self):
+ WithSizeArrayModel.objects.bulk_create([
+ WithSizeArrayModel(field=[1, 2]),
+ WithSizeArrayModel(field=[3, 4]),
+ ])
+
def test_empty_list(self):
NullableIntegerArrayModel.objects.create(field=[])
obj = (
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36107>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36107: PostgresSQL ArrayField with size crashes with bulk_create
-------------------------------------+-------------------------------------

Reporter: Claude Paroz | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | 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):

* stage: Unreviewed => Accepted

Comment:

Thanks Claude, I'll look at it shortly.

[https://github.com/django/django/blob/98767ba2eceafa0de10c561d051d54e340c2d29b/django/db/backends/postgresql/compiler.py#L46
The current heuristics] to disable the optimization when dealing with
`ArrayField` didn't account for sized ones.
--
Ticket URL: <https://code.djangoproject.com/ticket/36107#comment:1>

[Django]

#36107: PostgresSQL ArrayField with size crashes with bulk_create
-------------------------------------+-------------------------------------

Reporter: Claude Paroz | Owner: Simon
| Charette
Type: Bug | Status: assigned

Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | 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):

* owner: (none) => Simon Charette
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36107#comment:2>

[Django]

#36107: PostgresSQL ArrayField with size crashes with bulk_create
-------------------------------------+-------------------------------------
Reporter: Claude Paroz | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | 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

--
Ticket URL: <https://code.djangoproject.com/ticket/36107#comment:3>

[Django]

#36107: PostgresSQL ArrayField with size crashes with bulk_create
-------------------------------------+-------------------------------------
Reporter: Claude Paroz | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Claude Paroz):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36107#comment:4>

[Django]

#36107: PostgresSQL ArrayField with size crashes with bulk_create
-------------------------------------+-------------------------------------
Reporter: Claude Paroz | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 5.2
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce <42296566+sarahboyce@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"22fc151bb86a553d84c62d7effd289356e9b6c6c" 22fc151b]:
{{{#!CommitTicketReference repository=""
revision="22fc151bb86a553d84c62d7effd289356e9b6c6c"
Fixed #36107 -- Adjusted UNNEST bulk_create strategy to opt-out sized
arrays.

The array fields opt-out heuristic failed to account for sized arrays.

Note that we keep relying on db_type as opposed to performing an
ArrayField
instance check against the column's field as there could be other
implementations of model fields that use Postgres arrays to store the
optimization must be disabled for all of them.

Refs #35936.

Thanks Claude Paroz for the report and test.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36107#comment:5>

[Django]

#36107: PostgresSQL ArrayField with size crashes with bulk_create
-------------------------------------+-------------------------------------
Reporter: Claude Paroz | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"ad6bca92a87e72dc7e5572f792463ba33f4da37d" ad6bca92]:
{{{#!CommitTicketReference repository=""
revision="ad6bca92a87e72dc7e5572f792463ba33f4da37d"
[5.2.x] Fixed #36107 -- Adjusted UNNEST bulk_create strategy to opt-out

sized arrays.

The array fields opt-out heuristic failed to account for sized arrays.

Note that we keep relying on db_type as opposed to performing an
ArrayField
instance check against the column's field as there could be other
implementations of model fields that use Postgres arrays to store the
optimization must be disabled for all of them.

Refs #35936.

Thanks Claude Paroz for the report and test.

Backport of 22fc151bb86a553d84c62d7effd289356e9b6c6c from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36107#comment:6>