Re: Is it safe to rename an index through pg_class update?
| От | Kouber Saparev |
|---|---|
| Тема | Re: Is it safe to rename an index through pg_class update? |
| Дата | |
| Msg-id | CAN4RuQvm3gsErn-xWsoaO+B1eL_byUW=zdXjWU4SPwOoX0_kHQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Is it safe to rename an index through pg_class update? (Andres Freund <andres@anarazel.de>) |
| Ответы |
Re: Is it safe to rename an index through pg_class update?
|
| Список | pgsql-general |
На пн, 9.03.2020 г. в 20:34 Andres Freund <andres@anarazel.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
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
В списке pgsql-general по дате отправления: