pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

blocking issue when create index concurrently
2+ messages / 2 participants
[nested] [flat]

* blocking issue when create index concurrently
@ 2016-12-14 23:21 xu jian <jamesxu@outlook.com>
  2016-12-15 17:33 ` Re: blocking issue when create index concurrently Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: xu jian @ 2016-12-14 23:21 UTC (permalink / raw)
  To: pgsql-admin

Hello,

          I was trying to create 2 indexes concurrently(on different table) in same database. I didn't expect the first create index operation would block the other one since they were running on different table.

However, it is not true. I found one creating index command was blocking the other one.

so I run the lock dependency query which I find from https://wiki.postgresql.org/wiki/Lock_dependency_information,

       blocker_target        | blocker_pid | blocker_mode  | depth |           target            |  pid  |   mode    |     seq
-----------------------------+-------------+---------------+-------+-----------------------------+-------+-----------+-------------
 (virtualxid,,,,,1/2069,,,,) |       69466 | ExclusiveLock |     1 | (virtualxid,,,,,1/2069,,,,) | 69611 | ShareLock | 69466,69611

from pg_locks, I can only see locktype is virtualxid, and there is no relation info.
does anyone know why the blocking happens? I guess it might be blocked on system view or tables, but just guess.

thanks

James






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: blocking issue when create index concurrently
  2016-12-14 23:21 blocking issue when create index concurrently xu jian <jamesxu@outlook.com>
@ 2016-12-15 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2016-12-15 17:33 UTC (permalink / raw)
  To: xu jian <jamesxu@outlook.com>; +Cc: pgsql-admin

xu jian <jamesxu@outlook.com> writes:
>           I was trying to create 2 indexes concurrently(on different table) in same database. I didn't expect the first create index operation would block the other one since they were running on different table.

> However, it is not true. I found one creating index command was blocking the other one.

There are (IIRC) two separate points in CREATE INDEX CONCURRENTLY where it
has to wait for all other transactions to exit.  That would include a
transaction running another CREATE INDEX (CONCURRENTLY), even if it's for
a different table.  I think running two at once would end in a deadlock
failure, since they would likely end up waiting for each other.

Usually, if you're using C.I.C., you're trying to minimize the impact
on the rest of the system, so running two at once seems a bit
counterproductive anyway.

			regards, tom lane


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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2016-12-15 17:33 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-12-14 23:21 blocking issue when create index concurrently xu jian <jamesxu@outlook.com>
2016-12-15 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox