Re: Unneeded index created with unique=True and db_index=False in TextField

[Some Developer]

On 15/04/15 23:45, Kevin Christopher Henry wrote:

This issue was the subject of https://code.djangoproject.com/ticket/24082.

There, the accepted (but not implemented) solution is the same as
suggested here: allowing the user to opt out of the creation of the
additional index with `db_index=False`.


Ah, sorry I didn't see that. That solution would certainly work.

Thanks.

--
You received this message because you are subscribed to the Google Groups "Django 
developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/552EED1D.3060106%40googlemail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Unneeded index created with unique=True and db_index=False in TextField

[Kevin Christopher Henry]

This issue was the subject of https://code.djangoproject.com/ticket/24082.

There, the accepted (but not implemented) solution is the same as suggested 
here: allowing the user to opt out of the creation of the additional index 
with `db_index=False`.

On Wednesday, April 15, 2015 at 2:11:25 PM UTC-4, Some Developer wrote:
>
> On 15/04/15 18:22, Tim Graham wrote: 
> > #19441 is the ticket where it was decided that unique=True implies a 
> > database index. The documentation says, 
> > 
> > "Note that when ``unique`` is ``True``, you don't need to specify 
> > 
> > `Field.db_index`, because ``unique`` implies the creation of an index." 
> > 
>
> Understood. I just want to be able to stop the creation of the index in 
> some way. Whether that be via db_index=False or some other new method. 
>
> I would have thought that the creation of the index would be optional. I 
> understand that the default it to create the index and that is fine for 
> the vast majority of cases (in fact until I hit this little problem I 
> didn't care one way or another) but a user should have the option to 
> tell Django not to create the index if they are sure they know what they 
> are doing. 
>
> > Unless we decide that is wrong, I think the correct condition to solve 
> > the issue would be more like `field.db_index orfield.unique and 
> > field.db_index is not False`. We might also have to change the default 
> > keyword of Field from False to None so we know the difference between 
> > "unspecified" and False. 
> > 
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/7e481c16-ec13-4454-88b7-369c725ac30f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Unneeded index created with unique=True and db_index=False in TextField

[Some Developer]

On 15/04/15 18:22, Tim Graham wrote:

#19441 is the ticket where it was decided that unique=True implies a
database index. The documentation says,

"Note that when ``unique`` is ``True``, you don't need to specify

`Field.db_index`, because ``unique`` implies the creation of an index."



Understood. I just want to be able to stop the creation of the index in 
some way. Whether that be via db_index=False or some other new method.


I would have thought that the creation of the index would be optional. I 
understand that the default it to create the index and that is fine for 
the vast majority of cases (in fact until I hit this little problem I 
didn't care one way or another) but a user should have the option to 
tell Django not to create the index if they are sure they know what they 
are doing.



Unless we decide that is wrong, I think the correct condition to solve
the issue would be more like `field.db_index orfield.unique and
field.db_index is not False`. We might also have to change the default
keyword of Field from False to None so we know the difference between
"unspecified" and False.



--
You received this message because you are subscribed to the Google Groups "Django 
developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/552EA9BD.1030001%40googlemail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Unneeded index created with unique=True and db_index=False in TextField

[Tim Graham]

#19441 is the ticket where it was decided that unique=True implies a 
database index. The documentation says, 

"Note that when ``unique`` is ``True``, you don't need to 
specify`Field.db_index`, 
because ``unique`` implies the creation of an index."

Unless we decide that is wrong, I think the correct condition to solve the 
issue would be more like `field.db_index or field.unique and field.db_index 
is not False`. We might also have to change the default keyword of Field 
from False to None so we know the difference between "unspecified" and 
False.

On Wednesday, April 15, 2015 at 12:39:53 PM UTC-4, Some Developer wrote:
>
> On 15/04/15 03:37, Curtis Maloney wrote: 
> > Was the OP referring to the unique index, or the index created for the 
> > LIKE lookups? 
> > 
> > I was involved in a discussion recently [was there something on list 
> > too?] wanting to be able to opt-out of the second index because they 
> > knew they didn't need it, and it was _huge_ on their database. 
> > 
> > -- 
> > C 
> > 
>
> The index created for the LIKE lookups. 
>
> The index name even has _like appended on to the name so I'm pretty sure 
> that this is the problem at hand. 
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/c77a48fb-470c-47f6-94b5-cff1fdbfbe17%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Unneeded index created with unique=True and db_index=False in TextField

[Some Developer]

On 15/04/15 03:37, Curtis Maloney wrote:

Was the OP referring to the unique index, or the index created for the
LIKE lookups?

I was involved in a discussion recently [was there something on list
too?] wanting to be able to opt-out of the second index because they
knew they didn't need it, and it was _huge_ on their database.

--
C



The index created for the LIKE lookups.

The index name even has _like appended on to the name so I'm pretty sure 
that this is the problem at hand.


--
You received this message because you are subscribed to the Google Groups "Django 
developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/552E944D.7050500%40googlemail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Unneeded index created with unique=True and db_index=False in TextField

[Alex Hill]

I agree this is a bug, and I think it's independent of the discussion of 
customisable indexes.

Postgres creates the necessary index for unique constraints automatically 
as Tommy said. I'm guessing other backends do too, because the explicit 
index creation DDL is omitted when unique is True in the superclass's 
_model_indexes_sql. [1] So too should the xxx_pattern_ops index DDL be 
omitted unless db_index is True.

Line 24 of the linked file currently reads: [2]

if db_type is not None and (field.db_index or field.unique):
# Create the special text index

The xxx_pattern_ops operator classes only help with LIKE and regex queries; 
the unique constraint doesn't need them. I think we can change that to the 
following with no ill effects:

if db_type is not None and field.db_index:

Cheers,
Alex

[1] 
https://github.com/django/django/blob/28e89783254ac0899a26eee324555a9033ccbe9a/django/db/backends/base/schema.py#L852
[2] 
https://github.com/django/django/blob/28e89783254ac0899a26eee324555a9033ccbe9a/django/db/backends/postgresql_psycopg2/schema.py#L24

On Wednesday, April 15, 2015 at 10:37:42 AM UTC+8, Curtis Maloney wrote:
>
> Was the OP referring to the unique index, or the index created for the 
> LIKE lookups?
>
> I was involved in a discussion recently [was there something on list too?] 
> wanting to be able to opt-out of the second index because they knew they 
> didn't need it, and it was _huge_ on their database.
>
> --
> C
>
>
> On 15 April 2015 at 11:58, Tommy Beadle  
> wrote:
>
>> I believe that Postgres will *always* create an index on a column with a 
>> UNIQUE constraint.
>>
>> regression=> create table yo (id serial primary key, blah varchar(32) 
>> unique);
>> CREATE TABLE
>> regression=> \d yo
>> Table "public.yo"
>>  Column | Type  |
>> Modifiers
>>
>> +---+-
>>  id | integer   | not null default 
>> nextval('yo_id_seq'::regclass)
>>  blah   | character varying(32) | 
>> Indexes:
>> "yo_pkey" PRIMARY KEY, btree (id)
>> "yo_blah_key" UNIQUE CONSTRAINT, btree (blah)
>>
>> regression=> drop index yo_blah_key;
>> ERROR:  cannot drop index yo_blah_key because constraint yo_blah_key on 
>> table yo requires it
>> HINT:  You can drop constraint yo_blah_key on table yo instead.
>>
>>
>> On Tue, Apr 14, 2015 at 9:01 PM, Some Developer > > wrote:
>>
>>> Using Django 1.8, psycopg2 2.6 and PostgreSQL 9.4.1.
>>>
>>> I have a model with a models.TextField(unique=True, db_index=False, 
>>> primary_key=False) field in it.
>>>
>>> I understand that an index is created because of the comment shown in 
>>> this code:
>>>
>>> https://github.com/django/django/blob/master/django/db/
>>> backends/postgresql_psycopg2/schema.py#L17
>>>
>>> but even though the index is suggested for LIKE queries using non C 
>>> locales I would have thought the addition of db_index=False would have 
>>> negated that.
>>>
>>> I feel that this is a bug. An index is not required by PostgreSQL on a 
>>> unique constraint (it may be recommended but that is beside the point) and 
>>> if I explicitly state db_index=False then the Django ORM should remove the 
>>> index even though the index is recommended.
>>>
>>> Thoughts?
>>>
>>> -- 
>>> You received this message because you are subscribed to the Google 
>>> Groups "Django developers  (Contributions to Django itself)" group.
>>> To unsubscribe from this group and stop receiving emails from it, send 
>>> an email to django-develop...@googlegroups.com .
>>> To post to this group, send email to django-d...@googlegroups.com 
>>> .
>>> Visit this group at http://groups.google.com/group/django-developers.
>>> To view this discussion on the web visit https://groups.google.com/d/
>>> msgid/django-developers/552DB881.1090006%40googlemail.com.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>>
>> -- 
>> Grace and Peace,
>> Tommy B.
>>
>> I want to live like I know what I'm leaving.
>> --Switchfoot, "Awakening"
>>  
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "Django developers (Contributions to Django itself)" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to django-develop...@googlegroups.com .
>> To post to this group, send email to django-d...@googlegroups.com 
>> .
>> Visit this group at http://groups.google.com/group/django-developers.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/django-developers/CAKfxM0LJNbT%2BWtBW0n%3DD9K3QxNjLas7H3t2ZpMWfuaxXh1uxbQ%40mail.gmail.com
>>  
>> 
>> .
>>
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>

Re: Unneeded index created with unique=True and db_index=False in TextField

[Curtis Maloney]

Was the OP referring to the unique index, or the index created for the LIKE
lookups?

I was involved in a discussion recently [was there something on list too?]
wanting to be able to opt-out of the second index because they knew they
didn't need it, and it was _huge_ on their database.

--
C


On 15 April 2015 at 11:58, Tommy Beadle  wrote:

> I believe that Postgres will *always* create an index on a column with a
> UNIQUE constraint.
>
> regression=> create table yo (id serial primary key, blah varchar(32)
> unique);
> CREATE TABLE
> regression=> \d yo
> Table "public.yo"
>  Column | Type  |
> Modifiers
>
> +---+-
>  id | integer   | not null default
> nextval('yo_id_seq'::regclass)
>  blah   | character varying(32) |
> Indexes:
> "yo_pkey" PRIMARY KEY, btree (id)
> "yo_blah_key" UNIQUE CONSTRAINT, btree (blah)
>
> regression=> drop index yo_blah_key;
> ERROR:  cannot drop index yo_blah_key because constraint yo_blah_key on
> table yo requires it
> HINT:  You can drop constraint yo_blah_key on table yo instead.
>
>
> On Tue, Apr 14, 2015 at 9:01 PM, Some Developer  > wrote:
>
>> Using Django 1.8, psycopg2 2.6 and PostgreSQL 9.4.1.
>>
>> I have a model with a models.TextField(unique=True, db_index=False,
>> primary_key=False) field in it.
>>
>> I understand that an index is created because of the comment shown in
>> this code:
>>
>> https://github.com/django/django/blob/master/django/db/
>> backends/postgresql_psycopg2/schema.py#L17
>>
>> but even though the index is suggested for LIKE queries using non C
>> locales I would have thought the addition of db_index=False would have
>> negated that.
>>
>> I feel that this is a bug. An index is not required by PostgreSQL on a
>> unique constraint (it may be recommended but that is beside the point) and
>> if I explicitly state db_index=False then the Django ORM should remove the
>> index even though the index is recommended.
>>
>> Thoughts?
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django developers  (Contributions to Django itself)" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to django-developers+unsubscr...@googlegroups.com.
>> To post to this group, send email to django-developers@googlegroups.com.
>> Visit this group at http://groups.google.com/group/django-developers.
>> To view this discussion on the web visit https://groups.google.com/d/
>> msgid/django-developers/552DB881.1090006%40googlemail.com.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
>
> --
> Grace and Peace,
> Tommy B.
>
> I want to live like I know what I'm leaving.
> --Switchfoot, "Awakening"
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/CAKfxM0LJNbT%2BWtBW0n%3DD9K3QxNjLas7H3t2ZpMWfuaxXh1uxbQ%40mail.gmail.com
> 
> .
>
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAG_XiSBPGBr%3DPNAgiVKUzK%2Bv%2BCKgf5An-AO13NZqHrXnuNBEiQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: Unneeded index created with unique=True and db_index=False in TextField

[Tommy Beadle]

I believe that Postgres will *always* create an index on a column with a
UNIQUE constraint.

regression=> create table yo (id serial primary key, blah varchar(32)
unique);
CREATE TABLE
regression=> \d yo
Table "public.yo"
 Column | Type  |
Modifiers
+---+-
 id | integer   | not null default
nextval('yo_id_seq'::regclass)
 blah   | character varying(32) |
Indexes:
"yo_pkey" PRIMARY KEY, btree (id)
"yo_blah_key" UNIQUE CONSTRAINT, btree (blah)

regression=> drop index yo_blah_key;
ERROR:  cannot drop index yo_blah_key because constraint yo_blah_key on
table yo requires it
HINT:  You can drop constraint yo_blah_key on table yo instead.


On Tue, Apr 14, 2015 at 9:01 PM, Some Developer 
wrote:

> Using Django 1.8, psycopg2 2.6 and PostgreSQL 9.4.1.
>
> I have a model with a models.TextField(unique=True, db_index=False,
> primary_key=False) field in it.
>
> I understand that an index is created because of the comment shown in this
> code:
>
> https://github.com/django/django/blob/master/django/db/
> backends/postgresql_psycopg2/schema.py#L17
>
> but even though the index is suggested for LIKE queries using non C
> locales I would have thought the addition of db_index=False would have
> negated that.
>
> I feel that this is a bug. An index is not required by PostgreSQL on a
> unique constraint (it may be recommended but that is beside the point) and
> if I explicitly state db_index=False then the Django ORM should remove the
> index even though the index is recommended.
>
> Thoughts?
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers  (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-developers.
> To view this discussion on the web visit https://groups.google.com/d/
> msgid/django-developers/552DB881.1090006%40googlemail.com.
> For more options, visit https://groups.google.com/d/optout.
>



-- 
Grace and Peace,
Tommy B.

I want to live like I know what I'm leaving.
--Switchfoot, "Awakening"

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CAKfxM0LJNbT%2BWtBW0n%3DD9K3QxNjLas7H3t2ZpMWfuaxXh1uxbQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Unneeded index created with unique=True and db_index=False in TextField

[Some Developer]

Using Django 1.8, psycopg2 2.6 and PostgreSQL 9.4.1.

I have a model with a models.TextField(unique=True, db_index=False, 
primary_key=False) field in it.


I understand that an index is created because of the comment shown in 
this code:


https://github.com/django/django/blob/master/django/db/backends/postgresql_psycopg2/schema.py#L17

but even though the index is suggested for LIKE queries using non C 
locales I would have thought the addition of db_index=False would have 
negated that.


I feel that this is a bug. An index is not required by PostgreSQL on a 
unique constraint (it may be recommended but that is beside the point) 
and if I explicitly state db_index=False then the Django ORM should 
remove the index even though the index is recommended.


Thoughts?

--
You received this message because you are subscribed to the Google Groups "Django 
developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/552DB881.1090006%40googlemail.com.
For more options, visit https://groups.google.com/d/optout.