[Django] #36289: Postgres bulk_create error when geometry is null or a different SRID

[Django]

#36289: Postgres bulk_create error when geometry is null or a different SRID
-------------------------------------+-------------------------------------
Reporter: Joshua Goodwin | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
I have a model with a geometry field like this:

{{{
from django.contrib.gis.db import models

class Garage(models.Model):
name = models.CharField(max_length=100, blank=True)
location = models.PointField(null=True, blank=True)
}}}

With Django 5.2 and PostgreSQL (17.4 / PostGIS 3.5.2), {{{bulk_create}}}
fails when I try to more than one item with a null location:

{{{
Garage.objects.bulk_create([Garage(), Garage()])
}}}


{{{
InternalError: parse error - invalid geometry
LINE 1: ...stimes_garage" ("location") SELECT * FROM UNNEST(('{NULL,NUL...
^
HINT: "NU" <-- parse error at position 2 within geometry
}}}

or with an SRID other than the default

{{{
Garage.objects.bulk_create([Garage(location="SRID=29902;POINT(624210
307091)"), Garage(location="SRID=29902;POINT(624210 307091)")])
}}}

{{{
DataError: Geometry SRID (29902) does not match column SRID (4326)
}}}

This seems to be a regression in 5.2, maybe related to #35936 (Speeding up
Postgres bulk_create by using unnest)
--
Ticket URL: <https://code.djangoproject.com/ticket/36289>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36289: Postgres bulk_create error when geometry is null or a different SRID
-------------------------------------+-------------------------------------

Reporter: Joshua Goodwin | 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 Sarah Boyce):

* cc: Simon Charette, Mariusz Felisiak (added)
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted

Comment:

Replicated, regression in a16eedcf9c69d8a11d94cac1811018c5b996d491
Basic regression test if useful:
{{{#!diff
--- a/tests/gis_tests/geo3d/models.py
+++ b/tests/gis_tests/geo3d/models.py
@@ -58,7 +58,7 @@ class SimpleModel(models.Model):


class Point2D(SimpleModel):
- point = models.PointField()
+ point = models.PointField(null=True)


class Point3D(SimpleModel):
diff --git a/tests/gis_tests/geo3d/tests.py
b/tests/gis_tests/geo3d/tests.py
index b37deabb46..af772b07d6 100644
--- a/tests/gis_tests/geo3d/tests.py
+++ b/tests/gis_tests/geo3d/tests.py
@@ -206,6 +206,10 @@ class Geo3DTest(Geo3DLoadingHelper, TestCase):
lm.save()
self.assertEqual(3, MultiPoint3D.objects.count())

+ def bulk_create_point_field(self):
+ objs = Point2D.objects.bulk_create([Point2D(), Point2D()])
+ self.assertEqual(len(objs), 2)
+
@skipUnlessDBFeature("supports_3d_functions")
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36289#comment:1>

[Django]

#36289: Postgres bulk_create error when geometry is null or a different SRID
-------------------------------------+-------------------------------------
Reporter: Joshua Goodwin | 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
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

The most simple solution is likely to adapt `assemble_as_sql` to disable
the optimization if any field has a geometry db type.
--
Ticket URL: <https://code.djangoproject.com/ticket/36289#comment:2>

[Django]

#36289: Postgres bulk_create error when geometry is null or a different SRID
-------------------------------------+-------------------------------------

Reporter: Joshua Goodwin | 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/36289#comment:3>

[Django]

#36289: Postgres bulk_create error when geometry is null or a different SRID
-------------------------------------+-------------------------------------
Reporter: Joshua Goodwin | 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

Comment:

[https://github.com/django/django/pull/19337/ PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/36289#comment:4>

[Django]

#36289: Postgres bulk_create error when geometry is null or a different SRID
-------------------------------------+-------------------------------------
Reporter: Joshua Goodwin | 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 Mariusz Felisiak):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36289#comment:5>

[Django]

#36289: Postgres bulk_create error when geometry is null or a different SRID
-------------------------------------+-------------------------------------
Reporter: Joshua Goodwin | 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 Mariusz Felisiak <felisiak.mariusz@…>):

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

Comment:

In [changeset:"764af7a3d6c0b543dcf659a2c327f214da768fe4" 764af7a]:
{{{#!CommitTicketReference repository=""
revision="764af7a3d6c0b543dcf659a2c327f214da768fe4"
Fixed #36289 -- Fixed bulk_create() crash with nullable geometry fields on
PostGIS.

Swapped to an allow list instead of a deny list for field types to
determine if the UNNEST optimization can be enabled to avoid further
surprises with other types that would require further specialization to
adapt.

Regression in a16eedcf9c69d8a11d94cac1811018c5b996d491.

Thanks Joshua Goodwin for the report and Sarah Boyce for the test.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36289#comment:6>

[Django]

#36289: Postgres bulk_create error when geometry is null or a different SRID
-------------------------------------+-------------------------------------
Reporter: Joshua Goodwin | 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 Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"d9bf0d07cc13c0351b65b025d7e8063864086975" d9bf0d0]:
{{{#!CommitTicketReference repository=""
revision="d9bf0d07cc13c0351b65b025d7e8063864086975"
[5.2.x] Fixed #36289 -- Fixed bulk_create() crash with nullable geometry

fields on PostGIS.

Swapped to an allow list instead of a deny list for field types to
determine if the UNNEST optimization can be enabled to avoid further
surprises with other types that would require further specialization to
adapt.

Regression in a16eedcf9c69d8a11d94cac1811018c5b996d491.

Thanks Joshua Goodwin for the report and Sarah Boyce for the test.

Backport of 764af7a3d6c0b543dcf659a2c327f214da768fe4 from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36289#comment:7>