From: | Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com> |
---|---|
To: | "Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: autovacuum on pg_catalog tables |
Date: | 2021-06-04 15:37:03 |
Message-ID: | CAM+6J95q0srfnjNv62Ka=32eFXFCOmJA50Uvw4KzodWVS+2y8g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
ok, what i am sharing, *DO NOT DO IT.*
it is just to answer why it is not working :)
PostgreSQL: Documentation: 13: 19.17. Developer Options
<https://www.postgresql.org/docs/current/runtime-config-developer.html>
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(dot)Zwettler(at)zuerich(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Zwettler Markus (OIZ) | 2021-06-04 15:43:45 | AW: [Extern] Re: autovacuum on pg_catalog tables |
Previous Message | Laura Smith | 2021-06-04 15:33:50 | Re: EXCLUDE USING and tstzrange |