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

На чт, 27.02.2020 г. в 17:52 Tom Lane <tgl@sss.pgh.pa.us> написа:

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.

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?

Thanks and caffeine regards,
--
Kouber Saparev

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

Many decades ago (small fib).

There was a write up of someone stopping the postgres upgrade at a certain
point, editing a file with the table locations and then restarting the
upgrade process.

"Now, what are you trying to do?"

I have version specific directories (good for you), but...

/pgsql/9.5/tablespaces

I am updating to 12 and thus

/pgsql/12/tablespaces (is where I would like them).

Using the -link, it simples creates the PG12, files under
/pgsql/9.5/tablespace

I'd like to , even when using link, to say don't do that, but instead
please link the Ver 12 tables to /pgsql/12/tablespaces.

The information is in the file

pg_upgrade_dump_globals.sql:CREATE TABLESPACE "torquespace" OWNER "cls"
LOCATION '/pgsql/9.5/torque';

but while it's there, I'd like it not to be there (and it's possible that
I'm running into an initial design flaw and the table spaces should really
be under /pgsql and not /pgsql/$VERSION/

However is there a way to say create the new links under /pgsql/12/ vs
/pgsql/9.5/ using the pg_upgrade process?

Thanks
Tory

Finally found it

http://bajis-postgres.blogspot.com/2014/04/anyone-wants-to-change-tablespaces.html

Thanks
Tory

On Thu, Feb 27, 2020 at 12:40 PM Tory M Blue <tmblue@gmail.com> wrote:

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

На пт, 6.03.2020 г. в 21:00 Andres Freund <andres@anarazel.de> написа:

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.

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. There is nobody else doing DDLs
except me - Mr. DBA, so I guess I am safe on this side. ;)

Cheers,
--
Kouber Saparev

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

На пн, 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
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

Just in case somebody else also needs such a functionality in PostgreSQL <
12, I made a function in plpgsql:

https://github.com/kouber/pg_utils/blob/master/rename_index.sql

--
Kouber Saparev