Re: [HACKERS] proposal: schema PL session variables

2016-10-11 Thread Pavel Stehule
Hi

2016-02-23 20:52 GMT+01:00 Pavel Stehule :

>
>
> 2016-02-12 22:41 GMT+01:00 Jim Nasby :
>
>> On 2/12/16 2:58 PM, Pavel Stehule wrote:
>>
>>>
>>> So I think adding something like this needs to at least address
>>> *how* SQL level access would work, *when* it's eventually
>>> implemented.
>>>
>>>
>>> I understand - and I agree.
>>>
>>> small note: Private variables should not be executed from any SQL,
>>> because SQL has not directly related schema. It can be executed only
>>> from SQL embedded in some object with attached schema - PL functions,
>>> views, constraints, .. So for this use case, the important information
>>> is info about a container. We have to hold info about related schema in
>>> planner/executor context.
>>>
>>
>> I think that's probably true, but this also shows why we need to consider
>> different PLs too. As it stands right now, the only way to access a
>> variable outside of plpgsql would be to call a plpgsql function, and
>> currently there's no way to make a plpgsql function private. So for this to
>> work, private variables probably need to be exposed directly through the pl
>> handler.
>>
>> Again, I'm not saying this all has to be implemented up front, but there
>> needs to be a plan for how it would work.
>>
>> I think it would be a good idea to start a wiki page on this topic to
>> start collecting stuff together.
>
>
> I wrote some basic info - https://wiki.postgresql.org/
> wiki/CREATE_PRIVATE_VARIABLE
>

I though about this feature and now I am thinking so it is really similar
to sequences. Sure there are differences - but a workflow is pretty
similar. Created, dropped by CREATE, DROP statements, accessed with
functions everywhere (and in some iteration directly in PLpgSQL). The
content can be of any type stored in memory - session or transaction
closed. In first iteration initialized on default value when it is first
accessed in scope. Accessibility can be controlled by rights to schema.

syntax:

CREATE (SESSION|TRANSACTION) VARIABLE schema.xx datatype DEFAULT ...;
DROP VARIABLE schema.xx;

Access:

SELECT setvar(regclass, "any"); -- supported by Parser - enforcing "any" to
datatype
SELECT getvar(regclass) -- returns "any" -- supported by Parser --
enforcing "any" to datatype

The access rights on variables can be exactly same like rights on sequences.

Regards

Pavel





>
>
> I changed my opinion on initialization part. The private variables with
> non null default should be initialized in session start. It is much more
> practical and it can be used for triggering some ON CONNECT custom routines.
>
> Regards
>
> Pavel
>
>
>
>>
>> --
>> 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
>>
>
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-23 Thread Pavel Stehule
2016-02-12 22:41 GMT+01:00 Jim Nasby :

> On 2/12/16 2:58 PM, Pavel Stehule wrote:
>
>>
>> So I think adding something like this needs to at least address
>> *how* SQL level access would work, *when* it's eventually implemented.
>>
>>
>> I understand - and I agree.
>>
>> small note: Private variables should not be executed from any SQL,
>> because SQL has not directly related schema. It can be executed only
>> from SQL embedded in some object with attached schema - PL functions,
>> views, constraints, .. So for this use case, the important information
>> is info about a container. We have to hold info about related schema in
>> planner/executor context.
>>
>
> I think that's probably true, but this also shows why we need to consider
> different PLs too. As it stands right now, the only way to access a
> variable outside of plpgsql would be to call a plpgsql function, and
> currently there's no way to make a plpgsql function private. So for this to
> work, private variables probably need to be exposed directly through the pl
> handler.
>
> Again, I'm not saying this all has to be implemented up front, but there
> needs to be a plan for how it would work.
>
> I think it would be a good idea to start a wiki page on this topic to
> start collecting stuff together.


I wrote some basic info -
https://wiki.postgresql.org/wiki/CREATE_PRIVATE_VARIABLE

I changed my opinion on initialization part. The private variables with non
null default should be initialized in session start. It is much more
practical and it can be used for triggering some ON CONNECT custom routines.

Regards

Pavel



>
> --
> 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
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-12 Thread Pavel Stehule
2016-02-12 22:41 GMT+01:00 Jim Nasby :

> On 2/12/16 2:58 PM, Pavel Stehule wrote:
>
>> I think that's probably true, but this also shows why we need to consider
>> different PLs too. As it stands right now, the only way to access a
>> variable outside of plpgsql would be to call a plpgsql function, and
>> currently there's no way to make a plpgsql function private. So for this to
>> work, private variables probably need to be exposed directly through the pl
>> handler.
>>
>
>
> Again, I'm not saying this all has to be implemented up front, but there
> needs to be a plan for how it would work.
>
> I think it would be a good idea to start a wiki page on this topic to
> start collecting stuff together.


I'll do it. Thank you for comments

Regards

Pavel


>
> --
> 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
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-12 Thread Jim Nasby

On 2/10/16 1:29 PM, Pavel Stehule wrote:

I got off list mail with little bit different syntax proposal

CREATE VARIABLE xxx DEFAULT [ PRIVATE ]

I am thinking so more SQL natural is form:

CREATE [ PRIVATE ] VARIABLE xxx ...

There should not be only variables, there can be tables, views,
functions, ...

The "PRIVATE" in this context means - only accessible from current
schema. The syntax is different, than I propose, but the idea is same.


