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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Kouber Saparev <kouber(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Is it safe to rename an index through pg_class update?
Date: 2020-02-27 20:14:05
Message-ID: 13301.1582834445@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Kouber Saparev <kouber(at)gmail(dot)com> writes:
> На чт, 27.02.2020 г. в 17:52 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tory M Blue 2020-02-27 20:40:20 pg_upgrade custom table locations. Move table locations during upgrade?
Previous Message Tom Lane 2020-02-27 20:07:29 Re: trouble making PG use my Perl