Thread: 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
Re: should CREATE INDEX ON partitioned_table callPreventInTransactionBlock() ?
From
Alvaro Herrera
Date:
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
Re: should CREATE INDEX ON partitioned_table callPreventInTransactionBlock() ?
From
Justin Pryzby
Date:
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
Re: should CREATE INDEX ON partitioned_table callPreventInTransactionBlock() ?
From
Alvaro Herrera
Date:
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