autovacuum on pg_catalog tables
Locked
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
|
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) <
> wrote:
Thanks,
Vijay
Mumbai, India
|
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 <
>
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) < > wrote:
-- Thanks, Vijay Mumbai, India
|
Re: [Extern] Re: autovacuum on pg_catalog tables
|
|
Why not change the defaults? How many tables would hit this new threshold and you would NOT want autovacuum to process them?
|
Re: AW: [Extern] Re: autovacuum on pg_catalog tables
|
|
In reply to
this post
by Zwettler Markus (OIZ)
"Zwettler Markus (OIZ)" <
> 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 |
Re: AW: [Extern] Re: autovacuum on pg_catalog tables
|
|
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 |
AW: AW: [Extern] Re: autovacuum on pg_catalog tables
|
|
In reply to
this post
by Zwettler Markus (OIZ)
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) <
>
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 <
>
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) < > wrote:
-- Thanks, Vijay Mumbai, India
|
Re: AW: AW: [Extern] Re: autovacuum on pg_catalog tables
|
|
"Zwettler Markus (OIZ)" <
> 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 |
| Free forum by Nabble | Edit this page |