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