Re: [Django] #14904: TextField with unique (or in unique_together) constraint breaks for large inputs in Postgres

[Django]

#14904: TextField with unique (or in unique_together) constraint breaks for 
large
inputs in Postgres
-+-
 Reporter:  jorn |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.2
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  postgresql, index,   | Triage Stage:  Accepted
  textfield  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Sarah Messer):

 Have also observed this with Django 1.8 and postgres 9.3

 I'm linking references for fixes / workarounds found in a quick search:
 * http://stackoverflow.com/questions/20725671/how-to-truncate-column-in-
 order-to-create-indexes
 * https://www.postgresql.org/message-
 id/AANLkTikG_nHARKr9qeQXpS7Q6QXgJvuFUi6Wxpd0o7H7%40mail.gmail.com
 * https://www.postgresql.org/docs/current/static/indexes-opclass.html

 At the moment, it looks like any workaround involves writing some raw SQL.
 Digging through Django's code, indices are created by
 django.db.backends.base.schema.BaseDatabaseSchemaEditor._sql_create_index()
 That's pretty far outside the public API.

--
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.3eda9110bdf51ce2509a43717d1aa5b4%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #14904: TextField with unique (or in unique_together) constraint breaks for large inputs in Postgres

[Django]

#14904: TextField with unique (or in unique_together) constraint breaks for 
large
inputs in Postgres
-+-
   Reporter:  jorn   |  Owner:  nobody
   Type:  Bug| Status:  new
  Milestone: |  Component:  Database layer
Version:  1.2|  (models, ORM)
 Resolution: |   Severity:  Normal
   Triage Stage:  Accepted   |   Keywords:  postgresql, index,
Needs documentation:  0  |  textfield
Patch needs improvement:  0  |  Has patch:  0
  UI/UX:  0  |Needs tests:  0
 |  Easy pickings:  0
-+-
Changes (by anonymous):

 * ui_ux:   => 0
 * easy:   => 0


Comment:

 Have the same problem with mysql,

 _mysql_exceptions.OperationalError: (1071, 'Specified key was too long;
 max key length is 767 bytes')

 With
 {{{
 name = models.CharField(null=True, blank=True, max_length=300)
 user = models.ForeignKey(User,null=True, blank=True)

 class Meta :
 unique_together = (('name', 'user'),)
 }}}

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #14904: TextField with unique (or in unique_together) constraint breaks for large inputs in Postgres

[Django]

#14904: TextField with unique (or in unique_together) constraint breaks for 
large
inputs in Postgres
-+-
   Reporter:  jorn   |Owner:  nobody
   Type:  Bug|   Status:  new
  Milestone: |Component:  Database layer
Version:  1.2|  (models, ORM)
 Resolution: | Severity:  Normal
   Triage Stage:  Accepted   | Keywords:  postgresql, index,
Needs documentation:  0  |  textfield
Patch needs improvement:  0  |Has patch:  0
 |  Needs tests:  0
-+-
Changes (by jaddison):

 * type:   => Bug
 * severity:   => Normal


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #14904: TextField with unique (or in unique_together) constraint breaks for large inputs in Postgres

[Django]

#14904: TextField with unique (or in unique_together) constraint breaks for 
large
inputs in Postgres
---+
  Reporter:  jorn  | Owner:  nobody 
 
Status:  new   | Milestone: 
 
 Component:  Database layer (models, ORM)  |   Version:  1.2
 
Resolution:|  Keywords:  
postgresql, index, textfield
 Stage:  Accepted  | Has_patch:  0  
 
Needs_docs:  0 |   Needs_tests:  0  
 
Needs_better_patch:  0 |  
---+
Changes (by ramiro):

  * needs_better_patch:  => 0
  * stage:  Unreviewed => Accepted
  * needs_tests:  => 0
  * needs_docs:  => 0

Comment:

 It would be helpful to have a complete simple sample case description
 especially a hint at how much text content should be assigned to the field
 for this condition triggering, and thus can be reproduced and/or tested
 against other postgresql versions.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-upda...@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

[Django] #14904: TextField with unique (or in unique_together) constraint breaks for large inputs in Postgres

[Django]

#14904: TextField with unique (or in unique_together) constraint breaks for 
large
inputs in Postgres
--+-
 Reporter:  jorn  |   Owner:  nobody
   Status:  new   |   Milestone:
Component:  Database layer (models, ORM)  | Version:  1.2   
 Keywords:  postgresql, index, textfield  |   Stage:  Unreviewed
Has_patch:  0 |  
--+-
 Hi,

 i have a model like this one:

 {{{
 class foo(Model):
   a = CharField(max_length=8)
   b = TextField()

   class Meta:
 unique_together = (("a", "b"),)
 }}}

 Now this leads to a sql create table statement like the following in
 Postgresql:
 {{{
 CREATE TABLE "myapp_foo" (
 "id" serial NOT NULL PRIMARY KEY,
 "a" varchar(8) NOT NULL,
 "b" text NOT NULL,
 UNIQUE ("a", "b")
 );
 }}}

 The problem here is that b is a textfield which can get arbitrarily big
 (that's what they are there for), while values in a btree index can not:
 If the input for b is above a certain size postgresql will complain:
 {{{
 django.db.utils.DatabaseError: index row size 3032 exceeds maximum 2712
 for index "myapp_a_key"
 HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
 Consider a function index of an MD5 hash of the value, or use full text
 indexing.
 }}}

 The problem here is that the used unique constraint as of Postgres 8.4.5
 uses a btree internally, which as stated above doesn't work with values
 above a certain size.

 The solution for this problem would be: If a TextField has a / is part of
 a unique constraint or has an explicit index_db=True: _always_ index an
 md5 or better sha256 sum of TextField columns only (note that sha256 is in
 pgcrypto module only).
 In this case:
 {{{ UNIQUE ("a", md5("b")) }}}

 As mentioned this also applies to the universal index_db=True field
 argument.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-upda...@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.