autovacuum on pg_catalog tables

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

autovacuum on pg_catalog tables

Zwettler Markus (OIZ)

I would like to start a more aggressive autovacuum on pg_catalog tables like pg_largeobject.

 

So I tried as a superuser:

 

# alter table pg_catalog.pg_largeobject_metadata set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 5000);

ERROR:  permission denied: "pg_largeobject_metadata" is a system catalog

 

(How) Is it possible to change such table attributes on pg_catalog tables?

 

Thanks, Markus

 

 

Reply | Threaded
Open this post in threaded view
|

Re: autovacuum on pg_catalog tables

Vijaykumar Jain-2
ok, what i am sharing, DO NOT DO IT.
it is just to answer why it is not working  :)


postgres=# alter table pg_catalog.pg_largeobject_metadata set (AUTOVACUUM_VACUUM_COST_DELAY = 1);
ERROR:  permission denied: "pg_largeobject_metadata" is a system catalog
postgres=# show allow_system_table_mods;
 allow_system_table_mods
-------------------------
 off
(1 row)

postgres=# set allow_system_table_mods TO 1;
SET
postgres=# show allow_system_table_mods;
 allow_system_table_mods
-------------------------
 on
(1 row)

postgres=# alter table pg_catalog.pg_largeobject_metadata set (AUTOVACUUM_VACUUM_COST_DELAY = 1);
ALTER TABLE


but you can always run vacuum manually on the table.
vacuum (verbose,analyze) pg_catalog.pg_largeobject_metadata;
INFO:  vacuuming "pg_catalog.pg_largeobject_metadata"
INFO:  index "pg_largeobject_metadata_oid_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_largeobject_metadata": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 8083775
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  analyzing "pg_catalog.pg_largeobject_metadata"
INFO:  "pg_largeobject_metadata": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM



On Fri, 4 Jun 2021 at 20:10, Zwettler Markus (OIZ) <[hidden email]> wrote:

I would like to start a more aggressive autovacuum on pg_catalog tables like pg_largeobject.

 

So I tried as a superuser:

 

# alter table pg_catalog.pg_largeobject_metadata set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 5000);

ERROR:  permission denied: "pg_largeobject_metadata" is a system catalog

 

(How) Is it possible to change such table attributes on pg_catalog tables?

 

Thanks, Markus

 

 



--
Thanks,
Vijay
Mumbai, India
Reply | Threaded
Open this post in threaded view
|

AW: [Extern] Re: autovacuum on pg_catalog tables

Zwettler Markus (OIZ)
Email Signature

Thanks for the info.

 

I have a lot of LO manipulation and want a more aggressive autovacuum on some pg_catalog tables therefore.

 

I do not see any reason why this should not work or be at risk?

 

Markus

 

 

 

Von: Vijaykumar Jain <[hidden email]>
Gesendet: Freitag, 4. Juni 2021 17:37
An: Zwettler Markus (OIZ) <[hidden email]>
Cc: [hidden email]
Betreff: [Extern] Re: autovacuum on pg_catalog tables

 

ok, what i am sharing, DO NOT DO IT.

it is just to answer why it is not working  :)

 

 

postgres=# alter table pg_catalog.pg_largeobject_metadata set (AUTOVACUUM_VACUUM_COST_DELAY = 1);

ERROR:  permission denied: "pg_largeobject_metadata" is a system catalog

postgres=# show allow_system_table_mods;

 allow_system_table_mods

-------------------------

 off

(1 row)

 

postgres=# set allow_system_table_mods TO 1;

SET

postgres=# show allow_system_table_mods;

 allow_system_table_mods

-------------------------

 on

(1 row)

 

postgres=# alter table pg_catalog.pg_largeobject_metadata set (AUTOVACUUM_VACUUM_COST_DELAY = 1);

ALTER TABLE

 

 

but you can always run vacuum manually on the table.

