Re: blocking index creation

Dear,
With alternative, I tested the creation using concurrency
(CREATE INDEX CONCURRENCY NAME_IDX ON TABLE USING HASH (COLUMN);

from what I saw the index already appeared in the query result, because
before this, the index did not even appear in the result, only the Lineitem
table:

SELECT
L.mode, c.relname, locktype, l.GRANTED, l.transactionid,
virtualtransaction
FROM pg_locks l, pg_class c
where c.oid = l.relation

screen result after concurrency: https://i.stack.imgur.com/htzIY.jpg

Now, I'm waiting to finish creating the index.

2017-10-11 19:54 GMT-03:00 Neto pr <netopr9(at)gmail(dot)com>:

> Hello all,
> I ran the query on PG_STAT_ACTIVITY table (Select * From
> pg_stat_activity), see the complete result in this worksheet of the link
> below.
>
> https://sites.google.com/site/goissbr/img/Resultado_pg_stat_
> activity-create_index.xls
>
> The CREATE INDEX command line is identified with the orange background.
> At this point 18 hours have passed and the creation of a single index has
> not yet been completed.
> I have verified that the command is Active status, but I do not know if
> it's waiting for anything, can you help me analyze the attached output.
>
> Regards
> Neto
>
> 2017-10-11 18:08 GMT-03:00 Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>:
>
>>
>>
>> On 10/11/2017 04:11 PM, Neto pr wrote:
>> >
>> > 2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at
>> > <mailto:laurenz(dot)albe(at)cybertec(dot)at>>:
>> >
>> > Neto pr wrote:
>> > > When creating index on table of approximately 10GB of data, the
>> DBMS hangs (I think),
>> > > because even after waiting 10 hours there was no return of the
>> command.
>> > > It happened by creating Hash indexes and B + tree indexes.
>> > > However, for some columns, it was successfully (L_RETURNFLAG,
>> L_PARTKEY).
>> >
>> > > If someone has a hint how to speed up index creation so that it
>> completes successfully.
>> >
>> > Look if CREATE INDEX is running or waiting for a lock (check the
>> > "pg_locks" table, see if the backend consumes CPU time).
>> >
>> >
>> > In this moment now, there is an index being created in the Lineitem
>> > table (+ - 10 Gb), and apparently it is locked, since it started 7 hours
>> > ago.
>> > I've looked at the pg_locks table and look at the result, it's with
>> > "ShareLock" lock mode.
>> > Is this blocking correct? or should it be another type?
>> >
>>
>> Yes, CREATE INDEX acquire SHARE lock, see
>>
>> https://www.postgresql.org/docs/9.1/static/explicit-locking.html
>>
>> > Before creating the index, should I set the type of transaction lock?
>> What?
>>
>> Eeee? Not sure I understand. The command acquires all necessary locks
>> automatically.
>>
>> > ------------------------------------------------------------
>> -------------------------------
>> > SELECT
>> > L.mode, c.relname, locktype, l.GRANTED, l.transactionid,
>> > virtualtransaction
>> > FROM pg_locks l, pg_class c
>> > where c.oid = l.relation
>> >
>> > -------------- RESULT
>> > --------------------------------------------------------------
>> > AccessShareLock pg_class_tblspc_relfilenode_index
>> relation TRUE
>> > (null) 3/71
>> > AccessShareLock pg_class_relname_nsp_index relation
>> TRUE (null) 3/71
>> > AccessShareLock pg_class_oid_index relation TRUE
>> (null) 3/71
>> > AccessShareLock pg_class relation TRUE (null)
>> 3/71
>> > AccessShareLock pg_locks relation TRUE (null)
>> 3/71
>> > ShareLock lineitem relation TRUE (null) 21/3769
>> >
>> >
>>
>> Well, we see something is holding a SHARE lock on the "lineitem" table,
>> but we don't really know what the session is doing.
>>
>> There's a PID in the pg_locks table, you can use it to lookup the
>> session in pg_stat_activity which includes the query (and also "state"
>> column that will tell you if it's active or waiting for a lock.
>>
>> regards
>>
>> --
>> Tomas Vondra http://www.2ndQuadrant.com
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>