Hi,
This small patch implements a new GUC (default_index_tablespace) plus
supporting code.
Originated from a customer request, the feature intends to make
creation of indexes on SSD-backed tablespaces easy and convenient
(almost transparent) for users: the DBA can just set it and indexes will
be placed in the specified tablespace --as opposed to the same
tablespace where the referenced table is-- without having to specify it
every time.
Feedback appreciated.
Thanks,
/ J.L.
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 5622,5627 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
--- 5622,5664 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-default-index-tablespace"
xreflabel="default_index_tablespace">
+ <term><varname>default_index_tablespace</varname> (<type>string</type>)
+ <indexterm>
+ <primary><varname>default_index_tablespace</> configuration
parameter</primary>
+ </indexterm>
+ <indexterm><primary>tablespace</><secondary>default</></>
+ </term>
+ <listitem>
+ <para>
+ This variable specifies the default tablespace in which to create
+ indexes when a <command>CREATE INDEX</> command does
+ not explicitly specify a tablespace.
+ </para>
+
+ <para>
+ The value is either the name of a tablespace, or an empty string
+ to specify using the default tablespace of the current database
+ unless the <xref linkend="guc-default-tablespace"> is defined,
+ in which case the rules for that parameter apply.
+ If the value does not match the name of any existing tablespace,
+ <productname>PostgreSQL</> will automatically use the default
+ tablespace of the current database; the user must have
+ <literal>CREATE</> privilege for it, or creation attempts will fail.
+ </para>
+
+ <para>
+ This variable is not used for indexes on temporary tables; for them,
+ <xref linkend="guc-temp-tablespaces"> is consulted instead.
+ </para>
+
+ <para>
+ For more information on tablespaces,
+ see <xref linkend="manage-ag-tablespaces">.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-temp-tablespaces" xreflabel="temp_tablespaces">
<term><varname>temp_tablespaces</varname> (<type>string</type>)
<indexterm>
*** a/src/backend/commands/indexcmds.c
--- b/src/backend/commands/indexcmds.c
***************
*** 417,423 **** DefineIndex(Oid relationId,
}
else
{
! tablespaceId =
GetDefaultTablespace(rel->rd_rel->relpersistence);
/* note InvalidOid is OK in this case */
}
--- 417,423 ----
}
else
{
! tablespaceId = GetIndexTablespace(rel->rd_rel->relpersistence);
/* note InvalidOid is OK in this case */
}
*** a/src/backend/commands/tablespace.c
--- b/src/backend/commands/tablespace.c
***************
*** 86,91 ****
--- 86,92 ----
/* GUC variables */
char *default_tablespace = NULL;
+ char *default_index_tblspc = NULL;
char *temp_tablespaces = NULL;
***************
*** 1085,1090 **** GetDefaultTablespace(char relpersistence)
--- 1086,1149 ----
/*
+ * GetIndexTablespace -- get the OID of the tablespace for an index
+ *
+ * Temporary objects have different default tablespaces, hence the
+ * relpersistence parameter must be specified.
+ *
+ * May return InvalidOid to indicate "use the database's default tablespace".
+ *
+ * Note that caller is expected to check appropriate permissions for any
+ * result other than InvalidOid.
+ *
+ * This exists to hide (and possibly optimize the use of) the
+ * default_index_tablespace GUC variable.
+ */
+ Oid
+ GetIndexTablespace(char relpersistence)
+ {
+ Oid result;
+ const char *tablespace;
+
+ /* The temp-table case is handled elsewhere */
+ if (relpersistence == RELPERSISTENCE_TEMP)
+ {
+ PrepareTempTablespaces();
+ return GetNextTempTableSpace();
+ }
+
+ /* Fast path for empty GUC: check defaults */
+ if (default_index_tblspc == NULL || default_index_tblspc[0] == '\0')
+ {
+ /* if default_tablespace is also empty, return immediately */
+ if (default_tablespace == NULL || default_tablespace[0] == '\0')
+ return InvalidOid;
+ else
+ tablespace = default_tablespace;
+ }
+ else
+ tablespace = default_index_tblspc;
+
+ /*
+ * It is tempting to cache this lookup for more speed, but then we would
+ * fail to detect the case where the tablespace was dropped since the
GUC
+ * variable was set. Note also that we don't complain if the value
fails
+ * to refer to an existing tablespace; we just silently return
InvalidOid,
+ * causing the new object to be created in the database's tablespace.
+ */
+ result = get_tablespace_oid(tablespace, true);
+
+ /*
+ * Allow explicit specification of database's default tablespace in
+ * default_tablespace without triggering permissions checks.
+ */
+ if (result == MyDatabaseTableSpace)
+ result = InvalidOid;
+ return result;
+ }
+
+
+ /*
* Routines for handling the GUC variable 'temp_tablespaces'.
*/
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 109,114 **** extern int CommitDelay;
--- 109,115 ----
extern int CommitSiblings;
extern char *default_tablespace;
extern char *temp_tablespaces;
+ extern char *default_index_tblspc;
extern bool ignore_checksum_failure;
extern bool synchronize_seqscans;
***************
*** 2894,2899 **** static struct config_string ConfigureNamesString[] =
--- 2895,2911 ----
},
{
+ {"default_index_tablespace", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Sets the default tablespace to create
indexes in."),
+ gettext_noop("An empty string selects the database's
default tablespace."),
+ GUC_IS_NAME
+ },
+ &default_index_tblspc,
+ "",
+ check_default_tablespace, NULL, NULL
+ },
+
+ {
{"temp_tablespaces", PGC_USERSET, CLIENT_CONN_STATEMENT,
gettext_noop("Sets the tablespace(s) to use for
temporary tables and sort files."),
NULL,
*** a/src/include/commands/tablespace.h
--- b/src/include/commands/tablespace.h
***************
*** 49,54 **** extern Oid
AlterTableSpaceOptions(AlterTableSpaceOptionsStmt *stmt);
--- 49,55 ----
extern void TablespaceCreateDbspace(Oid spcNode, Oid dbNode, bool isRedo);
extern Oid GetDefaultTablespace(char relpersistence);
+ extern Oid GetIndexTablespace(char relpersistence);
extern void PrepareTempTablespaces(void);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
