[Django] #27813: BinaryField is inconsistent between sqlite3 and postgresql

[Django]

#27813: BinaryField is inconsistent between sqlite3 and postgresql
-------------------------------------+-------------------------------------
Reporter: Antonio | Owner: nobody
Terceiro |
Type: | Status: new
Uncategorized |
Component: Database | Version: 1.10
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I have an application that has the following model:

{{{
class Attachment(models.Model):
# ...
data = models.BinaryField(default=None)
}}}

under sqlite3:

{{{
In [1]: Attachment.objects.last().data
Out[1]: b'Mon Feb 6 16:53:37 UTC 2017\n'
}}}

under postgresql:

{{{
In [1]: Attachment.objects.last().data
Out[1]: <memory at 0x7f1ae8e93108>
}}}

i.e. under sqlite the BinaryField values comes as bytes(), and under
postgresql it comes as memoryview()

--
Ticket URL: <https://code.djangoproject.com/ticket/27813>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#27813: BinaryField is inconsistent between sqlite3 and postgresql
-------------------------------------+-------------------------------------

Reporter: Antonio Terceiro | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

I can confirm the difference, however, I can't say if a change should be
made. Do you have a suggestion about what to do?

--
Ticket URL: <https://code.djangoproject.com/ticket/27813#comment:1>

[Django]

#27813: BinaryField is inconsistent between sqlite3 and postgresql
-------------------------------------+-------------------------------------

Reporter: Antonio Terceiro | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Antonio Terceiro):

I noticed this because I had a test that checked the value of the
{{{data}}} field after a given operation, and since I was initially
developing against sqlite, I compared the value against a {{{bytes()}}}
literal b{{{'...'}}}. to make the test pass against postgresql as well, I
had to cast the value in the database to {{{bytes()}}} before the
comparison.

I would expect to always get the same type, either {{{bytes}}} or
{{{memoryview}}}. on the other hand, everything else other than that one
test just worked. the view that allows for that data to be downloaded
worked just fine, because {{{bytes}}} and {{{memoryview}}} have a similar
enough interface, so maybe this should be just clearly documented.

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

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)

-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody

Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: 1.10
(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 Tim Graham):

* stage: Unreviewed => Accepted
* type: Uncategorized => Cleanup/optimization

Comment:

Django's test suite is also
[https://github.com/django/django/blob/e124d2da94cc1233729d8f0200809589f6c5afc8/tests/model_fields/test_binaryfield.py#L16
casting to bytes] for comparison. Accepting for further investigation.

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

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)
-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 1.10
(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
-------------------------------------+-------------------------------------

Comment (by Václav Řehák):

The same applies to Oracle backend which also returns bytes. One of the
reasons we use Django in current project is that we can run it on Oracle
(as needed due to historical reasons) but hopefully convert it to
PostgreSQL one day. Subtle differences like this make it more complicated.
What is the reason to return different data type from PostgreSQL backend?

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

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)
-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 1.10
(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
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

My guess is that's how the database or database driver (psycopg2) works.

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

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)
-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 1.10
(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
-------------------------------------+-------------------------------------

Comment (by Stavros Korokithakis):

I am hit by this as well. Returning `HttpResponse(mymodel.mybinaryfield)`
returns a literal `<memory object at 0xdeadbeef>` string instead of the
data on Postgres.

--
Ticket URL: <https://code.djangoproject.com/ticket/27813#comment:6>

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)
-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 1.10
(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
-------------------------------------+-------------------------------------

Comment (by bmatschke):

When using BinaryField as a primary key, it leads to a bug.
For example deleting such a table raises a
{{{
TypeError: '<' not supported between instances of 'memoryview' and
'memoryview'
}}}

Besides, the resulting type should be consistent, independent on the
underlying database.

--
Ticket URL: <https://code.djangoproject.com/ticket/27813#comment:7>

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)
-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody

Type: Bug | Status: new
Component: Database layer | Version: 3.1

(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 Benjamin Matschke):

* version: 1.10 => 3.1
* type: Cleanup/optimization => Bug

--
Ticket URL: <https://code.djangoproject.com/ticket/27813#comment:8>

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)
-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(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 Egor R):

* cc: Egor R (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/27813#comment:9>

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)
-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(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
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

[https://forum.djangoproject.com/t/binaryfield-value-type-differs-across-
databases/23785/6 David Sanders] noticed that `psycopg>=3.0.17` has
started to return `bytes` instead of `memoryview` so I wonder if
[https://www.psycopg.org/psycopg3/docs/news.html#psycopg-3-0-17 we should
resolve as ''wont-fix'' here] given an available and even preferred
solution is available given `psycopg2` is on the way out,

--
Ticket URL: <https://code.djangoproject.com/ticket/27813#comment:10>

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)
-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(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 Ryan Cheley):

* cc: Ryan Cheley (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/27813#comment:11>

[Django]

#27813: BinaryField type inconsistent between sqlite3 (bytes) and postgresql
(memoryview)
-------------------------------------+-------------------------------------
Reporter: Antonio Terceiro | Owner: nobody

Type: Bug | Status: closed

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

Severity: Normal | Resolution: wontfix
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 Antonio Terceiro):

* status: new => closed
* resolution: => wontfix

Comment:

yeah, it makes sense to close this since the fix is actually elsewhere.
I'm closing.

--
Ticket URL: <https://code.djangoproject.com/ticket/27813#comment:12>