dataegret / pg_index_watch Goto Github PK

Add support second optional (!) argument for index_watch.periodic procedure: force BOOLEAN DEFAULT FALSE
and pass thought this value into CALL index_watch.do_reindex(_datname, NULL, NULL, NULL, FALSE); instead of hardcoded FALSE.

This will enable us to easily perform wholesale reindex of a multidatabase cluster.
Especially useful after a major upgrade with index internals changes.

With text:
"
The database version #{insert_version_here} is affected by PostgreSQL bugs which make the use of pg_index_watch potentially unsafe. Please update to the latest minor version. For additional info please see:
https://www.postgresql.org/message-id/[email protected]
and
https://www.postgresql.org/message-id/[email protected]
"

Надо по аналогии как oid обработку для переименовывания индексов добавляли (когда основной id индекса это не имя а oid)
добавить обработку случая переименовывания базы в целом (в pg_database есть колонка oid которая в случае переименования базы не меняется).
Иначе после переименовывания крупной базы - происходит полная совершенно ненужная реиндексаций её.

Add ignore indexes in process of concurrent building (pg_index.indislive=false)

Ignore not valid indexes (pg_index.indisvalid=false) and issue warning for each of such index found with text:
'Not valid index [INDEXNAME] on [SHEMANAME].[TABLENAME] found in [DBNAME].'

Move connect to particular db from dblink calls to the dedicated calls of
https://www.postgresql.org/docs/14/contrib-dblink-connect.html
and
https://www.postgresql.org/docs/14/contrib-dblink-disconnect.html
performed in one place (thus remove four copies of dblink('port='||current_setting('port')||$$ dbname='$$||_datname||$$'$$, code).

PS: this task is prerequisite to start use of https://www.postgresql.org/docs/14/contrib-dblink-connect-u.html and/or supplying user/password in index_watch config (required for managed db such as amazon aurora or RDS).

When I use “create index concurrently”, there can be failures due to various reasons such as lock timeouts, resulting in an invalid index being left behind. If the script for rebuilding indexes fails and leaves behind invalid indexes, is it possible to clean up those invalid indexes before the script execution is completed?

If major version of postgresql 14 and minor version less than 14.4 - decline to work and raise error suggesting upgrade to the latest 14 version because earlier version affected by BUG #17485.

Apply pg_size_pretty to size_before, size_after, tuples columns of index_watch.history view.

see lines 180-184 in index_watch_functions.sql

Good idea to make it future proof without requirement of code change with Postgresql 15 release.

To check server version use
select current_setting('server_version_num')::integer;
current_setting

      140002

syntax