На пн, 9.03.2020 г. в 20:34 Andres Freund <andres@xxxxxxxxxxx> написа:
You'd not have to take an ACCESS EXCLUSIVE. A lower level would
suffice, e.g. SHARE UPDATE EXCLUSIVE, which still allows data changes.
> There is nobody else doing DDLs except me - Mr. DBA, so I guess I am
> safe on this side. ;)
If autovacuum triggered a vacuum/analyze it'd would e.g. also try to
update pg_class.
I can so to say then use the strategy behind Peter Eisentraut's patch (reduce index rename locks) applied in version 12 in my case (9.3) manually.
As far as I can see (and understand the source code), only the table holding the index is locked (and not pg_class).
db=# begin;
BEGIN
db=*# lock table x in share update exclusive mode;
LOCK TABLE
db=*# update pg_class set relname = 'y_idx' where oid = 'x_idx'::regclass;
UPDATE 1
db=*# commit;
COMMIT
BEGIN
db=*# lock table x in share update exclusive mode;
LOCK TABLE
db=*# update pg_class set relname = 'y_idx' where oid = 'x_idx'::regclass;
UPDATE 1
db=*# commit;
COMMIT
It looks good. The only exceptional case I am able to discover is when the index is used within a constraint, in which case I should also update pg_constraint.
Thank's again for the accurate responses.
Regards,
--
Kouber Saparev