+1


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.


I don't understand well, and probably I don't explain my ideas well. But
this exactly what I would to implement. The security based on locality,
not based on roles.


+1 as well


 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.


SQL access to variables needs a) change in SQL parser (with difficult
discussion about syntax) or b) generic get/set functions. @b can be used
in other PL in first iteration.

I afraid to open pandora box and I would to hold the scope of this patch
too small what is possible - to be possible implement it in one release
cycle.


I agree about implementation. I disagree about design.

Right now it appears zero thought has gone into what SQL level access 
would eventually look like. Which means there's a high risk that 
something gets implemented now that we regret later.


So I think adding something like this needs to at least address *how* 
SQL level access would work, *when* it's eventually implemented.

--
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


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-12 Thread Jim Nasby

On 2/12/16 2:58 PM, Pavel Stehule wrote:


So I think adding something like this needs to at least address
*how* SQL level access would work, *when* it's eventually implemented.


I understand - and I agree.

small note: Private variables should not be executed from any SQL,
because SQL has not directly related schema. It can be executed only
from SQL embedded in some object with attached schema - PL functions,
views, constraints, .. So for this use case, the important information
is info about a container. We have to hold info about related schema in
planner/executor context.


I think that's probably true, but this also shows why we need to 
consider different PLs too. As it stands right now, the only way to 
access a variable outside of plpgsql would be to call a plpgsql 
function, and currently there's no way to make a plpgsql function 
private. So for this to work, private variables probably need to be 
exposed directly through the pl handler.


Again, I'm not saying this all has to be implemented up front, but there 
needs to be a plan for how it would work.


I think it would be a good idea to start a wiki page on this topic to 
start collecting stuff together.

--
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


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-12 Thread Pavel Stehule
Hi



>> SQL access to variables needs a) change in SQL parser (with difficult
>> discussion about syntax) or b) generic get/set functions. @b can be used
>> in other PL in first iteration.
>>
>> I afraid to open pandora box and I would to hold the scope of this patch
>> too small what is possible - to be possible implement it in one release
>> cycle.
>>
>
> I agree about implementation. I disagree about design.
>
> Right now it appears zero thought has gone into what SQL level access
> would eventually look like. Which means there's a high risk that something
> gets implemented now that we regret later.
>
> So I think adding something like this needs to at least address *how* SQL
> level access would work, *when* it's eventually implemented.


I understand - and I agree.

small note: Private variables should not be executed from any SQL, because
SQL has not directly related schema. It can be executed only from SQL
embedded in some object with attached schema - PL functions, views,
constraints, .. So for this use case, the important information is info
about a container. We have to hold info about related schema in
planner/executor context.

Regards

Pavel


>
> --
> 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
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
2016-02-09 23:31 GMT+01:00 Jim Nasby :

> On 2/8/16 10:02 AM, Pavel Stehule wrote:
>
>>
>> I think it would make sense to implement the interface in at least
>> one of our other supported PLs. I'm not entirely clear how well this
>> will match up with, say, plperl, but I'd be interested to see.
>>
>>
>> The minimalistic interface can be based on get/set functions. We can do
>> necessary transformations there.
>>
>
> get/set functions where?
>
> I don't think that really makes sense. I would expect schema variables to
> be exposed to a function as variables or attributes, either in the global
> namespace for that PL, or as an attribute of some object (ie the plpy
> object in plpython).
>

I don't know a python, and I don't know what is possible there and what I
know. Set/Get function I can implement in any PL other than PLpgSQL. You
have to do conversion from Postgres type to PL types and I can do it in
function.



>
> I certainly wouldn't expect this patch to do that for all existing PLs,
> but I think it's important to do it for one PL besides plpgsql to make sure
> there's no gotchas.
> --
> 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
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
Hi

Would it make sense to explicitly import variables in function definitions?
>
> CREATE SESSION VARIABLE foo integer;
> CREATE SESSION VARIABLE my_schema.bar text;
> SET SESSION VARIABLE foo to 4;
> SET SESSION VARIABLE my_schema.bar to 'hi mom';
>
> CREATE FUNCTION my_func (p_param text) returns boolean
> LANGUAGE SQL
> IMPORT g_foo integer FROM foo,
> IMPORT g_textval IN OUT text FROM my_schema.bar
>
> AS $$
>
> SELECT COUNT(*) > 1
> FROM my_table
> WHERE id = g_foo
> AND name = g_textval;
> $$;
>
>
> The IMPORT clause would be something like:
>
> IMPORT local_var_name [IN] [OUT] type FROM [session variable | expression ]
>
>
It cannot be implemented in SQL language, because there are not other
variables than function parameters.

It is possible in PLpgSQL, but I prefer the ALIAS keyword - introduction
new reserved keyword introduces a compatibility issues.


>
> And obviously it would reject importing an expression as an OUT type.
> Importing an expression would largely serve the purpose of compile-time
> macro, or allowing us to pass parameters into anonymous blocks, something
> we've wanted for a while now.
>
> With something like this, the session variables are seen as parameters
> inside the function regardless of language and with no new prefix, :, @, or
> otherwise.
>
> Oh, and I suggest we call them SESSION variables rather than SCHEMA
> variables, to reinforce the idea of how long the values in the variables
> live. A session variable is in a sense a 1x1 temp table, whose definition
> persists across sessions but whose value does not.
>

