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

Commit501e41d

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 parentf9463d2 commit501e41d

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
@@ -7383,6 +7383,7 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
73837383
Form_pg_attribute attrtuple;
73847384
AttrNumberattnum;
73857385
ObjectAddress address;
7386+
ListCell *lc;
73867387

73877388
Assert(IsA(newValue, String));
73887389
storagemode = strVal(newValue);
@@ -7442,6 +7443,52 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
74427443

74437444
heap_freetuple(tuple);
74447445

7446+
/*
7447+
* Apply the change to indexes as well (only for simple index columns,
7448+
* matching behavior of index.c ConstructTupleDescriptor()).
7449+
*/
7450+
foreach(lc, RelationGetIndexList(rel))
7451+
{
7452+
Oid indexoid = lfirst_oid(lc);
7453+
Relation indrel;
7454+
AttrNumberindattnum = 0;
7455+
7456+
indrel = index_open(indexoid, lockmode);
7457+
7458+
for (int i = 0; i < indrel->rd_index->indnatts; i++)
7459+
{
7460+
if (indrel->rd_index->indkey.values[i] == attnum)
7461+
{
7462+
indattnum = i + 1;
7463+
break;
7464+
}
7465+
}
7466+
7467+
if (indattnum == 0)
7468+
{
7469+
index_close(indrel, lockmode);
7470+
continue;
7471+
}
7472+
7473+
tuple = SearchSysCacheCopyAttNum(RelationGetRelid(indrel), indattnum);
7474+
7475+
if (HeapTupleIsValid(tuple))
7476+
{
7477+
attrtuple = (Form_pg_attribute) GETSTRUCT(tuple);
7478+
attrtuple->attstorage = newstorage;
7479+
7480+
CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
7481+
7482+
InvokeObjectPostAlterHook(RelationRelationId,
7483+
RelationGetRelid(rel),
7484+
attrtuple->attnum);
7485+
7486+
heap_freetuple(tuple);
7487+
}
7488+
7489+
index_close(indrel, lockmode);
7490+
}
7491+
74457492
table_close(attrelation, RowExclusiveLock);
74467493

74477494
ObjectAddressSubSet(address, RelationRelationId,

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2172,6 +2172,26 @@ where oid = 'test_storage'::regclass;
21722172
t
21732173
(1 row)
21742174

2175+
-- test that SET STORAGE propagates to index correctly
2176+
create index test_storage_idx on test_storage (b, a);
2177+
alter table test_storage alter column a set storage external;
2178+
\d+ test_storage
2179+
Table "public.test_storage"
2180+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2181+
--------+---------+-----------+----------+---------+----------+--------------+-------------
2182+
a | text | | | | external | |
2183+
b | integer | | | 0 | plain | |
2184+
Indexes:
2185+
"test_storage_idx" btree (b, a)
2186+
2187+
\d+ test_storage_idx
2188+
Index "public.test_storage_idx"
2189+
Column | Type | Key? | Definition | Storage | Stats target
2190+
--------+---------+------+------------+----------+--------------
2191+
b | integer | yes | b | plain |
2192+
a | text | yes | a | external |
2193+
btree, for table "public.test_storage"
2194+
21752195
-- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
21762196
CREATE TABLE test_inh_check (a float check (a > 10.2), b float);
21772197
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
@@ -134,7 +134,7 @@ CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT);
134134
CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t);
135135
ALTER TABLE no_index_cleanup ALTER COLUMN t SET STORAGE EXTERNAL;
136136
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(1,30),
137-
repeat('1234567890',300));
137+
repeat('1234567890',269));
138138
-- index cleanup option is ignored if VACUUM FULL
139139
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
140140
VACUUM (FULL TRUE) no_index_cleanup;
@@ -148,7 +148,7 @@ ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
148148
VACUUM no_index_cleanup;
149149
-- Parameter is set for both the parent table and its toast relation.
150150
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
151-
repeat('1234567890',300));
151+
repeat('1234567890',269));
152152
DELETE FROM no_index_cleanup WHERE i < 45;
153153
-- Only toast index is cleaned up.
154154
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
@@ -1472,6 +1472,12 @@ select reltoastrelid <> 0 as has_toast_table
14721472
from pg_class
14731473
whereoid='test_storage'::regclass;
14741474

1475+
-- test that SET STORAGE propagates to index correctly
1476+
createindextest_storage_idxon test_storage (b, a);
1477+
altertable test_storage alter column aset storage external;
1478+
\d+ test_storage
1479+
\d+ test_storage_idx
1480+
14751481
-- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
14761482
CREATETABLEtest_inh_check (a floatcheck (a>10.2), b float);
14771483
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
@@ -115,7 +115,7 @@ CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT);
115115
CREATEINDEXno_index_cleanup_idxON no_index_cleanup(t);
116116
ALTERTABLE no_index_cleanup ALTER COLUMN tSET STORAGE EXTERNAL;
117117
INSERT INTO no_index_cleanup(i, t)VALUES (generate_series(1,30),
118-
repeat('1234567890',300));
118+
repeat('1234567890',269));
119119
-- index cleanup option is ignored if VACUUM FULL
120120
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
121121
VACUUM (FULL TRUE) no_index_cleanup;
@@ -129,7 +129,7 @@ ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
129129
VACUUM no_index_cleanup;
130130
-- Parameter is set for both the parent table and its toast relation.
131131
INSERT INTO no_index_cleanup(i, t)VALUES (generate_series(31,60),
132-
repeat('1234567890',300));
132+
repeat('1234567890',269));
133133
DELETEFROM no_index_cleanupWHERE i<45;
134134
-- Only toast index is cleaned up.
135135
ALTERTABLE no_index_cleanupSET (vacuum_index_cleanup= false,

0 commit comments

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