Re: 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