Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:  fixed
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  postgresql   |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Claude Paroz ):

 In [changeset:"012229914cb1391ee4fcb6c183a341a6491cd377"]:
 {{{
 #!CommitTicketReference repository=""
 revision="012229914cb1391ee4fcb6c183a341a6491cd377"
 [1.5.x] Created special PostgreSQL text indexes when unique is True

 Refs #19441.
 Backport of c698c55966 from master.
 }}}

-- 
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.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:  fixed
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  postgresql   |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Claude Paroz ):

 In [changeset:"012229914cb1391ee4fcb6c183a341a6491cd377"]:
 {{{
 #!CommitTicketReference repository=""
 revision="012229914cb1391ee4fcb6c183a341a6491cd377"
 [1.5.x] Created special PostgreSQL text indexes when unique is True

 Refs #19441.
 Backport of c698c55966 from master.
 }}}

-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:  fixed
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  postgresql   |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Claude Paroz ):

 In [changeset:"c698c55966ed9179828857398d27bf69e64713a2"]:
 {{{
 #!CommitTicketReference repository=""
 revision="c698c55966ed9179828857398d27bf69e64713a2"
 Created special PostgreSQL text indexes when unique is True

 Refs #19441.
 }}}

-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:  fixed
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  postgresql   |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by dyve):

 Claude, this would be perfect. The explanation you added (unique=True
 implies db_index=True on all occasions) says it all, and it very elegant.

  The only "added weight" you collect by solving it like this is the
 creation of like-indexes on postgresql when the field is used as a unique
 identifier and not meant for like searching, The like indexes  would be
 created but never used. I think this is not a problem, as it is far more
 important to have a unified way that Django works (again, unique=True
 implies db_index=True) than to take different database vendors into
 account when deciding when or not to add db_index=True.

 To but it briefly, I'd be in favor of submitting this patch as the final
 solution for this issue.

-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:  fixed
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  postgresql   |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by claudep):

 Dylan, can you check the attached patch? Does it address your concerns?

 The alternative would be to document that Postgres users who want to make
 `like` queries on `unique` fields should set `db_index=True` in addition
 to `unique=True`.

-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:  fixed
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  postgresql   |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by dyve):

 Now that I;'ve worked with this solution in 1.5c1 I am wondering if this
 is 100% correct.

 For all systems except for postgres, unique=True makes db_index=True
 obsolete
 For postgres, you would basically need to add db_index=True to fields with
 unique=True if you intend to do LIKE searches on that field.

 So the choice is:
 - different technical behavior varying by db vendor
 - different functional behavior varying by db vendor

 Either way this might need some extra documentation. If you don't know
 where to look, this is a nasty one to find in Postgres country.

-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:  fixed
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  postgresql   |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Claude Paroz ):

 In [changeset:"ddea0e0d7ee1a885f8ecbe45d7306a39c7780d63"]:
 {{{
 #!CommitTicketReference repository=""
 revision="ddea0e0d7ee1a885f8ecbe45d7306a39c7780d63"
 [1.5.x] Fixed #19441 -- Created PostgreSQL varchar index when unique=True

 Thanks Dylan Verheul for the report and Anssi Kääriäinen for the
 review.
 Backport of 55972ee5c from master.
 }}}

-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:  fixed
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  postgresql   |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Claude Paroz ):

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


Comment:

 In [changeset:"55972ee5c799c75f2d3a320a46297076aaae614a"]:
 {{{
 #!CommitTicketReference repository=""
 revision="55972ee5c799c75f2d3a320a46297076aaae614a"
 Fixed #19441 -- Created PostgreSQL varchar index when unique=True

 Thanks Dylan Verheul for the report and Anssi Kääriäinen for the
 review.
 }}}

-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Ready for
 Keywords:  postgresql   |  checkin
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by akaariai):

 * stage:  Accepted => Ready for checkin


Comment:

 It seems that getting the LIKE index when db_index = True for unique field
 is correct.

 I tested the patch on top of 1.5 for the indexes tests. This looks ready
 for commit to me.

-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dyve |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  postgresql   |  Needs documentation:  0
Has patch:  1|  Patch needs improvement:  0
  Needs tests:  0|UI/UX:  0
Easy pickings:  0|
-+-
Changes (by aaugustin):

 * reporter:  dylan@… => dyve


-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dylan@…  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  postgresql   |  Needs documentation:  0
Has patch:  1|  Patch needs improvement:  0
  Needs tests:  0|UI/UX:  0
Easy pickings:  0|
-+-