I didn't propose SESSION variables - now there are some workarounds how to
anybody can emulate it, so this feature can wait. What we need is safe
session variables with limited access. And the border can be defined by
schema scope. So the keyword SCHEMA has sense, and it is necessary.


>
> Of course, if they do persist across sessions, then yeah, SCHEMA makes
> more sense. But every package variable in Oracle PL/SQL was initialized
> when the package was first loaded into the session.
>
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
2016-02-09 20:55 GMT+01:00 David G. Johnston :

> On Tue, Feb 9, 2016 at 11:32 AM, Corey Huinker 
> wrote:
>
>>
>> Oh, and I suggest we call them SESSION variables rather than SCHEMA
>> variables, to reinforce the idea of how long the values in the variables
>> live. A session variable is in a sense a 1x1 temp table, whose definition
>> persists across sessions but whose value does not.
>>
>> Of course, if they do persist across sessions, then yeah, SCHEMA makes
>> more sense. But every package variable in Oracle PL/SQL was initialized
>> when the package was first loaded into the session.
>>
>>
> ​The key distinction for SCHEMA was that all functions in the schema would
> be able to see them (and only those in the schema).
>
> I am a bit partial, with little deep thought, to the IMPORT mechanic.
> Changing them to actual session variables would be doable and you could
> allow for the IMPORT specification to use search_path or explicit means to
> locate said variables regardless of which schema​
>
> ​they exist in.
>

Very important part of my proposal is independence on search_path. With
search_path you have not any control over variable type, variable existence
- and there are possible lot of impacts on plan cache, behave. So I propose
SCHEMA VARIABLES with schema scope - and then search_path has zero effect
on the behave. It doesn't introduce new dependencies.

Pavel

>
> However, part of the goal is to blend into the broader database community
> and thus increase porting capabilities.  I'm not sure how well this would
> help fulfill that goal.
>
> David J.
> ​
>
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby

On 2/10/16 11:25 AM, Pavel Stehule wrote:


Oh, and I suggest we call them SESSION variables rather than SCHEMA
variables, to reinforce the idea of how long the values in the
variables live. A session variable is in a sense a 1x1 temp table,
whose definition persists across sessions but whose value does not.


I didn't propose SESSION variables - now there are some workarounds how
to anybody can emulate it, so this feature can wait. What we need is
safe session variables with limited access. And the border can be
defined by schema scope. So the keyword SCHEMA has sense, and it is
necessary.


BTW, if all that's desired here are session variables for plpgsql, I 
think it makes a lot more sense to start with implementing per-function 
session variables. That's a lot simpler design-wise and is something we 
should have anyway. You don't necessarily want session variables to be 
schema-level. (I realize the other PLs make them global, which is even 
worse, but that's no reason to continue that path.)

--
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


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
>> I didn't propose SESSION variables - now there are some workarounds how
>> to anybody can emulate it, so this feature can wait. What we need is
>> safe session variables with limited access. And the border can be
>> defined by schema scope. So the keyword SCHEMA has sense, and it is
>> necessary.
>>
>
> BTW, if all that's desired here are session variables for plpgsql, I think
> it makes a lot more sense to start with implementing per-function session
> variables. That's a lot simpler design-wise and is something we should have
> anyway. You don't necessarily want session variables to be schema-level. (I
> realize the other PLs make them global, which is even worse, but that's no
> reason to continue that path.)


I am not able to implement SET and GET content in one function effectively.
I believe so static variables can be enough for 50%, but it is too limited.
Postgres cannot to pass and work with references, so this C design can be
too expensive.

Regards

Pavel


>
> --
> 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
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
2016-02-10 20:25 GMT+01:00 Jim Nasby :

> On 2/10/16 1:17 PM, Pavel Stehule wrote:It is too simple and too like
> workaround :) I can do it this in plpgsql
>
>> extension probably.
>>
>
> I think it's something people will definitely want. If we don't have it,
> then they're going to be using schema variables as a work-around because
> they can't do a private static variable inside a single function.
>

the schema variables can be used for different purpose, but different
direction isn't possible. So I am starting with schema variables.

But schema variables doesn't block implementation private static variables,
and probably if we will have static variables, then schema variables can
reuse (or use) related code well.


>
> Most importantly, since this effects only plpgsql and only
>> individual functions, the design is simple and should be easy to
>> commit in 9.6. I don't have the same confidence with schema variables.
>>
>>
>> My target is not 9.6 - next commitfest will be full - finishing multi
>> CPU queries, logical replication, .. and I have still three opened
>> patches. But if we find a agreement in this spring, I can implement it
>> in summer, and it can be in upstream in early 9.7 commitfest. I know,
>> this topic is difficult, so have to start it now.
>>
>
> Sure. I think it would be useful to have a wiki page with info as it gets
> ironed out. A good starting point would be use cases. One that I don't
> think has been considered is different extensions adding/using different
> schema variables. Questions like should extension A have direct access to
> variables for extension B.


yes, it is question.

My reply is - not (my opinion), minimally in first iteration. a) I can use
a functions, b) direct sharing content (variables) between extensions,
schemas is not recommended generally (it is not just only my idea).

second question is "why you need direct access to variables between
extensions, schemas?". Can you write some use cases?

