Thread: autovacuum on pg_catalog tables
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
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 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 <vijaykumarjain.github@gmail.com>
Gesendet: Freitag, 4. Juni 2021 17:37
An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
Cc: pgsql-general@lists.postgresql.org
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) <Markus.Zwettler@zuerich.ch> 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.
"Zwettler Markus (OIZ)" <Markus.Zwettler@zuerich.ch> 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
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