What do you do, when you want to do REINDEX online? Build new index concurrently, drop old one and rename new. Only you can't do it for indexes supporting primary keys and other constraints without dropping constraint. So, in any case, you have to REINDEX them. And let everyone to be waiting...
And if you have several hundred indexes, it is not very convenient to do it manually. Also note that CREATE INDEX CONCURRENTLY can't be EXECUTE'd from plpgsql function. I wish we had concurrent reindex in PostgreSQL or at least autonomous transactions to deal with the latter problem... But, we must work with available functionality...
So, the decision for me was to write PL/pgSQL script which will generate necessary SQL commands and feed them to psql.
The script itself is the following:
DO
$$
DECLARE
ind record;
str text;
str_drop text;
str_rename text;
BEGIN
FOR ind IN (SELECT i.oid,i.relname FROM
pg_class r,pg_class i , pg_index ir, pg_namespace ns
WHERE ir.indexrelid=i.oid AND ir.indrelid=r.oid AND ns.oid=r.relnamespace
AND ns.nspname='public' AND NOT ir.indisprimary \
AND i.oid NOT IN
(SELECT conindid FROM pg_constraint)) LOOP
str:=replace(
pg_get_indexdef(ind.oid),
'INDEX '||ind.relname|| ' ON ',
'INDEX CONCURRENTLY '||ind.relname|| '_X999 ON ');
str_drop:='DROP INDEX ' || ind.relname;
str_rename:='ALTER INDEX ' || ind.relname||'_X999 RENAME TO ' ||ind.relname ;
RAISE NOTICE '%', str;
RAISE NOTICE '%', str_drop;
RAISE NOTICE '%', str_rename;
END LOOP;
FOR ind IN (SELECT i.oid,i.relname FROM
pg_class r,pg_class i, pg_index ir, pg_namespace ns
WHERE ir.indexrelid=i.oid AND ir.indrelid=r.oid AND ns.oid=r.relnamespace
AND ns.nspname='public' AND (ir.indisprimary
OR i.oid IN (SELECT conindid FROM pg_constraint)) LOOP
str:='REINDEX INDEX ' || ind.relname;
raise notice '%', str;
end loop;
END;
$$ LANGUAGE PLPGSQL;
Now with something like this:
$ psql -d DBNAME -f generate_reindex.plpgsql" 2>&1| awk -F 'NOTICE:' '{ if (NF==2) {print $2 ";" } ; }' | psql -d DBNAME
we can mostly concurrently rebuild all indexes in database DBNAME. And last thing to remember is to add
\set ON_ERROR_STOP
to your ~/.psqlrc file just to be safe...