vacuum (verbose,analyze) pg_catalog.pg_largeobject_metadata;

INFO:  vacuuming "pg_catalog.pg_largeobject_metadata"

INFO:  index "pg_largeobject_metadata_oid_index" now contains 0 row versions in 1 pages

DETAIL:  0 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  "pg_largeobject_metadata": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 8083775

There were 0 unused item identifiers.

Skipped 0 pages due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  analyzing "pg_catalog.pg_largeobject_metadata"

INFO:  "pg_largeobject_metadata": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

VACUUM

 

 

 

On Fri, 4 Jun 2021 at 20:10, Zwettler Markus (OIZ) <[hidden email]> wrote:

I would like to start a more aggressive autovacuum on pg_catalog tables like pg_largeobject.

 

So I tried as a superuser:

 

# alter table pg_catalog.pg_largeobject_metadata set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 5000);

ERROR:  permission denied: "pg_largeobject_metadata" is a system catalog

 

(How) Is it possible to change such table attributes on pg_catalog tables?

 

Thanks, Markus

 

 


 

--

Thanks,

Vijay

Mumbai, India


Achtung: Diese E-Mail wurde von einer externen Adresse verschickt. Klicken Sie auf keine Links und öffnen Sie keine angehängten Dateien, wenn Sie den Absender bzw. die Absenderin nicht kennen. Sind Sie sich unsicher, kontaktieren Sie den Service Desk der Stadt Zürich.

Reply | Threaded
Open this post in threaded view
|

Re: [Extern] Re: autovacuum on pg_catalog tables

Michael Lewis
Why not change the defaults? How many tables would hit this new threshold and you would NOT want autovacuum to process them?
Reply | Threaded
Open this post in threaded view
|

Re: AW: [Extern] Re: autovacuum on pg_catalog tables

Tom Lane-2
In reply to this post by Zwettler Markus (OIZ)
"Zwettler Markus (OIZ)" <[hidden email]> writes:
> I do not see any reason why this should not work or be at risk?

I think the only problem you'd be likely to run into is that
pg_dump/pg_upgrade won't propagate those settings for you.
autovacuum doesn't really treat catalogs differently from user
tables, AFAIR.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: AW: [Extern] Re: autovacuum on pg_catalog tables

Laurenz Albe
In reply to this post by Zwettler Markus (OIZ)
On Fri, 2021-06-04 at 15:43 +0000, Zwettler Markus (OIZ) wrote:
> I have a lot of LO manipulation and want a more aggressive autovacuum on some pg_catalog tables therefore.
>
> I do not see any reason why this should not work or be at risk?

It is not a risk per se.  The biggest problem is that any such catalog
modifications would be lost after an upgrade.

I would change the parameters in "postgresql.conf" and then override the
settings for user table to be less aggressive where necessary.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

AW: AW: [Extern] Re: autovacuum on pg_catalog tables

Zwettler Markus (OIZ)
In reply to this post by Zwettler Markus (OIZ)
Email Signature

I have some hundred (user) tables where only a small subset of them requires a more aggressive autovacuum.

Setting table level parameters on this small subset seems more straightforward to me.

Loosing this setting on pg_dump / pg_upgrade would be ok for me. Doesn't happen that often.

 

 

 

I tried this on postgres 9.6:

 

postgresql.conf -> allow_system_table_mods=on   +  pg_ctl restart

 

# alter table pg_catalog.pg_shdepend set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000);

ERROR:  shared tables cannot be toasted after initdb

Time: 0.618 ms

# alter table pg_catalog.pg_largeobject set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000);

ERROR:  AccessExclusiveLock required to add toast table.

Time: 0.590 ms

# alter table pg_catalog.pg_largeobject_metadata set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000);

ERROR:  AccessExclusiveLock required to add toast table.

Time: 1.601 ms

 

 

Any idea?

 

Thanks, Markus

 

 

 

 