Maybe we need a different class for this purpose -  some stream (pipe)
between extensions. But this is out of this scope. Although it is pretty
valid.

Regards

Pavel



>
> --
> 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
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby

On 2/10/16 1:04 PM, Pavel Stehule wrote:


BTW, if all that's desired here are session variables for plpgsql, I
think it makes a lot more sense to start with implementing
per-function session variables. That's a lot simpler design-wise and
is something we should have anyway. You don't necessarily want
session variables to be schema-level. (I realize the other PLs make
them global, which is even worse, but that's no reason to continue
that path.)


I am not able to implement SET and GET content in one function
effectively. I believe so static variables can be enough for 50%, but it
is too limited. Postgres cannot to pass and work with references, so
this C design can be too expensive.


You can always accept a boolean that tells you if you're setting or just 
returning. And there's probably some use cases where you don't even need 
to expose the variable outside of the function.


Most importantly, since this effects only plpgsql and only individual 
functions, the design is simple and should be easy to commit in 9.6. I 
don't have the same confidence with schema variables.

--
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


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby

On 2/10/16 11:33 AM, Pavel Stehule wrote:


I don't think that really makes sense. I would expect schema
variables to be exposed to a function as variables or attributes,
either in the global namespace for that PL, or as an attribute of
some object (ie the plpy object in plpython).


I don't know a python, and I don't know what is possible there and what
I know. Set/Get function I can implement in any PL other than PLpgSQL.
You have to do conversion from Postgres type to PL types and I can do it
in function.


Requiring PLs to go through the native SPI to get to schema variables is 
just plain ugly and inefficient. Why wouldn't they be exposed natively?


Not designing these things through is how we ended up with the mess that 
is composite types and arrays in plpython and pltcl. I'm not saying any 
PLs besides plpgsql need to support this natively out of the box, but we 
better have an idea of how they would support it (and it'd be a good 
idea if at least one other PL did support 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


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
2016-02-10 20:10 GMT+01:00 Jim Nasby :

> On 2/10/16 1:04 PM, Pavel Stehule wrote:
>
>>
>> BTW, if all that's desired here are session variables for plpgsql, I
>> think it makes a lot more sense to start with implementing
>> per-function session variables. That's a lot simpler design-wise and
>> is something we should have anyway. You don't necessarily want
>> session variables to be schema-level. (I realize the other PLs make
>> them global, which is even worse, but that's no reason to continue
>> that path.)
>>
>>
>> I am not able to implement SET and GET content in one function
>> effectively. I believe so static variables can be enough for 50%, but it
>> is too limited. Postgres cannot to pass and work with references, so
>> this C design can be too expensive.
>>
>
> You can always accept a boolean that tells you if you're setting or just
> returning. And there's probably some use cases where you don't even need to
> expose the variable outside of the function.
>

It is too simple and too like workaround :) I can do it this in plpgsql
extension probably.


> Most importantly, since this effects only plpgsql and only individual
> functions, the design is simple and should be easy to commit in 9.6. I
> don't have the same confidence with schema variables.


My target is not 9.6 - next commitfest will be full - finishing multi CPU
queries, logical replication, .. and I have still three opened patches. But
if we find a agreement in this spring, I can implement it in summer, and it
can be in upstream in early 9.7 commitfest. I know, this topic is
difficult, so have to start it now.

Regards

Pavel


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby

On 2/10/16 11:54 AM, Pavel Stehule wrote:

2016-02-09 23:41 GMT+01:00 Jim Nasby >:
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


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby

On 2/10/16 1:17 PM, Pavel Stehule wrote:



2016-02-10 20:10 GMT+01:00 Jim Nasby >:

On 2/10/16 1:04 PM, Pavel Stehule wrote:


 BTW, if all that's desired here are session variables for
plpgsql, I
 think it makes a lot more sense to start with implementing
 per-function session variables. That's a lot simpler
design-wise and
 is something we should have anyway. You don't necessarily want



It is too simple and too like workaround :) I can do it this in plpgsql
extension probably.


I think it's something people will definitely want. If we don't have it, 
then they're going to be using schema variables as a work-around because 
they can't do a private static variable inside a single function.



Most importantly, since this effects only plpgsql and only
individual functions, the design is simple and should be easy to
commit in 9.6. I don't have the same confidence with schema variables.


My target is not 9.6 - next commitfest will be full - finishing multi
CPU queries, logical replication, .. and I have still three opened
patches. But if we find a agreement in this spring, I can implement it
in summer, and it can be in upstream in early 9.7 commitfest. I know,
this topic is difficult, so have to start it now.


Sure. I think it would be useful to have a wiki page with info as it 
gets ironed out. A good starting point would be use cases. One that I 
don't think has been considered is different extensions adding/using 
different schema variables. Questions like should extension A have 
direct access to variables for extension B.

--
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


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
>>
>> 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.
>

the scope is schema. It is private for schema objects. The access to
content is limited from objects from same schema. I know so this concept is
new in Postgres, but I hope so it is useful. Anybody can expect different
behave related to some technical terms - so maybe "private" keyword isn't
best in this moment. I just variables with possible safe (limited) access.
Anything else I can do with functionality that we have already.

I got off list mail with little bit different syntax proposal

CREATE VARIABLE xxx DEFAULT [ PRIVATE ]

I am thinking so more SQL natural is form:

