questions about 2nd index on one column

On 01/03/2017 11:07 AM, Ravi Kapoor wrote:

I have a bit strange question. I am trying to figure out how to avoid
table locking while creating an index through Django (1.5.1) in Postgres
9.4.7

First Django 1.5.x has been past end of life for 2.25 years.
Second before it went EOL it was up to 1.5.12.

Django 1.5.1 does not support concurrent indexing. So my thought is to
first create a concurrent index using SQL prompt.
Then try to update django model to add index, which will effectively
create 2 indexes on same column.

Is the index going to be on a single column?

How are you going to update the model?

So my questions are:

If I create a 2nd index on one column,
1. Does postgres scan entire table to create index from scratch or does
it simply copy the first index?
2. Does postgres lock the table to create this index?

thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/03/2017 11:07 AM, Ravi Kapoor wrote:

I have a bit strange question. I am trying to figure out how to avoid
table locking while creating an index through Django (1.5.1) in Postgres
9.4.7

Django 1.5.1 does not support concurrent indexing. So my thought is to
first create a concurrent index using SQL prompt.
Then try to update django model to add index, which will effectively
create 2 indexes on same column.

I really need to read the entire subject. So in response to my own
question, yes it is on one column.

So my questions are:

If I create a 2nd index on one column,
1. Does postgres scan entire table to create index from scratch or does
it simply copy the first index?
2. Does postgres lock the table to create this index?

thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/03/2017 11:35 AM, Ravi Kapoor wrote:
Please reply to list also.
Ccing list.

Yes I am aware of django EOL. However, our company is still using it, we
have a migration plan later this year, however for now, I got to work
with what we have.

Still, you are missing 14 patch releases to the 1.5 version.

Correct, the index will be on one column.

In Django, the model is described in model.py file, so to update it, I
will simply change following line
votes1 = models.CharField(default='', max_length=200)
to following line
votes1 = models.CharField(db_index=True, default='', max_length=200)

and run Django migrations.

Hmm, from this:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/indexcmds.c;h=fdfa6ca4f5cd3be2db624e1c709ad037dc700b40;hb=c7681b2b9a115eb05048a485480826bc0efa6d3b

/*
* CheckIndexCompatible
* Determine whether an existing index definition is compatible with a
* prospective index definition, such that the existing index storage
* could become the storage of the new index, avoiding a rebuild.
*
/*

It would seem that the index would not be rebuilt, assuming all conditions are the same.

The part that has me questioning is this:

* This is tailored to the needs of ALTER TABLE ALTER TYPE, ..

Someone who knows the internals better will have to verify this.

On Tue, Jan 3, 2017 at 11:26 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 01/03/2017 11:07 AM, Ravi Kapoor wrote:

I have a bit strange question. I am trying to figure out how to
avoid
table locking while creating an index through Django (1.5.1) in
Postgres
9.4.7

Django 1.5.1 does not support concurrent indexing. So my thought
is to
first create a concurrent index using SQL prompt.
Then try to update django model to add index, which will effectively
create 2 indexes on same column.

I really need to read the entire subject. So in response to my own
question, yes it is on one column.

So my questions are:

If I create a 2nd index on one column,
1. Does postgres scan entire table to create index from scratch
or does
it simply copy the first index?
2. Does postgres lock the table to create this index?

thanks

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Please reply to list also.

apologies, my bad.

It would seem that the index would not be rebuilt, assuming all

conditions are the same.
Thanks for finding this. This is enough info for me to spend a day
experimenting. I did not want to waste a day if we knew upfront that it
wont work. But looks like it will be worth the time.

regards

On Tue, Jan 3, 2017 at 12:09 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 01/03/2017 11:35 AM, Ravi Kapoor wrote:
Please reply to list also.
Ccing list.

Yes I am aware of django EOL. However, our company is still using it, we
have a migration plan later this year, however for now, I got to work
with what we have.

Still, you are missing 14 patch releases to the 1.5 version.

Correct, the index will be on one column.

In Django, the model is described in model.py file, so to update it, I
will simply change following line
votes1 = models.CharField(default='', max_length=200)
to following line
votes1 = models.CharField(db_index=True, default='', max_length=200)

and run Django migrations.

Hmm, from this:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=
blob;f=src/backend/commands/indexcmds.c;h=fdfa6ca4f5cd3be2db624e1c709ad0
37dc700b40;hb=c7681b2b9a115eb05048a485480826bc0efa6d3b

/*
* CheckIndexCompatible
* Determine whether an existing index definition is
compatible with a
* prospective index definition, such that the existing index
storage
* could become the storage of the new index, avoiding a
rebuild.
*
/*

It would seem that the index would not be rebuilt, assuming all conditions
are the same.

The part that has me questioning is this:

* This is tailored to the needs of ALTER TABLE ALTER TYPE, ..

Someone who knows the internals better will have to verify this.

On Tue, Jan 3, 2017 at 11:26 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 01/03/2017 11:07 AM, Ravi Kapoor wrote:

I have a bit strange question. I am trying to figure out how to
avoid
table locking while creating an index through Django (1.5.1) in
Postgres
9.4.7

Django 1.5.1 does not support concurrent indexing. So my thought
is to
first create a concurrent index using SQL prompt.
Then try to update django model to add index, which will

effectively

create 2 indexes on same column.

I really need to read the entire subject. So in response to my own
question, yes it is on one column.

So my questions are:

If I create a 2nd index on one column,
1. Does postgres scan entire table to create index from scratch
or does
it simply copy the first index?
2. Does postgres lock the table to create this index?

thanks

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com