Re: Index build temp files

On 9 January 2013 23:12, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>> My view is it would fix the root cause of the problem, as explained.
>
> No; what it would do is provide a feature we don't currently have, ie
> the ability to let unprivileged users select a specific tablespace for
> temp files but not real tables. That may or may not be a useful feature
> (I do not recall any previous requests for it, and it's not immediately
> obvious why someone should be allowed to create 100GB of temp files but
> no not-temp files).

Mixing real data with temp files confuses things and complicates
planning for physical backups, which would be able to simpy skip a
tablespace if it was marked as temp. I guess that's an argument for
explicitly marking tablespaces as TEMP, rather than making it a
privilege.

ALTER TABLESPACE my_temp_space SET (usage = 'temp');

Options for usage would be TEMP or ANY

> But it does *not* address either of the key points
> that Stephen raised to begin with, which to my mind were:
>
> (1) It's unintuitive that setting temp_tablespaces in postgresql.conf
> isn't sufficient to make sessions use those tablespaces for temp files.
> (This is a definitional problem.)

Please compare with search_path which suffers just the same.

> (2) The current coding of the permissions tests is actively wrong,
> in that changes of role context in the backend don't affect what
> happens. (This is an implementation problem, but that doesn't mean
> it's simple to fix. Also, even if it acted as one might expect, there
> would still be unfortunate usability issues in sessions that actually
> do switch roles, as Stephen illustrated.)

Agreed

> Just checking a different permissions bit won't do a thing for either
> of those problems. I continue to suggest that the most effective fix
> for those issues is to reconsider what we want the overall behavior
> of temp_tablespaces to be in the first place. If we consider it to be
> controlled primarily by the DBA and not individual users, then we can
> make both (1) and (2) go away very easily.

As mentioned in my original suggestions, the fact that SHOW returns a
false view of the situation is at least as relevant as the point
above.

I'm not unhappy if you want to change the default, as long as it is revokeable.

Anyway, enough from me.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services