Call multiple REINDEX CONCURRENTLY commands

Question

I detected several indexes that need reindexing, then I created a simple script to call REINDEX CONCURRENTLY for them

reindex index concurrently public."chp_id2015";
reindex index concurrently public."chp_tm2015";
reindex index concurrently public."chp_or2015";

When I call the script from pgadmin4 I get

ERROR:  REINDEX CONCURRENTLY cannot run inside a transaction block 
SQL state: 25001

It might be some option from pgadmin (by default the session has AUTOCOMMIT ON), but I tried also with dbeaver and the results are similar in that case:

SQL Error [25001]: ERROR: REINDEX CONCURRENTLY cannot be executed within a pipeline

On the command line there's the reindex command (info), but since it can't read from a list of indexes you have to use a syntax like:

reindexdb -d dbname -i index1 -i index2 ...

Send these SQL statements individually to the database, not as a single group of statements. I think pgAdmin wraps this group into a single transaction — Frank Heikens
– Frank Heikens, Commented Feb 19 at 16:00
That's what I am trying to do, but I would like to run this process from pgadmin4, or any other GUI preferably — aseques
– aseques, Commented Feb 20 at 7:39
Some frameworks had (the same problem)[github.com/liquibase/liquibase/issues/3806] that is linked with the pipeline mode introduced to PostgreSQL in 2023. However a quick reading does not seem to show any solution using an SQL statement, only settings from the DB browsing application. — Guillaume Outters
– Guillaume Outters, Commented Feb 20 at 8:15
@GuillaumeOutters The Pipeline Mode was introduced in libpq for version 14 back in 2021. It's up to the application to use it or not. The client psql can read from a file and execute each query in this file one after the other, including REINDEX CONCURRENTLY. See the --file= option postgresql.org/docs/current/app-psql.html — Frank Heikens
– Frank Heikens, Commented Feb 20 at 15:03

aseques · Accepted Answer · 2025-02-20 15:42:53Z

0

Thanks to all of the answers, at the end it seems that currently there's no way to disable the transactions + pipe-lining in the typical database clients (pgadmin4, dbevaver), so I resorted to use the command line for postgres reindex.

I converted the reindex queries to this:

reindexdb -v --concurrently -d alx -i public."l2016mgix"
reindexdb -v --concurrently -d alx -i public."l2017dhix"

With the added benefit that with the -v, I get a small duration report after each reindex has been done.

answered Feb 20 at 15:42

aseques

6701 gold badge6 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Call multiple REINDEX CONCURRENTLY commands

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related