Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbf7233e

Browse files
committed
Propagate ALTER TABLE ... SET STORAGE to indexes
When creating a new index, the attstorage setting of the table columnis copied to regular (non-expression) index columns. But a laterALTER TABLE ... SET STORAGE is not propagated to indexes, thuscreating an inconsistent and undumpable state.Discussion:https://www.postgresql.org/message-id/flat/9765d72b-37c0-06f5-e349-2a580aafd989%402ndquadrant.com
1 parentfc3461c commitbf7233e

File tree

7 files changed

+86
-4
lines changed

7 files changed

+86
-4
lines changed

‎contrib/test_decoding/expected/toast.out

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -305,6 +305,10 @@ ALTER TABLE toasted_several REPLICA IDENTITY FULL;
305305
ALTER TABLE toasted_several ALTER COLUMN toasted_key SET STORAGE EXTERNAL;
306306
ALTER TABLE toasted_several ALTER COLUMN toasted_col1 SET STORAGE EXTERNAL;
307307
ALTER TABLE toasted_several ALTER COLUMN toasted_col2 SET STORAGE EXTERNAL;
308+
-- Change the storage of the index back to EXTENDED, separately from
309+
-- the table. This is currently not doable via DDL, but it is
310+
-- supported internally.
311+
UPDATE pg_attribute SET attstorage = 'x' WHERE attrelid = 'toasted_several_pkey'::regclass AND attname = 'toasted_key';
308312
INSERT INTO toasted_several(toasted_key) VALUES(repeat('9876543210', 10000));
309313
SELECT pg_column_size(toasted_key) > 2^16 FROM toasted_several;
310314
?column?

‎contrib/test_decoding/sql/toast.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -279,6 +279,11 @@ ALTER TABLE toasted_several ALTER COLUMN toasted_key SET STORAGE EXTERNAL;
279279
ALTERTABLE toasted_several ALTER COLUMN toasted_col1SET STORAGE EXTERNAL;
280280
ALTERTABLE toasted_several ALTER COLUMN toasted_col2SET STORAGE EXTERNAL;
281281

282+
-- Change the storage of the index back to EXTENDED, separately from
283+
-- the table. This is currently not doable via DDL, but it is
284+
-- supported internally.
285+
UPDATE pg_attributeSET attstorage='x'WHERE attrelid='toasted_several_pkey'::regclassAND attname='toasted_key';
286+
282287
INSERT INTO toasted_several(toasted_key)VALUES(repeat('9876543210',10000));
283288
SELECT pg_column_size(toasted_key)>2^16FROM toasted_several;
284289

‎src/backend/commands/tablecmds.c

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6973,6 +6973,7 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
69736973
Form_pg_attribute attrtuple;
69746974
AttrNumberattnum;
69756975
ObjectAddress address;
6976+
ListCell *lc;
69766977

69776978
Assert(IsA(newValue, String));
69786979
storagemode = strVal(newValue);
@@ -7032,6 +7033,52 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
70327033

70337034
heap_freetuple(tuple);
70347035

7036+
/*
7037+
* Apply the change to indexes as well (only for simple index columns,
7038+
* matching behavior of index.c ConstructTupleDescriptor()).
7039+
*/
7040+
foreach(lc, RelationGetIndexList(rel))
7041+
{
7042+
Oid indexoid = lfirst_oid(lc);
7043+
Relation indrel;
7044+
AttrNumberindattnum = 0;
7045+
7046+
indrel = index_open(indexoid, lockmode);
7047+
7048+
for (int i = 0; i < indrel->rd_index->indnatts; i++)
7049+
{
7050+
if (indrel->rd_index->indkey.values[i] == attnum)
7051+
{
7052+
indattnum = i + 1;
7053+
break;
7054+
}
7055+
}
7056+
7057+
if (indattnum == 0)
7058+
{
7059+
index_close(indrel, lockmode);
7060+
continue;
7061+
}
7062+
7063+
tuple = SearchSysCacheCopyAttNum(RelationGetRelid(indrel), indattnum);
7064+
7065+
if (HeapTupleIsValid(tuple))
7066+
{
7067+
attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
7068+
attrtuple->attstorage = newstorage;
7069+
7070+
CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
7071+
7072+
InvokeObjectPostAlterHook(RelationRelationId,
7073+
RelationGetRelid(rel),
7074+
attrtuple->attnum);
7075+
7076+
heap_freetuple(tuple);
7077+
}
7078+
7079+
index_close(indrel, lockmode);
7080+
}
7081+
70357082
table_close(attrelation, RowExclusiveLock);
70367083

