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 |
Message-ID: | e49f825b-fb25-0bc8-8afc-d5ad895c7975@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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
want.
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 http://www.2ndQuadrant.com/
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 |
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 |