Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
Hi, I also tried this. This looks nice but seems a bit difficult
to find a rasonable behavior.
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)?
Here is some random comments.
1. I think some users may want to store the temp tablespace in
specially created subdirectory, like this.
| =# CREATE TABLESPACE hoge LOCATION
'/mount_point_of_nonpersist_device/temptblspc1'
| WITH (only_temp_files = true);
I saw the following message for create table after restarting
after rm -r /mount...ice/*.
| =# create temp table thoge (a int) tablespace hoge;
| ERROR: could not create directory pg_tblspc/16435/PG_9.5_201408162: No
such file or directory
Multiple-depth mkdir would be needed.
2. Creating a temporary table in a tablespace with
(only_temp_files = false) after erasing the directory then
restarting the server failed showing me the following message
only for the first time,
| =# create temp table thoge (a int) tablespace hoge;
| ERROR: could not create directory pg_tblspc/16435/PG_9.5_201408162/13004:
Success
Unpatched head seems always showing 'No such file or directory'
from the first time for the case.
3. I saw the following error message during startup after
deleting the tablespace directory for the only-temp tablespace.
| $ postgres
| LOG: database system was shut down at 2014-09-02 16:54:39 JST
*| LOG: could not open tablespace directory
pg_tblspc/16435/PG_9.5_201408162: No such file or directory
| LOG: autovacuum launcher started
| LOG: database system is ready to accept connections
I think the server should refrain from showing this message for
laking of the directories for only-temp teblespaces.
4. You inhibited the option only_temp_files from ALTER'ing from
false to true but pg_tablesspace.spcoptions unfortunately can be
changed directly. Other reloptions for all objects seems not so
harmful.
| =# update pg_tablespace set spcoptions = '{only_temp_files=true}' where
spcname = 'hoge';
Are we allowed to store such a kind of option as reoptions? Or a
result from such a bogus operation should be ignored? Or do we
ought to protect spcoptions from direct modification? Or ...
Any Thoughts?
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On Sun, Jun 29, 2014 at 3:19 AM, Matheus de Oliveira matioli.math...@gmail.com wrote: 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)? I got the following warning messages at the compilation. dbcommands.c:420: warning: implicit declaration of function 'is_tablespace_temp_only' indexcmds.c:437: warning: implicit declaration of function 'is_tablespace_temp_only' tablecmds.c:527: warning: implicit declaration of function 'is_tablespace_temp_only' When I created temporary tablespace and executed pg_dumpall, it generated the SQL ALTER TABLESPACE hoge SET (only_temp_files=on);. This is clearly a bug because that SQL is not allowed to be executed. ERROR: this tablespace only allows temporary files I think that this ERROR message can be improved, for example, for the database creation case, what about something like the following? ERROR: database cannot be created on temporary tablespace HINT: temporary tablespace is allowed to store only temporary files. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On 06/29/2014 02:19 AM, Matheus de Oliveira wrote: 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. Right now PostgreSQL appears to rely on the absence of the tablespace directory as a flag to tell it don't start up, something's badly wrong here. If the user creates the tablespace directory, it figures they at least know what they're doing and carries on starting up with the empty tablespace. It's treated as an admin statement - I know it's gone, it's not coming back, just carry on as best you can. If Pg were to auto-create the directory, then if (say) a mount of a tablespace dir failed at boot, Pg would still happily start up and might create files in the tablespace, despite there being inaccessible tables/indexes/etc on the not-mounted volume that's *supposed* to be the tablespace storage. That'd create plenty of mess. So no, I don't think Pg should auto-create the tablespace directory if it's missing for non-temporary tablespaces. Not unless the current (less than friendly) behaviour around lost tablespaces is replaced with something like an `ignore_missing_tablespaces` or `drop_missing_tablespaces` GUC - akin to our existing `zero_missing_pages` recovery GUC. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On Mon, Jun 30, 2014 at 5:14 AM, Craig Ringer cr...@2ndquadrant.com wrote: Right now PostgreSQL appears to rely on the absence of the tablespace directory as a flag to tell it don't start up, something's badly wrong here. Well, in fact the behavior is just to give a LOG message: LOG: could not open tablespace directory pg_tblspc/100607/PG_9.3_201306121: No such file or directory But it starts fine. No? I ask because I'm relying on that. My patch does not on startup, so in the absence of the tablespace directory, the above LOG is shown even for temporary-tablespace, and it tries to create the directory when any process tries to use it. As we don't have catalog access on startup, it will require me to use some kind of _init fork for tablespace if we want it to be re-created during startup. Should we bother about this? If the user creates the tablespace directory, it figures they at least know what they're doing and carries on starting up with the empty tablespace. It's treated as an admin statement - I know it's gone, it's not coming back, just carry on as best you can. Well, I think for a tablespace like that it is okay to create it on-demand (note that I have done this only for temp, not ordinary ones). If Pg were to auto-create the directory, then if (say) a mount of a tablespace dir failed at boot, Pg would still happily start up and might create files in the tablespace, despite there being inaccessible tables/indexes/etc on the not-mounted volume that's *supposed* to be the tablespace storage. That'd create plenty of mess. So no, I don't think Pg should auto-create the tablespace directory if it's missing for non-temporary tablespaces. Ok. I agree. Let's create only for temporary ones (as done by the patch now). Not unless the current (less than friendly) behaviour around lost tablespaces is replaced with something like an `ignore_missing_tablespaces` or `drop_missing_tablespaces` GUC - akin to our existing `zero_missing_pages` recovery GUC. You meant `zero_damaged_pages`, I think. I don't think that is really necessary in this case. Anyway, I was thinking about some way to this tablespace to also allow creation of non-temporary indexes, and after a crash we could mark such indexes as `NOT VALID` (perhaps `zero_damaged_pages` could be of help here?!). That should be part of another patch thought, and would require more thoughts. But I think that would be a great improvement for some environments, like those on AWS with their ephemeral SSDs. Regards, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
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
***
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On Sun, Jun 22, 2014 at 2:35 AM, Craig Ringer cr...@2ndquadrant.com wrote: A way to put UNLOGGED objects in such a space and have them recovered if they vanish would also be valuable, IMO. Not necessarily in the same patch, I'd just rather keep it in mind so any chosen design doesn't preclude adding that later. The idea is nice, but I think you should think more about it. Were would we put the init files in this case? It surely can't be in the tablespace. Best regards, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On Wed, Jun 18, 2014 at 9:39 PM, Matheus de Oliveira matioli.math...@gmail.com wrote: Then, to summarize Matheus must do: * use an option instead of change the syntax and catalog to indicate that a tablespace is used to store temp objects Yes. I myself wasn't sure TEMPORARY syntax would be good, but I would just like to hear more about. Storing as an options seems nice to me. I kind of like the TEMPORARY syntax, by analogy with what we do for tables. On the other hand, this situation isn't quite analogous: the tablespace itself is permanent; it's only the objects inside the tablespace that are temporary. Hmm. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On Mon, Jun 23, 2014 at 1:17 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 18, 2014 at 9:39 PM, Matheus de Oliveira matioli.math...@gmail.com wrote: Then, to summarize Matheus must do: * use an option instead of change the syntax and catalog to indicate that a tablespace is used to store temp objects Yes. I myself wasn't sure TEMPORARY syntax would be good, but I would just like to hear more about. Storing as an options seems nice to me. I kind of like the TEMPORARY syntax, by analogy with what we do for tables. On the other hand, this situation isn't quite analogous: the tablespace itself is permanent; it's only the objects inside the tablespace that are temporary. Hmm. The suggested syntax is about TEMP LOCATION and not TEMP TABLESPACE... Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
* Craig Ringer (cr...@2ndquadrant.com) wrote: On 06/18/2014 08:00 PM, Stephen Frost wrote: PG would need to enforce that it's only used for temporary objects as well, of course.. Or at least, that was my thinking on this. A way to put UNLOGGED objects in such a space and have them recovered if they vanish would also be valuable, IMO. Interesting idea. Not necessarily in the same patch, I'd just rather keep it in mind so any chosen design doesn't preclude adding that later. Perhaps we need a more complex definition than just temporary tablespace, as in, we should have a way for users to say this tablespace is allowed to have objects of type X, Y, Z? I can see a couple of ways to do that and I don't think it'd require much in the way of changes to the current patch... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/18/2014 08:00 PM, Stephen Frost wrote: PG would need to enforce that it's only used for temporary objects as well, of course.. Or at least, that was my thinking on this. A way to put UNLOGGED objects in such a space and have them recovered if they vanish would also be valuable, IMO. Not necessarily in the same patch, I'd just rather keep it in mind so any chosen design doesn't preclude adding that later. - -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJTpmsOAAoJELBXNkqjr+S2SNUH/A1zor2WUbQg3PTyD+6wyxlM v6iMpH9zaeMWYyavIHM9N8a+h7hl9ajKDPMuK0vYLMs83W88LXI4T/t1iJlgZ3Po WhyjjdNonChn5v1NEJncb4KgqmRQxyWY43rzgEgofSJQgjFbo8UAwoD8Xa32ghDu 4dx2ijYqYsx/hC1Gb1u/HbQ9rX+OkTA0DwljdUgPqPorW/mn5RVscz9Qo5p/W+XH tdvpu9CKRi4AFPkshZOij+Mu3My863K1+k7sHUpRcsRGJT/AIO1V0wUQE2PuWTAV 5InU5vw/1C6CsYpH4v++XvpeTOnQ4QrMnllh99UTxV91UXN+jx/ENv1SYhBfYGE= =JGYF -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/22/2014 01:35 PM, Craig Ringer wrote: A way to put UNLOGGED objects in such a space and have them recovered if they vanish would also be valuable, IMO. By which I mean re-created empty, as if after a crash. - -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -BEGIN PGP SIGNATURE- Version: GnuPG v1 Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJTpmuwAAoJELBXNkqjr+S284oIALPSZVCkqv/A6Oriu48n9LOm KI0cnyiv+kJ8Kor8kDIbweM6DdaUaP0msQOj3vRh8t1GGldF1hmEo83IH/t+sx4t 3B2SUPmHQX5QHa27zmzCk8qAhYKYyUy/HOI0u2DxLm5fGtxpzCjyojo4k8+YkSgM b17eDGrAzlxXJF9BiRj4SBITOxekdgml0yF6J0UPWk98N4cTaXXsMv5r/QBlyXOm /3bdZ0rx0extLcuH+x9iUo7aXC4qCgFJ1ZEzp3xSVGVxU7VA3vQl1O1OR7GHRKJb E3SqS6RiHpWqT2JxD1su/a5XblpF/d5/EfXNxSY7etdNBBarHF5Q6xCNDNard8Y= =30iz -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On Wed, Jun 18, 2014 at 10:39 PM, Matheus de Oliveira matioli.math...@gmail.com wrote: I was going to answer each message, but Fabrízio summarized everything so far really nicely. Thanks Fabrízio. You're welcome. :-) On Wed, Jun 18, 2014 at 5:25 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Then, to summarize Matheus must do: * use an option instead of change the syntax and catalog to indicate that a tablespace is used to store temp objects Yes. I myself wasn't sure TEMPORARY syntax would be good, but I would just like to hear more about. Storing as an options seems nice to me. Are there somebody that reject this idea? Thoughts? * throw an exception if we try ALTER the option only_temp_relations I think we should postpone the ALTER option, perhaps as another patch. Wasn't that what was decided? Don't worry about that, we can help you. ;-) I'll investigate the options better before going this route. Let me work on CREATE first. See this files: - src/include/commands/tablespace.h (TableSpaceOpts struct at line 35) - src/backend/access/common/reloptions.c (tablespace_reloptions at line 1333) * add regression tests * add documentation And, of course, register to the next open commitfest [1] to get detailed feedback and review. Noted. It will be on the next commitfest. Just knowing some people do want this make me more comfortable on working better. Nice... I would like to be a reviewer. Att, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
* Matheus de Oliveira (matioli.math...@gmail.com) wrote: Hi Hackers, Having read only the subject- +1 from me on the idea. Maybe +1000. I was facing a situation were we wanted to set temp_tablespaces to a tablespace on a ephemeral disk (yes, it is AWS ephemeral disk), and I know many users have faced the same situation. Although it seems safe to create a tablespace on ephemeral disks if you use it to store only temporary files (either created by queries or temp tables), PostgreSQL does not have such information, and can't safely prevent a careless user of creating a non-temporary relation on this tablespace. Right. So, what you guys think about letting PG know somehow that a tablespace is temporary? PG would need to enforce that it's only used for temporary objects as well, of course.. Or at least, that was my thinking on this. I have took some small time to make a PoC just to see if that is doable. And so I did a new syntax like: CREATE TABLESPACE spcname [TEMP | TEMPORARY] LOCATION ... So, if TEMP or TEMPORARY is present, I mark a new column at pg_tablespace as true. On every table creation or moving to a new tablespace, I just check this, and fails if the tablespace is temporary but the relpersistence says the table is not. Not sure about that specific syntax (don't we have SET options now?) but I do like the general idea. The other part is, every time some query or relation is asked to be stored on this tablespace, I create (on-demand) the PG_TEMP_FILES_DIR inside of it (also if it is temporary). The attached patch (and also on my Github, [1]), shows the PoC. For now, I'm not worried about the code quality, there are yet a lot of work to be done there (like ALTER TABLESPACE, better testing, use relcache, etc...), and it is my first hacking on PG (so I'm a newbie). But I'd like to hear from you guys if such feature is desirable and if I could starting working on that for real. Also some thoughts about better way of implementing it. Yeah, +1 here and it should go into an appropriate commitfest to get a proper review. This would be *really* nice to have. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On Wed, Jun 18, 2014 at 9:00 AM, Stephen Frost sfr...@snowman.net wrote: I have took some small time to make a PoC just to see if that is doable. And so I did a new syntax like: CREATE TABLESPACE spcname [TEMP | TEMPORARY] LOCATION ... So, if TEMP or TEMPORARY is present, I mark a new column at pg_tablespace as true. On every table creation or moving to a new tablespace, I just check this, and fails if the tablespace is temporary but the relpersistence says the table is not. Not sure about that specific syntax (don't we have SET options now?) but I do like the general idea. Maybe something like that: CREATE TABLESPACE spcname LOCATION '/foo/bar' WITH (only_temp_relations = true); Have in mind you must take care if you use ALTER TABLESPACE spcname SET (...) to guarantee that exists only temp objects stored in the target tablespace, and if exists a regular object you must throw an exception. Makes sense? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: On Wed, Jun 18, 2014 at 9:00 AM, Stephen Frost sfr...@snowman.net wrote: Not sure about that specific syntax (don't we have SET options now?) but I do like the general idea. Maybe something like that: CREATE TABLESPACE spcname LOCATION '/foo/bar' WITH (only_temp_relations = true); Yeah, that's more along the lines of what I was thinking. Have in mind you must take care if you use ALTER TABLESPACE spcname SET (...) to guarantee that exists only temp objects stored in the target tablespace, and if exists a regular object you must throw an exception. Makes sense? Yes. I'd definitely like to see an ALTER TABLESPACE option, with an ERROR that lists out all of the non-temporary objects which are found (and lists any other databases which have objects in those tablespaces..). That would allow administrators who have existing notionally temporary-only tablespaces to go clean things up to make them actually temporary-only. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
Stephen Frost sfr...@snowman.net writes: Yes. I'd definitely like to see an ALTER TABLESPACE option, with an ERROR that lists out all of the non-temporary objects which are found (and lists any other databases which have objects in those tablespaces..). That would allow administrators who have existing notionally temporary-only tablespaces to go clean things up to make them actually temporary-only. That seems just about impossible from a concurrency standpoint (ie, what if someone is creating a new table in the tablespace concurrently with your check? Possibly in a different database?) I would certainly suggest that the first version of the patch not undertake to allow this property to be ALTERed; the cost-benefit ratio isn't there IMO. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On Wed, Jun 18, 2014 at 1:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Stephen Frost sfr...@snowman.net writes: Yes. I'd definitely like to see an ALTER TABLESPACE option, with an ERROR that lists out all of the non-temporary objects which are found (and lists any other databases which have objects in those tablespaces..). That would allow administrators who have existing notionally temporary-only tablespaces to go clean things up to make them actually temporary-only. That seems just about impossible from a concurrency standpoint (ie, what if someone is creating a new table in the tablespace concurrently with your check? Possibly in a different database?) You are correct, I completely forgot that CREATE TABLESPACE is not transaction safe. I would certainly suggest that the first version of the patch not undertake to allow this property to be ALTERed; the cost-benefit ratio isn't there IMO. +1 Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: Yes. I'd definitely like to see an ALTER TABLESPACE option, with an ERROR that lists out all of the non-temporary objects which are found (and lists any other databases which have objects in those tablespaces..). That would allow administrators who have existing notionally temporary-only tablespaces to go clean things up to make them actually temporary-only. That seems just about impossible from a concurrency standpoint (ie, what if someone is creating a new table in the tablespace concurrently with your check? Possibly in a different database?) Yeah, that's definitely an annoying complexity. I would certainly suggest that the first version of the patch not undertake to allow this property to be ALTERed; the cost-benefit ratio isn't there IMO. I suppose scheduling downtime to do the check manually across all databases, then drop and recreate the tablespace, would work. As someone who's working with a couple of these cases, it'd be awful nice if there was a way PG would handle it for me. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: Yes. I'd definitely like to see an ALTER TABLESPACE option, with an ERROR that lists out all of the non-temporary objects which are found (and lists any other databases which have objects in those tablespaces..). That would allow administrators who have existing notionally temporary-only tablespaces to go clean things up to make them actually temporary-only. I would certainly suggest that the first version of the patch not undertake to allow this property to be ALTERed; the cost-benefit ratio isn't there IMO. I suppose scheduling downtime to do the check manually across all databases, then drop and recreate the tablespace, would work. As someone who's working with a couple of these cases, it'd be awful nice if there was a way PG would handle it for me. I wonder if some form of NOT VALID marking could be useful here. Of course, this is not a constraint. But a mechanism of a similar spirit seems apropos. It seems reasonable to leave such a thing for future improvement. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
On Wed, Jun 18, 2014 at 4:53 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: Yes. I'd definitely like to see an ALTER TABLESPACE option, with an ERROR that lists out all of the non-temporary objects which are found (and lists any other databases which have objects in those tablespaces..). That would allow administrators who have existing notionally temporary-only tablespaces to go clean things up to make them actually temporary-only. I would certainly suggest that the first version of the patch not undertake to allow this property to be ALTERed; the cost-benefit ratio isn't there IMO. I suppose scheduling downtime to do the check manually across all databases, then drop and recreate the tablespace, would work. As someone who's working with a couple of these cases, it'd be awful nice if there was a way PG would handle it for me. I wonder if some form of NOT VALID marking could be useful here. Of course, this is not a constraint. But a mechanism of a similar spirit seems apropos. It seems reasonable to leave such a thing for future improvement. +1 Then, to summarize Matheus must do: * use an option instead of change the syntax and catalog to indicate that a tablespace is used to store temp objects * throw an exception if we try ALTER the option only_temp_relations * add regression tests * add documentation And, of course, register to the next open commitfest [1] to get detailed feedback and review. Regards, [1] https://commitfest.postgresql.org/ -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] How about a proper TEMPORARY TABLESPACE?
I was going to answer each message, but Fabrízio summarized everything so far really nicely. Thanks Fabrízio. On Wed, Jun 18, 2014 at 5:25 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Then, to summarize Matheus must do: * use an option instead of change the syntax and catalog to indicate that a tablespace is used to store temp objects Yes. I myself wasn't sure TEMPORARY syntax would be good, but I would just like to hear more about. Storing as an options seems nice to me. * throw an exception if we try ALTER the option only_temp_relations I think we should postpone the ALTER option, perhaps as another patch. Wasn't that what was decided? I'll investigate the options better before going this route. Let me work on CREATE first. * add regression tests * add documentation And, of course, register to the next open commitfest [1] to get detailed feedback and review. Noted. It will be on the next commitfest. Just knowing some people do want this make me more comfortable on working better. Best regards, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres
[HACKERS] How about a proper TEMPORARY TABLESPACE?
Hi Hackers,
I was facing a situation were we wanted to set temp_tablespaces to a
tablespace on a ephemeral disk (yes, it is AWS ephemeral disk), and I know
many users have faced the same situation. Although it seems safe to create
a tablespace on ephemeral disks if you use it to store only temporary files
(either created by queries or temp tables), PostgreSQL does not have such
information, and can't safely prevent a careless user of creating a
non-temporary relation on this tablespace.
Also, in case of a lost of this tablespace, its PG_TEMP_FILES_DIR should be
created by hand after recovering. That is fine actually, but many users may
not even noticed that.
So, what you guys think about letting PG know somehow that a tablespace is
temporary?
I have took some small time to make a PoC just to see if that is doable.
And so I did a new syntax like:
CREATE TABLESPACE spcname [TEMP | TEMPORARY] LOCATION ...
So, if TEMP or TEMPORARY is present, I mark a new column at pg_tablespace
as true. On every table creation or moving to a new tablespace, I just
check this, and fails if the tablespace is temporary but the
relpersistence says the table is not.
The other part is, every time some query or relation is asked to be stored
on this tablespace, I create (on-demand) the PG_TEMP_FILES_DIR inside of it
(also if it is temporary).
The attached patch (and also on my Github, [1]), shows the PoC. For now,
I'm not worried about the code quality, there are yet a lot of work to be
done there (like ALTER TABLESPACE, better testing, use relcache, etc...),
and it is my first hacking on PG (so I'm a newbie). But I'd like to hear
from you guys if such feature is desirable and if I could starting working
on that for real. Also some thoughts about better way of implementing it.
[1] https://github.com/matheusoliveira/postgres/compare/my_temptablespace
Waiting for thoughts on that.
Best regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
*** a/src/backend/commands/dbcommands.c
--- b/src/backend/commands/dbcommands.c
***
*** 388,399 createdb(const CreatedbStmt *stmt)
--- 388,406
aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
tablespacename);
+
/* pg_global must never be the default tablespace */
if (dst_deftablespace == GLOBALTABLESPACE_OID)
ereport(ERROR,
(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_storage_temporary(dst_deftablespace))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(cannot create a database on a tablespace in temporary storage)));
+
/*
* 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)
--- 1090,1101
(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_storage_temporary(dst_tblspcoid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(cannot create a database on a tablespace in temporary storage)));
+
/*
* 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_storage_temporary(OidIsValid(tablespaceId) ? tablespaceId : MyDatabaseTableSpace))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(cannot save relation on a tablespace in temporary storage)));
+ }
+
/*
* 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_storage_temporary(OidIsValid(tablespaceId) ? tablespaceId : MyDatabaseTableSpace))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(cannot save relation on a tablespace in temporary storage)));
+ }
+
/* Identify user ID that will own the table */
if (!OidIsValid(ownerId))
