Re: Index build temp files

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> We do have mechanism that forces search_path to be recomputed across
> changes of active role, but it's expensive to do that, and it seems
> of rather debatable value to do it here --- it certainly wouldn't
> improve Stephen's original problem, much less the other issues he
> raises here.

I agree that we would need something more for users to be able to be
able to tell what temp tablespace their temporary tables will end up in.
Generally, I think it'd be acceptable from a performance perspective to
figure out where to create the temporary objects at the time that they
are requested- at that point, you're going to be creating a new table,
index, or doing a large sort that spills to disk, all of which are
relatively heavy-weight operations.

> What would people think of just eliminating the access-permissions
> checks involved in temp_tablespaces?  It would likely be appropriate to
> change temp_tablespaces from USERSET to SUSET if we did so.  So
> essentially the worldview would become that the DBA is responsible for
> the temp_tablespaces setting, not individual users.

I believe it's important to be able to control what temp tablespaces are
used on a per-user basis and that 'set role;' or security definer
functions respect the tablespace which has been set for the current
role.  Temp tablespaces are extremely valuable for managing users who
unintentionally write run-away queries that fill up the tablespace.
Keeping those seperate from the temp tablespace used for SECURITY
DEFINER functions (which are written with care) or other ongoing system
activity is necessary.  Perhaps there would be a way to still do that
with your approach, but it didn't sound like it initially.

Perhaps we can simply keep track of what the current role was when we
cache'd which temp tablespace was selected for a given session and, if
the current role has changed, reconsider the temp tablespace selected?
We would need to update the documentation to reflect that you might not
*always* have the same tablespace for a session, if there are security
definer and/or set role's happening, but that seems reasonable to me.
Thanks,
    Stephen