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

Commit8f67a6c

Browse files
committed
Make sure ALTER TABLE preserves index tablespaces.
When rebuilding an existing index, ALTER TABLE correctly kept thephysical file in the same tablespace, but it messed up the pg_classentry if the index had been in the database's default tablespaceand "default_tablespace" was set to some non-default tablespace.This led to an inaccessible index.Fix by fixing pg_get_indexdef_string() to always include a tablespaceclause, whether or not the index is in the default tablespace. Theprevious behavior was installed in commit537e92e, and I think it justwasn't thought through very clearly; certainly the possible effect ofdefault_tablespace wasn't considered. There's some risk in changing thebehavior of this function, but there are no other call sites in the corecode. Even if it's being used by some third party extension, it's fairlyhard to envision a usage that is okay with a tablespace clause beingappended some of the time but can't handle it being appended all the time.Back-patch to all supported versions.Code fix by me, investigation and test cases by Michael Paquier.Discussion: <1479294998857-5930602.post@n3.nabble.com>
1 parent624839a commit8f67a6c

File tree

3 files changed

+149
-10
lines changed

3 files changed

+149
-10
lines changed

‎src/backend/utils/adt/ruleutils.c

Lines changed: 13 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -932,7 +932,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
932932
*
933933
* Note that the SQL-function versions of this omit any info about the
934934
* index tablespace; this is intentional because pg_dump wants it that way.
935-
* However pg_get_indexdef_string() includes index tablespace if not default.
935+
* However pg_get_indexdef_string() includestheindex tablespace.
936936
* ----------
937937
*/
938938
Datum
@@ -964,7 +964,11 @@ pg_get_indexdef_ext(PG_FUNCTION_ARGS)
964964
prettyFlags)));
965965
}
966966

967-
/* Internal version that returns a palloc'd C string; no pretty-printing */
967+
/*
968+
* Internal version for use by ALTER TABLE.
969+
* Includes a tablespace clause in the result.
970+
* Returns a palloc'd C string; no pretty-printing.
971+
*/
968972
char*
969973
pg_get_indexdef_string(Oidindexrelid)
970974
{
@@ -1213,20 +1217,19 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
12131217
}
12141218

12151219
/*
1216-
*If it's in a nondefaulttablespace, say so, but only if requested
1220+
*Printtablespace, but only if requested
12171221
*/
12181222
if (showTblSpc)
12191223
{
12201224
Oidtblspc;
12211225

12221226
tblspc=get_rel_tablespace(indexrelid);
1223-
if (OidIsValid(tblspc))
1224-
{
1225-
if (isConstraint)
1226-
appendStringInfoString(&buf," USING INDEX");
1227-
appendStringInfo(&buf," TABLESPACE %s",
1228-
quote_identifier(get_tablespace_name(tblspc)));
1229-
}
1227+
if (!OidIsValid(tblspc))
1228+
tblspc=MyDatabaseTableSpace;
1229+
if (isConstraint)
1230+
appendStringInfoString(&buf," USING INDEX");
1231+
appendStringInfo(&buf," TABLESPACE %s",
1232+
quote_identifier(get_tablespace_name(tblspc)));
12301233
}
12311234

