Re: should CREATE INDEX ON partitioned_table call PreventInTransactionBlock() ?

On 2020-Jun-08, Justin Pryzby wrote:

> This blocks writes to all partitions until commit:
>
> postgres=# begin; CREATE INDEX ON pt(i);
> BEGIN
> CREATE INDEX
>
> Compare with CLUSTER rel1, rel2, ..., and REINDEX {SCHEMA|DATABASE|SYSTEM},
> which release their locks as soon as each rel is processed.

Well, that would also require that transactions are committed and
started for each partition. Merely adding PreventInTransactionBlock
would not do that. Moreover, since this would break DDL-in-transactions
that would otherwise work, it should be optional and thus need a keyword
in the command. But CONCURRENTLY isn't it (because that means something
else) so we'd have to discuss what it would be.

> I noticed while implementing REINDEX for partitioned tables, which, it seems
> clear, should also avoid slowly accumulating more and more write locks across
> an entire partition heirarchy.

Right.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services