70377084
ObjectAddressSubSet(address, RelationRelationId,

‎src/test/regress/expected/alter_table.out

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2164,6 +2164,26 @@ where oid = 'test_storage'::regclass;
21642164
t
21652165
(1 row)
21662166

2167+
-- test that SET STORAGE propagates to index correctly
2168+
create index test_storage_idx on test_storage (b, a);
2169+
alter table test_storage alter column a set storage external;
2170+
\d+ test_storage
2171+
Table "public.test_storage"
2172+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2173+
--------+---------+-----------+----------+---------+----------+--------------+-------------
2174+
a | text | | | | external | |
2175+
b | integer | | | 0 | plain | |
2176+
Indexes:
2177+
"test_storage_idx" btree (b, a)
2178+
2179+
\d+ test_storage_idx
2180+
Index "public.test_storage_idx"
2181+
Column | Type | Key? | Definition | Storage | Stats target
2182+
--------+---------+------+------------+----------+--------------
2183+
b | integer | yes | b | plain |
2184+
a | text | yes | a | external |
2185+
btree, for table "public.test_storage"
2186+
21672187
-- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
21682188
CREATE TABLE test_inh_check (a float check (a > 10.2), b float);
21692189
CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);

‎src/test/regress/expected/vacuum.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -98,7 +98,7 @@ CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT);
9898
CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t);
9999
ALTER TABLE no_index_cleanup ALTER COLUMN t SET STORAGE EXTERNAL;
100100
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(1,30),
101-
repeat('1234567890',300));
101+
repeat('1234567890',269));
102102
-- index cleanup option is ignored if VACUUM FULL
103103
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
104104
VACUUM (FULL TRUE) no_index_cleanup;
@@ -112,7 +112,7 @@ ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
112112
VACUUM no_index_cleanup;
113113
-- Parameter is set for both the parent table and its toast relation.
114114
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
115-
repeat('1234567890',300));
115+
repeat('1234567890',269));
116116
DELETE FROM no_index_cleanup WHERE i < 45;
117117
-- Only toast index is cleaned up.
118118
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false,

‎src/test/regress/sql/alter_table.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1463,6 +1463,12 @@ select reltoastrelid <> 0 as has_toast_table
14631463
from pg_class
14641464
whereoid='test_storage'::regclass;
14651465

1466+
-- test that SET STORAGE propagates to index correctly
1467+
createindextest_storage_idxon test_storage (b, a);
1468+
altertable test_storage alter column aset storage external;
1469+
\d+ test_storage
1470+
\d+ test_storage_idx
1471+
14661472
-- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
14671473
CREATETABLEtest_inh_check (a floatcheck (a>10.2), b float);
14681474
CREATETABLEtest_inh_check_child() INHERITS(test_inh_check);

‎src/test/regress/sql/vacuum.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -81,7 +81,7 @@ CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT);
8181
CREATEINDEXno_index_cleanup_idxON no_index_cleanup(t);
8282
ALTERTABLE no_index_cleanup ALTER COLUMN tSET STORAGE EXTERNAL;
8383
INSERT INTO no_index_cleanup(i, t)VALUES (generate_series(1,30),
84-
repeat('1234567890',300));
84+
repeat('1234567890',269));
8585
-- index cleanup option is ignored if VACUUM FULL
8686
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
8787
VACUUM (FULL TRUE) no_index_cleanup;
@@ -95,7 +95,7 @@ ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
9595
VACUUM no_index_cleanup;
9696
-- Parameter is set for both the parent table and its toast relation.
9797
INSERT INTO no_index_cleanup(i, t)VALUES (generate_series(31,60),
98-
repeat('1234567890',300));
98+
repeat('1234567890',269));
9999
DELETEFROM no_index_cleanupWHERE i<45;
100100
-- Only toast index is cleaned up.
101101
ALTERTABLE no_index_cleanupSET (vacuum_index_cleanup= false,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp