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

Commit03a8a5f

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 parentf918b5d commit03a8a5f

File tree

5 files changed

+83
-0
lines changed

5 files changed

+83
-0
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: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6734,6 +6734,7 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
67346734
Form_pg_attributeattrtuple;
67356735
AttrNumberattnum;
67366736
ObjectAddressaddress;
6737+
ListCell*lc;
67376738

67386739
Assert(IsA(newValue,String));
67396740
storagemode=strVal(newValue);
@@ -6793,6 +6794,53 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
67936794

67946795
heap_freetuple(tuple);
67956796

6797+
/*
6798+
* Apply the change to indexes as well (only for simple index columns,
6799+
* matching behavior of index.c ConstructTupleDescriptor()).
6800+
*/
6801+
foreach(lc,RelationGetIndexList(rel))
6802+
{
6803+
Oidindexoid=lfirst_oid(lc);
6804+
Relationindrel;
6805+
inti;
6806+
AttrNumberindattnum=0;
6807+
6808+
indrel=index_open(indexoid,lockmode);
6809+
6810+
for (i=0;i<indrel->rd_index->indnatts;i++)
6811+
{
6812+
if (indrel->rd_index->indkey.values[i]==attnum)
6813+
{
6814+
indattnum=i+1;
6815+
break;
6816+
}
6817+
}
6818+
6819+
if (indattnum==0)
6820+
{
6821+
index_close(indrel,lockmode);
6822+
continue;
6823+
}
6824+
6825+
tuple=SearchSysCacheCopyAttNum(RelationGetRelid(indrel),indattnum);
6826+
6827+
if (HeapTupleIsValid(tuple))
6828+
{
6829+
attrtuple= (Form_pg_attribute)GETSTRUCT(tuple);
6830+
attrtuple->attstorage=newstorage;
6831+
6832+
CatalogTupleUpdate(attrelation,&tuple->t_self,tuple);
6833+
6834+
InvokeObjectPostAlterHook(RelationRelationId,
6835+
RelationGetRelid(rel),
6836+
attrtuple->attnum);
6837+
6838+
heap_freetuple(tuple);
6839+
}
6840+
6841+
index_close(indrel,lockmode);
6842+
}
6843+
67966844
heap_close(attrelation,RowExclusiveLock);
67976845

67986846
ObjectAddressSubSet(address,RelationRelationId,

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2255,6 +2255,26 @@ where oid = 'test_storage'::regclass;
22552255
t
22562256
(1 row)
22572257

2258+
-- test that SET STORAGE propagates to index correctly
2259+
create index test_storage_idx on test_storage (b, a);
2260+
alter table test_storage alter column a set storage external;
2261+
\d+ test_storage
2262+
Table "public.test_storage"
2263+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2264+
--------+---------+-----------+----------+---------+----------+--------------+-------------
2265+
a | text | | | | external | |
2266+
b | integer | | | 0 | plain | |
2267+
Indexes:
2268+
"test_storage_idx" btree (b, a)
2269+
2270+
\d+ test_storage_idx
2271+
Index "public.test_storage_idx"
2272+
Column | Type | Key? | Definition | Storage | Stats target
2273+
--------+---------+------+------------+----------+--------------
2274+
b | integer | yes | b | plain |
2275+
a | text | yes | a | external |
2276+
btree, for table "public.test_storage"
2277+
22582278
-- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
22592279
CREATE TABLE test_inh_check (a float check (a > 10.2), b float);
22602280
CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);

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

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

1506+
-- test that SET STORAGE propagates to index correctly
1507+
createindextest_storage_idxon test_storage (b, a);
1508+
altertable test_storage alter column aset storage external;
1509+
\d+ test_storage
1510+
\d+ test_storage_idx
1511+
15061512
-- ALTER COLUMN TYPE with a check constraint and a child table (bug #13779)
15071513
CREATETABLEtest_inh_check (a floatcheck (a>10.2), b float);
15081514
CREATETABLEtest_inh_check_child() INHERITS(test_inh_check);

0 commit comments

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