Skip to content

Commit 16d8e59

Browse files
mhagandermhagander
committed
Remove spclocation field from pg_tablespace
Instead, add a function pg_tablespace_location(oid) used to return the same information, and do this by reading the symbolic link. Doing it this way makes it possible to relocate a tablespace when the database is down by simply changing the symbolic link.
1 parent c6e3ac1 commit 16d8e59

File tree

13 files changed

+115
-41
lines changed

13 files changed

+115
-41
lines changed

‎contrib/pg_upgrade/info.c

Lines changed: 17 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -193,15 +193,21 @@ get_db_infos(ClusterInfo *cluster)
193193
int i_datname,
194194
i_oid,
195195
i_spclocation;
196+
char query[QUERY_ALLOC];
196197

197-
res = executeQueryOrDie(conn,
198-
"SELECT d.oid, d.datname, t.spclocation "
199-
"FROM pg_catalog.pg_database d "
200-
" LEFT OUTER JOIN pg_catalog.pg_tablespace t "
201-
" ON d.dattablespace = t.oid "
202-
"WHERE d.datallowconn = true "
198+
snprintf(query, sizeof(query),
199+
"SELECT d.oid, d.datname, %s "
200+
"FROM pg_catalog.pg_database d "
201+
" LEFT OUTER JOIN pg_catalog.pg_tablespace t "
202+
" ON d.dattablespace = t.oid "
203+
"WHERE d.datallowconn = true "
203204
/* we don't preserve pg_database.oid so we sort by name */
204-
"ORDER BY 2");
205+
"ORDER BY 2",
206+
/* 9.2 removed the spclocation column */
207+
(GET_MAJOR_VERSION(old_cluster.major_version) <= 901) ?
208+
"t.spclocation" : "pg_catalog.pg_tablespace_location(t.oid) AS spclocation");
209+
210+
res = executeQueryOrDie(conn, "%s", query);
205211

206212
i_oid = PQfnumber(res, "oid");
207213
i_datname = PQfnumber(res, "datname");
@@ -265,7 +271,7 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo)
265271

266272
snprintf(query, sizeof(query),
267273
"SELECT c.oid, n.nspname, c.relname, "
268-
" c.relfilenode, t.spclocation "
274+
" c.relfilenode, %s "
269275
"FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n "
270276
" ON c.relnamespace = n.oid "
271277
" LEFT OUTER JOIN pg_catalog.pg_tablespace t "
@@ -280,6 +286,9 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo)
280286
" relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index'%s) )) "
281287
/* we preserve pg_class.oid so we sort by it to match old/new */
282288
"ORDER BY 1;",
289+
/* 9.2 removed the spclocation column */
290+
(GET_MAJOR_VERSION(old_cluster.major_version) <= 901) ?
291+
"t.spclocation" : "pg_catalog.pg_tablespace_location(t.oid) AS spclocation",
283292
/* see the comment at the top of old_8_3_create_sequence_script() */
284293
(GET_MAJOR_VERSION(old_cluster.major_version) <= 803) ?
285294
"" : ", 'S'",

‎contrib/pg_upgrade/tablespace.c

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -44,12 +44,18 @@ get_tablespace_paths(void)
4444
PGresult *res;
4545
int tblnum;
4646
int i_spclocation;
47-
48-
res = executeQueryOrDie(conn,
49-
"SELECT spclocation "
50-
"FROM pg_catalog.pg_tablespace "
51-
"WHERE spcname != 'pg_default' AND "
52-
" spcname != 'pg_global'");
47+
char query[QUERY_ALLOC];
48+
49+
snprintf(query, sizeof(query),
50+
"SELECT %s "
51+
"FROM pg_catalog.pg_tablespace "
52+
"WHERE spcname != 'pg_default' AND "
53+
" spcname != 'pg_global'",
54+
/* 9.2 removed the spclocation column */
55+
(GET_MAJOR_VERSION(old_cluster.major_version) <= 901) ?
56+
"t.spclocation" : "pg_catalog.pg_tablespace_location(oid) AS spclocation");
57+
58+
res = executeQueryOrDie(conn, "%s", query);
5359

