ALTER TABLE on system catalogs

From: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: ALTER TABLE on system catalogs
Date: 2018-06-27 20:31:30
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

ALTER TABLE on system catalogs is occasionally useful, for example

ALTER TABLE pg_attribute SET (autovacuum_vacuum_scale_factor=0);

However, this doesn't actually work. The above command produces

ERROR: AccessExclusiveLock required to add toast table.

If it's a shared catalog, it will produce

ERROR: shared tables cannot be toasted after initdb

In other cases it will work but then silently add a TOAST table to a
catalog, which I think we don't want.

The problem is that for (almost) any ALTER TABLE command, it afterwards
checks if the just-altered table now needs a TOAST table and tries to
add it if so, which will either fail or add a TOAST table that we don't

I propose that we instead silently ignore attempts to add TOAST tables
to shared and system catalogs after bootstrapping. This fixes the above
issues. I have attached a patch for this, and also a test that
enshrines which system catalogs are supposed to have TOAST tables.

As an alternative, I tried to modify the ALTER TABLE code to avoid the
try-to-add-TOAST-table path depending on what ALTER TABLE actions were
done, but that seemed incredibly more complicated and harder to maintain
in the long run.

(You still need allow_system_table_mods=on for all of this. Perhaps
that's also worth revisiting, but it's a separate issue.)

Peter Eisentraut
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
0001-Add-test-for-system-catalog-TOAST-tables.patch text/plain 1.9 KB
0002-Ignore-attempts-to-add-TOAST-table-to-shared-or-cata.patch text/plain 2.0 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Don Seiler 2018-06-27 20:36:42 Re: Bulk Insert into PostgreSQL
Previous Message Peter Geoghegan 2018-06-27 20:12:19 Re: Allow cancelling VACUUM of nbtrees with corrupted right links