Re: [HACKERS] proposal: session server side variables

Hello Jim,

> 1) Variables would be completely non-transactional. [...] A solution to 
> this problem would be to provide a plpgsql equivalent to plperl or 
> plpython's session hashes.

That is what I have in mind with "session variables" à la MS/MY SQL, but 
at the SQL level, not PL/pgSQL level.

> 2) Variables provide permissions. Theoretically you could allow the 
> hypothetical plpgsql session variables in (1) to be marked private, but 
> that means you now have to keep all those variables on a per-role basis,

No, I think a common "session hash" could be used, with some basic prefix 
convention, there is no need to per-role hash.

> users are forced to create accessor functions,

Yes if the private variable should be accessed. If the variable is 
private, then it is private and nothing is needed. Idem for public.

> and you run a serious risk of confusion from getting the function 
> ownerships wrong.

One can get the permissions on special session variable wrong as well... I 
do not see how it differs.

> That certainly seems no better than defining permanent variables and 
> giving them permissions (as Pavel suggested).

As far as permissions is concerned, ISTM that it is the same.

The point is that there would be only usual fast "session variables", like 
MS/MY/Oracle SQL, and they could be used for Pavel rather special use case 
as well.

> More importantly, the security definer trick you're suggesting has a 
> fatal flaw: you can't call one SECDEF function from another SECDEF 
> function.

I do not see why there would be such a restriction?
 postgres@db> CREATE FUNCTION secfunc() RETURNS TEXT SECURITY DEFINER              AS $$ SELECT CURRENT_USER::TEXT; $$
LANGUAGESQL;
 
 fabien@db> CREATE FUNCTION secfunc2() RETURNS TEXT SECURITY DEFINER            AS $$ SELECT secfunc() || ' - ' ||
CURRENT_USER;$$ LANGUAGE SQL;
 
 *@db> SELECT secfunc2(); -- returns: "postgres - fabien" from both sides...

> So as soon as you have multiple privileged roles making use of 
> variables, there's a serious risk of not being able to make use of these 
> private variables at all.

?

> Now maybe pg_class is absolutely the wrong place to store info about 
> predefined variables, but that's an implementation detail, not a design 
> flaw.

Yes, but is not my only point.

> We should protect for the possibility of truly global (as in cross-session) 
> variables.

Yes, why not... Although having "cross-session session variable" seems to 
create some problems of its own... Would they be cross-database as well?

> Presumably these would have to be pre-defined via DDL before use.

Probably.

> These would be uniquely valuable as a means of communication between sessions
> that are connected to different databases.

Why not.

> I could also see use in cross-database in-memory queues. AFAIK both of 
> these would be pretty easy to do with the shared memory infrastructure 
> we now have.

Yep, that would means keeping some "session hash" in shared buffers.

> It would be nice if we could come up with a plan for what permanently defined 
> temp tables looked like, so the syntax and operation was similar to the 
> permanently defined session variables that Pavel is proposing.

Yes. And basic session variables as well.

> That said, given how long that has been an open issue I think it's 
> completely unfair to stonewall this feature if we can't get permanent 
> temp tables figured out.

...

> While permanent temp tables would eliminate some objections to store "session 
> variables", the fact still remains that any kind of table would still be 
> MVCC, and that is NOT always what you want.

Yes, I understood that. But sometimes it may be what is wanted.

> It would be nice if whatever syntax was decided for defined session variables 
> allowed room for "variables" that were actually MVCC, because sometimes that 
> actually is what you want.

Yep, that is what I'm arguing about, having a global variable design, and 
then maybe inside this design just a special case implemented.

> Yes, you could simulate the same thing with functions, but why make 
> users do all that work if we could easily provide the same 
> functionality?

The easy is unclear. Eg if special declared with permissions partially 
persistent session variables preclude future basic session variables, or 
their efficiency, or their syntax, it would be a problem. Hence the point 
of discussing before proceeding.

> These should probably be called something other than 
> "variables", but presumably all the other syntax and settings could be 
> the same. Again, it's not the job of this proposal to boil that ocean, 
> but it would be nice to leave the option open.

Yep.

-- 
Fabien.