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; $$ LANGUAGE SQL;
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