Comment (by dyve):

 Apologies if this is too much of a vanity request, but could this ticket's
 reporter be set to my user (dyve) instead of to my e-mail? Makes it easier
 to keep track of. Apparently I wasn't logged in properly when I filed it.

-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dylan@…  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  postgresql   |  Needs documentation:  0
Has patch:  1|  Patch needs improvement:  0
  Needs tests:  0|UI/UX:  0
Easy pickings:  0|
-+-
Changes (by claudep):

 * has_patch:  0 => 1


-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dylan@…  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |   Resolution:
 Severity:  Normal   | Triage Stage:  Accepted
 Keywords:  postgresql   |  Needs documentation:  0
Has patch:  0|  Patch needs improvement:  0
  Needs tests:  0|UI/UX:  0
Easy pickings:  0|
-+-
Changes (by claudep):

 * keywords:   => postgresql
 * version:  1.5-beta-1 => master
 * stage:  Unreviewed => Accepted


-- 
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 https://groups.google.com/groups/opt_out.

Re: [Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
-+-
 Reporter:  dylan@…  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:
  (models, ORM)  |  1.5-beta-1
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
Has patch:  0|  Unreviewed
  Needs tests:  0|  Needs documentation:  0
Easy pickings:  0|  Patch needs improvement:  0
 |UI/UX:  0
-+-
Changes (by claudep):

 * needs_better_patch:   => 0
 * needs_tests:   => 0
 * needs_docs:   => 0


Old description:

> class Species(models.Model):
> scientific_name = models.CharField(max_length=200, db_index=True)
>
> Genererates 2 indexes
>
> BEGIN;
> CREATE INDEX "species_species_scientific_name" ON "species_species"
> ("scientific_name");
> CREATE INDEX "species_species_scientific_name_like" ON "species_species"
> ("scientific_name" varchar_pattern_ops);
>
> COMMIT;
>
> class Species(models.Model):
> scientific_name = models.CharField(max_length=200, unique=True,
> db_index=True)
>
> Does not create any indexes, because it depends on UNIQUE, but it SHOULD
> generate the index
>
> CREATE INDEX "species_species_scientific_name_like" ON "species_species"
> ("scientific_name" varchar_pattern_ops);
>
> to make sure Postgres is optimized for LIKE operator.

New description:

 {{{
 class Species(models.Model):
 scientific_name = models.CharField(max_length=200, db_index=True)
 }}}

 Generates 2 indexes:
 {{{
 BEGIN;
 CREATE INDEX "species_species_scientific_name" ON "species_species"
 ("scientific_name");
 CREATE INDEX "species_species_scientific_name_like" ON "species_species"
 ("scientific_name" varchar_pattern_ops);

 COMMIT;
 }}}

 {{{
 class Species(models.Model):
 scientific_name = models.CharField(max_length=200, unique=True,
 db_index=True)
 }}}
 ...does not create any indexes, because it depends on UNIQUE, but it
 SHOULD generate the index:

 {{{CREATE INDEX "species_species_scientific_name_like" ON
 "species_species" ("scientific_name" varchar_pattern_ops);}}}

 to make sure Postgres is optimized for LIKE operator.

--

-- 
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 https://groups.google.com/groups/opt_out.

[Django] #19441: No Postgres _like index when unique=True

[Django]

#19441: No Postgres _like index when unique=True
--+
 Reporter:  dylan@…   |  Owner:  nobody
 Type:  Bug   | Status:  new
Component:  Database layer (models, ORM)  |Version:  1.5-beta-1
 Severity:  Normal|   Keywords:
 Triage Stage:  Unreviewed|  Has patch:  0
Easy pickings:  0 |  UI/UX:  0
--+
 class Species(models.Model):
 scientific_name = models.CharField(max_length=200, db_index=True)

 Genererates 2 indexes

 BEGIN;
 CREATE INDEX "species_species_scientific_name" ON "species_species"
 ("scientific_name");
 CREATE INDEX "species_species_scientific_name_like" ON "species_species"
 ("scientific_name" varchar_pattern_ops);

 COMMIT;

 class Species(models.Model):
 scientific_name = models.CharField(max_length=200, unique=True,
 db_index=True)

 Does not create any indexes, because it depends on UNIQUE, but it SHOULD
 generate the index

 CREATE INDEX "species_species_scientific_name_like" ON "species_species"
 ("scientific_name" varchar_pattern_ops);

 to make sure Postgres is optimized for LIKE operator.

-- 
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 https://groups.google.com/groups/opt_out.