CREATE [ PRIVATE ] VARIABLE xxx ...

There should not be only variables, there can be tables, views, functions,
...

The "PRIVATE" in this context means - only accessible from current schema.
The syntax is different, than I propose, but the idea is same.


>
> 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.
>

I don't understand well, and probably I don't explain my ideas well. But
this exactly what I would to implement. The security based on locality, not
based on roles.


>
> 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.


SQL access to variables needs a) change in SQL parser (with difficult
discussion about syntax) or b) generic get/set functions. @b can be used in
other PL in first iteration.

I afraid to open pandora box and I would to hold the scope of this patch
too small what is possible - to be possible implement it in one release
cycle.

Regards

Pavel


>
> --
> 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
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
Hi

2016-02-09 23:41 GMT+01:00 Jim Nasby :

> On 2/9/16 4:13 PM, Corey Huinker wrote:
>
>>
>> We're not going to get source compatibility without implementing
>> packages, and there's no enthusiasm for that. It's been stated a few
>> times before by some that the only value they see in packages is the
>> package/session variables. Pavel's idea gives us that.
>>
>
> 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.


> 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.


>
> 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.
>
> I forgot to mention that if we're FROM-phobic the syntax could also be
>>
>> IMPORT my_schema.bar AS g_localtext IN OUT text
>>
>> Either way, you get the idea: the function defines what external globals
>> it's willing to see, and gives an alias for them, and it's the same
>> regardless of what the function language is.
>>
>
> ISTM that for plpgsql it would be better to add a namespace level above
> the current top level (which is the function level).


It is. Outer function level is a schema.

I though about possible feature:

DECLARE xxx int%SCHEMASCOPE;

But it can be pretty difficult checked - other function can has "DECLARE
xxx bigint%SCHEMASCOPE;" and what is valid version. Currently we can do
validation of any function without checking any other functions. If I miss
extern living object, then I have to do validation all functions in schema
together. What is much more expensive. I don't would to introduce slower
Oracle compilations and dependency issues. So I need externally created
object. It was reason, why I used a statement CREATE instead statement
DECLARE.

Regards

Pavel

Regards

Pavel


Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Marko Tiikkaja

On 08/02/16 14:16, Pavel Stehule wrote:

2016-02-08 13:53 GMT+01:00 Marko Tiikkaja :


Yeah, and that's exactly what I don't want, because that means that CREATE
SCHEMA VARIABLE suddenly breaks existing code.



theoretically yes, but this conflict can be 100% detected - so no quiet bug
is possible, and plpgsql_check can find this issue well. If you don't use
schema variable, then your code will be correct. You have to explicitly
create the variable, and if there will be any problem, then the problem
will be only in PL functions in one schema. And you can identify it by
statical analyse.


I'm sorry, but I think you've got your priorities completely backwards. 
 You're saying that it's OK to add a footgun because blown-off pieces 
of feet can be found by using a third party static analyzer barely 
anyone uses.  And at best, that footgun is only a very minor convenience 
(though I'd argue that omitting it actually hurts readability).


That makes absolutely no sense to me at all.


.m


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Pavel Stehule
2016-02-09 15:32 GMT+01:00 Marko Tiikkaja :

> On 08/02/16 14:16, Pavel Stehule wrote:
>
>> 2016-02-08 13:53 GMT+01:00 Marko Tiikkaja :
>>
>>>
>>> Yeah, and that's exactly what I don't want, because that means that
>>> CREATE
>>> SCHEMA VARIABLE suddenly breaks existing code.
>>>
>>>
>> theoretically yes, but this conflict can be 100% detected - so no quiet
>> bug
>> is possible, and plpgsql_check can find this issue well. If you don't use
>> schema variable, then your code will be correct. You have to explicitly
>> create the variable, and if there will be any problem, then the problem
>> will be only in PL functions in one schema. And you can identify it by
>> statical analyse.
>>
>
> I'm sorry, but I think you've got your priorities completely backwards.
> You're saying that it's OK to add a footgun because blown-off pieces of
> feet can be found by using a third party static analyzer barely anyone
> uses.  And at best, that footgun is only a very minor convenience (though
> I'd argue that omitting it actually hurts readability).
>

I don't block the integration plpgsql_check to upstream. I spent hundreds
hours for it.

Can we look on this problem with different side? What I can do it for safe
using proposed schema variables.

The possible ways:

1. requirement prefix like : or @. I don't prefer it because a) hard to
find a agreement - Oracle fans like ":", MSSQL like @, other maybe $, b)
with any currently unsupported syntax I have to fix SQL lexer, parser

2. requirement to use qualified name everywhere - it can works, but I don't
prefer it, because sometimes can be unfunny to write long qualified
identifiers. There are not aliases on schema in PLpgSQL. Possible solved by
variable aliases. But it requires alias.

3. plpgsql GUC where schema variables are: a) disabled, b) enabled, c) only
qualified names are allowed - it is similar to #variable_conflict option

I prefer @3 with "c" as default, but I can live with @2, and dislike @1 due
mentioned reasons.

Can you be satisfied by any mentioned variant?

Regards

Pavel


>
> That makes absolutely no sense to me at all.
>
>
> .m
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Jim Nasby

On 2/9/16 4:13 PM, Corey Huinker wrote:


