Is it safe to rename an index through pg_class update?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Is it safe to rename an index through pg_class update?

Kouber Saparev-4
Hello everybody,

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;

Are there any risks to corrupt the database or to lose concurrent transactions? I tried to make some tests with parallel queries and locks over the table, but I did not discover anything special.

--
Kouber Saparev
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Kouber Saparev <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Kouber Saparev-4
На чт, 27.02.2020 г. в 17:52 Tom Lane <[hidden email]> написа:
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
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Kouber Saparev <[hidden email]> writes:
> На чт, 27.02.2020 г. в 17:52 Tom Lane <[hidden email]> написа:
>> 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


Reply | Threaded
Open this post in threaded view
|

pg_upgrade custom table locations. Move table locations during upgrade?

Tory M Blue
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
Reply | Threaded
Open this post in threaded view
|

Re: pg_upgrade custom table locations. Move table locations during upgrade?

Tory M Blue

On Thu, Feb 27, 2020 at 12:40 PM Tory M Blue <[hidden email]> wrote:
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
Reply | Threaded
Open this post in threaded view
|

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

Andres Freund
In reply to this post by Tom Lane-2
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


Reply | Threaded
Open this post in threaded view
|

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

Kouber Saparev-4
На пт, 6.03.2020 г. в 21:00 Andres Freund <[hidden email]> написа:
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
Reply | Threaded
Open this post in threaded view
|

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

Andres Freund
Hi,

On 2020-03-09 17:47:23 +0200, Kouber Saparev wrote:

> На пт, 6.03.2020 г. в 21:00 Andres Freund <[hidden email]> написа:
> > 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


Reply | Threaded
Open this post in threaded view
|

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

Kouber Saparev-4
На пн, 9.03.2020 г. в 20:34 Andres Freund <[hidden email]> написа:
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
Reply | Threaded
Open this post in threaded view
|

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

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