Von: Zwettler Markus (OIZ) <[hidden email]>
Gesendet: Freitag, 4. Juni 2021 17:44
An: Vijaykumar Jain <[hidden email]>
Cc: [hidden email]
Betreff: AW: [Extern] Re: autovacuum on pg_catalog tables

 

Thanks for the info.

 

I have a lot of LO manipulation and want a more aggressive autovacuum on some pg_catalog tables therefore.

 

I do not see any reason why this should not work or be at risk?

 

Markus

 

 

 

Von: Vijaykumar Jain <[hidden email]>
Gesendet: Freitag, 4. Juni 2021 17:37
An: Zwettler Markus (OIZ) <[hidden email]>
Cc: [hidden email]
Betreff: [Extern] Re: autovacuum on pg_catalog tables

 

ok, what i am sharing, DO NOT DO IT.

it is just to answer why it is not working  :)

 

 

postgres=# alter table pg_catalog.pg_largeobject_metadata set (AUTOVACUUM_VACUUM_COST_DELAY = 1);

ERROR:  permission denied: "pg_largeobject_metadata" is a system catalog

postgres=# show allow_system_table_mods;

 allow_system_table_mods

-------------------------

 off

(1 row)

 

postgres=# set allow_system_table_mods TO 1;

SET

postgres=# show allow_system_table_mods;

 allow_system_table_mods

-------------------------

 on

(1 row)

 

postgres=# alter table pg_catalog.pg_largeobject_metadata set (AUTOVACUUM_VACUUM_COST_DELAY = 1);

ALTER TABLE

 

 

but you can always run vacuum manually on the table.

vacuum (verbose,analyze) pg_catalog.pg_largeobject_metadata;

INFO:  vacuuming "pg_catalog.pg_largeobject_metadata"

INFO:  index "pg_largeobject_metadata_oid_index" now contains 0 row versions in 1 pages

DETAIL:  0 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  "pg_largeobject_metadata": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 8083775

There were 0 unused item identifiers.

Skipped 0 pages due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  analyzing "pg_catalog.pg_largeobject_metadata"

INFO:  "pg_largeobject_metadata": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

VACUUM

 

 

 

On Fri, 4 Jun 2021 at 20:10, Zwettler Markus (OIZ) <[hidden email]> wrote:

I would like to start a more aggressive autovacuum on pg_catalog tables like pg_largeobject.

 

So I tried as a superuser:

 

# alter table pg_catalog.pg_largeobject_metadata set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 5000);

ERROR:  permission denied: "pg_largeobject_metadata" is a system catalog

 

(How) Is it possible to change such table attributes on pg_catalog tables?

 

Thanks, Markus

 

 


 

--

Thanks,

Vijay

Mumbai, India


Achtung: Diese E-Mail wurde von einer externen Adresse verschickt. Klicken Sie auf keine Links und öffnen Sie keine angehängten Dateien, wenn Sie den Absender bzw. die Absenderin nicht kennen. Sind Sie sich unsicher, kontaktieren Sie den Service Desk der Stadt Zürich.

Reply | Threaded
Open this post in threaded view
|

Re: AW: AW: [Extern] Re: autovacuum on pg_catalog tables

Tom Lane-2
"Zwettler Markus (OIZ)" <[hidden email]> writes:
> I tried this on postgres 9.6:

> postgresql.conf -> allow_system_table_mods=on   +  pg_ctl restart

> # alter table pg_catalog.pg_shdepend set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000);
> ERROR:  shared tables cannot be toasted after initdb
> Time: 0.618 ms
> # alter table pg_catalog.pg_largeobject set (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000);
> ERROR:  AccessExclusiveLock required to add toast table.
> Time: 0.590 ms

Interesting.  FWIW, these examples do seem to work in v12 and up.
Since we don't consider this a supported case, I doubt anyone would
be interested in trying to back-port whatever rearrangement allowed
it to work.

                        regards, tom lane