5460
if ((os_info.num_tablespaces = PQntuples(res)) != 0)
5561
os_info.tablespaces = (char **) pg_malloc(

‎doc/src/sgml/catalogs.sgml

Lines changed: 0 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -5391,13 +5391,6 @@
53915391
<entry>Owner of the tablespace, usually the user who created it</entry>
53925392
</row>
53935393

5394-
<row>
5395-
<entry><structfield>spclocation</structfield></entry>
5396-
<entry><type>text</type></entry>
5397-
<entry></entry>
5398-
<entry>Location (directory path) of the tablespace</entry>
5399-
</row>
5400-
54015394
<row>
54025395
<entry><structfield>spcacl</structfield></entry>
54035396
<entry><type>aclitem[]</type></entry>

‎doc/src/sgml/func.sgml

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13611,6 +13611,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1361113611
<primary>pg_tablespace_databases</primary>
1361213612
</indexterm>
1361313613

13614+
<indexterm>
13615+
<primary>pg_tablespace_location</primary>
13616+
</indexterm>
13617+
1361413618
<indexterm>
1361513619
<primary>pg_typeof</primary>
1361613620
</indexterm>
@@ -13758,6 +13762,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1375813762
<entry><type>setof oid</type></entry>
1375913763
<entry>get the set of database OIDs that have objects in the tablespace</entry>
1376013764
</row>
13765+
<row>
13766+
<entry><literal><function>pg_tablespace_location(<parameter>tablespace_oid</parameter>)</function></literal></entry>
13767+
<entry><type>text</type></entry>
13768+
<entry>get the path in the filesystem that this tablespace is located in</entry>
13769+
</row>
1376113770
<row>
1376213771
<entry><literal><function>pg_typeof(<parameter>any</parameter>)</function></literal></entry>
1376313772
<entry><type>regtype</type></entry>

‎doc/src/sgml/xaggr.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -154,7 +154,7 @@ SELECT attrelid::regclass, array_accum(attname)
154154

155155
attrelid | array_accum
156156
---------------+---------------------------------------
157-
pg_tablespace | {spcname,spcowner,spclocation,spcacl}
157+
pg_tablespace | {spcname,spcowner,spcacl,spcoptions}
158158
(1 row)
159159

160160
SELECT attrelid::regclass, array_accum(atttypid::regtype)
@@ -164,7 +164,7 @@ SELECT attrelid::regclass, array_accum(atttypid::regtype)
164164

165165
attrelid | array_accum
166166
---------------+---------------------------
167-
pg_tablespace | {name,oid,text,aclitem[]}
167+
pg_tablespace | {name,oid,aclitem[],text[]}
168168
(1 row)
169169
</programlisting>
170170
</para>

‎src/backend/commands/tablespace.c

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -314,8 +314,6 @@ CreateTableSpace(CreateTableSpaceStmt *stmt)
314314
DirectFunctionCall1(namein, CStringGetDatum(stmt->tablespacename));
315315
values[Anum_pg_tablespace_spcowner - 1] =
316316
ObjectIdGetDatum(ownerId);
317-
values[Anum_pg_tablespace_spclocation - 1] =
318-
CStringGetTextDatum(location);
319317
nulls[Anum_pg_tablespace_spcacl - 1] = true;
320318
nulls[Anum_pg_tablespace_spcoptions - 1] = true;
321319

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

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@
1818
#include <signal.h>
1919
#include <dirent.h>
2020
#include <math.h>
21+
#include <unistd.h>
2122

2223
#include "catalog/catalog.h"
2324
#include "catalog/pg_tablespace.h"
@@ -261,6 +262,44 @@ pg_tablespace_databases(PG_FUNCTION_ARGS)
261262
}
262263

263264

