proposal: schema PL session variables

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

2016-02-08 13:03 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

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

2016-02-08 13:03 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

2016-02-08 13:22 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

2016-02-08 13:03 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

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

2016-02-08 13:22 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

2016-02-08 13:53 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

2016-02-08 13:22 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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.

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

[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

2016-02-08 16:45 GMT+01:00 jflack <jflack@math.purdue.edu>:

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

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

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

2016-02-08 13:53 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

2016-02-09 15:32 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

2016-02-08 13:53 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

On Tue, Feb 9, 2016 at 9:58 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2016-02-09 15:32 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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

2016-02-08 13:53 GMT+01:00 Marko Tiikkaja <marko@joh.to>:

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.

On Tue, Feb 9, 2016 at 11:32 AM, Corey Huinker <corey.huinker@gmail.com>
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.
​

On Tue, Feb 9, 2016 at 2:55 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tue, Feb 9, 2016 at 11:32 AM, Corey Huinker <corey.huinker@gmail.com>
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.

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

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

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.

2016-02-09 20:55 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:

On Tue, Feb 9, 2016 at 11:32 AM, Corey Huinker <corey.huinker@gmail.com>
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

2016-02-09 23:31 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

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.

Hi

2016-02-09 23:41 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

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

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

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

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

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

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

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

2016-02-10 20:10 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

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

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

2016-02-10 20:10 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com
<mailto:Jim.Nasby@bluetreble.com>>:

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

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

2016-02-10 20:25 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

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

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

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

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

2016-02-12 22:41 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

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

2016-02-12 22:41 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

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

Hi

2016-02-23 20:52 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2016-02-12 22:41 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

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