Re: proposal: schema PL session variables

On 2/10/16 11:54 AM, Pavel Stehule wrote:
> 2016-02-09 23:41 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com
> <mailto:Jim.Nasby@bluetreble.com>>:
>     The other big thing you get is public vs private. You can
>     sorta-kinda-almost simulate that with permissions in simple cases,
>     but it ultimately falls apart as soon as you want a private function
>     that does something as the user calling the function.
>
>
> The schema variables are private by design. It can be enhanced in
> future, but now it is out my scope. If you need public access to these
> variables, you can use a functions. The access to functions can be
> controlled by a rights. We can introduce a private (schema limited)
> function too, but again it is out scope of this proposal.

So it's not possible for function schema_a.blah to access variables in 
schema_b? If it is then variables are NOT private.

>     When it comes to variables, I think it's a mistake to discuss this
>     patch while pretending that packages don't exist. For example all we
>     wanted were session variables, there's no reason they need to be
>     tied to schemas. The only reason to tie them to schemas is to try
>     and fake package support via schemas. I think it'd be a mistake to
>     have non-schema variables, but lets not fool ourselves as to why
>     that would be a mistake.
>
>
> I am happy, so you are opened the question about that package.
> Originally the Oracle package is a Ada language feature, but if you
> compare Oracle schemas and Postgresql schemas, you should to see a
> significant differences. Our schemas are much more similar to Oracle
> packages than Oracle schemas. So introduction of packages to Postgres is
> contra productive  -  will be pretty messy to have the packages and the
> schemas together. We don't need packages, because we have schemas, but
> we have not any safe (and simply used) schema scope tools. I implemented
> Orafce and the main problems there are not missing packages, but
> different default casting rules and missing procedures.

I'm not saying we have to implement packages the same way oracle did. Or 
at all.

My point is that there are MAJOR features that packages offer that we 
don't have at all, with or without schemas. One of those features is the 
idea of private objects. You CAN NOT do the same thing with permissions 
either, because public vs private doesn't care one iota about what role 
is executing something. They only care about what's in the call stack.

>     Another problem I have with this is it completely ignores
>     public/private session variables. The current claim is that's not a
>     big deal because you can only access the variables from a PL, but I
>     give it 2 days of this being released before people are asking for a
>     way to access the variables directly from SQL. Now you have a
>     problem because if you want private variables (which I think is
>     pretty important) you're only choice is to use SECDEF functions,
>     which is awkward at best.

While this patch doesn't need to implement SQL access to variables, I 
think the design needs to address it.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com