pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate index concurrently blocked by other query
4+ messages / 4 participants
[nested] [flat]
* create index concurrently blocked by other query
@ 2024-03-23 13:29 James Pang <jamespang886@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: James Pang @ 2024-03-23 13:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
PGv14, it's a time range partition table, we first create index ... on
ONLY parent table and it's done very fast, then we are doing "create index
concurrently ... on each partition table", then we saw it blocked by some
other sessopm "select on other tables" , is it expected ?
from pg_locks, and pg_stat_activity :
for this relation we are creating index concurrently, only "access share"
lock by other sessions:
blocked pid 512694:
blocked query： CREATE INDEX CONCURRENTLY IF NOT EXISTS
IDX_XMEETINGMETADATA_P202401_xxxID ON xxxxxx_P202401(xxID,SssID); |
blocking pid 90252
blocking query: it's a select from other tables not the one we are creating
index concurrently..
SELECT m.Meetingxxx mxxxKey, m.MTGxxxx....,
+
| | | |
Thanks,
James
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: create index concurrently blocked by other query
@ 2024-03-23 16:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: James Pang <jamespang886@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2024-03-23 16:37 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
James Pang <jamespang886@gmail.com> writes:
> PGv14, it's a time range partition table, we first create index ... on
> ONLY parent table and it's done very fast, then we are doing "create index
> concurrently ... on each partition table", then we saw it blocked by some
> other sessopm "select on other tables" , is it expected ?
Yes, read the documentation. CONCURRENTLY avoids blocking other
transactions, at the cost of being easily blocked itself.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: create index concurrently blocked by other query
@ 2024-03-25 09:35 SOzcn <selahattinozcnma@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: SOzcn @ 2024-03-25 09:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org
You can manage the index maintenance on the OS with Crontab if you want to
make it concurrently.
If you only need index maintenance you can schedule it with pg_cron.
The only way to do this is to write a bash script in Linux and insert it
with the loop. You can text the bash script for that.
Tom Lane <tgl@sss.pgh.pa.us>, 23 Mar 2024 Cmt, 19:37 tarihinde şunu yazdı:
> James Pang <jamespang886@gmail.com> writes:
> > PGv14, it's a time range partition table, we first create index ...
> on
> > ONLY parent table and it's done very fast, then we are doing "create
> index
> > concurrently ... on each partition table", then we saw it blocked by
> some
> > other sessopm "select on other tables" , is it expected ?
>
> Yes, read the documentation. CONCURRENTLY avoids blocking other
> transactions, at the cost of being easily blocked itself.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: create index concurrently blocked by other query
@ 2024-03-25 12:07 hubert depesz lubaczewski <depesz@depesz.com>
parent: James Pang <jamespang886@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: hubert depesz lubaczewski @ 2024-03-25 12:07 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Sat, Mar 23, 2024 at 09:29:55PM +0800, James Pang wrote:
> Hi,
> PGv14, it's a time range partition table, we first create index ... on
> ONLY parent table and it's done very fast, then we are doing "create index
> concurrently ... on each partition table", then we saw it blocked by some
> other sessopm "select on other tables" , is it expected ?
Yes. create index will continue its work as soon as all transactions
that are started than itself (the create index transaction) will end.
Best regards,
depesz
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-03-25 12:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-23 13:29 create index concurrently blocked by other query James Pang <jamespang886@gmail.com>
2024-03-23 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-25 09:35 ` SOzcn <selahattinozcnma@gmail.com>
2024-03-25 12:07 ` hubert depesz lubaczewski <depesz@depesz.com>
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