We're not going to get source compatibility without implementing
packages, and there's no enthusiasm for that. It's been stated a few
times before by some that the only value they see in packages is the
package/session variables. Pavel's idea gives us that.


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.


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.


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.



I forgot to mention that if we're FROM-phobic the syntax could also be

IMPORT my_schema.bar AS g_localtext IN OUT text

Either way, you get the idea: the function defines what external globals
it's willing to see, and gives an alias for them, and it's the same
regardless of what the function language is.


ISTM that for plpgsql it would be better to add a namespace level above 
the current top level (which is the function level).

--
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


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Corey Huinker
On Tue, Feb 9, 2016 at 2:55 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, Feb 9, 2016 at 11:32 AM, Corey Huinker 
> wrote:
>
>>
>> Oh, and I suggest we call them SESSION variables rather than SCHEMA
>> variables, to reinforce the idea of how long the values in the variables
>> live. A session variable is in a sense a 1x1 temp table, whose definition
>> persists across sessions but whose value does not.
>>
>> Of course, if they do persist across sessions, then yeah, SCHEMA makes
>> more sense. But every package variable in Oracle PL/SQL was initialized
>> when the package was first loaded into the session.
>>
>>
> ​The key distinction for SCHEMA was that all functions in the schema would
> be able to see them (and only those in the schema).
>
> I am a bit partial, with little deep thought, to the IMPORT mechanic.
> Changing them to actual session variables would be doable and you could
> allow for the IMPORT specification to use search_path or explicit means to
> locate said variables regardless of which schema​
>
> ​they exist in.
>
> However, part of the goal is to blend into the broader database community
> and thus increase porting capabilities.  I'm not sure how well this would
> help fulfill that goal.
>
>
We're not going to get source compatibility without implementing packages,
and there's no enthusiasm for that. It's been stated a few times before by
some that the only value they see in packages is the package/session
variables. Pavel's idea gives us that.

I forgot to mention that if we're FROM-phobic the syntax could also be

IMPORT my_schema.bar AS g_localtext IN OUT text

Either way, you get the idea: the function defines what external globals
it's willing to see, and gives an alias for them, and it's the same
regardless of what the function language is.


Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Jim Nasby

On 2/8/16 10:02 AM, Pavel Stehule wrote:


I think it would make sense to implement the interface in at least
one of our other supported PLs. I'm not entirely clear how well this
will match up with, say, plperl, but I'd be interested to see.


The minimalistic interface can be based on get/set functions. We can do
necessary transformations there.


get/set functions where?

I don't think that really makes sense. I would expect schema variables 
to be exposed to a function as variables or attributes, either in the 
global namespace for that PL, or as an attribute of some object (ie the 
plpy object in plpython).


I certainly wouldn't expect this patch to do that for all existing PLs, 
but I think it's important to do it for one PL besides plpgsql to make 
sure there's no gotchas.

--
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


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread David G. Johnston
On Tue, Feb 9, 2016 at 11:32 AM, Corey Huinker 
wrote:

>
> Oh, and I suggest we call them SESSION variables rather than SCHEMA
> variables, to reinforce the idea of how long the values in the variables
> live. A session variable is in a sense a 1x1 temp table, whose definition
> persists across sessions but whose value does not.
>
> Of course, if they do persist across sessions, then yeah, SCHEMA makes
> more sense. But every package variable in Oracle PL/SQL was initialized
> when the package was first loaded into the session.
>
>
​The key distinction for SCHEMA was that all functions in the schema would
be able to see them (and only those in the schema).

I am a bit partial, with little deep thought, to the IMPORT mechanic.
Changing them to actual session variables would be doable and you could
allow for the IMPORT specification to use search_path or explicit means to
locate said variables regardless of which schema​

​they exist in.

However, part of the goal is to blend into the broader database community
and thus increase porting capabilities.  I'm not sure how well this would
help fulfill that goal.

David J.
​


Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Corey Huinker
On Tue, Feb 9, 2016 at 9:58 AM, Pavel Stehule 
wrote:

>
>
> 2016-02-09 15:32 GMT+01:00 Marko Tiikkaja :
>
>> On 08/02/16 14:16, Pavel Stehule wrote:
>>
>>> 2016-02-08 13:53 GMT+01:00 Marko Tiikkaja :
>>>

 Yeah, and that's exactly what I don't want, because that means that
 CREATE
 SCHEMA VARIABLE suddenly breaks existing code.


>>> theoretically yes, but this conflict can be 100% detected - so no quiet
>>> bug
>>> is possible, and plpgsql_check can find this issue well. If you don't use
>>> schema variable, then your code will be correct. You have to explicitly
>>> create the variable, and if there will be any problem, then the problem
>>> will be only in PL functions in one schema. And you can identify it by
>>> statical analyse.
>>>
>>
>> I'm sorry, but I think you've got your priorities completely backwards.
>> You're saying that it's OK to add a footgun because blown-off pieces of
>> feet can be found by using a third party static analyzer barely anyone
>> uses.  And at best, that footgun is only a very minor convenience (though
>> I'd argue that omitting it actually hurts readability).
>>
>
> I don't block the integration plpgsql_check to upstream. I spent hundreds
> hours for it.
>
> Can we look on this problem with different side? What I can do it for safe
> using proposed schema variables.
>
> The possible ways:
>
> 1. requirement prefix like : or @. I don't prefer it because a) hard to
> find a agreement - Oracle fans like ":", MSSQL like @, other maybe $, b)
> with any currently unsupported syntax I have to fix SQL lexer, parser
>
> 2. requirement to use qualified name everywhere - it can works, but I
> don't prefer it, because sometimes can be unfunny to write long qualified
> identifiers. There are not aliases on schema in PLpgSQL. Possible solved by
> variable aliases. But it requires alias.
>
> 3. plpgsql GUC where schema variables are: a) disabled, b) enabled, c)
> only qualified names are allowed - it is similar to #variable_conflict
> option
>
> I prefer @3 with "c" as default, but I can live with @2, and dislike @1
> due mentioned reasons.
>
> Can you be satisfied by any mentioned variant?
>
> Regards
>
> Pavel
>
>
>>
>> That makes absolutely no sense to me at all.
>>
>>
>> .m
>>
>
>
Would it make sense to explicitly import variables in function definitions?

