ALTER TABLE ... SET STORAGE does not propagate to indexes, even though
indexes created afterwards get the new storage setting. So depending on
the order of commands, you can get inconsistent storage settings between
indexes and tables. For example:
create table foo1 (a text);
alter table foo1 alter column a set storage external;
create index foo1i on foo1(a);
insert into foo1 values(repeat('a', 10000));
ERROR: index row requires 10016 bytes, maximum size is 8191
(Storage "external" disables compression.)
but
create table foo1 (a text);
create index foo1i on foo1(a);
alter table foo1 alter column a set storage external;
insert into foo1 values(repeat('a', 10000));
-- no error
Also, this second state cannot be reproduced by pg_dump, so a possible
effect is that such a database would fail to restore.
Attached is a patch that attempts to fix this by propagating the storage
change to existing indexes. This triggers a few regression test
failures (going from no error to error), which I attempted to fix up,
but I haven't analyzed what the tests were trying to do, so it might
need another look.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 85f19d31a2def2e2b98d669daa4bd4a3a2c2c09f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Mon, 6 Jan 2020 11:54:03 +0100
Subject: [PATCH] Propagate ALTER TABLE ... SET STORAGE to indexes
---
contrib/test_decoding/expected/toast.out | 4 +--
contrib/test_decoding/sql/toast.sql | 4 +--
src/backend/commands/tablecmds.c | 33 ++++++++++++++++++++++++
src/test/regress/expected/vacuum.out | 4 +--
src/test/regress/sql/vacuum.sql | 4 +--
5 files changed, 41 insertions(+), 8 deletions(-)
diff --git a/contrib/test_decoding/expected/toast.out
b/contrib/test_decoding/expected/toast.out
index 91a9a1e86d..2baa06f304 100644
--- a/contrib/test_decoding/expected/toast.out
+++ b/contrib/test_decoding/expected/toast.out
@@ -305,8 +305,8 @@ ALTER TABLE toasted_several REPLICA IDENTITY FULL;
ALTER TABLE toasted_several ALTER COLUMN toasted_key SET STORAGE EXTERNAL;
ALTER TABLE toasted_several ALTER COLUMN toasted_col1 SET STORAGE EXTERNAL;
ALTER TABLE toasted_several ALTER COLUMN toasted_col2 SET STORAGE EXTERNAL;
-INSERT INTO toasted_several(toasted_key) VALUES(repeat('9876543210', 10000));
-SELECT pg_column_size(toasted_key) > 2^16 FROM toasted_several;
+INSERT INTO toasted_several(toasted_key) VALUES(repeat('9876543210', 269));
+SELECT pg_column_size(toasted_key) > 2^11 FROM toasted_several;
?column?
----------
t
diff --git a/contrib/test_decoding/sql/toast.sql
b/contrib/test_decoding/sql/toast.sql
index ef11d2bfff..5cf6b87b3a 100644
--- a/contrib/test_decoding/sql/toast.sql
+++ b/contrib/test_decoding/sql/toast.sql
@@ -279,8 +279,8 @@ CREATE TABLE toasted_several (
ALTER TABLE toasted_several ALTER COLUMN toasted_col1 SET STORAGE EXTERNAL;
ALTER TABLE toasted_several ALTER COLUMN toasted_col2 SET STORAGE EXTERNAL;
-INSERT INTO toasted_several(toasted_key) VALUES(repeat('9876543210', 10000));
-SELECT pg_column_size(toasted_key) > 2^16 FROM toasted_several;
+INSERT INTO toasted_several(toasted_key) VALUES(repeat('9876543210', 269));
+SELECT pg_column_size(toasted_key) > 2^11 FROM toasted_several;
SELECT regexp_replace(data, '^(.{100}).*(.{100})$', '\1..\2') FROM
pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-xids',
'0', 'skip-empty-xacts', '1');
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1c4394abea..c4bc058e28 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6900,6 +6900,7 @@ ATExecSetStorage(Relation rel, const char *colName, Node
*newValue, LOCKMODE loc
Form_pg_attribute attrtuple;
AttrNumber attnum;
ObjectAddress address;
+ ListCell *lc;
Assert(IsA(newValue, String));
storagemode = strVal(newValue);
@@ -6959,6 +6960,38 @@ ATExecSetStorage(Relation rel, const char *colName, Node
*newValue, LOCKMODE loc
heap_freetuple(tuple);
+ /*
+ * Apply the change to indexes as well.
+ *
+ * XXX should probably use pg_index.indkey to find the right columns,
not
+ * the column name
+ */
+ foreach(lc, RelationGetIndexList(rel))
+ {
+ Oid indexoid = lfirst_oid(lc);
+ Relation indrel;
+
+ indrel = index_open(indexoid, lockmode);
+
+ tuple = SearchSysCacheCopyAttName(RelationGetRelid(indrel),
colName);
+
+ if (HeapTupleIsValid(tuple))
+ {
+ attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
+ attrtuple->attstorage = newstorage;
+
+ CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+
RelationGetRelid(rel),
+
attrtuple->attnum);
+
+ heap_freetuple(tuple);
+ }
+
+ index_close(indrel, lockmode);
+ }
+
table_close(attrelation, RowExclusiveLock);
ObjectAddressSubSet(address, RelationRelationId,
diff --git a/src/test/regress/expected/vacuum.out
b/src/test/regress/expected/vacuum.out
index 9996d882d1..e2b9cd954d 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -98,7 +98,7 @@ CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT);
CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t);
ALTER TABLE no_index_cleanup ALTER COLUMN t SET STORAGE EXTERNAL;
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(1,30),
- repeat('1234567890',300));
+ repeat('1234567890',269));
-- index cleanup option is ignored if VACUUM FULL
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
VACUUM (FULL TRUE) no_index_cleanup;
@@ -112,7 +112,7 @@ ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup =
true);
VACUUM no_index_cleanup;
-- Parameter is set for both the parent table and its toast relation.
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
- repeat('1234567890',300));
+ repeat('1234567890',269));
DELETE FROM no_index_cleanup WHERE i < 45;
-- Only toast index is cleaned up.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false,
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 69987f75e9..aaacdfe082 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -81,7 +81,7 @@ CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT);
CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t);
ALTER TABLE no_index_cleanup ALTER COLUMN t SET STORAGE EXTERNAL;
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(1,30),
- repeat('1234567890',300));
+ repeat('1234567890',269));
-- index cleanup option is ignored if VACUUM FULL
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
VACUUM (FULL TRUE) no_index_cleanup;
@@ -95,7 +95,7 @@ CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t);
VACUUM no_index_cleanup;
-- Parameter is set for both the parent table and its toast relation.
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
- repeat('1234567890',300));
+ repeat('1234567890',269));
DELETE FROM no_index_cleanup WHERE i < 45;
-- Only toast index is cleaned up.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false,
--
2.24.1
