Re: Is it safe to rename an index through pg_class update?

На пн, 9.03.2020 г. в 20:34 Andres Freund <andres(at)anarazel(dot)de> написа:

> 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
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