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

Commitbd673e8

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 parente343dfa commitbd673e8

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
@@ -1061,7 +1061,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
10611061
*
10621062
* Note that the SQL-function versions of this omit any info about the
10631063
* index tablespace; this is intentional because pg_dump wants it that way.
1064-
* However pg_get_indexdef_string() includes index tablespace if not default.
1064+
* However pg_get_indexdef_string() includestheindex tablespace.
10651065
* ----------
10661066
*/
10671067
Datum
@@ -1102,7 +1102,11 @@ pg_get_indexdef_ext(PG_FUNCTION_ARGS)
11021102
PG_RETURN_TEXT_P(string_to_text(res));
11031103
}
11041104

1105-
/* Internal version that returns a palloc'd C string; no pretty-printing */
1105+
/*
1106+
* Internal version for use by ALTER TABLE.
1107+
* Includes a tablespace clause in the result.
1108+
* Returns a palloc'd C string; no pretty-printing.
1109+
*/
11061110
char*
11071111
pg_get_indexdef_string(Oidindexrelid)
11081112
{
@@ -1360,20 +1364,19 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
13601364
}
13611365

13621366
/*
1363-
*If it's in a nondefaulttablespace, say so, but only if requested
1367+
*Printtablespace, but only if requested
13641368
*/
13651369
if (showTblSpc)
13661370
{
13671371
Oidtblspc;
13681372

13691373
tblspc=get_rel_tablespace(indexrelid);
1370-
if (OidIsValid(tblspc))
1371-
{
1372-
if (isConstraint)
1373-
appendStringInfoString(&buf," USING INDEX");
1374-
appendStringInfo(&buf," TABLESPACE %s",
1375-
quote_identifier(get_tablespace_name(tblspc)));
1376-
}
1374+
if (!OidIsValid(tblspc))
1375+
tblspc=MyDatabaseTableSpace;
1376+
if (isConstraint)
1377+
appendStringInfoString(&buf," USING INDEX");
1378+
appendStringInfo(&buf," TABLESPACE %s",
1379+
quote_identifier(get_tablespace_name(tblspc)));
13771380
}
13781381

13791382
/*

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

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

47+
-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds
48+
CREATE TABLE testschema.test_default_tab(id bigint) TABLESPACE regress_tblspace;
49+
INSERT INTO testschema.test_default_tab VALUES (1);
50+
CREATE INDEX test_index1 on testschema.test_default_tab (id);
51+
CREATE INDEX test_index2 on testschema.test_default_tab (id) TABLESPACE regress_tblspace;
52+
\d testschema.test_index1
53+
\d testschema.test_index2
54+
-- use a custom tablespace for default_tablespace
55+
SET default_tablespace TO regress_tblspace;
56+
-- tablespace should not change if no rewrite
57+
ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
58+
\d testschema.test_index1
59+
\d testschema.test_index2
60+
SELECT * FROM testschema.test_default_tab;
61+
-- tablespace should not change even if there is an index rewrite
62+
ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
63+
\d testschema.test_index1
64+
\d testschema.test_index2
65+
SELECT * FROM testschema.test_default_tab;
66+
-- now use the default tablespace for default_tablespace
67+
SET default_tablespace TO '';
68+
-- tablespace should not change if no rewrite
69+
ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
70+
\d testschema.test_index1
71+
\d testschema.test_index2
72+
-- tablespace should not change even if there is an index rewrite
73+
ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
74+
\d testschema.test_index1
75+
\d testschema.test_index2
76+
DROP TABLE testschema.test_default_tab;
77+
4778
-- let's try moving a table from one place to another
4879
CREATE TABLE testschema.atable AS VALUES (1), (2);
4980
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
@@ -61,6 +61,111 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
6161
foo_idx | regress_tblspace
6262
(1 row)
6363

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

0 commit comments

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