pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: cannot create index on partitioned table concurrently
3+ messages / 3 participants
[nested] [flat]
* ERROR: cannot create index on partitioned table concurrently
@ 2024-03-19 08:14 James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: James Pang @ 2024-03-19 08:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
PGv14 , we migrated from Oracle to Postgresql, recently, we migrate
index job from Oracle to PG. in Oracle, it's ok to create index online on
partitioned table, but in Postgresql, ERROR: cannot create index on
partitioned table concurrently. Instead, I go to create index concurrently
on each child table separately.
my question is, from SQL optimizer perspective, any difference between
"create index on partitioned root table" and "create each index on child
table directly", looks like optimizer still can use indexes with directly
created on each child table.
Thanks,
James
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ERROR: cannot create index on partitioned table concurrently
@ 2024-03-19 13:36 Laurenz Albe <laurenz.albe@cybertec.at>
parent: James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2024-03-19 13:36 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org
On Tue, 2024-03-19 at 16:14 +0800, James Pang wrote:
> PGv14 , we migrated from Oracle to Postgresql, recently, we migrate
> index job from Oracle to PG. in Oracle, it's ok to create index online
> on partitioned table, but in Postgresql, ERROR: cannot create index
> on partitioned table concurrently. Instead, I go to create index
> concurrently on each child table separately.
> my question is, from SQL optimizer perspective, any difference
> between "create index on partitioned root table" and "create each
> index on child table directly", looks like optimizer still can use
> indexes with directly created on each child table.
Right.
But it is good to have the partitioned index to make sure that all
partitions have an index.
See https://www.postgresql.org/docs/current/sql-createindex.html
"Concurrent builds for indexes on partitioned tables are currently
not supported. However, you may concurrently build the index on
each partition individually and then finally create the partitioned
index non-concurrently in order to reduce the time where writes to
the partitioned table will be locked out. In this case, building
the partitioned index is a metadata only operation."
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: ERROR: cannot create index on partitioned table concurrently
@ 2024-03-20 01:55 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: James Pang (chaolpan) @ 2024-03-20 01:55 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thanks a lot, it did help.
James
-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Tuesday, March 19, 2024 9:37 PM
To: James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: cannot create index on partitioned table concurrently
On Tue, 2024-03-19 at 16:14 +0800, James Pang wrote:
> PGv14 , we migrated from Oracle to Postgresql, recently, we migrate
> index job from Oracle to PG. in Oracle, it's ok to create index online
> on partitioned table, but in Postgresql, ERROR: cannot create index
> on partitioned table concurrently. Instead, I go to create index
> concurrently on each child table separately.
> my question is, from SQL optimizer perspective, any difference
> between "create index on partitioned root table" and "create each
> index on child table directly", looks like optimizer still can use
> indexes with directly created on each child table.
Right.
But it is good to have the partitioned index to make sure that all partitions have an index.
See https://www.postgresql.org/docs/current/sql-createindex.html
"Concurrent builds for indexes on partitioned tables are currently
not supported. However, you may concurrently build the index on
each partition individually and then finally create the partitioned
index non-concurrently in order to reduce the time where writes to
the partitioned table will be locked out. In this case, building
the partitioned index is a metadata only operation."
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-03-20 01:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-19 08:14 ERROR: cannot create index on partitioned table concurrently James Pang <jamespang886@gmail.com>
2024-03-19 13:36 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-20 01:55 ` James Pang (chaolpan) <chaolpan@cisco.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