Обсуждение: should CREATE INDEX ON partitioned_table callPreventInTransactionBlock() ?

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.

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.

-- 
Justin

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

On Mon, Jun 08, 2020 at 11:27:26AM -0400, Alvaro Herrera wrote:
> 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.

(Correcting myself, I guess I mean "CLUSTER;" - it doesn't accept multiple
relation arguments.)

> 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 wasn't thinking of a new feature but rather if it would be desirable to
change behavior for v14 to always start/commit transaction for each partition.

-- 
Justin

On 2020-Jun-08, Justin Pryzby wrote:

> On Mon, Jun 08, 2020 at 11:27:26AM -0400, Alvaro Herrera wrote:

> > 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 wasn't thinking of a new feature but rather if it would be desirable to
> change behavior for v14 to always start/commit transaction for each partition.

Well, I was saying that I don't think a blanket behavior change is
desirable.  For example, if you have a script that creates a partitioned
table and a few partitions and a few indexes, and it does all that in a
transaction, it'll break.

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