2017-10-11 10:46 GMT-03:00 Laurenz Albe <laurenz.albe@xxxxxxxxxxx>:
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?
Before creating the index, should I set the type of transaction lock? What?
-------------------------------------------------------------------------------------------
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?
Before creating the index, should I set the type of transaction lock? What?
-------------------------------------------------------------------------------------------
SELECT
L.mode, c.relname, locktype, l.GRANTED, l.transactionid, virtualtransaction
FROM pg_locks l, pg_class c
where c.oid = l.relation
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 |
Maybe there is a long-running transaction that blocks the
ACCESS EXCLUSIVE lock required. It could also be a prepared
transaction.
Yours,
Laurenz Albe
Best Regards
Neto