CREATE SESSION VARIABLE foo integer;
CREATE SESSION VARIABLE my_schema.bar text;
SET SESSION VARIABLE foo to 4;
SET SESSION VARIABLE my_schema.bar to 'hi mom';

CREATE FUNCTION my_func (p_param text) returns boolean
LANGUAGE SQL
IMPORT g_foo integer FROM foo,
IMPORT g_textval IN OUT text FROM my_schema.bar

AS $$

SELECT COUNT(*) > 1
FROM my_table
WHERE id = g_foo
AND name = g_textval;
$$;


The IMPORT clause would be something like:

IMPORT local_var_name [IN] [OUT] type FROM [session variable | expression ]


And obviously it would reject importing an expression as an OUT type.
Importing an expression would largely serve the purpose of compile-time
macro, or allowing us to pass parameters into anonymous blocks, something
we've wanted for a while now.

With something like this, the session variables are seen as parameters
inside the function regardless of language and with no new prefix, :, @, or
otherwise.

Oh, and I suggest we call them SESSION variables rather than SCHEMA
variables, to reinforce the idea of how long the values in the variables
live. A session variable is in a sense a 1x1 temp table, whose definition
persists across sessions but whose value does not.

Of course, if they do persist across sessions, then yeah, SCHEMA makes more
sense. But every package variable in Oracle PL/SQL was initialized when the
package was first loaded into the session.


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
2016-02-08 16:45 GMT+01:00 jflack :

> On 02/08/2016 03:16 AM, Pavel Stehule wrote:
>
> > Only a owner of schema can edit functions inside schema
>
> Can't anyone granted CREATE on the schema do that? Would
> that be changed by this proposal?
>

yes, anybody with necessary rights can do it.

regards

Pavel


>
> -Chap
>
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
Hi


>> I propose really basic functionality, that can be enhanced in future -
>> step by step. This proposal doesn't contain any controversial feature or
>> syntax, I hope. It is related to PLpgSQL only, but described feature can be
>> used from any PL languages with implemented interface.
>>
>
>
> I think it would make sense to implement the interface in at least one of
> our other supported PLs. I'm not entirely clear how well this will match up
> with, say, plperl, but I'd be interested to see.
>

The minimalistic interface can be based on get/set functions. We can do
necessary transformations there.

Regards

