Hi Hackers,
I have worked on that patch a little more. So now I have functional patch
(although still WIP) attached. The feature works as following:
- Added a boolean parameter "only_temp_files" to pg_tablespace.spcoptions;
- This parameter can be set to true only during CREATE TABLESPACE, not on
ALTER TABLESPACE (I have thought of ways of implementing the latter, and
I'd like to discuss it more latter);
- On the creation of relations, it is checked if it is a
temporary-tablespace, and an error occurs when it is and the relation is
not temporary (temp table or index on a temp table);
- When a temporary file (either relation file or sort/agg file) is created
inside a temporary-tablespace, the entire directories structure is created
on-demand (e.g. if pg_tblspc/<oid>/<TABLESPACE_VERSION_DIRECTORY> is
missing, it is created on demand) it is done on
OpenTemporaryFileInTablespace, at fd.c (I wonder if shouldn't we do that
for any tablespace) and on TablespaceCreateDbspace, at tablespace.c.
I still haven't change documentation, as I think I need some insights about
the changes. I have some more thoughts about the syntax and I still think
that "TEMP LOCATION" syntax is better suited for this patch. First because
of the nature of the changes I made, it seems more suitable to a column on
pg_tablespace rather than an option. Second because no ALTER is available
(so far) and I think it is odd to have an option that can't be changed.
Third, I think "TEMP" keyword is more clear and users can be more used to
it.
Thoughts?
I'm going to add the CF app entry next. Could I get some review now or
after discussion about how things are going (remember I'm a newbie on this,
so I'm a little lost)?
Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
*** a/src/backend/access/common/reloptions.c
--- b/src/backend/access/common/reloptions.c
***************
*** 71,76 **** static relopt_bool boolRelOpts[] =
--- 71,84 ----
},
{
{
+ "only_temp_files",
+ "Allow only temporary files to be created on this tablespace",
+ RELOPT_KIND_TABLESPACE
+ },
+ false
+ },
+ {
+ {
"fastupdate",
"Enables \"fast update\" feature for this GIN index",
RELOPT_KIND_GIN
***************
*** 1337,1343 **** tablespace_reloptions(Datum reloptions, bool validate)
int numoptions;
static const relopt_parse_elt tab[] = {
{"random_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, random_page_cost)},
! {"seq_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, seq_page_cost)}
};
options = parseRelOptions(reloptions, validate, RELOPT_KIND_TABLESPACE,
--- 1345,1352 ----
int numoptions;
static const relopt_parse_elt tab[] = {
{"random_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, random_page_cost)},
! {"seq_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, seq_page_cost)},
! {"only_temp_files", RELOPT_TYPE_BOOL, offsetof(TableSpaceOpts, only_temp_files)}
};
options = parseRelOptions(reloptions, validate, RELOPT_KIND_TABLESPACE,
*** a/src/backend/commands/dbcommands.c
--- b/src/backend/commands/dbcommands.c
***************
*** 394,399 **** createdb(const CreatedbStmt *stmt)
--- 394,405 ----
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("pg_global cannot be used as default tablespace")));
+ /* can't create a database on temporary tablespace */
+ if (is_tablespace_temp_only(dst_deftablespace))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("this tablespace only allows temporary files")));
+
/*
* If we are trying to change the default tablespace of the template,
* we require that the template not have any files in the new default
***************
*** 1083,1088 **** movedb(const char *dbname, const char *tblspcname)
--- 1089,1100 ----
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("pg_global cannot be used as default tablespace")));
+ /* can't create a database on temporary tablespace */
+ if (is_tablespace_temp_only(dst_tblspcoid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("this tablespace only allows temporary files")));
+
/*
* No-op if same tablespace
*/
*** a/src/backend/commands/indexcmds.c
--- b/src/backend/commands/indexcmds.c
***************
*** 432,437 **** DefineIndex(Oid relationId,
--- 432,446 ----
get_tablespace_name(tablespaceId));
}
+ /* Can't save relations on temporary tablespace */
+ if (rel->rd_rel->relpersistence != RELPERSISTENCE_TEMP &&
+ is_tablespace_temp_only(OidIsValid(tablespaceId) ? tablespaceId : MyDatabaseTableSpace))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("this tablespace only allows temporary files")));
+ }
+
/*
* Force shared indexes into the pg_global tablespace. This is a bit of a
* hack but seems simpler than marking them in the BKI commands. On the
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 523,528 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId)
--- 523,537 ----
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("only shared relations can be placed in pg_global tablespace")));
+ /* Can't save relations on temporary tablespace */
+ if (stmt->relation->relpersistence != RELPERSISTENCE_TEMP &&
+ is_tablespace_temp_only(OidIsValid(tablespaceId) ? tablespaceId : MyDatabaseTableSpace))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("this tablespace only allows temporary files")));
+ }
+
/* Identify user ID that will own the table */
if (!OidIsValid(ownerId))
ownerId = GetUserId();
***************
*** 8824,8829 **** ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, char *tablespacename, L
--- 8833,8847 ----
aclcheck_error(aclresult, ACL_KIND_TABLESPACE, tablespacename);
}
+ /* Can't save relations on temporary tablespace */
+ if (rel->rd_rel->relpersistence != RELPERSISTENCE_TEMP &&
+ is_tablespace_temp_only(OidIsValid(tablespaceId) ? tablespaceId : MyDatabaseTableSpace))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("this tablespace only allows temporary files")));
+ }
+
/* Save info for Phase 3 to do the real work */
if (OidIsValid(tab->newTableSpace))
ereport(ERROR,
*** a/src/backend/commands/tablespace.c
--- b/src/backend/commands/tablespace.c
***************
*** 81,86 ****
--- 81,87 ----
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/tqual.h"
+ #include "utils/spccache.h"
/* GUC variables */
***************
*** 154,170 **** TablespaceCreateDbspace(Oid spcNode, Oid dbNode, bool isRedo)
{
char *parentdir;
! /* Failure other than not exists or not in WAL replay? */
! if (errno != ENOENT || !isRedo)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not create directory \"%s\": %m",
dir)));
/*
! * Parent directories are missing during WAL replay, so
! * continue by creating simple parent directories rather
! * than a symlink.
*/
/* create two parents up if not exist */
--- 155,172 ----
{
char *parentdir;
! /* Failure other than not exists or not in WAL replay with a non-temp tablespace? */
! if (errno != ENOENT || !( isRedo || is_tablespace_temp_only(spcNode) ) )
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not create directory \"%s\": %m",
dir)));
/*
! * Parent directories are missing during WAL replay, and
! * they can be missing for temp tablespaces, so continue
! * by creating simple parent directories rather than a
! * symlink.
*/
/* create two parents up if not exist */
***************
*** 929,934 **** AlterTableSpaceOptions(AlterTableSpaceOptionsStmt *stmt)
--- 931,938 ----
Oid tablespaceoid;
Datum datum;
Datum newOptions;
+ TableSpaceOpts *tsopts;
+ TableSpaceOpts *tsoptsOld;
Datum repl_val[Natts_pg_tablespace];
bool isnull;
bool repl_null[Natts_pg_tablespace];
***************
*** 960,969 **** AlterTableSpaceOptions(AlterTableSpaceOptionsStmt *stmt)
/* Generate new proposed spcoptions (text array) */
datum = heap_getattr(tup, Anum_pg_tablespace_spcoptions,
RelationGetDescr(rel), &isnull);
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
stmt->options, NULL, NULL, false,
stmt->isReset);
! (void) tablespace_reloptions(newOptions, true);
/* Build new tuple. */
memset(repl_null, false, sizeof(repl_null));
--- 964,983 ----
/* Generate new proposed spcoptions (text array) */
datum = heap_getattr(tup, Anum_pg_tablespace_spcoptions,
RelationGetDescr(rel), &isnull);
+ tsoptsOld = (TableSpaceOpts *) tablespace_reloptions(datum, false);
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
stmt->options, NULL, NULL, false,
stmt->isReset);
! tsopts = (TableSpaceOpts *) tablespace_reloptions(newOptions, true);
!
! /* Can't save relations on temporary tablespace */
! if (tsopts->only_temp_files)
! {
! if (!tsoptsOld->only_temp_files)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg("cannot alter a tablespace to become temporary")));
! }
/* Build new tuple. */
memset(repl_null, false, sizeof(repl_null));
*** a/src/backend/storage/file/fd.c
--- b/src/backend/storage/file/fd.c
***************
*** 76,81 ****
--- 76,82 ----
#include "storage/ipc.h"
#include "utils/guc.h"
#include "utils/resowner_private.h"
+ #include "utils/spccache.h"
/*
***************
*** 1132,1137 **** OpenTemporaryFileInTablespace(Oid tblspcOid, bool rejectError)
--- 1133,1156 ----
file = PathNameOpenFile(tempfilepath,
O_RDWR | O_CREAT | O_TRUNC | PG_BINARY,
0600);
+ /* On a temporary tablespace, we need to recreate its structure */
+ if (file <= 0 && is_tablespace_temp_only(tblspcOid))
+ {
+ /*
+ * XXX: Should we only do that for temp tablespace? Or blindly do for
+ * any tablespace?
+ */
+ char *parentdir;
+ parentdir = pstrdup(tempdirpath);
+ get_parent_directory(parentdir);
+ /* As above, don't check error for mkdir */
+ mkdir(parentdir, S_IRWXU);
+ pfree(parentdir);
+
+ file = PathNameOpenFile(tempfilepath,
+ O_RDWR | O_CREAT | O_TRUNC | PG_BINARY,
+ 0600);
+ }
if (file <= 0 && rejectError)
elog(ERROR, "could not create temporary file \"%s\": %m",
tempfilepath);
*** a/src/backend/utils/cache/spccache.c
--- b/src/backend/utils/cache/spccache.c
***************
*** 199,201 **** get_tablespace_page_costs(Oid spcid,
--- 199,230 ----
*spc_seq_page_cost = spc->opts->seq_page_cost;
}
}
+
+ /*
+ * is_tablespace_temp_only
+ * Return true if the tablespace only allows temporary files
+ */
+ bool
+ is_tablespace_temp_only(Oid spcid)
+ {
+ TableSpaceCacheEntry *spc;
+
+ /*
+ * pg_global and pg_default are never temporary, so no need to
+ * check the cache
+ */
+ if (spcid == GLOBALTABLESPACE_OID || spcid == DEFAULTTABLESPACE_OID)
+ return false;
+
+ spc = get_tablespace(spcid);
+
+ Assert(spc != NULL);
+
+ if (spc->opts == NULL)
+ {
+ /* no options, so this tablespace can't be considered temporary */
+ return false;
+ }
+
+ return spc->opts->only_temp_files;
+ }
*** a/src/include/commands/tablespace.h
--- b/src/include/commands/tablespace.h
***************
*** 37,42 **** typedef struct TableSpaceOpts
--- 37,43 ----
int32 vl_len_; /* varlena header (do not touch directly!) */
float8 random_page_cost;
float8 seq_page_cost;
+ bool only_temp_files;
} TableSpaceOpts;
extern Oid CreateTableSpace(CreateTableSpaceStmt *stmt);
*** a/src/include/utils/spccache.h
--- b/src/include/utils/spccache.h
***************
*** 15,19 ****
--- 15,20 ----
void get_tablespace_page_costs(Oid spcid, float8 *spc_random_page_cost,
float8 *spc_seq_page_cost);
+ bool is_tablespace_temp_only(Oid spcid);
#endif /* SPCCACHE_H */
*** a/src/test/regress/input/tablespace.source
--- b/src/test/regress/input/tablespace.source
***************
*** 81,89 **** ALTER TABLESPACE testspace_renamed MOVE ALL TO pg_default;
--- 81,133 ----
-- Should show notice that nothing was done
ALTER TABLESPACE testspace_renamed MOVE ALL TO pg_default;
+ -- Try changing only_temp_files
+ ALTER TABLESPACE testspace_renamed SET (only_temp_files = true); --fail
+ ALTER TABLESPACE testspace_renamed SET (only_temp_files = on); --fail
+ SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspace_renamed';
+ ALTER TABLESPACE testspace_renamed SET (only_temp_files = false); --ok, already non-temporary, just explicit set
+ SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspace_renamed';
+ ALTER TABLESPACE testspace_renamed SET (only_temp_files = off); --ok, already non-temporary, just explicit set
+ SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspace_renamed';
+ ALTER TABLESPACE testspace_renamed RESET (only_temp_files); --ok
+
-- Should succeed
DROP TABLESPACE testspace_renamed;
+ -- Now, let's create temporary tablespace
+ CREATE TABLESPACE testspace LOCATION '@testtablespace@' WITH (only_temp_files = true); -- ok
+ CREATE TABLE testschema.nontemp(a int) TABLESPACE testspace; -- fail
+ CREATE TABLE testschema.nontemp(a int); -- ok
+ ALTER TABLE testschema.nontemp SET TABLESPACE testspace; -- fail
+ CREATE INDEX nontemp_idx ON testschema.nontemp(a) TABLESPACE testspace; -- fail
+ CREATE INDEX nontemp_idx ON testschema.nontemp(a); -- ok
+ ALTER INDEX testschema.nontemp_idx SET TABLESPACE testspace; -- fail
+
+ -- Explicit created (on CREATE and ALTER)
+ CREATE TEMP TABLE temptbl(a int) TABLESPACE testspace;
+ CREATE INDEX temptbl_idx ON temptbl(a);
+ CREATE TEMP TABLE temptbl2(a int);
+ CREATE INDEX temptbl2_idx ON temptbl2(a);
+ ALTER TABLE temptbl2 SET TABLESPACE testspace;
+ ALTER INDEX temptbl2_idx SET TABLESPACE testspace;
+ CREATE INDEX temptbl_idx ON temptbl (a);
+ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+ where c.reltablespace = t.oid AND c.relnamespace = pg_my_temp_schema() AND c.relname IN ('temptbl', 'temptbl_idx', 'temptbl2', 'temptbl2_idx');
+ DROP TABLE temptbl;
+ DROP TABLE temptbl2;
+
+ -- Use temp_tablespaces
+ SET temp_tablespaces TO 'testspace';
+ CREATE TEMP TABLE temptbl(a int);
+ CREATE INDEX temptbl_idx ON temptbl (a);
+ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+ where c.reltablespace = t.oid AND c.relnamespace = pg_my_temp_schema() AND c.relname IN ('temptbl', 'temptbl_idx');
+
+ -- Use this tablespace in a sort operation, and check if any files on pgsql_tmp has been used
+ SET work_mem TO '1MB';
+ WITH o AS (SELECT i, md5(random()::text) FROM generate_series(1, 100000) i ORDER BY md5) SELECT count(*) > 0 FROM (SELECT pg_ls_dir('pg_tblspc/' || oid || '/' || pg_ls_dir('pg_tblspc/' || oid || '/') || '/pgsql_tmp/') FROM pg_tablespace, (SELECT count(*) FROM o) t1 WHERE spcname = 'testspace') t2;
+
+ -- Should succeed
DROP SCHEMA testschema CASCADE;
DROP ROLE tablespace_testuser1;
*** a/src/test/regress/output/tablespace.source
--- b/src/test/regress/output/tablespace.source
***************
*** 97,109 **** ALTER TABLESPACE testspace_renamed MOVE ALL TO pg_default;
-- Should show notice that nothing was done
ALTER TABLESPACE testspace_renamed MOVE ALL TO pg_default;
NOTICE: no matching relations in tablespace "testspace_renamed" found
-- Should succeed
DROP TABLESPACE testspace_renamed;
DROP SCHEMA testschema CASCADE;
! NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to table testschema.foo
drop cascades to table testschema.asselect
drop cascades to table testschema.asexecute
drop cascades to table testschema.atable
DROP ROLE tablespace_testuser1;
DROP ROLE tablespace_testuser2;
--- 97,189 ----
-- Should show notice that nothing was done
ALTER TABLESPACE testspace_renamed MOVE ALL TO pg_default;
NOTICE: no matching relations in tablespace "testspace_renamed" found
+ -- Try changing only_temp_files
+ ALTER TABLESPACE testspace_renamed SET (only_temp_files = true); --fail
+ ERROR: cannot alter a tablespace to become temporary
+ ALTER TABLESPACE testspace_renamed SET (only_temp_files = on); --fail
+ ERROR: cannot alter a tablespace to become temporary
+ SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspace_renamed';
+ spcoptions
+ ------------
+
+ (1 row)
+
+ ALTER TABLESPACE testspace_renamed SET (only_temp_files = false); --ok, already non-temporary, just explicit set
+ SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspace_renamed';
+ spcoptions
+ -------------------------
+ {only_temp_files=false}
+ (1 row)
+
+ ALTER TABLESPACE testspace_renamed SET (only_temp_files = off); --ok, already non-temporary, just explicit set
+ SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspace_renamed';
+ spcoptions
+ -----------------------
+ {only_temp_files=off}
+ (1 row)
+
+ ALTER TABLESPACE testspace_renamed RESET (only_temp_files); --ok
-- Should succeed
DROP TABLESPACE testspace_renamed;
+ -- Now, let's create temporary tablespace
+ CREATE TABLESPACE testspace LOCATION '@testtablespace@' WITH (only_temp_files = true); -- ok
+ CREATE TABLE testschema.nontemp(a int) TABLESPACE testspace; -- fail
+ ERROR: this tablespace only allows temporary files
+ CREATE TABLE testschema.nontemp(a int); -- ok
+ ALTER TABLE testschema.nontemp SET TABLESPACE testspace; -- fail
+ ERROR: this tablespace only allows temporary files
+ CREATE INDEX nontemp_idx ON testschema.nontemp(a) TABLESPACE testspace; -- fail
+ ERROR: this tablespace only allows temporary files
+ CREATE INDEX nontemp_idx ON testschema.nontemp(a); -- ok
+ ALTER INDEX testschema.nontemp_idx SET TABLESPACE testspace; -- fail
+ ERROR: this tablespace only allows temporary files
+ -- Explicit created (on CREATE and ALTER)
+ CREATE TEMP TABLE temptbl(a int) TABLESPACE testspace;
+ CREATE INDEX temptbl_idx ON temptbl(a);
+ CREATE TEMP TABLE temptbl2(a int);
+ CREATE INDEX temptbl2_idx ON temptbl2(a);
+ ALTER TABLE temptbl2 SET TABLESPACE testspace;
+ ALTER INDEX temptbl2_idx SET TABLESPACE testspace;
+ CREATE INDEX temptbl_idx ON temptbl (a);
+ ERROR: relation "temptbl_idx" already exists
+ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+ where c.reltablespace = t.oid AND c.relnamespace = pg_my_temp_schema() AND c.relname IN ('temptbl', 'temptbl_idx', 'temptbl2', 'temptbl2_idx');
+ relname | spcname
+ --------------+-----------
+ temptbl | testspace
+ temptbl2_idx | testspace
+ temptbl2 | testspace
+ (3 rows)
+
+ DROP TABLE temptbl;
+ DROP TABLE temptbl2;
+ -- Use temp_tablespaces
+ SET temp_tablespaces TO 'testspace';
+ CREATE TEMP TABLE temptbl(a int);
+ CREATE INDEX temptbl_idx ON temptbl (a);
+ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+ where c.reltablespace = t.oid AND c.relnamespace = pg_my_temp_schema() AND c.relname IN ('temptbl', 'temptbl_idx');
+ relname | spcname
+ -------------+-----------
+ temptbl | testspace
+ temptbl_idx | testspace
+ (2 rows)
+
+ -- Use this tablespace in a sort operation, and check if any files on pgsql_tmp has been used
+ SET work_mem TO '1MB';
+ WITH o AS (SELECT i, md5(random()::text) FROM generate_series(1, 100000) i ORDER BY md5) SELECT count(*) > 0 FROM (SELECT pg_ls_dir('pg_tblspc/' || oid || '/' || pg_ls_dir('pg_tblspc/' || oid || '/') || '/pgsql_tmp/') FROM pg_tablespace, (SELECT count(*) FROM o) t1 WHERE spcname = 'testspace') t2;
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- Should succeed
DROP SCHEMA testschema CASCADE;
! NOTICE: drop cascades to 5 other objects
DETAIL: drop cascades to table testschema.foo
drop cascades to table testschema.asselect
drop cascades to table testschema.asexecute
drop cascades to table testschema.atable
+ drop cascades to table testschema.nontemp
DROP ROLE tablespace_testuser1;
DROP ROLE tablespace_testuser2;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
