Обсуждение: Is it safe to rename an index through pg_class update?

Kouber Saparev <kouber@gmail.com> writes:
> Renaming an index the classical way through "ALTER INDEX xxx RENAME TO yyy"
> needs an AccessExclusiveLock over the table holding the index (at least on
> 9.3 it does). Instead, couldn't I simply:

> UPDATE pg_class SET relname = 'yyy' WHERE oid = 'xxx'::regclass;

There's a lot of stuff like that that you can probably get away with...
but I'm not sure it's prudent to try it on valuable production data.
If it breaks your database nobody is going to have any sympathy for you.

FWIW, I can't immediately think of a reason this would cause a problem,
at least not on 9.4 and up which use MVCC catalog scans.  If you're
really still on 9.3 then it's notably more risky.  In any case, I've
not had any caffeine yet today, so this doesn't count for much.

            regards, tom lane

Kouber Saparev <kouber@gmail.com> writes:
> На чт, 27.02.2020 г. в 17:52 Tom Lane <tgl@sss.pgh.pa.us> написа:
>> FWIW, I can't immediately think of a reason this would cause a problem,
>> at least not on 9.4 and up which use MVCC catalog scans.  If you're
>> really still on 9.3 then it's notably more risky.  In any case, I've
>> not had any caffeine yet today, so this doesn't count for much.

> Ah, 9.3 is not using MVCC for system catalogs?... Ouch. Then most probably
> it is really not a good idea. That said, I am not modifying table names,
> only index names... and I guess the internals, the planner etc. are not
> working with names, but with oids instead?

The issue is whether a SnapshotNow scan would find any row at all.
If it reaches the new row version before that's committed good, and
the old one after that's committed dead, you'll get some weird
"cache lookup failed" or similar failure --- just transiently, but
nonetheless a failure.  Pre-9.4 versions were dependent on proper
locking to avoid that issue, and what you propose would bypass that.

            regards, tom lane

Hi,

On 2020-02-27 10:52:36 -0500, Tom Lane wrote:
> FWIW, I can't immediately think of a reason this would cause a problem,
> at least not on 9.4 and up which use MVCC catalog scans.  If you're
> really still on 9.3 then it's notably more risky.  In any case, I've
> not had any caffeine yet today, so this doesn't count for much.

It likely could cause some problems if somebody concurrently executed
DDL affecting the same table. At least some "concurrently updated"
errors, and perhaps some worse ones.  I'd at least add an explicit LOCK
TABLE on the underlying table that prevents concurrent catalog
modifications.

Greetings,

Andres Freund

Hi,

On 2020-03-09 17:47:23 +0200, Kouber Saparev wrote:
> На пт, 6.03.2020 г. в 21:00 Andres Freund <andres@anarazel.de> написа:
> > On 2020-02-27 10:52:36 -0500, Tom Lane wrote:
> > > FWIW, I can't immediately think of a reason this would cause a problem,
> > > at least not on 9.4 and up which use MVCC catalog scans.  If you're
> > > really still on 9.3 then it's notably more risky.  In any case, I've
> > > not had any caffeine yet today, so this doesn't count for much.
> >
> > It likely could cause some problems if somebody concurrently executed
> > DDL affecting the same table. At least some "concurrently updated"
> > errors, and perhaps some worse ones.  I'd at least add an explicit LOCK
> > TABLE on the underlying table that prevents concurrent catalog
> > modifications.
> >
> 
> I am trying to escape the Access Exclusive lock over the table indeed,
> otherwise I would use the ALTER statement instead anyway, which makes a
> lock implicitly. Thanks for the responses.

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.

Greetings,

Andres Freund