Pavel


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Chapman Flack
[resending because thunderbird helpfully defaulted my sender
address to the one that -isn't- subscribed to -hackers, sorry]


On 02/08/2016 03:16 AM, Pavel Stehule wrote:

> Only a owner of schema can edit functions inside schema

Can't anyone granted CREATE on the schema do that? Would
that be changed by this proposal?

-Chap



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


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Marko Tiikkaja

On 08/02/16 09:16, Pavel Stehule wrote:

Usage
=

DROP SCHEMA IF EXISTS test_schema CASCADE;
SET SCHEMA test_schema;

CREATE SCHEMA VARIABLE local_counter AS int DEFAULT 0;

CREATE OR REPLACE FUNCTION increment_counter()
RETURNS void AS $$
BEGIN
   local_counter := local_counter + 1;
END;
$$ LANGUAGE plpgsql;


How does this function know which schema variables are visible?


.m


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Marko Tiikkaja

On 08/02/16 13:17, Pavel Stehule wrote:

2016-02-08 13:03 GMT+01:00 Marko Tiikkaja :

How does this function know which schema variables are visible?


function see all schema variables from same schema as function's schema


Personally I find that undesirable.  I don't know what oracle does, but 
variables being visible without schema-qualifying them can introduce 
variable conflicts in PL/PgSQL.  I'd prefer if you could only refer to 
them by prefixing them with the schema name (or maybe allow search_path 
to be used).



.m


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Marko Tiikkaja

On 08/02/16 13:41, Pavel Stehule wrote:

2016-02-08 13:22 GMT+01:00 Marko Tiikkaja :

Personally I find that undesirable.  I don't know what oracle does, but
variables being visible without schema-qualifying them can introduce
variable conflicts in PL/PgSQL.  I'd prefer if you could only refer to them
by prefixing them with the schema name (or maybe allow search_path to be
used).


I hope so there are not new conflicts - schema variable is not directly
visible from SQL (in this iteration) - they are visible only from functions
- and the behave is same like global plpgsql variable. So schema variable
can be in conflict with SQL identifier only exactly identically as plpgsql
variable


Yeah, and that's exactly what I don't want, because that means that 
CREATE SCHEMA VARIABLE suddenly breaks existing code.



But prefix can be used.


Sure, but I don't see the point.  Is there a reason not to require such 
variable references to be prefixed with the schema name?  Or explicitly 
bring them into scope in the DECLARE section somehow.



.m


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
2016-02-08 13:03 GMT+01:00 Marko Tiikkaja :

> On 08/02/16 09:16, Pavel Stehule wrote:
>
>> Usage
>> =
>>
>> DROP SCHEMA IF EXISTS test_schema CASCADE;
>> SET SCHEMA test_schema;
>>
>> CREATE SCHEMA VARIABLE local_counter AS int DEFAULT 0;
>>
>> CREATE OR REPLACE FUNCTION increment_counter()
>> RETURNS void AS $$
>> BEGIN
>>local_counter := local_counter + 1;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>
> How does this function know which schema variables are visible?
>

function see all schema variables from same schema as function's schema

Pavel


>
>
> .m
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
2016-02-08 13:22 GMT+01:00 Marko Tiikkaja :

> On 08/02/16 13:17, Pavel Stehule wrote:
>
>> 2016-02-08 13:03 GMT+01:00 Marko Tiikkaja :
>>
>>> How does this function know which schema variables are visible?
>>>
>>
>> function see all schema variables from same schema as function's schema
>>
>
> Personally I find that undesirable.  I don't know what oracle does, but
> variables being visible without schema-qualifying them can introduce
> variable conflicts in PL/PgSQL.  I'd prefer if you could only refer to them
> by prefixing them with the schema name (or maybe allow search_path to be
> used).
>

I hope so there are not new conflicts - schema variable is not directly
visible from SQL (in this iteration) - they are visible only from functions
- and the behave is same like global plpgsql variable. So schema variable
can be in conflict with SQL identifier only exactly identically as plpgsql
variable, and cannot be in conflict with PLpgSQL variable, because any
plpgsql variable can overwrite it. But prefix can be used.

example:

CREATE OR REPLACE FUNCTION increment_counter()
RETURNS void AS $$
BEGIN
  test_schema.local_counter := test_schema.local_counter + 1;
END;
$$ LANGUAGE plpgsql;

I would not to allow dependency on SEARCH_PATH, because then the change of
SEARCH_PATH can require replanning and possibly can change result type. So
using SEARCH PATH is way to hell. More I would to "protect" content of
variable - and the schema scope can work like good guard. If you need
public visible variables, then you can use trivial functions, that will do
it - and publish content by functions.

Regards

Pavel




>
>
> .m
>


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Andrew Dunstan



On 02/08/2016 03:16 AM, Pavel Stehule wrote:

Hi

On Russian PgConf I had a talk with Oleg about missing features in 
PLpgSQL, that can complicates a migrations from Oracle to PostgreSQL. 
Currently I see only one blocker - missing protected session 
variables. PL/SQL has package variables with possible only package 
scope and session life cycle. Currently we cannot to ensure/enforce 
schema scope visibility - and we cannot to implement this 
functionality in PL languages other than C.


I propose really basic functionality, that can be enhanced in future - 
step by step. This proposal doesn't contain any controversial feature 
or syntax, I hope. It is related to PLpgSQL only, but described 
feature can be used from any PL languages with implemented interface.



I think it would make sense to implement the interface in at least one 
of our other supported PLs. I'm not entirely clear how well this will 
match up with, say, plperl, but I'd be interested to see.



cheers

andrew


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


Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
2016-02-08 13:53 GMT+01:00 Marko Tiikkaja :

> On 08/02/16 13:41, Pavel Stehule wrote:
>
>> 2016-02-08 13:22 GMT+01:00 Marko Tiikkaja :
>>
>>> Personally I find that undesirable.  I don't know what oracle does, but
>>> variables being visible without schema-qualifying them can introduce
>>> variable conflicts in PL/PgSQL.  I'd prefer if you could only refer to
>>> them
>>> by prefixing them with the schema name (or maybe allow search_path to be
>>> used).
>>>
>>
>> I hope so there are not new conflicts - schema variable is not directly
>> visible from SQL (in this iteration) - they are visible only from
>> functions
>> - and the behave is same like global plpgsql variable. So schema variable
>> can be in conflict with SQL identifier only exactly identically as plpgsql
>> variable
>>
>
> Yeah, and that's exactly what I don't want, because that means that CREATE
> SCHEMA VARIABLE suddenly breaks existing code.
>

theoretically yes, but this conflict can be 100% detected - so no quiet bug
is possible, and plpgsql_check can find this issue well. If you don't use
schema variable, then your code will be correct. You have to explicitly
create the variable, and if there will be any problem, then the problem
will be only in PL functions in one schema. And you can identify it by
statical analyse.


>
> But prefix can be used.
>>
>
> Sure, but I don't see the point.  Is there a reason not to require such
> variable references to be prefixed with the schema name?  Or explicitly
> bring them into scope in the DECLARE section somehow.
>

we can define any rules, but I see better to be consistent with current
variables design. I don't prefer any mandatory prefixes when it is not
necessary.

explicit safe prefixing can be used by developers "_" local variable, "__"
schema variable.

I though about DECLARE section too. But more declarations, more copy/paste
or different bugs, and these bugs can be worse detected by static analyse.


>
>
> .m
>