12321235
/*

‎src/test/regress/input/tablespace.source

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,37 @@ CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE testspace;
3434
SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
3535
where c.reltablespace = t.oid AND c.relname = 'foo_idx';
3636

37+
-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds
38+
CREATE TABLE testschema.test_default_tab(id bigint) TABLESPACE testspace;
39+
INSERT INTO testschema.test_default_tab VALUES (1);
40+
CREATE INDEX test_index1 on testschema.test_default_tab (id);
41+
CREATE INDEX test_index2 on testschema.test_default_tab (id) TABLESPACE testspace;
42+
\d testschema.test_index1
43+
\d testschema.test_index2
44+
-- use a custom tablespace for default_tablespace
45+
SET default_tablespace TO testspace;
46+
-- tablespace should not change if no rewrite
47+
ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
48+
\d testschema.test_index1
49+
\d testschema.test_index2
50+
SELECT * FROM testschema.test_default_tab;
51+
-- tablespace should not change even if there is an index rewrite
52+
ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
53+
\d testschema.test_index1
54+
\d testschema.test_index2
55+
SELECT * FROM testschema.test_default_tab;
56+
-- now use the default tablespace for default_tablespace
57+
SET default_tablespace TO '';
58+
-- tablespace should not change if no rewrite
59+
ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
60+
\d testschema.test_index1
61+
\d testschema.test_index2
62+
-- tablespace should not change even if there is an index rewrite
63+
ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
64+
\d testschema.test_index1
65+
\d testschema.test_index2
66+
DROP TABLE testschema.test_default_tab;
67+
3768
-- let's try moving a table from one place to another
3869
CREATE TABLE testschema.atable AS VALUES (1), (2);
3970
CREATE UNIQUE INDEX anindex ON testschema.atable(column1);

‎src/test/regress/output/tablespace.source

Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,111 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
4848
foo_idx | testspace
4949
(1 row)
5050

51+
-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds
52+
CREATE TABLE testschema.test_default_tab(id bigint) TABLESPACE testspace;
53+
INSERT INTO testschema.test_default_tab VALUES (1);
54+
CREATE INDEX test_index1 on testschema.test_default_tab (id);
55+
CREATE INDEX test_index2 on testschema.test_default_tab (id) TABLESPACE testspace;
56+
\d testschema.test_index1
57+
Index "testschema.test_index1"
58+
Column | Type | Definition
59+
--------+--------+------------
60+
id | bigint | id
61+
btree, for table "testschema.test_default_tab"
62+
63+
\d testschema.test_index2
64+
Index "testschema.test_index2"
65+
Column | Type | Definition
66+
--------+--------+------------
67+
id | bigint | id
68+
btree, for table "testschema.test_default_tab"
69+
Tablespace: "testspace"
70+
71+
-- use a custom tablespace for default_tablespace
72+
SET default_tablespace TO testspace;
73+
-- tablespace should not change if no rewrite
74+
ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
75+
\d testschema.test_index1
76+
Index "testschema.test_index1"
77+
Column | Type | Definition
78+
--------+--------+------------
79+
id | bigint | id
80+
btree, for table "testschema.test_default_tab"
81+
82+
\d testschema.test_index2
83+
Index "testschema.test_index2"
84+
Column | Type | Definition
85+
--------+--------+------------
86+
id | bigint | id
87+
btree, for table "testschema.test_default_tab"
88+
Tablespace: "testspace"
89+
90+
SELECT * FROM testschema.test_default_tab;
91+
id
92+
----
93+
1
94+
(1 row)
95+
96+
-- tablespace should not change even if there is an index rewrite
97+
ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
98+
\d testschema.test_index1
99+
Index "testschema.test_index1"
100+
Column | Type | Definition
101+
--------+---------+------------
102+
id | integer | id
103+
btree, for table "testschema.test_default_tab"
104+
105+
\d testschema.test_index2
106+
Index "testschema.test_index2"
107+
Column | Type | Definition
108+
--------+---------+------------
109+
id | integer | id
110+
btree, for table "testschema.test_default_tab"
111+
Tablespace: "testspace"
112+
113+
SELECT * FROM testschema.test_default_tab;
114+
id
115+
----
116+
1
117+
(1 row)
118+
119+
-- now use the default tablespace for default_tablespace
120+
SET default_tablespace TO '';
121+
-- tablespace should not change if no rewrite
122+
ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
123+
\d testschema.test_index1
124+
Index "testschema.test_index1"
125+
Column | Type | Definition
126+
--------+---------+------------
127+
id | integer | id
128+
btree, for table "testschema.test_default_tab"
129+
130+
\d testschema.test_index2
131+
Index "testschema.test_index2"
132+
Column | Type | Definition
133+
--------+---------+------------
134+
id | integer | id
135+
btree, for table "testschema.test_default_tab"
136+
Tablespace: "testspace"
137+
138+
-- tablespace should not change even if there is an index rewrite
139+
ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
140+
\d testschema.test_index1
141+
Index "testschema.test_index1"
142+
Column | Type | Definition
143+
--------+--------+------------
144+
id | bigint | id
145+
btree, for table "testschema.test_default_tab"
146+
147+
\d testschema.test_index2
148+
Index "testschema.test_index2"
149+
Column | Type | Definition
150+
--------+--------+------------
151+
id | bigint | id
152+
btree, for table "testschema.test_default_tab"
153+
Tablespace: "testspace"
154+
155+
DROP TABLE testschema.test_default_tab;
51156
-- let's try moving a table from one place to another
52157
CREATE TABLE testschema.atable AS VALUES (1), (2);
53158
CREATE UNIQUE INDEX anindex ON testschema.atable(column1);

0 commit comments

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