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?


            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.


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.


Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to