265+
/*
266+
* pg_tablespace_location - get location for a tablespace
267+
*/
268+
Datum
269+
pg_tablespace_location(PG_FUNCTION_ARGS)
270+
{
271+
Oid tablespaceOid = PG_GETARG_OID(0);
272+
char sourcepath[MAXPGPATH];
273+
char targetpath[MAXPGPATH];
274+
int rllen;
275+
276+
/*
277+
* Return empty string for our two default tablespace
278+
*/
279+
if (tablespaceOid == DEFAULTTABLESPACE_OID ||
280+
tablespaceOid == GLOBALTABLESPACE_OID)
281+
PG_RETURN_TEXT_P(cstring_to_text(""));
282+
283+
#if defined(HAVE_READLINK) || defined(WIN32)
284+
/*
285+
* Find the location of the tablespace by reading the symbolic link that is
286+
* in pg_tblspc/<oid>.
287+
*/
288+
snprintf(sourcepath, sizeof(sourcepath), "pg_tblspc/%u", tablespaceOid);
289+
rllen =readlink(sourcepath, targetpath, sizeof(targetpath));
290+
if (rllen < 0 || rllen >= sizeof(targetpath))
291+
ereport(ERROR,
292+
(errmsg("could not read symbolic link \"%s\": %m", sourcepath)));
293+
targetpath[rllen] = '\0';
294+
295+
PG_RETURN_TEXT_P(cstring_to_text(targetpath));
296+
#else
297+
ereport(ERROR,
298+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
299+
errmsg("tablespaces are not supported on this platform")));
300+
#endif
301+
}
302+
264303
/*
265304
* pg_sleep - delay for N seconds
266305
*/

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -997,7 +997,16 @@ dumpTablespaces(PGconn *conn)
997997
* Get all tablespaces except built-in ones (which we assume are named
998998
* pg_xxx)
999999
*/
1000-
if (server_version >= 90000)
1000+
if (server_version >= 90200)
1001+
res = executeQuery(conn, "SELECT oid, spcname, "
1002+
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1003+
"pg_catalog.pg_tablespace_location(oid), spcacl, "
1004+
"array_to_string(spcoptions, ', '),"
1005+
"pg_catalog.shobj_description(oid, 'pg_tablespace') "
1006+
"FROM pg_catalog.pg_tablespace "
1007+
"WHERE spcname !~ '^pg_' "
1008+
"ORDER BY 1");
1009+
else if (server_version >= 90000)
10011010
res = executeQuery(conn, "SELECT oid, spcname, "
10021011
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
10031012
"spclocation, spcacl, "

‎src/bin/psql/describe.c

Lines changed: 16 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -139,13 +139,22 @@ describeTablespaces(const char *pattern, bool verbose)
139139

140140
initPQExpBuffer(&buf);
141141

142-
printfPQExpBuffer(&buf,
143-
"SELECT spcname AS \"%s\",\n"
144-
" pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
145-
" spclocation AS \"%s\"",
146-
gettext_noop("Name"),
147-
gettext_noop("Owner"),
148-
gettext_noop("Location"));
142+
if (pset.sversion >= 90200)
143+
printfPQExpBuffer(&buf,
144+
"SELECT spcname AS \"%s\",\n"
145+
" pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
146+
" pg_catalog.pg_tablespace_location(oid) AS \"%s\"",
147+
gettext_noop("Name"),
148+
gettext_noop("Owner"),
149+
gettext_noop("Location"));
150+
else
151+
printfPQExpBuffer(&buf,
152+
"SELECT spcname AS \"%s\",\n"
153+
" pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
154+
" spclocation AS \"%s\"",
155+
gettext_noop("Name"),
156+
gettext_noop("Owner"),
157+
gettext_noop("Location"));
149158

150159
if (verbose)
151160
{

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201112061
56+
#define CATALOG_VERSION_NO 201112071
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2686,6 +2686,9 @@ DESCR("statistics: reset collected statistics for a single table or index in the
26862686
DATA(insert OID = 3777 ( pg_stat_reset_single_function_counters PGNSP PGUID 12 1 0 0 0 f f f f f v 1 0 2278 "26" _null_ _null_ _null_ _null_ pg_stat_reset_single_function_counters _null_ _null_ _null_ ));
26872687
DESCR("statistics: reset collected statistics for a single function in the current database");
26882688

2689+
DATA(insert OID = 3778 ( pg_tablespace_location PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 25 "26" _null_ _null_ _null_ _null_ pg_tablespace_location _null_ _null_ _null_ ));
2690+
DESCR("tablespace location");
2691+
26892692
DATA(insert OID = 1946 ( encode PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 25 "17 25" _null_ _null_ _null_ _null_ binary_encode _null_ _null_ _null_ ));
26902693
DESCR("convert bytea value into some ascii-only text string");
26912694
DATA(insert OID = 1947 ( decode PGNSP PGUID 12 1 0 0 0 f f f t f i 2 0 17 "25 25" _null_ _null_ _null_ _null_ binary_decode _null_ _null_ _null_ ));

‎src/include/catalog/pg_tablespace.h

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,6 @@ CATALOG(pg_tablespace,1213) BKI_SHARED_RELATION
3232
{
3333
NameData spcname; /* tablespace name */
3434
Oid spcowner; /* owner of tablespace */
35-
text spclocation; /* physical location (VAR LENGTH) */
3635
aclitem spcacl[1]; /* access permissions (VAR LENGTH) */
3736
text spcoptions[1]; /* per-tablespace options */
3837
} FormData_pg_tablespace;
@@ -49,15 +48,14 @@ typedef FormData_pg_tablespace *Form_pg_tablespace;
4948
* ----------------
5049
*/
5150

52-
#define Natts_pg_tablespace 5
51+
#define Natts_pg_tablespace 4
5352
#define Anum_pg_tablespace_spcname 1
5453
#define Anum_pg_tablespace_spcowner 2
55-
#define Anum_pg_tablespace_spclocation 3
56-
#define Anum_pg_tablespace_spcacl 4
57-
#define Anum_pg_tablespace_spcoptions 5
54+
#define Anum_pg_tablespace_spcacl 3
55+
#define Anum_pg_tablespace_spcoptions 4
5856

59-
DATA(insert OID = 1663 ( pg_default PGUID "" _null_ _null_ ));
60-
DATA(insert OID = 1664 ( pg_global PGUID "" _null_ _null_ ));
57+
DATA(insert OID = 1663 ( pg_default PGUID _null_ _null_ ));
58+
DATA(insert OID = 1664 ( pg_global PGUID _null_ _null_ ));
6159

6260
#define DEFAULTTABLESPACE_OID 1663
6361
#define GLOBALTABLESPACE_OID 1664

‎src/include/utils/builtins.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -469,6 +469,7 @@ extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
469469
extern Datum pg_terminate_backend(PG_FUNCTION_ARGS);
470470
extern Datum pg_reload_conf(PG_FUNCTION_ARGS);
471471
extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
472+
extern Datum pg_tablespace_location(PG_FUNCTION_ARGS);
472473
extern Datum pg_rotate_logfile(PG_FUNCTION_ARGS);
473474
extern Datum pg_sleep(PG_FUNCTION_ARGS);
474475
extern Datum pg_get_keywords(PG_FUNCTION_ARGS);

0 commit comments

Comments
 (0)