Re: blocking index creation

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
>