proposal: session server side variables

On 14 October 2016 at 13:30, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi,

long time I working on this topic. Session server side variables are one
major missing feature in PLpgSQL. Now I hope, I can summarize requests for
implementation in Postgres:

+1

2. accessed with respecting access rights:

GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role

This bit is important.

For those wondering "why the hell would you want these, just (ab)use
GUCs"... this is why.

Think RLS. Especially when we eventually have session start / at login
triggers, but even before then, you can initialise some expensive
state once at the start of the session, transfer it from the app, or
whatever. You initialise it via a SECURITY DEFINER procedure so the
session user does not have the rights to write to the variable, and it
can only be set via arbitration from the database security logic. From
then on your RLS policies, your triggers, etc, can all simply inspect
the session variable.

People use package variables in another major database with a feature
called virtual private database for something similar. So this will
interest anyone who wants to make porting those users easier, too.

4. non transactional - the metadata are transactional, but the content is
not.

but only within the session, right? You're not proposing some kind of
inter-backend IPC where one backend sets a session var and another
backend accesses it and sees the value set by the first session?

Speaking of which: parallel query. How do you envision this working in
parallel query, where the workers are different backends? Especially
since things like RLS are where it'd be quite desirable.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Pavel Stehule wrote

Session server side variables are one major missing feature in PLpgSQL.

I think this would also be useful outside of PL/pgSQL to support query level
variables similar to what SQL Server does.

--
View this message in context: http://postgresql.nabble.com/proposal-session-server-side-variables-tp5925827p5925836.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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-10-14 9:56 GMT+02:00 Craig Ringer <craig@2ndquadrant.com>:

On 14 October 2016 at 13:30, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi,

long time I working on this topic. Session server side variables are one
major missing feature in PLpgSQL. Now I hope, I can summarize requests

for

implementation in Postgres:

+1

2. accessed with respecting access rights:

GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role

This bit is important.

For those wondering "why the hell would you want these, just (ab)use
GUCs"... this is why.

Think RLS. Especially when we eventually have session start / at login
triggers, but even before then, you can initialise some expensive
state once at the start of the session, transfer it from the app, or
whatever. You initialise it via a SECURITY DEFINER procedure so the
session user does not have the rights to write to the variable, and it
can only be set via arbitration from the database security logic. From
then on your RLS policies, your triggers, etc, can all simply inspect
the session variable.

People use package variables in another major database with a feature
called virtual private database for something similar. So this will
interest anyone who wants to make porting those users easier, too.

4. non transactional - the metadata are transactional, but the content

is

not.

but only within the session, right? You're not proposing some kind of
inter-backend IPC where one backend sets a session var and another
backend accesses it and sees the value set by the first session?

In this moment I propose only local (not shared variables). I hope so
access can be safe with IMMUTABLE access function.

First time I would to implement basic set of features, that can be enhanced
in future.

1. shared variables
2. variables initialized on session start, ...

Speaking of which: parallel query. How do you envision this working in
parallel query, where the workers are different backends? Especially
since things like RLS are where it'd be quite desirable.

It should be solved by IMMUTABLE PARALLEL SAFE access functions. The
content of variable should be processed in planning time, and then the
access from more processes is not necessary.

Default access function should VOLATILE PARALLEL UNSAFE - but immutable
sets can be defined and used (and I see a sense of these function, because
with these function the variables are accessed in query planning time).

Regards

Pavel

Show quoted text

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi

2016-10-14 9:56 GMT+02:00 Craig Ringer <craig@2ndquadrant.com>:

On 14 October 2016 at 13:30, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi,

long time I working on this topic. Session server side variables are one
major missing feature in PLpgSQL. Now I hope, I can summarize requests

for

implementation in Postgres:

+1

2. accessed with respecting access rights:

GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role

This bit is important.

For those wondering "why the hell would you want these, just (ab)use
GUCs"... this is why.

Think RLS. Especially when we eventually have session start / at login
triggers, but even before then, you can initialise some expensive
state once at the start of the session, transfer it from the app, or
whatever. You initialise it via a SECURITY DEFINER procedure so the
session user does not have the rights to write to the variable, and it
can only be set via arbitration from the database security logic. From
then on your RLS policies, your triggers, etc, can all simply inspect
the session variable.

People use package variables in another major database with a feature
called virtual private database for something similar. So this will
interest anyone who wants to make porting those users easier, too.

4. non transactional - the metadata are transactional, but the content

is

not.

but only within the session, right? You're not proposing some kind of
inter-backend IPC where one backend sets a session var and another
backend accesses it and sees the value set by the first session?

Speaking of which: parallel query. How do you envision this working in
parallel query, where the workers are different backends? Especially
since things like RLS are where it'd be quite desirable.

In first stage the session variables should be marked as parallel unsafe -
but in future - there can be used similar technique like shared hashjoin.

I am sending proof concept - it doesn't support access to fields of
composite variables, but any other functionality is done.

Most important features:

1. the values are stored in native types
2. access to content is protected by ACL - like the content of tables
3. the content is not MVCC based - no any cost of UPDATE
4. simple API allows access to content of variables from any supported
environment.

Regards

Pavel

Show quoted text

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 14 October 2016 at 23:09, Pavel Stehule <pavel.stehule@gmail.com> wrote:

but only within the session, right? You're not proposing some kind of
inter-backend IPC where one backend sets a session var and another
backend accesses it and sees the value set by the first session?

In this moment I propose only local (not shared variables). I hope so access
can be safe with IMMUTABLE access function.

OK, good. Though I suspect you'll have a hard time with IMMUTABLE
functions and need STABLE.

I don't think it's correct to claim that these vars are immutable,
since that'd allow users to do silly things like build them into index
expressions. Splat.

Default access function should VOLATILE PARALLEL UNSAFE - but immutable sets
can be defined and used (and I see a sense of these function, because with
these function the variables are accessed in query planning time).

I don't really understand the purpose of an immutable variable. It
seems inherently contradictory.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Hi

2016-11-25 15:32 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

2016-10-14 9:56 GMT+02:00 Craig Ringer <craig@2ndquadrant.com>:

On 14 October 2016 at 13:30, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi,

long time I working on this topic. Session server side variables are one
major missing feature in PLpgSQL. Now I hope, I can summarize requests

for

implementation in Postgres:

+1

2. accessed with respecting access rights:

GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role

This bit is important.

For those wondering "why the hell would you want these, just (ab)use
GUCs"... this is why.

Think RLS. Especially when we eventually have session start / at login
triggers, but even before then, you can initialise some expensive
state once at the start of the session, transfer it from the app, or
whatever. You initialise it via a SECURITY DEFINER procedure so the
session user does not have the rights to write to the variable, and it
can only be set via arbitration from the database security logic. From
then on your RLS policies, your triggers, etc, can all simply inspect
the session variable.

People use package variables in another major database with a feature
called virtual private database for something similar. So this will
interest anyone who wants to make porting those users easier, too.

4. non transactional - the metadata are transactional, but the content

is

not.

but only within the session, right? You're not proposing some kind of
inter-backend IPC where one backend sets a session var and another
backend accesses it and sees the value set by the first session?

Speaking of which: parallel query. How do you envision this working in
parallel query, where the workers are different backends? Especially
since things like RLS are where it'd be quite desirable.

In first stage the session variables should be marked as parallel unsafe -
but in future - there can be used similar technique like shared hashjoin.

I am sending proof concept - it doesn't support access to fields of
composite variables, but any other functionality is done.

Most important features:

1. the values are stored in native types
2. access to content is protected by ACL - like the content of tables
3. the content is not MVCC based - no any cost of UPDATE
4. simple API allows access to content of variables from any supported
environment.

next update - setattr, getattr functions are working now

notes, comments?

Regards

Pavel

Show quoted text

Regards

Pavel

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 28.11.2016 10:42, Pavel Stehule wrote:

next update - setattr, getattr functions are working now

notes, comments?

Regards

Pavel

It is interesting!

Do you have plans to support also table variables? For example, like this:

create type composite_type_2 as (a int, b text);
create variable var7 composite_type_2;
select insertvar('var7','(10,Hello world\, Hello world\, Hello world)');
select insertvar('var7','(1000,Hola, hola!)');
select * from getvar('var7');
a | b
------+---------------------------------------
10 | Hello world, Hello world, Hello world
1000 | Hola, hola!

Or it is a bad idea? Or it is not related to this patch?

We have the extension (https://github.com/postgrespro/pg_variables). And
it supports table like variables. It shows better performance against
temporary tables.

--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

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

Hi

2016-11-28 10:39 GMT+01:00 Artur Zakirov <a.zakirov@postgrespro.ru>:

On 28.11.2016 10:42, Pavel Stehule wrote:

next update - setattr, getattr functions are working now

notes, comments?

Regards

Pavel

It is interesting!

Do you have plans to support also table variables? For example, like this:

create type composite_type_2 as (a int, b text);
create variable var7 composite_type_2;
select insertvar('var7','(10,Hello world\, Hello world\, Hello world)');
select insertvar('var7','(1000,Hola, hola!)');
select * from getvar('var7');
a | b
------+---------------------------------------
10 | Hello world, Hello world, Hello world
1000 | Hola, hola!

Or it is a bad idea? Or it is not related to this patch?

Minimally in first stage I have not plan to support tables. It opens lot of
questions - lot of code to implement - how to implement indexes,
statistics, MVCC?

But some workaround is not hard - you can store a array of composite types.

postgres=# select setvar('a', array(select row(10,'ahoj')::test from
generate_series(1,10)));
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│
a
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
│
{"(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,aho
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
(1 row)

Time: 0,992 ms
postgres=# select * from unnest(getvar('a'));
┌────┬──────┐
│ a │ b │
╞════╪══════╡
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
└────┴──────┘
(10 rows)

For fast append it needs another significant work (and can be done in next
step), but almost all work did Tom already.

Show quoted text

We have the extension (https://github.com/postgrespro/pg_variables). And
it supports table like variables. It shows better performance against
temporary tables.

--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Hi

Most important features:

1. the values are stored in native types
2. access to content is protected by ACL - like the content of tables
3. the content is not MVCC based - no any cost of UPDATE
4. simple API allows access to content of variables from any supported
environment.

next update - setattr, getattr functions are working now

new update - rebased after partitioning patch

Regards

Pavel

Show quoted text

notes, comments?

Regards

Pavel

Regards

Pavel

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

2016-12-15 15:36 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

Most important features:

1. the values are stored in native types
2. access to content is protected by ACL - like the content of tables
3. the content is not MVCC based - no any cost of UPDATE
4. simple API allows access to content of variables from any supported
environment.

next update - setattr, getattr functions are working now

new update - rebased after partitioning patch

next update - with some initial doc

Regards

Pavel

Show quoted text

Regards

Pavel

notes, comments?

Regards

Pavel

Regards

Pavel

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hello Pavel,

The session variables should be:

I have often wished I had such a feature, psql client side :-variables are
just awful raw text things.

A few comments, mostly about the design:

1. persistent objects with temporal unshared typed content. The life of
content should be limited by session or by transaction. The content is
initialized to default (when it is defined) or to NULL when variable is
first accessed in variable' time scope (session, transaction).

CREATE VARIABLE [schema.]variable type [DEFAULT default_value]
[TRANSACTION|SESION SCOPE]

I'm not sure of the order, and from a parser perspective it is nice to
announce the type before the value.

Maybe a SQL-server like @-prefix would be nice, something like:

CREATE VARIABLE @foo TEXT DEFAULT 'hello' SCOPE SESSION;

DROP VARIABLE [schema.]variable

In the long term, What would be the possible scopes?

TRANSACTION, SESSION, PERSISTANT ?

Would some scopes orthogonal (eg SHARED between sessions for a USER in a
DATABASE, SHARED at the cluster level?).

How to deal with namespace issues?

2. accessed with respecting access rights:

GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role

At least for transaction and session scopes it does not make sense that
they would be accessible outside the session/transaction, so grant/revoke
do not seem necessary?

3. accessed/updated with special function "getvar", "setvar":

FUNCTION getvar(regclass) RETURNS type
FUNCTION setvar(regclass, type) RETURNS void

From an aesthetical point of view, I do not like that much.

If you use CREATE & DROP, then logically you should use ALTER:

CREATE VARIABLE @name TEXT DEFAULT 'calvin';
CREATE VARIABLE @name TEXT = 'calvin';
ALTER VARIABLE @name SET VALUE TO 'hobbes';
ALTER VARIABLE @name = 'hoobes';
DROP VARIABLE @name;

Maybe "SET" could be an option as well, but it is less logical:

SET @name = 'susie';

But then "SET @..." would just be a shortcut for ALTER VARIABLE.

Also a nicer way to reference them would be great, like SQL server.

SELECT * FROM SomeTable WHERE name = @name;

A function may be called behind the scene, I'm just arguing about the
syntax here...

Important question, what nice syntax to assign the result of a query to a
variable? Maybe it could be:

SET @name = query-returning-one-row; -- hmmm
SET @name FROM query-returning-one-row; -- maybe better

Or:

ALTER VARIABLE @name WITH one-row-query;

Special variables could allow to get the number of rows modified by the
last option, like in PL/pgSQL but at the SQL level?

4. non transactional - the metadata are transactional, but the content is
not.

Hmmm... Do you mean:

CREATE VARIABLE foo INT DEFAULT 1 SCOPE SESSION;
BEGIN;
SET @foo = 2;
ROLLBACK;

Then @foo is 2 despite the roolback? Yuk!

I think that if the implementation is based on some system table for
storage, then you could get the transaction properties for free, and it
seems more logical to do so:

CREATE TEMPORARY TABLE pg_session_variables(name TEXT PRIMARY KEY, value TEXT, oidtype, ...);

CREATE VARIABLE @foo INTEGER; -- INSERT INTO TABLE ...

SELECT * FROM x WHERE name = @foo;
-- SELECT * FROM x WHERE name = (SELECT value::INT FROM pg_session_variables WHERE name='foo')

So maybe some simple syntactic rewriting would be enough? Or some SPI
function?

--
Fabien.

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

2016-12-23 16:27 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

The session variables should be:

I have often wished I had such a feature, psql client side :-variables are
just awful raw text things.

A few comments, mostly about the design:

1. persistent objects with temporal unshared typed content. The life of

content should be limited by session or by transaction. The content is
initialized to default (when it is defined) or to NULL when variable is
first accessed in variable' time scope (session, transaction).

CREATE VARIABLE [schema.]variable type [DEFAULT default_value]
[TRANSACTION|SESION SCOPE]

I'm not sure of the order, and from a parser perspective it is nice to
announce the type before the value.

I little bit dislike this style - in my proposal the session variables are
very near to a sequences - and we have not any special symbols for
sequences.
Session secure variables are some different than in MSSQL or MySQL - so I
would not to use same syntax.

I really would to use pg_class as base for metadata of variables -
conflicts are not possible. I can reuse safe GRANT/REVOKE mechanism ..

With different syntax it all lost sense - and I'll to implement it again.

Maybe a SQL-server like @-prefix would be nice, something like:

CREATE VARIABLE @foo TEXT DEFAULT 'hello' SCOPE SESSION;

DROP VARIABLE [schema.]variable

In the long term, What would be the possible scopes?

TRANSACTION, SESSION, PERSISTANT ?

Would some scopes orthogonal (eg SHARED between sessions for a USER in a
DATABASE, SHARED at the cluster level?).

I have a plan to support TRANSACTION and SESSION scope. Persistent or
shared scope needs much more complex rules, and some specialized extensions
will be better.

How to deal with namespace issues?

2. accessed with respecting access rights:

GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role

At least for transaction and session scopes it does not make sense that
they would be accessible outside the session/transaction, so grant/revoke
do not seem necessary?

It is necessary - and I think so it is fundamental feature - any other
features can be more or less replaced by extensions, but this one cannot or
not simply - you have to protect content against some users - some
cookies, ids have to be protected. It can be used well with RLS.

Ada language has packages, package variables. I would not to introduce
packages because are redundant to schemas, but I need some mechanism for
content protecting. I would not to introduce packages, because than I will
have problem with joining ADA packages with Perl, Python. Instead I
introduce secure granted access. More - I don't need to solve lexical scope
- and I can use a wide used mechanism.

3. accessed/updated with special function "getvar", "setvar":

FUNCTION getvar(regclass) RETURNS type
FUNCTION setvar(regclass, type) RETURNS void

From an aesthetical point of view, I do not like that much.

If you use CREATE & DROP, then logically you should use ALTER:

CREATE VARIABLE @name TEXT DEFAULT 'calvin';
CREATE VARIABLE @name TEXT = 'calvin';
ALTER VARIABLE @name SET VALUE TO 'hobbes';
ALTER VARIABLE @name = 'hoobes';
DROP VARIABLE @name;

Maybe "SET" could be an option as well, but it is less logical:

SET @name = 'susie';

But then "SET @..." would just be a shortcut for ALTER VARIABLE.

I would to use a SET statement too. But it is used for another target now.
Using ALTER in this content looks strange to me. It is used for changing
metadata not a value.

Next step can be support of SQL statements

With SQL support you can do

SELECT varname;
SELECT * FROM compositevarname;
UPDATE varname SET value TO xxx;
UPDATE compositevarname SET field TO xxx;

Also a nicer way to reference them would be great, like SQL server.

SELECT * FROM SomeTable WHERE name = @name;

A function may be called behind the scene, I'm just arguing about the
syntax here...

Important question, what nice syntax to assign the result of a query to a
variable? Maybe it could be:

SET @name = query-returning-one-row; -- hmmm
SET @name FROM query-returning-one-row; -- maybe better

Or:

ALTER VARIABLE @name WITH one-row-query;

Special variables could allow to get the number of rows modified by the
last option, like in PL/pgSQL but at the SQL level?

4. non transactional - the metadata are transactional, but the content is

not.

Hmmm... Do you mean:

CREATE VARIABLE foo INT DEFAULT 1 SCOPE SESSION;
BEGIN;
SET @foo = 2;
ROLLBACK;

Then @foo is 2 despite the roolback? Yuk!

This is similar to sequences.

If you need transactional content - then you should to use tables.

Show quoted text

I think that if the implementation is based on some system table for
storage, then you could get the transaction properties for free, and it
seems more logical to do so:

CREATE TEMPORARY TABLE pg_session_variables(name TEXT PRIMARY KEY, value
TEXT, oidtype, ...);

CREATE VARIABLE @foo INTEGER; -- INSERT INTO TABLE ...

SELECT * FROM x WHERE name = @foo;
-- SELECT * FROM x WHERE name = (SELECT value::INT FROM
pg_session_variables WHERE name='foo')

So maybe some simple syntactic rewriting would be enough? Or some SPI
function?

--
Fabien.

Hello,

I little bit dislike this style - in my proposal the session variables are
very near to a sequences - and we have not any special symbols for
sequences.

Yep, but we do not need a syntax to reference a sequence either... it is
automatic and usually hidden behind SERIAL. I know there is a NEXTVAL
function, I just never call it, so it is fine... If I define a variable I
expect to have to use it.

Session secure variables are some different than in MSSQL or MySQL - so I
would not to use same syntax.

I'm not sure why pg variables should be different from these other tools.

What is the use case to cover? The few times I wished I had variables
would have been covered by session-limited variables, for which
grant/revoke do not make sense.

I really would to use pg_class as base for metadata of variables -
conflicts are not possible. I can reuse safe GRANT/REVOKE mechanism ..
With different syntax it all lost sense - and I'll to implement it again.

I also hate having my time going down the drain, but this cannot be the
justification for a feature.

I have a plan to support TRANSACTION and SESSION scope.

That looks ok to me.

Persistent or shared scope needs much more complex rules, and some
specialized extensions will be better.

Or maybe they should be avoided altogether?

[GRANT].
It is necessary - and I think so it is fundamental feature - any other
features can be more or less replaced by extensions, but this one cannot or
not simply - you have to protect content against some users - some
cookies, ids have to be protected. It can be used well with RLS.
Ada language has packages, package variables. I would not to introduce
packages because are redundant to schemas, but I need some mechanism for
content protecting.

I do not understand why GRANT make sense. If a variable is set by a
session/tx and only accessible to this session/tx, then only the client
who put it can get it back, so it is more of a syntactic commodity?

What appropriate use case would need more?

I would not to introduce packages, because than I will have problem with
joining ADA packages with Perl, Python. Instead I introduce secure
granted access. More - I don't need to solve lexical scope - and I can
use a wide used mechanism.

3. accessed/updated with special function "getvar", "setvar":

FUNCTION getvar(regclass) RETURNS type
FUNCTION setvar(regclass, type) RETURNS void

From an aesthetical point of view, I do not like that much.

If you use CREATE & DROP, then logically you should use ALTER:

CREATE VARIABLE @name TEXT DEFAULT 'calvin';
CREATE VARIABLE @name TEXT = 'calvin';
ALTER VARIABLE @name SET VALUE TO 'hobbes';
ALTER VARIABLE @name = 'hoobes';
DROP VARIABLE @name;

Maybe "SET" could be an option as well, but it is less logical:

SET @name = 'susie';

But then "SET @..." would just be a shortcut for ALTER VARIABLE.

I would to use a SET statement too. But it is used for another target now.
Using ALTER in this content looks strange to me. It is used for changing
metadata not a value.

ALTER SEQUENCE does allow to change its value? Or maybe use UPDATE, as you
suggest below...

Next step can be support of SQL statements
With SQL support you can do

SELECT varname;
UPDATE varname SET value TO xxx;

SELECT * FROM compositevarname;
UPDATE compositevarname SET field TO xxx;

I'm not at ease with the syntax because varname is both a value and a
relation somehow... But maybe that make sense? Not sure, I'll think about
it.

Hmmm... Do you mean:

CREATE VARIABLE foo INT DEFAULT 1 SCOPE SESSION;
BEGIN;
SET @foo = 2;
ROLLBACK;

Then @foo is 2 despite the roolback? Yuk!

This is similar to sequences.

That is not a good reason to do the same. Sequences are special objects
for which the actual value is expected to be of no importance, only that
it is different from the previous and the next. I do not think that
"variables" should behave like that, because their value is important.

If you need transactional content - then you should to use tables.

Why not.

Maybe variables just need be a syntactic convenience around that?

A variable is a table with one row holding one value... In which case
GRANT/REVOKE makes sense, because a table may be shared and persistent,
thus is not limited to a session or a transaction.

That allows to set constraints.

CREATE VARIABLE foo INT NOT NULL DEFAULT 1 SCOPE SESSION/SESSION SCOPE;
-> CREATE TEMPORARY TABLE foo(val INT NOT NULL DEFAULT 1) ONE ROW;
-> INSERT INTO foo VALUES();

@foo
-> (SELECT val FROM foo LIMIT 1)

@foo.field
-> (SELECT field FROM foo LIMIT 1)

SET @foo = 2;
-> UPDATE @foo SET val = 2;
SET @foo.field = 3;
-> UPDATE foo SET field = 3;

DROP VARIABLE foo;
-> DROP TABLE foo;

--
Fabien.

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

2016-12-23 18:46 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello,

I little bit dislike this style - in my proposal the session variables are

very near to a sequences - and we have not any special symbols for
sequences.

Yep, but we do not need a syntax to reference a sequence either... it is
automatic and usually hidden behind SERIAL. I know there is a NEXTVAL
function, I just never call it, so it is fine... If I define a variable I
expect to have to use it.

Session secure variables are some different than in MSSQL or MySQL - so I

would not to use same syntax.

I'm not sure why pg variables should be different from these other tools.

because MySQL variables are not declared - and allows assign everywhere -
and MSSQL variables are not persistent. Its total different creatures.

What is the use case to cover? The few times I wished I had variables
would have been covered by session-limited variables, for which
grant/revoke do not make sense.

I really would to use pg_class as base for metadata of variables -

conflicts are not possible. I can reuse safe GRANT/REVOKE mechanism ..
With different syntax it all lost sense - and I'll to implement it again.

I also hate having my time going down the drain, but this cannot be the
justification for a feature.

I have a plan to support TRANSACTION and SESSION scope.

That looks ok to me.

Persistent or shared scope needs much more complex rules, and some

specialized extensions will be better.

Or maybe they should be avoided altogether?

[GRANT].

It is necessary - and I think so it is fundamental feature - any other
features can be more or less replaced by extensions, but this one cannot
or
not simply - you have to protect content against some users - some
cookies, ids have to be protected. It can be used well with RLS.
Ada language has packages, package variables. I would not to introduce
packages because are redundant to schemas, but I need some mechanism for
content protecting.

I do not understand why GRANT make sense. If a variable is set by a
session/tx and only accessible to this session/tx, then only the client who
put it can get it back, so it is more of a syntactic commodity?

In one session you can use lot of roles - some code can be used for
securing interactive work, some can be for securing some API, sometimes you
can secure a access to some sources. You can switch lot of roles by using
security definer functions.

What appropriate use case would need more?

I would not to introduce packages, because than I will have problem with

joining ADA packages with Perl, Python. Instead I introduce secure granted
access. More - I don't need to solve lexical scope - and I can use a wide
used mechanism.

3. accessed/updated with special function "getvar", "setvar":

FUNCTION getvar(regclass) RETURNS type
FUNCTION setvar(regclass, type) RETURNS void

From an aesthetical point of view, I do not like that much.

If you use CREATE & DROP, then logically you should use ALTER:

CREATE VARIABLE @name TEXT DEFAULT 'calvin';
CREATE VARIABLE @name TEXT = 'calvin';
ALTER VARIABLE @name SET VALUE TO 'hobbes';
ALTER VARIABLE @name = 'hoobes';
DROP VARIABLE @name;

Maybe "SET" could be an option as well, but it is less logical:

SET @name = 'susie';

But then "SET @..." would just be a shortcut for ALTER VARIABLE.

I would to use a SET statement too. But it is used for another target now.
Using ALTER in this content looks strange to me. It is used for changing
metadata not a value.

ALTER SEQUENCE does allow to change its value? Or maybe use UPDATE, as you
suggest below...

Next step can be support of SQL statements

With SQL support you can do

SELECT varname;
UPDATE varname SET value TO xxx;

SELECT * FROM compositevarname;

UPDATE compositevarname SET field TO xxx;

I'm not at ease with the syntax because varname is both a value and a
relation somehow... But maybe that make sense? Not sure, I'll think about
it.

Hmmm... Do you mean:

CREATE VARIABLE foo INT DEFAULT 1 SCOPE SESSION;
BEGIN;
SET @foo = 2;
ROLLBACK;

Then @foo is 2 despite the roolback? Yuk!

This is similar to sequences.

That is not a good reason to do the same. Sequences are special objects
for which the actual value is expected to be of no importance, only that it
is different from the previous and the next. I do not think that
"variables" should behave like that, because their value is important.

If you need transactional content - then you should to use tables.

Why not.

Maybe variables just need be a syntactic convenience around that?

There is pretty similar relation between sequences and tables and variables
and tables.

A variable is a table with one row holding one value... In which case
GRANT/REVOKE makes sense, because a table may be shared and persistent,
thus is not limited to a session or a transaction.

That allows to set constraints.

In first iteration the constraint can be implemented with domains - but
there is not any break to implement constraints directly on variables.

Show quoted text

CREATE VARIABLE foo INT NOT NULL DEFAULT 1 SCOPE SESSION/SESSION SCOPE;
-> CREATE TEMPORARY TABLE foo(val INT NOT NULL DEFAULT 1) ONE ROW;
-> INSERT INTO foo VALUES();

@foo
-> (SELECT val FROM foo LIMIT 1)

@foo.field
-> (SELECT field FROM foo LIMIT 1)

SET @foo = 2;
-> UPDATE @foo SET val = 2;
SET @foo.field = 3;
-> UPDATE foo SET field = 3;

DROP VARIABLE foo;
-> DROP TABLE foo;

--
Fabien.

On 12/23/2016 08:20 AM, Pavel Stehule wrote:

2016-12-23 16:27 GMT+01:00 Fabien COELHO:

I have often wished I had such a feature, psql client side :-variables are
just awful raw text things.

Agreed.

A few comments, mostly about the design:

1. persistent objects with temporal unshared typed content. The life of

content should be limited by session or by transaction. The content is
initialized to default (when it is defined) or to NULL when variable is
first accessed in variable' time scope (session, transaction).

CREATE VARIABLE [schema.]variable type [DEFAULT default_value]
[TRANSACTION|SESION SCOPE]

I haven't looked, but I take it the SQL standard is silent on the issue
of variables?

I really would to use pg_class as base for metadata of variables -
conflicts are not possible. I can reuse safe GRANT/REVOKE mechanism ..

That would be very useful.

In the long term, What would be the possible scopes?

TRANSACTION, SESSION, PERSISTANT ?

Would some scopes orthogonal (eg SHARED between sessions for a USER in a
DATABASE, SHARED at the cluster level?).

I have a plan to support TRANSACTION and SESSION scope. Persistent or
shared scope needs much more complex rules, and some specialized extensions
will be better.

I can see where persistent variables would be very useful though.

2. accessed with respecting access rights:

GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role

At least for transaction and session scopes it does not make sense that
they would be accessible outside the session/transaction, so grant/revoke
do not seem necessary?

It is necessary - and I think so it is fundamental feature - any other
features can be more or less replaced by extensions, but this one cannot or
not simply - you have to protect content against some users - some
cookies, ids have to be protected. It can be used well with RLS.

How would this work for transaction and session scopes though? What
would be the point -- no other access is possible other than what
happens in the session. Do you envision something like

CREATE VARIABLE foo ...;
GRANT SELECT ON VARIABLE foo TO bob;
SET ROLE bob;

?

3. accessed/updated with special function "getvar", "setvar":

FUNCTION getvar(regclass) RETURNS type
FUNCTION setvar(regclass, type) RETURNS void

From an aesthetical point of view, I do not like that much.

If you use CREATE & DROP, then logically you should use ALTER:

CREATE VARIABLE @name TEXT DEFAULT 'calvin';
CREATE VARIABLE @name TEXT = 'calvin';
ALTER VARIABLE @name SET VALUE TO 'hobbes';
ALTER VARIABLE @name = 'hoobes';
DROP VARIABLE @name;

Makes sense.

Maybe "SET" could be an option as well, but it is less logical:

SET @name = 'susie';

But then "SET @..." would just be a shortcut for ALTER VARIABLE.

Maybe. Not sure I like that.

I would to use a SET statement too. But it is used for another target now.
Using ALTER in this content looks strange to me. It is used for changing
metadata not a value.

Next step can be support of SQL statements

With SQL support you can do

SELECT varname;

+1

SELECT * FROM compositevarname;

+1

UPDATE varname SET value TO xxx;
UPDATE compositevarname SET field TO xxx;

These need more thought I think.

Also a nicer way to reference them would be great, like SQL server.

SELECT * FROM SomeTable WHERE name = @name;

A function may be called behind the scene, I'm just arguing about the
syntax here...

Important question, what nice syntax to assign the result of a query to a
variable? Maybe it could be:

SET @name = query-returning-one-row; -- hmmm
SET @name FROM query-returning-one-row; -- maybe better

Or:

ALTER VARIABLE @name WITH one-row-query;

Special variables could allow to get the number of rows modified by the
last option, like in PL/pgSQL but at the SQL level?

I think the SET syntax is growing on me, but I suspect there may be push
back on overloading that syntax.

4. non transactional - the metadata are transactional, but the content is

not.

Hmmm... Do you mean:

CREATE VARIABLE foo INT DEFAULT 1 SCOPE SESSION;
BEGIN;
SET @foo = 2;
ROLLBACK;

Then @foo is 2 despite the roolback? Yuk!

Agreed

This is similar to sequences.

I don't see how variables really have anything to do with sequences.

If you need transactional content - then you should to use tables.

I definitely have use-cases where transactional variables would be useful.

I think that if the implementation is based on some system table for
storage, then you could get the transaction properties for free, and it
seems more logical to do so:

CREATE TEMPORARY TABLE pg_session_variables(name TEXT PRIMARY KEY, value
TEXT, oidtype, ...);

CREATE VARIABLE @foo INTEGER; -- INSERT INTO TABLE ...

SELECT * FROM x WHERE name = @foo;
-- SELECT * FROM x WHERE name = (SELECT value::INT FROM
pg_session_variables WHERE name='foo')

So maybe some simple syntactic rewriting would be enough? Or some SPI
function?

I was thinking along those lines too.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

because MySQL variables are not declared - and allows assign everywhere -

Ok. I do not do MySQL.

and MSSQL variables are not persistent.

Yep, but they might be?

In one session you can use lot of roles - some code can be used for
securing interactive work, some can be for securing some API, sometimes you
can secure a access to some sources. You can switch lot of roles by using
security definer functions.

Hmmm. Switching role within a transaction. I never did need that... but
that is a use case.

If you need transactional content - then you should to use tables.

Why not.

Maybe variables just need be a syntactic convenience around that?

There is pretty similar relation between sequences and tables and variables
and tables.

Yep. A sequence is a one row table, so a variable may be also a one row
table as well, but with more flexibility about its type, and some nice
syntactic sugar (like SERIAL which is syntactic sugar for CREATE SEQUENCE
...).

In first iteration the constraint can be implemented with domains - but
there is not any break to implement constraints directly on variables.

Hmmm. If a variable is implemented as a one row table, then constraints
are already available there, as well as grant & revoke, they can be any
type including composite, nearly nothing to implement to get...

A "one row" table would be a CREATE + one INSERT, UPDATE allowed, further
INSERT and DELETE are disallowed by construction. Then some syntactic
sugar for variables (session => temporary table, persistent => standard
table). Note sure about a "transaction variable", though... maybe an
[unlogged] table automatically dropped on commit?

--
Fabien.

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

Fabien COELHO <coelho@cri.ensmp.fr> writes:

In first iteration the constraint can be implemented with domains - but
there is not any break to implement constraints directly on variables.

Hmmm. If a variable is implemented as a one row table, then constraints
are already available there, as well as grant & revoke, they can be any
type including composite, nearly nothing to implement to get...

A "one row" table would be a CREATE + one INSERT, UPDATE allowed, further
INSERT and DELETE are disallowed by construction. Then some syntactic
sugar for variables (session => temporary table, persistent => standard
table). Note sure about a "transaction variable", though... maybe an
[unlogged] table automatically dropped on commit?

I think it's entirely silly to be inventing something that's morally a
one-row table, when we already have perfectly good one-row tables.
The value of a server-side variable facility would be mostly that it
doesn't have all the overhead implied by tables. I think that is a
direct reason not to think about overhead like constraints, as well.

regards, tom lane

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

2016-12-23 19:28 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

because MySQL variables are not declared - and allows assign everywhere -

Ok. I do not do MySQL.

and MSSQL variables are not persistent.

Yep, but they might be?

In one session you can use lot of roles - some code can be used for

securing interactive work, some can be for securing some API, sometimes
you
can secure a access to some sources. You can switch lot of roles by using
security definer functions.

Hmmm. Switching role within a transaction. I never did need that... but
that is a use case.

Any application with security definer functions - depends on different
communities - it is used sometimes strongly.

If you need transactional content - then you should to use tables.

Why not.

Maybe variables just need be a syntactic convenience around that?

There is pretty similar relation between sequences and tables and
variables
and tables.

Yep. A sequence is a one row table, so a variable may be also a one row
table as well, but with more flexibility about its type, and some nice
syntactic sugar (like SERIAL which is syntactic sugar for CREATE SEQUENCE
...).

In first iteration the constraint can be implemented with domains - but

there is not any break to implement constraints directly on variables.

Hmmm. If a variable is implemented as a one row table, then constraints
are already available there, as well as grant & revoke, they can be any
type including composite, nearly nothing to implement to get...

A "one row" table would be a CREATE + one INSERT, UPDATE allowed, further
INSERT and DELETE are disallowed by construction. Then some syntactic sugar
for variables (session => temporary table, persistent => standard table).
Note sure about a "transaction variable", though... maybe an [unlogged]
table automatically dropped on commit?

Probably we have different expectation from variables. I don't expect so
variable can be changed by any rollback.

What is use case for transactional variables? I miss any experience - I
wrote lot plpgsql lines and newer would it.

When I remove ACID, and allow only one value - then the implementation can
be simple and fast - some next step can be support of expandable types.
Sure - anybody can use temporary tables now and in future. But it is slow -
more now, because we doesn't support global temporary tables. But ACID
needs lot of CPU times, needs possible VACUUM, ...

No ACID variables are simple to implement, simple to directly accessible
from any PL (although I am thinking about better support in 2nd phase for
PLpgSQL).

Show quoted text

--
Fabien.

Hello Tom,

Hmmm. If a variable is implemented as a one row table, then constraints
are already available there, as well as grant & revoke, they can be any
type including composite, nearly nothing to implement to get...

A "one row" table would be a CREATE + one INSERT, UPDATE allowed, further
INSERT and DELETE are disallowed by construction. Then some syntactic
sugar for variables (session => temporary table, persistent => standard
table). Note sure about a "transaction variable", though... maybe an
[unlogged] table automatically dropped on commit?

I think it's entirely silly

Thanks for "silly". Last time it was "academic". Is it better? :-)

to be inventing something that's morally a one-row table, when we
already have perfectly good one-row tables.

Hmmm. Although I can think of ways to ensure that a table is one row
(unique, check, trigger, rules, whatever...), none are straightforward.

The value of a server-side variable facility would be mostly that it
doesn't have all the overhead implied by tables.

I do not know that. I think that discussing semantics and syntax does
have value as well.

ISTM that there are 4 intermixed issues related to server-side variables:

(1) what should be their possible semantics and capabilities
e.g. with respect to transactions, permissions, namespace, types,
constraints, ...

=> what is the use cases for them?

(2) what should be their syntax

=> what do you want do write to use them?

(3) how to implement them

(4) how to optimize the implementation, eventually

I think that a special purpose variable infrastructure implied by your
remark is just starting from the end point. The first three points seem
relevant too because they help focus on other issues.

I'm not claiming that having variables as one-row tables is the best ever
solution, but I do not think that it is such a terrible idea either. At
least it provides a clear and consistent range of existing semantics out
of the box (unlogged/temporary/standard, permissions...), thus provide a
clean model for discussion, even if it is rejected for some reason
afterwards.

Also it seems easy to implement (as syntactic sugar) and play with, and
I'm not sure that sure that performance would be that bad, as for session
(temporary unlogged?) variables they would probably simply stay in cache.
If the performance is an issue in concrete use cases they could be
optimized in the end, to reduce time and space, which would be great.

I think that is a direct reason not to think about overhead like
constraints, as well.

Hmmm. If there is no constraint, the overhead is limited? If one has use
for constraints, then they can pay the overhead?

--
Fabien.

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

On 12/23/16 4:24 PM, Fabien COELHO wrote:

I think that a special purpose variable infrastructure implied by your
remark is just starting from the end point. The first three points seem
relevant too because they help focus on other issues.

If you want to ignore performance, there are things you can do with
non-transactional variables that are simply not possible with tables.
But even ignoring that, the performance cost of temp tables is massive
compared to variables. Not only is the access far more complex, but
bloating is a major problem (both in the table itself as well as in the
catalog). That's part of the driver for all the discussion about things
like permanent temp tables (which still leaves a bloat and performance
problem in the table itself).
--
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
855-TREBLE2 (855-873-2532)

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

Hello Pavel,

Hmmm. Switching role within a transaction. I never did need that... but
that is a use case.

Any application with security definer functions - depends on different
communities - it is used sometimes strongly.

Hmmm. So I understand that you would like to do something like:

- call a secure function which sets a session variable with restricted
permissions
- do some things which cannot access or change the variable
- call another secure function which can access, update, remove the
variable...

Probably we have different expectation from variables. I don't expect so
variable can be changed by any rollback.

Indeed, it seems that we do not have the same expectations.

What is use case for transactional variables? I miss any experience - I
wrote lot plpgsql lines and newer would it.

Here are two use cases, which are neither good nor bad, but that I have in
mind when I'm argumenting.

(1) First use case I'm thinking of is software update, with persistent
transactional variables, eg:

-- let assume we have application_version = 1
BEGIN;
-- lock things up
-- update application schema and data to version 2
-- set application_version = 2
-- unlock things
COMMIT;

I would not want the application_version to remain at 2 if the COMMIT
fails, obviously. This is usually implemented with a one-row table, but
some kind of variable syntax could be quite elegant. For this use case, a
variable should be persistant, it does not it to be efficient, it should
have permissions and should be transactional.

(2) Second use case I'm thinking of is some kind of large batch
management.

-- variable batch_1_is_done = false
BEGIN;
-- try to do large batch 1...
-- set batch_1_is_done = true
COMMIT;
-- then test whether it worked, do some cleanup if not...
-- there are some discussions to get some \if in psql...

For this second example, I would not like batch_is_done to be true if the
commit failed, but I do not think that any permissions would be useful,
and it would be fine if it is just accessible from a session only.

When I remove ACID, and allow only one value - then the implementation can
be simple and fast - some next step can be support of expandable types.
Sure - anybody can use temporary tables now and in future. But it is slow -
more now, because we doesn't support global temporary tables. But ACID
needs lot of CPU times, needs possible VACUUM, ...

Yep, but if you need persistant and transactional then probably you can
accept less performant...

No ACID variables are simple to implement, simple to directly accessible
from any PL (although I am thinking about better support in 2nd phase for
PLpgSQL).

ACID may be simple to implement with some kind of underlying table, or
maybe a row in a table. How efficient it could be is another question, but
then if the feature does not allow some use cases, and it not so
interesting to have it. That is why I think that it is worth discussing
"silly" semantics and syntax.

The namespace issue is unclear to me. Would a variable name clash with a
table name? It should if you want to be able write "SELECT stuff FROM
variablename", which may or may not be a good idea.

--
Fabien.

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

2016-12-24 12:19 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

Hmmm. Switching role within a transaction. I never did need that... but

that is a use case.

Any application with security definer functions - depends on different
communities - it is used sometimes strongly.

Hmmm. So I understand that you would like to do something like:

- call a secure function which sets a session variable with restricted
permissions
- do some things which cannot access or change the variable
- call another secure function which can access, update, remove the
variable...

Probably we have different expectation from variables. I don't expect so

variable can be changed by any rollback.

Indeed, it seems that we do not have the same expectations.

What is use case for transactional variables? I miss any experience - I

wrote lot plpgsql lines and newer would it.

Here are two use cases, which are neither good nor bad, but that I have in
mind when I'm argumenting.

(1) First use case I'm thinking of is software update, with persistent
transactional variables, eg:

-- let assume we have application_version = 1
BEGIN;
-- lock things up
-- update application schema and data to version 2
-- set application_version = 2
-- unlock things
COMMIT;

I would not want the application_version to remain at 2 if the COMMIT
fails, obviously. This is usually implemented with a one-row table, but
some kind of variable syntax could be quite elegant. For this use case, a
variable should be persistant, it does not it to be efficient, it should
have permissions and should be transactional.

(2) Second use case I'm thinking of is some kind of large batch management.

-- variable batch_1_is_done = false
BEGIN;
-- try to do large batch 1...
-- set batch_1_is_done = true
COMMIT;
-- then test whether it worked, do some cleanup if not...
-- there are some discussions to get some \if in psql...

For this second example, I would not like batch_is_done to be true if the
commit failed, but I do not think that any permissions would be useful, and
it would be fine if it is just accessible from a session only.

On server side you can use PLpgSQL and handling exception.

On client side you can use technique used in MSSQL, where variables are not
transactional too.

BEGIN
-- servar state 1
statement;
-- servar state 2
statement;
COMMIT

We should to introduce client side session variable :STATUS

\if eq(:STATUS, 'ok')
...

When I remove ACID, and allow only one value - then the implementation can

be simple and fast - some next step can be support of expandable types.
Sure - anybody can use temporary tables now and in future. But it is slow
-
more now, because we doesn't support global temporary tables. But ACID
needs lot of CPU times, needs possible VACUUM, ...

Yep, but if you need persistant and transactional then probably you can
accept less performant...

When you accept less performance, then you can use temporary tables. You
can easy wrap it by few polymorphic functions.

No ACID variables are simple to implement, simple to directly accessible

from any PL (although I am thinking about better support in 2nd phase for
PLpgSQL).

ACID may be simple to implement with some kind of underlying table, or
maybe a row in a table. How efficient it could be is another question, but
then if the feature does not allow some use cases, and it not so
interesting to have it. That is why I think that it is worth discussing
"silly" semantics and syntax.

The namespace issue is unclear to me. Would a variable name clash with a
table name? It should if you want to be able write "SELECT stuff FROM
variablename", which may or may not be a good idea.

It is based on history and experience - one fundamental issue of languages
for stored procedures is a conflict of variables and SQL identifiers. When
variables are based on pg_class, there are not possibility to any new
conflict.

More I can use a security related to schema - It is partial coverage of
package variables.

Regards

Pavel

Show quoted text

--
Fabien.

Hello Jim,

If you want to ignore performance, there are things you can do with
non-transactional variables that are simply not possible with tables. But
even ignoring that, the performance cost of temp tables is massive compared
to variables.

Ok, then read "variables are like tables" instead of "variables are
tables". Note that I'm definitely interested in performance in the end,
but I'm first interested in discussing features.

Currently I'm unconvinced by a proposal of variables that are in the
relation namespace (pg_class), are subject to GRANT/REVOKE, are managed
with CREATE/DROP *but* are somehow partly non transactional, and only
alive through sessions, and are only accessed through functions...

My current thinking is that the proposed design is either too heavy
(pg_class, permissions) or too light (then why not possible persistence
and/or transactions?): I would be more at ease with very light-weight
typed? session variables stored in the server process "as is", without any
attempt at pg_class & permissions, or with more full featured variables,
but not something half-baked which seems designed for a particular use
case that I do not have.

Not only is the access far more complex, but bloating is a major problem
(both in the table itself as well as in the catalog). That's part of the
driver for all the discussion about things like permanent temp tables
(which still leaves a bloat and performance problem in the table
itself).

If a variable as currently discussed is in pg_class and subject to
permissions, then probably it will cost on the catalog side anyway, and at
least their existent would be transactional even if their value is not.

--
Fabien.

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

2016-12-26 8:30 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Jim,

If you want to ignore performance, there are things you can do with

non-transactional variables that are simply not possible with tables. But
even ignoring that, the performance cost of temp tables is massive compared
to variables.

Ok, then read "variables are like tables" instead of "variables are
tables". Note that I'm definitely interested in performance in the end, but
I'm first interested in discussing features.

Currently I'm unconvinced by a proposal of variables that are in the
relation namespace (pg_class), are subject to GRANT/REVOKE, are managed
with CREATE/DROP *but* are somehow partly non transactional, and only alive
through sessions, and are only accessed through functions...

My current thinking is that the proposed design is either too heavy
(pg_class, permissions) or too light (then why not possible persistence
and/or transactions?): I would be more at ease with very light-weight
typed? session variables stored in the server process "as is", without any
attempt at pg_class & permissions, or with more full featured variables,
but not something half-baked which seems designed for a particular use case
that I do not have.

Not only is the access far more complex, but bloating is a major problem

(both in the table itself as well as in the catalog). That's part of the
driver for all the discussion about things like permanent temp tables
(which still leaves a bloat and performance problem in the table itself).

If a variable as currently discussed is in pg_class and subject to
permissions, then probably it will cost on the catalog side anyway, and at
least their existent would be transactional even if their value is not.

the access right check has **constant** small cost (check in object cache).
The proposed variables has not any negative effect on catalogue bloating
because a metadata are persistent.

It is reason why I use statement "CREATE VARIABLE", not "DECLARE VARIABLE"

Regards

Pavel

Show quoted text

--
Fabien.

Hello Pavel,

On Sat, 24 Dec 2016, Pavel Stehule wrote:

Maybe you could consider removing the part of the message that you are not
responding to, so that it would be easier for the reader to see your
answers and comments.

Hmmm. So I understand that you would like to do something like:

- call a secure function which sets a session variable with restricted
permissions
- do some things which cannot access or change the variable
- call another secure function which can access, update, remove the
variable...

I'm still not clear with your use case. Did I read you correctly? ISTM
that the above use case could be managed with insert/update/delete in a
table with chosen permissions from the functions...

Yep, but if you need persistant and transactional then probably you can
accept less performant...

When you accept less performance, then you can use temporary tables. You
can easy wrap it by few polymorphic functions.

Probably. This is probably true as well from what I understood from your
use case.

The namespace issue is unclear to me. Would a variable name clash with a
table name? It should if you want to be able write "SELECT stuff FROM
variablename", which may or may not be a good idea.

It is based on history and experience - one fundamental issue of languages
for stored procedures is a conflict of variables and SQL identifiers.

I agree that this is a pain, which could be solved by using a prefix, say
$<name> for instance.

When variables are based on pg_class, there are not possibility to any
new conflict.

If variables are based on pg_class, ISTM that they will cost anyway.

To sum up my current opinion, taking into accounts your use case and Tom &
Jim argments about performance, I think that variables should be either:

- full-featured database objects well integrated in the database logic:
CREATE/ALTER/DROP, in pg_class, subject to standard permissions,
constraints, transactions, possibly persistent... Basically like a one-row
table, although the implementation should be more efficient, I agree.

** OR **

- very light-weight, a simple server process key-value store, which would
not use CREATE/ALTER/DROP which suggest otherwise, they would not be
subject to permissions nor transactions nor persistence but die with the
session, goodbye. A possible concession to permissions would be to have a
per-role store, and/or some visibility/accessibility declaration at
creation time, but certainly not GRANT/RESTORE syntax which suggest a
database persistent object.

I'm very reserved about anything in between these two options, which looks
like a database object but is not really one, so I think that it create
confusion.

In both case, the syntax should be nice and elegant... i.e. not only based
on functions, probably it should use some prefix convention (@, $)...
For the light weight option.

DECLARE @someday DATE [ = <SQL EXPRESSION> ] [visibility restriction?];
... then use @now as a possible value anywhere, which will be
substituted quite early in the execution process, before planning.
-- update a variable value:
[SET, ASSIGN, ... nothing?] @someday = <SQL EXPRESSION>;

Ok, that is basically more or less the mysql syntax, too bad, but I think
it makes sense for a lightweight object which should not look like a
database object at all to avoid confusion.

As far as implementation is concerned, I would use a TEXT to TEXT hash
table, and implicit cast the result when substituting.

@var -> 'text value of var'::type_it_was_declared_with

--
Fabien.

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

Hi

In both case, the syntax should be nice and elegant... i.e. not only based
on functions, probably it should use some prefix convention (@, $)...
For the light weight option.

DECLARE @someday DATE [ = <SQL EXPRESSION> ] [visibility restriction?];
... then use @now as a possible value anywhere, which will be
substituted quite early in the execution process, before planning.
-- update a variable value:
[SET, ASSIGN, ... nothing?] @someday = <SQL EXPRESSION>;

Ok, that is basically more or less the mysql syntax, too bad, but I think
it makes sense for a lightweight object which should not look like a
database object at all to avoid confusion.

As far as implementation is concerned, I would use a TEXT to TEXT hash
table, and implicit cast the result when substituting.

@var -> 'text value of var'::type_it_was_declared_with

We are talking about two different features (although the part of name can
be same):

you are talk about light session variables like MSSQL or MySQL (with same
syntax), I am talking about secure session variables like Oracle package
variables (with similar access syntax).

Theoretically, there can be implemented both - but cannot be implemented
together. Its are partially different features. My proposal is clearly
related to analogy with Oracle package variables and should to help to
people who does migration from Oracle, or who writing application in Oracle
style - database first, almost logic in database.

I have two important reasons why I insist on pg_class base.

1. security .. it is really fundamental part
2. possibility to static check by plpgsql_check - without entry in pg_class
(or other catalogue table) I have not any valid information about type,
existence of any variable.

Although I am not supporter (due possible issues with plpgsql_checks) of
MySQL or MSSQL style variables I am not strongly against this
implementation with same syntax. But it is different feature, with
different benefits and costs.

I didn't proposed the packages (and package variables) due issues in
multilingual PostgreSQL environment and because it is redundant to
PostgreSQL schemas. Instead I proposed >>secure global session variables<<
(global like global temporary tables).

Currently light session variables can be implemented as not big extension.
Secure session variables depends on pg_class internals.

I am not sure if we need a special symbols - it is traditional only.
Set/Get functions can do same work - years we use same technique for
sequences. Setter function is simply. Currently is impossible to write
elegant getter function - because the analyzer has limited work with "any"
returning functions.

Can be nice to have special hook for functions that returns "any" to push
there some other external informations.

Regards

Pavel

Show quoted text

--
Fabien.

2016-12-26 10:54 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

In both case, the syntax should be nice and elegant... i.e. not only
based on functions, probably it should use some prefix convention (@, $)...
For the light weight option.

DECLARE @someday DATE [ = <SQL EXPRESSION> ] [visibility restriction?];
... then use @now as a possible value anywhere, which will be
substituted quite early in the execution process, before planning.
-- update a variable value:
[SET, ASSIGN, ... nothing?] @someday = <SQL EXPRESSION>;

Ok, that is basically more or less the mysql syntax, too bad, but I think
it makes sense for a lightweight object which should not look like a
database object at all to avoid confusion.

As far as implementation is concerned, I would use a TEXT to TEXT hash
table, and implicit cast the result when substituting.

@var -> 'text value of var'::type_it_was_declared_with

We are talking about two different features (although the part of name can
be same):

you are talk about light session variables like MSSQL or MySQL (with same
syntax), I am talking about secure session variables like Oracle package
variables (with similar access syntax).

Theoretically, there can be implemented both - but cannot be implemented
together. Its are partially different features. My proposal is clearly
related to analogy with Oracle package variables and should to help to
people who does migration from Oracle, or who writing application in Oracle
style - database first, almost logic in database.

I have two important reasons why I insist on pg_class base.

1. security .. it is really fundamental part

Dynamic created variables (like MySQL) cannot be safe - anybody can create
variables with self preferred visibility.

Show quoted text

2. possibility to static check by plpgsql_check - without entry in
pg_class (or other catalogue table) I have not any valid information about
type, existence of any variable.

Although I am not supporter (due possible issues with plpgsql_checks) of
MySQL or MSSQL style variables I am not strongly against this
implementation with same syntax. But it is different feature, with
different benefits and costs.

I didn't proposed the packages (and package variables) due issues in
multilingual PostgreSQL environment and because it is redundant to
PostgreSQL schemas. Instead I proposed >>secure global session variables<<
(global like global temporary tables).

Currently light session variables can be implemented as not big extension.
Secure session variables depends on pg_class internals.

I am not sure if we need a special symbols - it is traditional only.
Set/Get functions can do same work - years we use same technique for
sequences. Setter function is simply. Currently is impossible to write
elegant getter function - because the analyzer has limited work with "any"
returning functions.

Can be nice to have special hook for functions that returns "any" to push
there some other external informations.

Regards

Pavel

--
Fabien.

Hello Pavel,

you are talk about light session variables like MSSQL or MySQL (with same
syntax), I am talking about secure session variables like Oracle package
variables (with similar access syntax).

Hmmm. I do not know this Oracle stuff... After looking at the online
documentation, my understanding of "Oracle package variables" refers to
full fledged database objects, in particular they are not session
limited. The example I found is about a variable holding the total number
of employees, with functions hire & fire (well, they call it remove_emp)
to update them when inserting or deleting an employee.

AFAICS they are shared between backends, subjects to transactions and
permissions, constraints and so on. So they look more like the first
category I outlined, and probably they cost as any persistent database
object, which make sense. They constitute a consistent design.

This is * not * what you are proposing.

[...] I have two important reasons why I insist on pg_class base.

1. security .. it is really fundamental part
2. possibility to static check by plpgsql_check - without entry in pg_class
(or other catalogue table) I have not any valid information about type,
existence of any variable.

Hmmm. I'm not quite convinced that putting session variables in pg_class
is a good idea, because it adds significant costs for the use case of
"standard" simple session variables, which is quite more probable than
session-but-with-permissions variables.

As far as security is concerned, ISTM that a limited but still useful
access control can be implemented for a key-value store with simple
session variables, see below.

As far as typing is concerned, ISTM that it can be done as well for
session variables by going through text and using casts when setting and
getting values, or through some other simple ad-hoc checking.

Although I am not supporter (due possible issues with plpgsql_checks) of
MySQL or MSSQL style variables I am not strongly against this
implementation with same syntax. But it is different feature, with
different benefits and costs.

I didn't proposed the packages (and package variables) due issues in
multilingual PostgreSQL environment and because it is redundant to
PostgreSQL schemas.

Instead I proposed >>secure global session variables<< (global like
global temporary tables).

That's where I'm leaving you and start disagreeing, because it is not
consistent: you are proposing session variables that do not look like
session variable and are somehow costly.

I could agree with real "secure global variables" as in Oracle packages, a
consistent kind of database object which stores a persistent value safely
and securely. That would cost, but that is life in a database, you have
great things for a price. Probably that could be implemented as a row in
some special table, or as a one-row table, or whatever.

I could also agree with à la MS or MY-SQL session variables that look like
session variables, with limited ambition, light-weight and inexpensive.

I disagree with having a half-backed stuff, where something looks like a
database object (i.e. CREATE/ALTER/DROP/GRANT/REVOKE) but is really a
session object with strange properties. I also disagree to the pg_class
approach as it creates in effect an expensive session object while a
simple session object would cost much less and would be much more useful.

To summarize, I still think that your design is not consistent, even if it
makes sense for some degree wrt the implementation.

A possible compromise I have proposed is to have some declared access
restrictions on simple session variables, so that say only the owner can
access it, but they should stay and look like light-weight session
variables nevertheless. That could look like:

SET ROLE Admin;
DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin
SET @secure_variable = 3;

SET ROLE BasicUser;
SELECT @secure_variable; -- say NULL or error does not exist...

SET ROLE Admin;
SELECT @secure_variable; 3
...

Currently light session variables can be implemented as not big
extension.

Sure. I would review that as well.

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

2016-12-26 13:08 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

you are talk about light session variables like MSSQL or MySQL (with same

syntax), I am talking about secure session variables like Oracle package
variables (with similar access syntax).

Hmmm. I do not know this Oracle stuff... After looking at the online
documentation, my understanding of "Oracle package variables" refers to
full fledged database objects, in particular they are not session limited.
The example I found is about a variable holding the total number of
employees, with functions hire & fire (well, they call it remove_emp) to
update them when inserting or deleting an employee.

AFAICS they are shared between backends, subjects to transactions and
permissions, constraints and so on. So they look more like the first
category I outlined, and probably they cost as any persistent database
object, which make sense. They constitute a consistent design.

no
http://stackoverflow.com/questions/2383061/scope-of-oracle-package-level-variables

This is * not * what you are proposing.

[...] I have two important reasons why I insist on pg_class base.

1. security .. it is really fundamental part
2. possibility to static check by plpgsql_check - without entry in
pg_class
(or other catalogue table) I have not any valid information about type,
existence of any variable.

Hmmm. I'm not quite convinced that putting session variables in pg_class
is a good idea, because it adds significant costs for the use case of
"standard" simple session variables, which is quite more probable than
session-but-with-permissions variables.

As far as security is concerned, ISTM that a limited but still useful
access control can be implemented for a key-value store with simple session
variables, see below.

As far as typing is concerned, ISTM that it can be done as well for
session variables by going through text and using casts when setting and
getting values, or through some other simple ad-hoc checking.

Although I am not supporter (due possible issues with plpgsql_checks) of

MySQL or MSSQL style variables I am not strongly against this
implementation with same syntax. But it is different feature, with
different benefits and costs.

I didn't proposed the packages (and package variables) due issues in

multilingual PostgreSQL environment and because it is redundant to
PostgreSQL schemas.

Instead I proposed >>secure global session variables<< (global like global

temporary tables).

That's where I'm leaving you and start disagreeing, because it is not
consistent: you are proposing session variables that do not look like
session variable and are somehow costly.

I could agree with real "secure global variables" as in Oracle packages, a
consistent kind of database object which stores a persistent value safely
and securely. That would cost, but that is life in a database, you have
great things for a price. Probably that could be implemented as a row in
some special table, or as a one-row table, or whatever.

I could also agree with à la MS or MY-SQL session variables that look like
session variables, with limited ambition, light-weight and inexpensive.

I disagree with having a half-backed stuff, where something looks like a
database object (i.e. CREATE/ALTER/DROP/GRANT/REVOKE) but is really a
session object with strange properties. I also disagree to the pg_class
approach as it creates in effect an expensive session object while a simple
session object would cost much less and would be much more useful.

To summarize, I still think that your design is not consistent, even if it
makes sense for some degree wrt the implementation.

A possible compromise I have proposed is to have some declared access
restrictions on simple session variables, so that say only the owner can
access it, but they should stay and look like light-weight session
variables nevertheless. That could look like:

SET ROLE Admin;
DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin
SET @secure_variable = 3;

SET ROLE BasicUser;
SELECT @secure_variable; -- say NULL or error does not exist...

what will be if BasicUser does DECLARE @secure_variable

There are not any granularity of rights - you cannot to grant access ...

SET ROLE Admin;
SELECT @secure_variable; 3

I am sorry, I don't see benefit in your proposal. Probably there will be
only one agreement, so there are not agreement between us :(

Regards

Pavel

Show quoted text

...

Currently light session variables can be implemented as not big extension.

Sure. I would review that as well.

--
Fabien.

A possible compromise I have proposed is to have some declared access
restrictions on simple session variables, so that say only the owner can
access it, but they should stay and look like light-weight session
variables nevertheless. That could look like:

SET ROLE Admin;
DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin

Why introduce any another security system?

Regards

Pavel

Hello Pavel,

AFAICS they are shared between backends, [...] They constitute a
consistent design.

no
http://stackoverflow.com/questions/2383061/scope-of-oracle-package-level-variables

If stackoverflow says so, too bad for me:-) Now I do not understand the
point of the example I read on Oracle documentation: why having an
employee count accessed by some functions if it is reset on each new
session?

So I do retract "it constitute a consistent design". It looks more like a
PL/SQL confined hack.

Note that Oracle also seems to have session variables with set with DEFINE
and referenced with &variable.

[...] That could look like:

SET ROLE Admin;
DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin
SET @secure_variable = 3;

SET ROLE BasicUser;
SELECT @secure_variable; -- say NULL or error does not exist...

what will be if BasicUser does DECLARE @secure_variable

Then there would be a distinct global @secure_variable unrelated to the
previous one, that would be hidden from Admin who would see its own
private @secure_variable. Maybe "restrict" is not the right word, though,
let us use "private".

SET ROLE User1;
-- use @var: does not exist in scope error
DECLARE @var INTEGER PRIVATE;
SET @var = 1;
-- use @var: get 1

SET ROLE User2;
-- use @var: does not exist in scope error
DECLARE @var INTEGER PUBLIC;
SET @var = 2;
-- use @var; get 2

SET ROLE User1;
-- use @var: get 1 (private version)

SET ROLE User3;
-- use @var: get 2 (public version created by User2).

There are not any granularity of rights - you cannot to grant access ...

Indeed, at least directly. With the above version you can just control
whether everybody or only the owner has access.

However with some minimal more effort the owner of a private session
variable could provide a grantable function for accessing this variable:
the benefit would be that the function is permanent, i.e. would not need
to be granted each time the variable is used, it could be done once and
for all.

CREATE FUNCTION setSecret(INT) SECURITY DEFINER ... AS $$
DECLARE IF NOT EXISTS @secret TEXT PRIVATE;
SET @secret = $1;
$$ LANGUAGE SQL;

CREATE FUNCTION useSecret(TEXT) SECURITY DEFINER TEXT AS $$
-- would fail if @secret has not been set yet...
SELECT sha256sum(@secret || ':' || $1);
$$ LANGUAGE SQL;

CREATE FUNCTION getSecret() RETURNS TEXT SECURITY DEFINER AS $$
DECLARE IF NOT EXISTS @secret TEXT PRIVATE;
SELECT @secret;
$$ LANGUAGE SQL;

-- then
REVOKE/GRANT ... ON FUNCTION set/use/getSecret(...);

I am sorry, I don't see benefit in your proposal.

The benefit I see is to have MS/MY-SQL/Oracle like light-weight
(inexpensive, untransactional) session variables and still a minimal
access control which might be enough for significant use cases.

If more is really needed, consider the function hack, or maybe some
one-row table with all the power of grant. Ok, the table solution is more
heavy weight, but then this is also for a special requirement, and it
would work as well for persistence.

Probably there will be only one agreement, so there are not agreement
between us :(

It seems so. I do believe that I am trying to propose a solution which
take into account your use case as I understand it (you did not confirm
nor infirm) which is to store securely but not safely some kind of
temporary data between different function calls with SECURITY DEFINER
within the same session.

I'm trying to avoid "special-case" medium-weight (i.e. pg_class-based)
session variables with permissions, which could preclude MY/MS-SQL/Oracle
like light-weight session variables which are I think interesting in their
own right.

--
Fabien.

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

2016-12-26 15:53 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

AFAICS they are shared between backends, [...] They constitute a

consistent design.

no
http://stackoverflow.com/questions/2383061/scope-of-oracle-
package-level-variables

If stackoverflow says so, too bad for me:-) Now I do not understand the
point of the example I read on Oracle documentation: why having an employee
count accessed by some functions if it is reset on each new session?

please, can send link?

so some better documentation
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS99926

I am sure, so package variables are not shared between sessions/backends -
bacause Oracle uses different mechanism for interprocess communication -
wrote it in Orafce

"When a session references a package item, Oracle Database instantiates the
package for that session. Every session that references a package has its
own instantiation of that package."

So I do retract "it constitute a consistent design". It looks more like a
PL/SQL confined hack.

Note that Oracle also seems to have session variables with set with DEFINE
and referenced with &variable.

[...] That could look like:

SET ROLE Admin;
DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin
SET @secure_variable = 3;

SET ROLE BasicUser;
SELECT @secure_variable; -- say NULL or error does not exist...

what will be if BasicUser does DECLARE @secure_variable

Then there would be a distinct global @secure_variable unrelated to the
previous one, that would be hidden from Admin who would see its own private
@secure_variable. Maybe "restrict" is not the right word, though, let us
use "private".

SET ROLE User1;
-- use @var: does not exist in scope error
DECLARE @var INTEGER PRIVATE;
SET @var = 1;
-- use @var: get 1

SET ROLE User2;
-- use @var: does not exist in scope error
DECLARE @var INTEGER PUBLIC;
SET @var = 2;
-- use @var; get 2

SET ROLE User1;
-- use @var: get 1 (private version)

SET ROLE User3;
-- use @var: get 2 (public version created by User2).

There are not any granularity of rights - you cannot to grant access ...

Indeed, at least directly. With the above version you can just control
whether everybody or only the owner has access.

However with some minimal more effort the owner of a private session
variable could provide a grantable function for accessing this variable:
the benefit would be that the function is permanent, i.e. would not need to
be granted each time the variable is used, it could be done once and for
all.

CREATE FUNCTION setSecret(INT) SECURITY DEFINER ... AS $$
DECLARE IF NOT EXISTS @secret TEXT PRIVATE;
SET @secret = $1;
$$ LANGUAGE SQL;

CREATE FUNCTION useSecret(TEXT) SECURITY DEFINER TEXT AS $$
-- would fail if @secret has not been set yet...
SELECT sha256sum(@secret || ':' || $1);
$$ LANGUAGE SQL;

CREATE FUNCTION getSecret() RETURNS TEXT SECURITY DEFINER AS $$
DECLARE IF NOT EXISTS @secret TEXT PRIVATE;
SELECT @secret;
$$ LANGUAGE SQL;

-- then
REVOKE/GRANT ... ON FUNCTION set/use/getSecret(...);

I am sorry, I don't see benefit in your proposal.

The benefit I see is to have MS/MY-SQL/Oracle like light-weight
(inexpensive, untransactional) session variables and still a minimal access
control which might be enough for significant use cases.

If more is really needed, consider the function hack, or maybe some
one-row table with all the power of grant. Ok, the table solution is more
heavy weight, but then this is also for a special requirement, and it would
work as well for persistence.

Probably there will be only one agreement, so there are not agreement

between us :(

It seems so. I do believe that I am trying to propose a solution which
take into account your use case as I understand it (you did not confirm nor
infirm) which is to store securely but not safely some kind of temporary
data between different function calls with SECURITY DEFINER within the same
session.

I'm trying to avoid "special-case" medium-weight (i.e. pg_class-based)
session variables with permissions, which could preclude MY/MS-SQL/Oracle
like light-weight session variables which are I think interesting in their
own right.

I am sorry, it is not secure. Theoretically it can work if you have
granted order of function calls, but if not?

regards

Pavel

Show quoted text

--
Fabien.

Hello Pavel,

SET ROLE Admin;
DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin

Why introduce another security system?

That is a good question.

I would prefer to avoid it and just have simple session variables... but
this is not what you want, so I'm trying to find a compromise which both
gives you the feature you are seeking and would keep session variables as
inexpensive, i.e. without catalog costs.

A simplistic PUBLIC/PRIVATE permissions on simple session variable can be
done with nothing (no additional data structures): (store: hash_put(<owner
id if private or nothing> || '@varname', value); consult: if exists(<owner
id> || '@varname') then return it else if exists('@varname') then return
it else "error variable does not exist").

Now if you can offer an inexpensive GRANT/REVOKE on simple session
variables, i.e. without catalog changes, then I think I would agree to it,
even if I would not like it much.

The reason I "do not like much" is subjective. I associate SQL permission
commands (GRANT, REVOKE...) to real SQL "objects" (i.e. persistent,
transactional, secured things subject to CREATE ALTER DROP...). However
light-weight session variables are not really like that.

Also if you can have inexpensive GRANT/REVOKE then probably I would also
have to accept "CREATE SESSION VARIABLE @foo", because it would be
consistent to have it with GRANT/REVOKE. I would "not like it much" either
to have CREATE for an non persistant object, but that is life...

However I understood that for permissions you do need "pg_class", which
means catalog changes on session variable creation, which means expensive
for simple session variables, so not desirable.

--
Fabien.

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

2016-12-26 17:15 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

SET ROLE Admin;

DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin

Why introduce another security system?

That is a good question.

I would prefer to avoid it and just have simple session variables... but
this is not what you want, so I'm trying to find a compromise which both
gives you the feature you are seeking and would keep session variables as
inexpensive, i.e. without catalog costs.

A simplistic PUBLIC/PRIVATE permissions on simple session variable can be
done with nothing (no additional data structures): (store: hash_put(<owner
id if private or nothing> || '@varname', value); consult: if exists(<owner
id> || '@varname') then return it else if exists('@varname') then return it
else "error variable does not exist").

Now if you can offer an inexpensive GRANT/REVOKE on simple session
variables, i.e. without catalog changes, then I think I would agree to it,
even if I would not like it much.

The reason I "do not like much" is subjective. I associate SQL permission
commands (GRANT, REVOKE...) to real SQL "objects" (i.e. persistent,
transactional, secured things subject to CREATE ALTER DROP...). However
light-weight session variables are not really like that.

Also if you can have inexpensive GRANT/REVOKE then probably I would also
have to accept "CREATE SESSION VARIABLE @foo", because it would be
consistent to have it with GRANT/REVOKE. I would "not like it much" either
to have CREATE for an non persistant object, but that is life...

However I understood that for permissions you do need "pg_class", which
means catalog changes on session variable creation, which means expensive
for simple session variables, so not desirable.

I don't understand to "expensive" word. How much often you create/drop
these variables? The deployment cycle is similar to functions. I don't
propose any feature, that can enforce bloating of system catalogue.

The variable metadata will be persistent across sessions. I can imagine the
local (temporal) session variable where life cycle of metadata will be
related to session like our current temporary tables. But it is not in this
proposal.

Regards

Pavel

Show quoted text

--
Fabien.

please, can send link?

My badly interpreted PL/SQL example was on the same page you point to
below:

so some better documentation
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS99926

There is a private 'number_hired' which given its name I thought was
counting the number of employee, but it was just counting the number of
"hire_employee" calls in the current session... Not very interesting.

I am sure, so package variables are not shared between sessions/backends

Indeed, I misinterpreted the Oracle documentation example.

[ grantable function example to access a private session variable... ]

I am sorry, it is not secure. Theoretically it can work if you have
granted order of function calls, but if not?

I'm not sure I understand.

If you do not grant/revoke permissions as you want on the functions, then
it can be invoked by anybody.

My point is that it is *possible* to tune permissions so as to control
exactly who may access a private session variable.

That is exactly the same with a grantable session variable if you do not
have done the necessary grant/revoke, there is no difference?

--
Fabien.

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

2016-12-26 17:33 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

please, can send link?

My badly interpreted PL/SQL example was on the same page you point to
below:

so some better documentation

https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packa
ges.htm#LNPLS99926

There is a private 'number_hired' which given its name I thought was
counting the number of employee, but it was just counting the number of
"hire_employee" calls in the current session... Not very interesting.

I am sure, so package variables are not shared between sessions/backends

Indeed, I misinterpreted the Oracle documentation example.

[ grantable function example to access a private session variable... ]

I am sorry, it is not secure. Theoretically it can work if you have
granted order of function calls, but if not?

I'm not sure I understand.

If you do not grant/revoke permissions as you want on the functions, then
it can be invoked by anybody.

My point is that it is *possible* to tune permissions so as to control
exactly who may access a private session variable.

That is exactly the same with a grantable session variable if you do not
have done the necessary grant/revoke, there is no difference?

If you use pattern DECLARE IF NOT EXISTS, you cannot be sure so some other
did it. It is working only if you create variables in session as first.

Only if object is fixed in schema, then object is trustworthy - because you
have to have rights to modify schema. In my proposal only trustworthy user
can create the variable in some schema. Not trustworthy user can use public
schema, or we can support temporary objects (similar to your proposal) in
hypothetical schema "private". I have strong tools in Postgres for
enforcing security, and I would to use it.

Regards

Pavel

Show quoted text

--
Fabien.

Hello Pavel,

I don't understand to "expensive" word.

Hmmm...

How much often you create/drop these variables?

Hmmm... As for "session variables" à la MY/MS-SQL, ISTM that a variable is
"created" each time it is asked for, and it disappears completely at the
end of the session... So you can have some kind of minimal SQL scripting
with variables executed server-side, without resorting to a PL. Also
useful in interactive mode, although there the performance is less an
issue.

The deployment cycle is similar to functions. I don't
propose any feature, that can enforce bloating of system catalogue.

Hmmm....

The variable metadata will be persistent across sessions. I can imagine the
local (temporal) session variable where life cycle of metadata will be
related to session like our current temporary tables. But it is not in this
proposal.

So it seems that I misunderstood a key detail in your proposal. Please
accept my apology for my slow witedness. It is better to discuss in front
of a white board...

Now I understand that you want to create a kind of "persistant" session
variable... that is at each new session the variable is instancianted in
the session "automatically", whether the session will use it or not... Or
is it instanciated on demand, i.e. when explicitely accessed?

Could you confirm the interpretation? ISTM that "on demand" would be
better.

As you gathered I was understanding that the "CREATE VARIABLE & GRANTS"
were to be issued in each session for each variable used, inducing catalog
changes each time, hence my quite heavy ranting...

What I understand now is still a somehow strange object, but nevertheless
the CREATE, DROP, GRANT, REVOKE at least are more justified because then
object is somehow really persistent in the database, even if not with a
value.

So I'll have to think about it...

A few more questions:

Bar the grantability, why wouldn't simple session variables work for this
purpose? That is what is the added value of having them "declared"
permanently, compared to created on demand, if the creation is really
light weight?

ISTM that in the Oracle package version, they are linked to PL/SQL, they
are not part of SQL layer itself, so maybe they are only created when some
PL/SQL from the package is invoked, and not created otherwise?

How would this feature interact with a kind of non persistent "simple"
session variables that are found in MY/MS/Oracle SQL? One of my concern is
that such a feature should not preclude other kind of session variables.

And how would it interact with some "fully persistent/shared" variables?

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

2016-12-26 18:20 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

I don't understand to "expensive" word.

Hmmm...

How much often you create/drop these variables?

Hmmm... As for "session variables" à la MY/MS-SQL, ISTM that a variable is
"created" each time it is asked for, and it disappears completely at the
end of the session... So you can have some kind of minimal SQL scripting
with variables executed server-side, without resorting to a PL. Also useful
in interactive mode, although there the performance is less an issue.

The deployment cycle is similar to functions. I don't

propose any feature, that can enforce bloating of system catalogue.

Hmmm....

The variable metadata will be persistent across sessions. I can imagine the

local (temporal) session variable where life cycle of metadata will be
related to session like our current temporary tables. But it is not in
this
proposal.

So it seems that I misunderstood a key detail in your proposal. Please
accept my apology for my slow witedness. It is better to discuss in front
of a white board...

sure, mainly with my language skills

Now I understand that you want to create a kind of "persistant" session
variable... that is at each new session the variable is instancianted in
the session "automatically", whether the session will use it or not... Or
is it instanciated on demand, i.e. when explicitely accessed?

I am starting with simple default solution - variable is initialized on
demand (when it is used first time in session). Attention: in my concept -
initialization and creation are different things.

In future initialization can be joined with login - we can have a autologin
function (trigger), or we can have a login initialized variables (and if
default expr is function call, then these functions can be used as
autologin functions).

Could you confirm the interpretation? ISTM that "on demand" would be
better.

Now, what I propose, and what is implemented in prototype is "on demand"

As you gathered I was understanding that the "CREATE VARIABLE & GRANTS"
were to be issued in each session for each variable used, inducing catalog
changes each time, hence my quite heavy ranting...

What I understand now is still a somehow strange object, but nevertheless
the CREATE, DROP, GRANT, REVOKE at least are more justified because then
object is somehow really persistent in the database, even if not with a
value.

metadata are persistent like functions, sequences - the value is related to
session, only value.

So I'll have to think about it...

A few more questions:

Bar the grantability, why wouldn't simple session variables work for this
purpose? That is what is the added value of having them "declared"
permanently, compared to created on demand, if the creation is really light
weight?

the rights should be persistent, and should be attached to some persistent
object. Hypothetically, we can introduce new kind of objects, but it
disallow future usage of direct DML and SELECT statements.

ISTM that in the Oracle package version, they are linked to PL/SQL, they
are not part of SQL layer itself, so maybe they are only created when some
PL/SQL from the package is invoked, and not created otherwise?

PL/SQL is different creature - it is living outside SQL catalogue - in
packages. I would not to reimplemented it for PL/pgSQL from following
reasons: we have schemas (that can be used as Oracle packages), we have a
extensions (that can be used partially as Oracle's packages), we have a mix
PL languages - more time I mixed PLpgSQL and PLPerlu. So mapping 1:1 from
Oracle is not good for Postgres.

How would this feature interact with a kind of non persistent "simple"
session variables that are found in MY/MS/Oracle SQL? One of my concern is
that such a feature should not preclude other kind of session variables.

depends .. In my terminology your proposal is "untrusted temporary local
session variables" - it can share 50% of code - more if implementation will
be based on getter/setter function, less if it will be based on gram
implementation.

These variables should not be declared explicitly - it can be declared
implicitly by setting. They should not use any schema - bat can use
getter/setter functions

so you can write

select setvar('@var1', 10);
select getvar('@var1')

I little bit afraid of direct using the variables in query - inside special
functions we (and users) have control to choose volatility: direct using
can do some unexpected behave for users.

And how would it interact with some "fully persistent/shared" variables?

I have not any use case for this. I don't know about any similar feature in
other database systems. Oracle uses dbms_pipe or dbms_alert for
interprocess communication.

I am thinking so it is possible to implement. If it is not ACID, then it
can work as custom statistic counters. If it should be ACID? Then is better
to use table. What I know, now is preferred share nothing design in
parallel systems - so for me, it looks like little bit dangerous feature -
and I see only one use case - custom statistics - where possible race
condition is not hard issue.

But I don't plan to implement it in first stage. There should be strong use
case for implementing any complex feature in shared memory. But any
implementation doesn't breaking to implement it in feature.

Regards

Pavel

Show quoted text

--
Fabien.

2016-12-26 19:13 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2016-12-26 18:20 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

And how would it interact with some "fully persistent/shared" variables?

I have not any use case for this. I don't know about any similar feature
in other database systems. Oracle uses dbms_pipe or dbms_alert for
interprocess communication.

I am thinking so it is possible to implement. If it is not ACID, then it
can work as custom statistic counters. If it should be ACID? Then is better
to use table. What I know, now is preferred share nothing design in
parallel systems - so for me, it looks like little bit dangerous feature -
and I see only one use case - custom statistics - where possible race
condition is not hard issue.

But I don't plan to implement it in first stage. There should be strong
use case for implementing any complex feature in shared memory. But any
implementation doesn't breaking to implement it in feature.

for custom statistic some extension based on bg worker can be better -
sharing variables are risk of race conditions - and developers are people
only.

I have not a clean opinion about this - the implementation should not be
hard, but I am not sure if this gun I would to give to users.

Regards

Pavel

Show quoted text

Regards

Pavel

--
Fabien.

On Fri, Dec 23, 2016 at 4:00 PM, Joe Conway <mail@joeconway.com> wrote:

In the long term, What would be the possible scopes?

TRANSACTION, SESSION, PERSISTANT ?

Would some scopes orthogonal (eg SHARED between sessions for a USER in

a

DATABASE, SHARED at the cluster level?).

I have a plan to support TRANSACTION and SESSION scope. Persistent or
shared scope needs much more complex rules, and some specialized

extensions

will be better.

I can see where persistent variables would be very useful though.

Can you talk more about your though??

I'm thinking about PERSISTENT VARIABLES and maybe they can be the redesign
of our hard coded "reloptions", with the ability to provide users to create
their own customized. If we think more carefully we already have some
persistent variables with specialized context: reloptions (hardcoded),
security labels (local and shared catalog) and comments (local and shared
catalog). I was clear enough?

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Show quoted text

Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello

2016-12-27 21:38 GMT+01:00 Fabrízio de Royes Mello <fabriziomello@gmail.com>
:

On Fri, Dec 23, 2016 at 4:00 PM, Joe Conway <mail@joeconway.com> wrote:

In the long term, What would be the possible scopes?

TRANSACTION, SESSION, PERSISTANT ?

Would some scopes orthogonal (eg SHARED between sessions for a USER

in a

DATABASE, SHARED at the cluster level?).

I have a plan to support TRANSACTION and SESSION scope. Persistent or
shared scope needs much more complex rules, and some specialized

extensions

will be better.

I can see where persistent variables would be very useful though.

Can you talk more about your though??

I'm thinking about PERSISTENT VARIABLES and maybe they can be the redesign
of our hard coded "reloptions", with the ability to provide users to create
their own customized. If we think more carefully we already have some
persistent variables with specialized context: reloptions (hardcoded),
security labels (local and shared catalog) and comments (local and shared
catalog). I was clear enough?

What is difference from table Values(keys varchar primary key, value text)
? Where is any benefit?

Best regards

Pavel

Show quoted text

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello

On Tue, Dec 27, 2016 at 6:55 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2016-12-27 21:38 GMT+01:00 Fabrízio de Royes Mello <

fabriziomello@gmail.com>:

On Fri, Dec 23, 2016 at 4:00 PM, Joe Conway <mail@joeconway.com> wrote:

In the long term, What would be the possible scopes?

TRANSACTION, SESSION, PERSISTANT ?

Would some scopes orthogonal (eg SHARED between sessions for a USER

in a

DATABASE, SHARED at the cluster level?).

I have a plan to support TRANSACTION and SESSION scope. Persistent or
shared scope needs much more complex rules, and some specialized

extensions

will be better.

I can see where persistent variables would be very useful though.

Can you talk more about your though??

I'm thinking about PERSISTENT VARIABLES and maybe they can be the

redesign of our hard coded "reloptions", with the ability to provide users
to create their own customized. If we think more carefully we already have
some persistent variables with specialized context: reloptions (hardcoded),
security labels (local and shared catalog) and comments (local and shared
catalog). I was clear enough?

What is difference from table Values(keys varchar primary key, value

text) ? Where is any benefit?

IMHO it's a totally different thing because with this approach we'll have:
- track dependency (aka pg_depend)
- different LockLevel for each persistent variable (reloption)
- syscache to improve performance

And It's a way to store metadata about metadata, i.e. property about our
objects. This can be useful to:
- author extensions to store your own properties with each object
- postgres developers to have a simple way to add a new reloption just
adding a new row to bootstrap data

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Show quoted text

Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello

Fabien, I don't really see the point of "persistent variables". What
benefit do they add over relations?

You can add a simple function to fetch a tuple if you want it not to
look like a subquery. Do it with heap access in C if you like, save
the (trivial) planning costs.

I do see value to two different things discussed here:

* Pavel's proposal for persistent-declaration, non-persistent-value
session variables with access control. These will be very beneficial
with RLS, where we need very fast lookups. Their purpose is that you
can set up expensive state with SECURITY DEFINER functions, C-level
functions, etc, then test it very cheaply later from RLS and from
other functions. Their advantage over relations is very cheap, fast
access.

I can maybe see global temporary relations being an alternative to
these, if we optimise by using a tuplestore to back them and only
switch to a relfilenode if the relation grows. The pg_catalog entries
would be persistent so you could GRANT or REVOKE access to them, etc.
Especially if we optimised the 1-row case this could work. It'd be
less like what Oracle does, but might let us re-use more functionality
and have fewer overlapping features. Pavel?

* Fabien's earlier mention of transient session / query variables,
a-la MySQL or MS-SQL. They're obviously handy for more general purpose
programming, but our strict division between SQL and plpgsql makes
them a bit less useful than in MS-SQL with T-SQL. I think it's a very
separate topic to this and should be dealt with in a separate thread
if/when someone wants to work on them.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Hello Craig,

Fabien, I don't really see the point of "persistent variables". What
benefit do they add over relations?

A relation is a set of values, a variable is a scalar with one value.

It is always possible to declare a set and use it as a singleton, but
somehow it seems cleaner to ask for what you want and have the database
maintain the singleton property just like any other constraint.

Behind the scene a "persistent variable" would probably be implemented as
a row in a special table or some kind of one-row table... So there is no
deep semantical difference, but mostly a syntactic one: you ask for a
variable and you use it as a variable, i.e. there can be a simple well
integrated syntax to get its value without having to "SELECT FROM" or
resorting to functions.

You can add a simple function to fetch a tuple if you want it not to
look like a subquery.

ISTM that if there are some kind of (persistent/session/...) variables,
there should be a simple direct way of getting its value, like @var or
&var or whatever. If one must write pg_get_variable_value('var')::ZZZ, it
somehow defeats the purpose, as "(SELECT var FROM some_table)" is shorter.

I do see value to two different things discussed here:

* Pavel's proposal for persistent-declaration, non-persistent-value
session variables with access control. [...]

Yep, that is one. I missed the half-persistence property at the
beginning...

* Fabien's earlier mention of transient session / query variables, a-la
[...] I think it's a very separate topic to this and should be dealt
with in a separate thread if/when someone wants to work on them.

Yes and no: ISTM that at least a global design should be discussed
*before* some kind of special-case variables (session-alive,
persistent-in-existence-but-not-in-value, not-transactional,
subject-to-permissions, not-subject-to-constraints...) are introduced, so
that the special case does not preclude the possible future existence of
other types of variables.

Then I would be more at ease with having a special case implemented first,
knowing that others may come and fit neatly, both semantically and
syntaxically.

I'm bothered by the half-persistence proposed, because it interferes both
with possible session (light-weight, only in memory) and persistent
(heavy-weight, in catalog) variables.

Also, I'm not yet convinced that simple privatizable transcient/session
variables would not be enough to fit the use case, so that for the same
price there would be session variables for all, not only special ones with
permissions.

--
Fabien.

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

2016-12-28 14:19 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Craig,

Fabien, I don't really see the point of "persistent variables". What

benefit do they add over relations?

A relation is a set of values, a variable is a scalar with one value.

It is always possible to declare a set and use it as a singleton, but
somehow it seems cleaner to ask for what you want and have the database
maintain the singleton property just like any other constraint.

Behind the scene a "persistent variable" would probably be implemented as
a row in a special table or some kind of one-row table... So there is no
deep semantical difference, but mostly a syntactic one: you ask for a
variable and you use it as a variable, i.e. there can be a simple well
integrated syntax to get its value without having to "SELECT FROM" or
resorting to functions.

You can add a simple function to fetch a tuple if you want it not to

look like a subquery.

ISTM that if there are some kind of (persistent/session/...) variables,
there should be a simple direct way of getting its value, like @var or &var
or whatever. If one must write pg_get_variable_value('var')::ZZZ, it
somehow defeats the purpose, as "(SELECT var FROM some_table)" is shorter.

just note - getter function returns typed value - there are not necessary
any other casting

Show quoted text

I do see value to two different things discussed here:

* Pavel's proposal for persistent-declaration, non-persistent-value
session variables with access control. [...]

Yep, that is one. I missed the half-persistence property at the
beginning...

* Fabien's earlier mention of transient session / query variables, a-la

[...] I think it's a very separate topic to this and should be dealt with
in a separate thread if/when someone wants to work on them.

Yes and no: ISTM that at least a global design should be discussed
*before* some kind of special-case variables (session-alive,
persistent-in-existence-but-not-in-value, not-transactional,
subject-to-permissions, not-subject-to-constraints...) are introduced, so
that the special case does not preclude the possible future existence of
other types of variables.

Then I would be more at ease with having a special case implemented first,
knowing that others may come and fit neatly, both semantically and
syntaxically.

I'm bothered by the half-persistence proposed, because it interferes both
with possible session (light-weight, only in memory) and persistent
(heavy-weight, in catalog) variables.

Also, I'm not yet convinced that simple privatizable transcient/session
variables would not be enough to fit the use case, so that for the same
price there would be session variables for all, not only special ones with
permissions.

--
Fabien.

On 28 December 2016 at 21:19, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Also, I'm not yet convinced that simple privatizable transcient/session
variables would not be enough to fit the use case, so that for the same
price there would be session variables for all, not only special ones with
permissions.

Since, unlike Oracle, we don't have compiled packages or plan-caching
above the session level, there's not the same hard requirement for the
variable definition to be persistent.

So... maybe? The main question then becomes how you integrate access control.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

2016-12-28 4:40 GMT+01:00 Craig Ringer <craig@2ndquadrant.com>:

Fabien, I don't really see the point of "persistent variables". What
benefit do they add over relations?

You can add a simple function to fetch a tuple if you want it not to
look like a subquery. Do it with heap access in C if you like, save
the (trivial) planning costs.

I do see value to two different things discussed here:

* Pavel's proposal for persistent-declaration, non-persistent-value
session variables with access control. These will be very beneficial
with RLS, where we need very fast lookups. Their purpose is that you
can set up expensive state with SECURITY DEFINER functions, C-level
functions, etc, then test it very cheaply later from RLS and from
other functions. Their advantage over relations is very cheap, fast
access.

I can maybe see global temporary relations being an alternative to
these, if we optimise by using a tuplestore to back them and only
switch to a relfilenode if the relation grows. The pg_catalog entries
would be persistent so you could GRANT or REVOKE access to them, etc.
Especially if we optimised the 1-row case this could work. It'd be
less like what Oracle does, but might let us re-use more functionality
and have fewer overlapping features. Pavel?

This is hard question - I have different expectation from variables and
from tables. Sure, there can be a optimization for one row global temporary
tables, but it fails on first update. And with this optimization we should
not to use current heap access functionality - or we increase a complexity
of currently lot of complex code. From success global temp tables
implementation I am expecting unbloating catalogue and all relation related
functionality - indexes, column statistics, usage statistics, MVCC. I can't
to imagine a one row optimization there. More it has limit - it is pretty
hard implement there expandable types.

I see very useful to define variable as memory based NOT ACID, not MVCC
storage (always). Tables are ACI(D) MVCC. The minimalism is great, but have
to has practical limit - we have variables inside/outside plpgsql -
although we can use temporary tables.

There are secondary question if we can introduce some better interface for
writing getter/setter function, where current relations can be used.

Show quoted text

* Fabien's earlier mention of transient session / query variables,
a-la MySQL or MS-SQL. They're obviously handy for more general purpose
programming, but our strict division between SQL and plpgsql makes
them a bit less useful than in MS-SQL with T-SQL. I think it's a very
separate topic to this and should be dealt with in a separate thread
if/when someone wants to work on them.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

2016-12-28 15:00 GMT+01:00 Craig Ringer <craig@2ndquadrant.com>:

On 28 December 2016 at 21:19, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Also, I'm not yet convinced that simple privatizable transcient/session
variables would not be enough to fit the use case, so that for the same
price there would be session variables for all, not only special ones

with

permissions.

Since, unlike Oracle, we don't have compiled packages or plan-caching
above the session level, there's not the same hard requirement for the
variable definition to be persistent.

So... maybe? The main question then becomes how you integrate access
control.

For security the variable should be persistent.

If you would to do statical analyse (what you usually would), then variable
should be persistent.

Currently the big issue of plpgsql_check is work with temporary tables.
Local objects or dynamic sql is stop for static check.

Regards

Pavel

Show quoted text

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Also, I'm not yet convinced that simple privatizable transcient/session
variables would not be enough to fit the use case, [...]

So... maybe? The main question then becomes how you integrate access control.

For what it's worth, permissions on persistent functions could be used to
control access to private-to-a-role transcient/session variables, see:

https://www.mail-archive.com/pgsql-hackers@postgresql.org/msg300651.html

The good point is that the implementation on top of session variables
would be trivial and very efficient, just prefix the variable with the
owner id in the key-value storage. The bad point is that there is no
full-featured GRANT on the variable itself, and it looks adhoc, somehow.

--
Fabien.

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

For security the variable should be persistent.

Why should they? If it is a session variable, being created when needed or
used with the right type could be enough?

If you would to do statical analyse (what you usually would), then variable
should be persistent.

I do not understand what static analysis you would need/want to do on
session variables.

Currently the big issue of plpgsql_check is work with temporary tables.

Do you mean that temporary table are too slow/costly?

Local objects or dynamic sql is stop for static check.

Hmm. If something is dynamic, it is not static, but I do not understand
your point.

--
Fabien.

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

2016-12-28 15:38 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

For security the variable should be persistent.

Why should they? If it is a session variable, being created when needed or
used with the right type could be enough?

You cannot to trust some fuzzy object - or you have to play hard game with
securing content - hashing, coding, decoding - it is slow, cpu intensive

If you would to do statical analyse (what you usually would), then variable

should be persistent.

I do not understand what static analysis you would need/want to do on
session variables.

Currently the big issue of plpgsql_check is work with temporary tables.

Do you mean that temporary table are too slow/costly?

No, I mean so when you use temporary tables inside plpgsql functions, then
the static analyze like plpgsql check is almost impossible.

Local objects or dynamic sql is stop for static check.

Hmm. If something is dynamic, it is not static, but I do not understand
your point.

I cannot to speak instead you, but lot of people prefer static analyze of
code. The static analyze can be done only on static (persistent metadata).
You cannot do it with dynamic (unfixed in schema) objects.

regards

Pavel

Show quoted text

--
Fabien.

On 28 December 2016 at 22:04, Pavel Stehule <pavel.stehule@gmail.com> wrote:

For security the variable should be persistent.

If you would to do statical analyse (what you usually would), then variable
should be persistent.

Currently the big issue of plpgsql_check is work with temporary tables.
Local objects or dynamic sql is stop for static check.

Someone asked me off-list what use cases such a thing would have,
since it seems not to be spelled out very clearly in this discussion.
I think we're all assuming knowledge here.

So.

* Session starts
* app does SELECT setup_user('user-auth-key-data', 'some-other-blob')
** setup_user is SECURITY DEFINER to 'appadmin'
** 'appadmin' owns a variable IS_AUDITOR. Other roles have only read
access to it.
** setup_user(...) does whatever expensive/slow work it has to do
** setup_user sets USER_IS_AUDITOR var
* Later RLS policies simply reference USER_IS_AUDITOR var. They don't
need to know the 'user-auth-key-data', or do whatever expensive
processing that it does.
* Other later triggers, etc, also reference USER_IS_AUDITOR
* User cannot make themselves an auditor by SETting USER_IS_AUDITOR

That's the general idea.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Hello,

Why should they? If it is a session variable, being created when needed or
used with the right type could be enough?

You cannot to trust some fuzzy object - or you have to play hard game with
securing content - hashing, coding, decoding - it is slow, cpu intensive

I'm afraid that I do not understand. I do not think that a hash get in
memory is particularly costly. I do not see how you could do less that
that to manage variables with associated values.

Currently the big issue of plpgsql_check is work with temporary tables.

No, I mean so when you use temporary tables inside plpgsql functions, then
the static analyze like plpgsql check is almost impossible.

I cannot to speak instead you, but lot of people prefer static analyze of
code.

Hmmm... I know a little bit about that field, ISTM that you are speaking
of the current capabilities of a particular static analysis tool, but I'm
not sure that the tool capabilities could not be enhanced to manage more
things.

The static analyze can be done only on static (persistent metadata).

A session variable is a bit like a global temporary variable. A static
analysis tool could take into account a global temporary variable.

You cannot do it with dynamic (unfixed in schema) objects.

The private session variables I suggested have a fixed (static) name, and
their type could be infered by a static analysis tool, eg:

   ...
   DECLARE @foo BOOLEAN PRIVATE;
   -- I know that there is private a boolean variable "@foo" of unknown value
   SET @foo = TRUE;
   --- I know that @foo is true...
   ...

--
Fabien.

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

2016-12-28 16:42 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello,

Why should they? If it is a session variable, being created when needed or

used with the right type could be enough?

You cannot to trust some fuzzy object - or you have to play hard game with
securing content - hashing, coding, decoding - it is slow, cpu intensive

I'm afraid that I do not understand. I do not think that a hash get in
memory is particularly costly. I do not see how you could do less that that
to manage variables with associated values.

Currently the big issue of plpgsql_check is work with temporary tables.

No, I mean so when you use temporary tables inside plpgsql functions, then
the static analyze like plpgsql check is almost impossible.

I cannot to speak instead you, but lot of people prefer static analyze of

code.

Hmmm... I know a little bit about that field, ISTM that you are speaking
of the current capabilities of a particular static analysis tool, but I'm
not sure that the tool capabilities could not be enhanced to manage more
things.

It cannot be - the static analyze is limited to function scope - in static
analyze you don't know a order of calls.

The static analyze can be done only on static (persistent metadata).

A session variable is a bit like a global temporary variable. A static
analysis tool could take into account a global temporary variable.

You cannot do it with dynamic (unfixed in schema) objects.

The private session variables I suggested have a fixed (static) name, and
their type could be infered by a static analysis tool, eg:

...
DECLARE @foo BOOLEAN PRIVATE;
-- I know that there is private a boolean variable "@foo" of unknown
value
SET @foo = TRUE;
--- I know that @foo is true...
...

This is not too friendly - you have to repeat DECLARE in every function.
What is somebody somewhere write @fooo or use DECLARE @foo integer instead.
There is big space for errors.

Regards

Pavel

Show quoted text

--
Fabien.

My 0.02€ to try to illustrate a possible private session variable based
implementation for this use case:

* Session starts

\c app

* app does SELECT setup_user('user-auth-key-data', 'some-other-blob')

SELECT setup_user('fjshdfjkshfjks', 'jklfsjfklsjfk');

** setup_user is SECURITY DEFINER to 'appadmin'

-- appadmin did:
CREATE FUNCTION setup_user(TEXT, TEXT)
RETURNS BOOLEAN SECURITY DEFINER AS $$

** 'appadmin' owns a variable IS_AUDITOR. Other roles have only read
access to it.

not sure how it is used afterwards... is it the same as USER_IS_AUDITOR?

** setup_user(...) does whatever expensive/slow work it has to do

... checks, updates, whatever

** setup_user sets USER_IS_AUDITOR var

     -- declare a private session variable
     DECLARE @user_is_auditor BOOLEAN PRIVATE;
     -- set its value to whatever appropriate
     SET @user_is_auditor = ???;
     --- returns its value
     RETURN @user_is_auditor;
$$ LANGUAGE xxx;

* Later RLS policies simply reference USER_IS_AUDITOR var. They don't
need to know the 'user-auth-key-data', or do whatever expensive
processing that it does.

-- appadmin did:
CREATE FUNCTION isUserAuditor()
RETURNS BOOLEAN SECURITY DEFINER AS $$
-- say variable is just confirmed if it exists already in session?
DECLARE @user_is_auditor BOOLEAN PRIVATE;
RETURN @user_is_auditor;
$$ LANGUAGE xxx;

* Other later triggers, etc, also reference USER_IS_AUDITOR

The variable is not directly referenced, one would have to call
isUserAuditor() to access the private session value, but then you can
GRANT/REVOKE whatever you want on the access function.

* User cannot make themselves an auditor by SETting USER_IS_AUDITOR

Indeed, the user cannot access the private variable, only appadmin can,
and probably root could.

The user could create its own private session variable @user_is_auditor,
or a public session variable of the same name. That would be distinct
variables which would not influence isUserAuditor which would use its own.

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

2016-12-28 16:57 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

My 0.02€ to try to illustrate a possible private session variable based
implementation for this use case:

* Session starts

\c app

* app does SELECT setup_user('user-auth-key-data', 'some-other-blob')

SELECT setup_user('fjshdfjkshfjks', 'jklfsjfklsjfk');

** setup_user is SECURITY DEFINER to 'appadmin'

-- appadmin did:
CREATE FUNCTION setup_user(TEXT, TEXT)
RETURNS BOOLEAN SECURITY DEFINER AS $$

** 'appadmin' owns a variable IS_AUDITOR. Other roles have only read

access to it.

not sure how it is used afterwards... is it the same as USER_IS_AUDITOR?

** setup_user(...) does whatever expensive/slow work it has to do

... checks, updates, whatever

** setup_user sets USER_IS_AUDITOR var

-- declare a private session variable
DECLARE @user_is_auditor BOOLEAN PRIVATE;
-- set its value to whatever appropriate
SET @user_is_auditor = ???;
--- returns its value
RETURN @user_is_auditor;
$$ LANGUAGE xxx;

* Later RLS policies simply reference USER_IS_AUDITOR var. They don't

need to know the 'user-auth-key-data', or do whatever expensive
processing that it does.

-- appadmin did:
CREATE FUNCTION isUserAuditor()
RETURNS BOOLEAN SECURITY DEFINER AS $$
-- say variable is just confirmed if it exists already in session?
DECLARE @user_is_auditor BOOLEAN PRIVATE;
RETURN @user_is_auditor;
$$ LANGUAGE xxx;

* Other later triggers, etc, also reference USER_IS_AUDITOR

The variable is not directly referenced, one would have to call
isUserAuditor() to access the private session value, but then you can
GRANT/REVOKE whatever you want on the access function.

* User cannot make themselves an auditor by SETting USER_IS_AUDITOR

Indeed, the user cannot access the private variable, only appadmin can,
and probably root could.

The user could create its own private session variable @user_is_auditor,
or a public session variable of the same name. That would be distinct
variables which would not influence isUserAuditor which would use its own.

so what is worse - I did one new entry in pg_class and one entry in
pg_attributes. You wrote two entries in pg_proc function - more you have to
ensure consistency of these functions.

Regards

Pavel

Show quoted text

--
Fabien.

On 12/28/16 9:57 AM, Fabien COELHO wrote:

* Other later triggers, etc, also reference USER_IS_AUDITOR

The variable is not directly referenced, one would have to call
isUserAuditor() to access the private session value, but then you can
GRANT/REVOKE whatever you want on the access function.

Why force users to create Yet Another Function as a getter?

There's 2 big points that I think keep getting missed:

1) Variables would be completely non-transactional. The only way you can
do that today is to use a "non-standard" language (such as plperl or
plpython), or by creating a custom GUC (which is ugly because it
necessitates changing postgresql.conf and is only text). A solution to
this problem would be to provide a plpgsql equivalent to plperl or
plpython's session hashes. I'm sure there are use cases that would be
satisfied by simple doing that, but...

2) Variables provide permissions. Theoretically you could allow the
hypothetical plpgsql session variables in (1) to be marked private, but
that means you now have to keep all those variables on a per-role basis,
users are forced to create accessor functions, and you run a serious
risk of confusion from getting the function ownerships wrong. That
certainly seems no better than defining permanent variables and giving
them permissions (as Pavel suggested). More importantly, the security
definer trick you're suggesting has a fatal flaw: you can't call one
SECDEF function from another SECDEF function. So as soon as you have
multiple privileged roles making use of variables, there's a serious
risk of not being able to make use of these private variables at all.

Now maybe pg_class is absolutely the wrong place to store info about
predefined variables, but that's an implementation detail, not a design
flaw.

Some other points:
We should protect for the possibility of truly global (as in
cross-session) variables. Presumably these would have to be pre-defined
via DDL before use. These would be uniquely valuable as a means of
communication between sessions that are connected to different
databases. I could also see use in cross-database in-memory queues.
AFAIK both of these would be pretty easy to do with the shared memory
infrastructure we now have.

It would be nice if we could come up with a plan for what permanently
defined temp tables looked like, so the syntax and operation was similar
to the permanently defined session variables that Pavel is proposing.
That said, given how long that has been an open issue I think it's
completely unfair to stonewall this feature if we can't get permanent
temp tables figured out.

While permanent temp tables would eliminate some objections to store
"session variables", the fact still remains that any kind of table would
still be MVCC, and that is NOT always what you want.

It would be nice if whatever syntax was decided for defined session
variables allowed room for "variables" that were actually MVCC, because
sometimes that actually is what you want. Yes, you could simulate the
same thing with functions, but why make users do all that work if we
could easily provide the same functionality? These should probably be
called something other than "variables", but presumably all the other
syntax and settings could be the same. Again, it's not the job of this
proposal to boil that ocean, but it would be nice to leave the option open.
--
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
855-TREBLE2 (855-873-2532)

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

Hello Jim,

1) Variables would be completely non-transactional. [...] A solution to
this problem would be to provide a plpgsql equivalent to plperl or
plpython's session hashes.

That is what I have in mind with "session variables" à la MS/MY SQL, but
at the SQL level, not PL/pgSQL level.

2) Variables provide permissions. Theoretically you could allow the
hypothetical plpgsql session variables in (1) to be marked private, but
that means you now have to keep all those variables on a per-role basis,

No, I think a common "session hash" could be used, with some basic prefix
convention, there is no need to per-role hash.

users are forced to create accessor functions,

Yes if the private variable should be accessed. If the variable is
private, then it is private and nothing is needed. Idem for public.

and you run a serious risk of confusion from getting the function
ownerships wrong.

One can get the permissions on special session variable wrong as well... I
do not see how it differs.

That certainly seems no better than defining permanent variables and
giving them permissions (as Pavel suggested).

As far as permissions is concerned, ISTM that it is the same.

The point is that there would be only usual fast "session variables", like
MS/MY/Oracle SQL, and they could be used for Pavel rather special use case
as well.

More importantly, the security definer trick you're suggesting has a
fatal flaw: you can't call one SECDEF function from another SECDEF
function.

I do not see why there would be such a restriction?

postgres@db> CREATE FUNCTION secfunc() RETURNS TEXT SECURITY DEFINER
AS $$ SELECT CURRENT_USER::TEXT; $$ LANGUAGE SQL;

fabien@db> CREATE FUNCTION secfunc2() RETURNS TEXT SECURITY DEFINER
AS $$ SELECT secfunc() || ' - ' || CURRENT_USER; $$ LANGUAGE SQL;

*@db> SELECT secfunc2(); -- returns: "postgres - fabien" from both sides...

So as soon as you have multiple privileged roles making use of
variables, there's a serious risk of not being able to make use of these
private variables at all.

?

Now maybe pg_class is absolutely the wrong place to store info about
predefined variables, but that's an implementation detail, not a design
flaw.

Yes, but is not my only point.

We should protect for the possibility of truly global (as in cross-session)
variables.

Yes, why not... Although having "cross-session session variable" seems to
create some problems of its own... Would they be cross-database as well?

Presumably these would have to be pre-defined via DDL before use.

Probably.

These would be uniquely valuable as a means of communication between sessions
that are connected to different databases.

Why not.

I could also see use in cross-database in-memory queues. AFAIK both of
these would be pretty easy to do with the shared memory infrastructure
we now have.

Yep, that would means keeping some "session hash" in shared buffers.

It would be nice if we could come up with a plan for what permanently defined
temp tables looked like, so the syntax and operation was similar to the
permanently defined session variables that Pavel is proposing.

Yes. And basic session variables as well.

That said, given how long that has been an open issue I think it's
completely unfair to stonewall this feature if we can't get permanent
temp tables figured out.

...

While permanent temp tables would eliminate some objections to store "session
variables", the fact still remains that any kind of table would still be
MVCC, and that is NOT always what you want.

Yes, I understood that. But sometimes it may be what is wanted.

It would be nice if whatever syntax was decided for defined session variables
allowed room for "variables" that were actually MVCC, because sometimes that
actually is what you want.

Yep, that is what I'm arguing about, having a global variable design, and
then maybe inside this design just a special case implemented.

Yes, you could simulate the same thing with functions, but why make
users do all that work if we could easily provide the same
functionality?

The easy is unclear. Eg if special declared with permissions partially
persistent session variables preclude future basic session variables, or
their efficiency, or their syntax, it would be a problem. Hence the point
of discussing before proceeding.

These should probably be called something other than
"variables", but presumably all the other syntax and settings could be
the same. Again, it's not the job of this proposal to boil that ocean,
but it would be nice to leave the option open.

Yep.

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

2016-12-28 17:53 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 12/28/16 9:57 AM, Fabien COELHO wrote:

* Other later triggers, etc, also reference USER_IS_AUDITOR

The variable is not directly referenced, one would have to call
isUserAuditor() to access the private session value, but then you can
GRANT/REVOKE whatever you want on the access function.

Why force users to create Yet Another Function as a getter?

There's 2 big points that I think keep getting missed:

1) Variables would be completely non-transactional. The only way you can
do that today is to use a "non-standard" language (such as plperl or
plpython), or by creating a custom GUC (which is ugly because it
necessitates changing postgresql.conf and is only text). A solution to this
problem would be to provide a plpgsql equivalent to plperl or plpython's
session hashes. I'm sure there are use cases that would be satisfied by
simple doing that, but...

2) Variables provide permissions. Theoretically you could allow the
hypothetical plpgsql session variables in (1) to be marked private, but
that means you now have to keep all those variables on a per-role basis,
users are forced to create accessor functions, and you run a serious risk
of confusion from getting the function ownerships wrong. That certainly
seems no better than defining permanent variables and giving them
permissions (as Pavel suggested). More importantly, the security definer
trick you're suggesting has a fatal flaw: you can't call one SECDEF
function from another SECDEF function. So as soon as you have multiple
privileged roles making use of variables, there's a serious risk of not
being able to make use of these private variables at all.

Now maybe pg_class is absolutely the wrong place to store info about
predefined variables, but that's an implementation detail, not a design
flaw.

We can talk about implementation - for me a variable is a object that holds
data - more, I would to fix this object in schema without possible
collision with tables. I plan to support SELECT for access and UPDATE for
changes in future - so using pg_class is natural. On second hand - lot of
fields in pg_class are not used for variables everywhere. I would to fix
variables in some schema, but I would not to solve possible collision
between variables and other SQL objects - due possible direct access inside
SQL statements in future.

Some other points:
We should protect for the possibility of truly global (as in
cross-session) variables. Presumably these would have to be pre-defined via
DDL before use. These would be uniquely valuable as a means of
communication between sessions that are connected to different databases. I
could also see use in cross-database in-memory queues. AFAIK both of these
would be pretty easy to do with the shared memory infrastructure we now
have.

I didn't write any what close this possibility.

Show quoted text

It would be nice if we could come up with a plan for what permanently
defined temp tables looked like, so the syntax and operation was similar to
the permanently defined session variables that Pavel is proposing. That
said, given how long that has been an open issue I think it's completely
unfair to stonewall this feature if we can't get permanent temp tables
figured out.

While permanent temp tables would eliminate some objections to store
"session variables", the fact still remains that any kind of table would
still be MVCC, and that is NOT always what you want.

It would be nice if whatever syntax was decided for defined session
variables allowed room for "variables" that were actually MVCC, because
sometimes that actually is what you want. Yes, you could simulate the same
thing with functions, but why make users do all that work if we could
easily provide the same functionality? These should probably be called
something other than "variables", but presumably all the other syntax and
settings could be the same. Again, it's not the job of this proposal to
boil that ocean, but it would be nice to leave the option open.

--
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
855-TREBLE2 (855-873-2532)

On 12/28/16 11:29 AM, Fabien COELHO wrote:

Hello Jim,

1) Variables would be completely non-transactional. [...] A solution
to this problem would be to provide a plpgsql equivalent to plperl or
plpython's session hashes.

That is what I have in mind with "session variables" à la MS/MY SQL, but
at the SQL level, not PL/pgSQL level.

I'm just saying there might be use for a plpgsql equivalent to the
session hashes that other PLs provide, but that's a different issue.

users are forced to create accessor functions,

Yes if the private variable should be accessed. If the variable is
private, then it is private and nothing is needed. Idem for public.

Why force the extra busywork? Just allow for them to be public.

For that matter, if we're going to start introducing private objects,
that certainly needs to be thought through.

and you run a serious risk of confusion from getting the function
ownerships wrong.

One can get the permissions on special session variable wrong as well...
I do not see how it differs.

It's a lot harder to mess up an explicit grant than it is to mess up
object ownership.

More importantly, the security definer trick you're suggesting has a
fatal flaw: you can't call one SECDEF function from another SECDEF
function.

I do not see why there would be such a restriction?

postgres@db> CREATE FUNCTION secfunc() RETURNS TEXT SECURITY DEFINER
AS $$ SELECT CURRENT_USER::TEXT; $$ LANGUAGE SQL;

fabien@db> CREATE FUNCTION secfunc2() RETURNS TEXT SECURITY DEFINER
AS $$ SELECT secfunc() || ' - ' || CURRENT_USER; $$ LANGUAGE
SQL;

*@db> SELECT secfunc2(); -- returns: "postgres - fabien" from both
sides...

Perhaps I've got the restrictions on SECDEF wrong, but I know there's
problems with it. Certainly one issue is you can't change roles back to
the calling user.

Maybe they would be workable in this case, but it's just a bunch of
extra busywork for the user that serves no real purpose.

We should protect for the possibility of truly global (as in
cross-session) variables.

Yes, why not... Although having "cross-session session variable" seems
to create some problems of its own... Would they be cross-database as well?

Yes. It'd be a shared catalog.

...

Yes, you could simulate the same thing with functions, but why make
users do all that work if we could easily provide the same functionality?

The easy is unclear. Eg if special declared with permissions partially
persistent session variables preclude future basic session variables, or
their efficiency, or their syntax, it would be a problem. Hence the
point of discussing before proceeding.

Then IMHO what needs to happen is to have a discussion on actual syntax
instead of calling into question the value of the feature. Following
this thread has been very painful because the communications have not
been very clear. Focus on grammar would probably be a big improvement in
that regard.
--
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
855-TREBLE2 (855-873-2532)

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

On 12/28/2016 10:17 AM, Jim Nasby wrote:

Then IMHO what needs to happen is to have a discussion on actual syntax
instead of calling into question the value of the feature. Following
this thread has been very painful because the communications have not
been very clear. Focus on grammar would probably be a big improvement in
that regard.

+1

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

2016-12-28 19:17 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 12/28/16 11:29 AM, Fabien COELHO wrote:

Hello Jim,

1) Variables would be completely non-transactional. [...] A solution

to this problem would be to provide a plpgsql equivalent to plperl or
plpython's session hashes.

That is what I have in mind with "session variables" à la MS/MY SQL, but
at the SQL level, not PL/pgSQL level.

I'm just saying there might be use for a plpgsql equivalent to the session
hashes that other PLs provide, but that's a different issue.

users are forced to create accessor functions,

Yes if the private variable should be accessed. If the variable is
private, then it is private and nothing is needed. Idem for public.

Why force the extra busywork? Just allow for them to be public.

For that matter, if we're going to start introducing private objects, that
certainly needs to be thought through.

and you run a serious risk of confusion from getting the function

ownerships wrong.

One can get the permissions on special session variable wrong as well...
I do not see how it differs.

It's a lot harder to mess up an explicit grant than it is to mess up
object ownership.

More importantly, the security definer trick you're suggesting has a

fatal flaw: you can't call one SECDEF function from another SECDEF
function.

I do not see why there would be such a restriction?

postgres@db> CREATE FUNCTION secfunc() RETURNS TEXT SECURITY DEFINER
AS $$ SELECT CURRENT_USER::TEXT; $$ LANGUAGE SQL;

fabien@db> CREATE FUNCTION secfunc2() RETURNS TEXT SECURITY DEFINER
AS $$ SELECT secfunc() || ' - ' || CURRENT_USER; $$ LANGUAGE
SQL;

*@db> SELECT secfunc2(); -- returns: "postgres - fabien" from both
sides...

Perhaps I've got the restrictions on SECDEF wrong, but I know there's
problems with it. Certainly one issue is you can't change roles back to the
calling user.

Maybe they would be workable in this case, but it's just a bunch of extra
busywork for the user that serves no real purpose.

We should protect for the possibility of truly global (as in

cross-session) variables.

Yes, why not... Although having "cross-session session variable" seems
to create some problems of its own... Would they be cross-database as
well?

Yes. It'd be a shared catalog.

...

Yes, you could simulate the same thing with functions, but why make

users do all that work if we could easily provide the same functionality?

The easy is unclear. Eg if special declared with permissions partially
persistent session variables preclude future basic session variables, or
their efficiency, or their syntax, it would be a problem. Hence the
point of discussing before proceeding.

Then IMHO what needs to happen is to have a discussion on actual syntax
instead of calling into question the value of the feature. Following this
thread has been very painful because the communications have not been very
clear. Focus on grammar would probably be a big improvement in that regard.

I don't think. There are some significant questions:

1. Should be "variables" fixed in schema? Should be metadata persistent?
2. Should we use GRANT/REVOKE statements for "variables"?
3. Should be "variable" name unique in schema like tables, indexes,
sequences?
4. The content of "variables" should be nontransactional or transactional.
5. What mode we should to support, what mode will be default "unshared",
"shared"

That is all. All discussion is about these questions. These questions
creates multidimensional space, that can be covered. But we cannot to
implement all in one stage.

We can have schema unbound variables declared by

DECLARE @xx AS int, and accessed with get('@xx') and set('@xx', val)

and we have to have bound variables created by

CREATE VARIABLE public.xx AS int and accessed with get('public.xx'),
set('public.xx', val)

We can have both - I don't see any problem. Implementation DECLARE
statement doesn't eliminate implementation CREATE statement. I can
understand so somebody doesn't need secure variables - so it doesn't need
CREATE statement. But if you need secure variables then some PRIVATE flags
is minimally redundant to our standard security design.

My proposal doesn't eliminate Fabien's proposal - proposal by Fabien can be
good enough for Fabien - and for interactive work, but is not good enough
for some checking and security usage.

Regards

Pavel

Show quoted text

--
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
855-TREBLE2 (855-873-2532)

Hello Jim,

Yes if the private variable should be accessed. If the variable is
private, then it is private and nothing is needed. Idem for public.

Why force the extra busywork? Just allow for them to be public.

There is no extra busywork, having possibly private session variables cost
nearly nothing, and I'm arguing that they could be enough for Pavel
special use case.

For that matter, if we're going to start introducing private objects, that
certainly needs to be thought through.

Yep. It is a discussion.

One can get the permissions on special session variable wrong as well...
I do not see how it differs.

It's a lot harder to mess up an explicit grant than it is to mess up object
ownership.

ISTM that it rather depends on the default permissions on the object... If
a variable object is publicly accessible by default, then you have to take
care about revoke & grant and you can mess up with it. Moreover, it also
suggest that session variables could be private by default.

*@db> SELECT secfunc2(); -- returns: "postgres - fabien" from both
sides...

Perhaps I've got the restrictions on SECDEF wrong, but I know there's
problems with it. Certainly one issue is you can't change roles back to the
calling user.

It is the same with suid executable on Unix, I do not see as an issue.

Maybe they would be workable in this case, but it's just a bunch of extra
busywork for the user that serves no real purpose.

Pavel special case is the purpose.

Then IMHO what needs to happen is to have a discussion on actual syntax
instead of calling into question the value of the feature.

I think that the discussion should be *both* about syntax and semantics.

Following this thread has been very painful because the communications
have not been very clear.

Yep. The fact that I initially missed the "half persistence" property of
Pavel's design has not helped.

I suggest to move the discussion on the following wiki page that I have
just bootstrapped:

https://wiki.postgresql.org/wiki/Variable_Design

--
Fabien

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

Hello Pavel,

Hmmm... I know a little bit about that field, ISTM that you are speaking
of the current capabilities of a particular static analysis tool, but I'm
not sure that the tool capabilities could not be enhanced to manage more
things.

It cannot be - the static analyze is limited to function scope - in static
analyze you don't know a order of calls.

I have been doing interprocedural static analysis for the last 25 years,
and I can assure you that those techniques are not limited to the scope of
a function. As for global variables, I agree that you may proove more
things about them if you know the order of calls.

The private session variables I suggested have a fixed (static) name, and
their type could be infered by a static analysis tool, eg:
...
DECLARE @foo BOOLEAN PRIVATE;
-- I know that there is private a boolean variable "@foo" of unknown
value
SET @foo = TRUE;
--- I know that @foo is true...
...

This is not too friendly

Friendly is subjective. ISTM That it gets the job done with minimal syntax
and implementation. It avoids getter/setter functions which are unfriendly
to me.

- you have to repeat DECLARE in every function.

That is the same in nearly all programming languages, you have to declare
external variables somehow before using them, that is life.

Some declarations could be avoided if an unknown variable is assumed to
have untyped value NULL by default.

What is somebody somewhere write @fooo

NULL ? Unkown variable error ?

or use DECLARE @foo integer instead.

It would not matter if it is someone else, because @foo would be their
private version. If it is yourself, an error could be raised if a session
variable is found to be declared with two distinct types. A static
analysis tool would be able to detect that as well.

There is big space for errors.

Whatever the features and syntax, you can always shoot yourself in the
foot.

I have open a wiki page to help with this discussion:

https://wiki.postgresql.org/wiki/Variable_Design

--
Fabien.

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

2016-12-29 9:36 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Pavel,

Hmmm... I know a little bit about that field, ISTM that you are speaking

of the current capabilities of a particular static analysis tool, but I'm
not sure that the tool capabilities could not be enhanced to manage more
things.

It cannot be - the static analyze is limited to function scope - in static
analyze you don't know a order of calls.

I have been doing interprocedural static analysis for the last 25 years,
and I can assure you that those techniques are not limited to the scope of
a function. As for global variables, I agree that you may proove more
things about them if you know the order of calls.

The private session variables I suggested have a fixed (static) name, and

their type could be infered by a static analysis tool, eg:
...
DECLARE @foo BOOLEAN PRIVATE;
-- I know that there is private a boolean variable "@foo" of unknown
value
SET @foo = TRUE;
--- I know that @foo is true...
...

This is not too friendly

Friendly is subjective. ISTM That it gets the job done with minimal syntax
and implementation. It avoids getter/setter functions which are unfriendly
to me.rst

getter/setter functions are implementation in first step. I spoke, so this
step is not last.

- you have to repeat DECLARE in every function.

That is the same in nearly all programming languages, you have to declare
external variables somehow before using them, that is life.

Some declarations could be avoided if an unknown variable is assumed to
have untyped value NULL by default.

What is somebody somewhere write @fooo

NULL ? Unkown variable error ?

or use DECLARE @foo integer instead.

It would not matter if it is someone else, because @foo would be their
private version. If it is yourself, an error could be raised if a session
variable is found to be declared with two distinct types. A static analysis
tool would be able to detect that as well.

There is big space for errors.

Whatever the features and syntax, you can always shoot yourself in the
foot.

I disagree - some concepts are more robust, other less.

Regards

Pavel

Show quoted text

I have open a wiki page to help with this discussion:

https://wiki.postgresql.org/wiki/Variable_Design

--
Fabien.

CREATE FUNCTION setup_user(TEXT, TEXT)
RETURNS BOOLEAN SECURITY DEFINER AS $$

CREATE FUNCTION isUserAuditor()
RETURNS BOOLEAN SECURITY DEFINER AS $$

so what is worse - I did one new entry in pg_class and one entry in
pg_attributes. You wrote two entries in pg_proc function - more you have to
ensure consistency of these functions.

You are not comparing the same perimeter, the setup_user() function is
necessary to both approaches for the described use case where a read-only
value is needed:

With your approach:

1. CREATE VARIABLE secure_stuff SESSION SCOPE ...
2. REVOKE/GRANT ... on VARIABLE secure_stuff
3. CREATE FUNCTION setup_user(...)

With this approach:

1. CREATE FUNCTION access_secure_stuff(...)
2. REVOKE/GRANT ... on FUNCTION access_secure_stuff
3. CREATE FUNCTION setup_user(...)

The REVOKE/GRANT are basically the same on VARIABLE and on FUNCTION.

So it is not really that different as far as catalog entry count is
concerned.

The benefit is that it avoids a special concept and use a more generic
one, i.e. basic session variables.

The added cost is that a two line function must be written, which does not
look like a big issue to implement a pretty special use case.

--
Fabien.

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

2016-12-29 9:46 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

CREATE FUNCTION setup_user(TEXT, TEXT)

RETURNS BOOLEAN SECURITY DEFINER AS $$

CREATE FUNCTION isUserAuditor()

RETURNS BOOLEAN SECURITY DEFINER AS $$

so what is worse - I did one new entry in pg_class and one entry in
pg_attributes. You wrote two entries in pg_proc function - more you have
to
ensure consistency of these functions.

You are not comparing the same perimeter, the setup_user() function is
necessary to both approaches for the described use case where a read-only
value is needed:

With your approach:

1. CREATE VARIABLE secure_stuff SESSION SCOPE ...
2. REVOKE/GRANT ... on VARIABLE secure_stuff
3. CREATE FUNCTION setup_user(...)

With this approach:

1. CREATE FUNCTION access_secure_stuff(...)
2. REVOKE/GRANT ... on FUNCTION access_secure_stuff
3. CREATE FUNCTION setup_user(...)

The REVOKE/GRANT are basically the same on VARIABLE and on FUNCTION.

So it is not really that different as far as catalog entry count is
concerned.

The benefit is that it avoids a special concept and use a more generic
one, i.e. basic session variables.

There is big difference - you concept missing any safe point. You have to
specify same information more times.

I am sorry, this discussion is in cycle - there is no sense to continue.

Regards

Pavel

Show quoted text

The added cost is that a two line function must be written, which does not
look like a big issue to implement a pretty special use case.

--
Fabien.

Please, could you remove the part of the mail you are not responding to
and just keep the relevant part?

Whatever the features and syntax, you can always shoot yourself in the
foot.

I disagree

Hmmm... I have succeeded in shotting myself in the foot with possibly
every feature of every language I have used. This is called experience...
in the end you do know how NOT to do things:-)

- some concepts are more robust, other less.

Sure. The use-case under discussion is about ONE session variable holding
an expensive to compute security status which can be consulted by other
functions. I think that probably one can have it right with both
approaches, even if it is on the second try...

--
Fabien.

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

2016-12-29 9:57 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Please, could you remove the part of the mail you are not responding to
and just keep the relevant part?

Whatever the features and syntax, you can always shoot yourself in the

foot.

I disagree

Hmmm... I have succeeded in shotting myself in the foot with possibly
every feature of every language I have used. This is called experience...
in the end you do know how NOT to do things:-)

- some concepts are more robust, other less.

Sure. The use-case under discussion is about ONE session variable holding
an expensive to compute security status which can be consulted by other
functions. I think that probably one can have it right with both
approaches, even if it is on the second try...

The robustness in not only about content, but about code maintenance,
possibility to quickly search errors or better don't do errors

Show quoted text

--
Fabien.

There is big difference - you concept missing any safe point. You have to
specify same information more times.

Not necessarily, and if so maybe twice. I'm ok to recognize that it is a
difference between both approaches, and an inconvenient of the one I'm
proposing. There also see inconvenients to the other design as well, so
there will not be any perfect solution, IMO. That is the point of
discussing.

I am sorry, this discussion is in cycle - there is no sense to continue.

If the only open option is to agree with your initial design, then
obviously this is not a path for reaching a consensus.

Could you put your ideal (final) design proposition on the wiki page? That
would avoid repeating the same cyclic arguments, they would be written
only once...

--
Fabien.

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

2016-12-29 10:11 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

There is big difference - you concept missing any safe point. You have to

specify same information more times.

Not necessarily, and if so maybe twice. I'm ok to recognize that it is a
difference between both approaches, and an inconvenient of the one I'm
proposing. There also see inconvenients to the other design as well, so
there will not be any perfect solution, IMO. That is the point of
discussing.

I am sorry, this discussion is in cycle - there is no sense to continue.

If the only open option is to agree with your initial design, then
obviously this is not a path for reaching a consensus.

There are two concepts - both can be implemented, and used (can be used
together). Both these concepts has some advantage and some disadvantages.
It is hard to expect, so there is possible full agreement - because
everybody has different preferences.

I understand so for you can be your proposal more readable, but for me,
your design of usage and security looks not well. It is acceptable without
PRIVATE flags and similar flags. It is not designed be secure. (MySQL has
nothing similar, I don't know if MSSQL has some, but probably not). Ok. We
have different priorities. For you is not usual so in one session there can
be more more times switch of secure context. It is usual for me, and for
applications what I write.

Could you put your ideal (final) design proposition on the wiki page? That
would avoid repeating the same cyclic arguments, they would be written only
once...

yes, I'll do it.

Regards

Pavel

Show quoted text

--
Fabien.

2016-12-29 10:32 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2016-12-29 10:11 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

There is big difference - you concept missing any safe point. You have to

specify same information more times.

Not necessarily, and if so maybe twice. I'm ok to recognize that it is a
difference between both approaches, and an inconvenient of the one I'm
proposing. There also see inconvenients to the other design as well, so
there will not be any perfect solution, IMO. That is the point of
discussing.

I am sorry, this discussion is in cycle - there is no sense to continue.

If the only open option is to agree with your initial design, then
obviously this is not a path for reaching a consensus.

There are two concepts - both can be implemented, and used (can be used
together). Both these concepts has some advantage and some disadvantages.
It is hard to expect, so there is possible full agreement - because
everybody has different preferences.

I understand so for you can be your proposal more readable, but for me,
your design of usage and security looks not well. It is acceptable without
PRIVATE flags and similar flags. It is not designed be secure. (MySQL has
nothing similar, I don't know if MSSQL has some, but probably not). Ok. We
have different priorities. For you is not usual so in one session there can
be more more times switch of secure context. It is usual for me, and for
applications what I write.

Could you put your ideal (final) design proposition on the wiki page?
That would avoid repeating the same cyclic arguments, they would be written
only once...

yes, I'll do it.

But I'll remove some strange ideas. Why persistent variables?

Please, one argument. We have tables. What is wrong on tables?

Anything what will be persistent will have similar performance like tables.

Regards

Pavel

Show quoted text

Regards

Pavel

--
Fabien.

Hello Pavel,

There are two concepts - both can be implemented, and used (can be used
together).

That is one point I would like to ascertain clearly and explicitely, so
having various designs side by side, eg in the wiki page, would help if
and where they interact.

The second point I am keen on discussing is how the proposed designs
provide a solution to different use cases, and at what cost.

I've added sections about use cases (I have listed 3) and how they could
be supported in the wiki page.

Both these concepts has some advantage and some disadvantages. It is
hard to expect, so there is possible full agreement - because everybody
has different preferences.

Sure.

I understand so for you can be your proposal more readable, but for me,
your design of usage and security looks not well.

Yep, there are pros and cons to all proposals. I wish they are listed
somewhere, and possibly discussed, because some pros/cons depends on
some detailed features.

It is acceptable without PRIVATE flags and similar flags. It is not
designed be secure.

Indeed. I've taken this point somehow into account and changed my proposal
so that session variables are private by default, and now I'm not even
sure that there should exist public session variables at all...

(MySQL has nothing similar, I don't know if MSSQL has some, but probably
not). Ok. We have different priorities. For you is not usual so in one
session there can be more more times switch of secure context. It is
usual for me, and for applications what I write.

I have added a section in the wiki to present succintely existing stuff in
other products.

Could you put your ideal (final) design proposition on the wiki page?

yes, I'll do it.

Good!

--
Fabien.

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

yes, I'll do it.

But I'll remove some strange ideas.

Why?

I rather expect that you would comment that you find them strange and
argue why: there is no reason to "remove" a concept idea as such at least
early in a discussion process...

Why persistent variables?

Because *you* want persistent session variables... I did not invent it,
I just removed the "session" word and generalized the concept.

Sometimes one wants to store sets, sometimes one wants to only store
value.

Please, one argument. We have tables. What is wrong on tables?

Nothing is wrong as such. Cons arguments are: the syntax is verbose just
for one scalar, and the one-row property is currently not easily enforced
by pg, AFAIK.

Note that I'm not claiming that it should be implemented, but if some kind
of half-persistent variables are implemented, I think it should be
consistent with possibly fully-persistent variable as well, even if they
are not implemented immediately, or ever.

Anything what will be persistent will have similar performance like
tables.

Yes, sure. It is a different use case. Argue in the wiki!

--
Fabien.

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

[Oops, resent, wrong from address, please accept my apologies]

Hello Pavel,

There are two concepts - both can be implemented, and used (can be used
together).

That is one point I would like to ascertain clearly and explicitely, so having
various designs side by side, eg in the wiki page, would help if and where they
interact.

The second point I am keen on discussing is how the proposed designs provide a
solution to different use cases, and at what cost.

I've added sections about use cases (I have listed 3) and how they could be
supported in the wiki page.

Both these concepts has some advantage and some disadvantages. It is hard to
expect, so there is possible full agreement - because everybody has different
preferences.

Sure.

I understand so for you can be your proposal more readable, but for me,
your design of usage and security looks not well.

Yep, there are pros and cons to all proposals. I wish they are listed
somewhere, and possibly discussed, because some pros/cons depends on some
detailed features.

It is acceptable without PRIVATE flags and similar flags. It is not designed
be secure.

Indeed. I've taken this point somehow into account and changed my proposal so
that session variables are private by default, and now I'm not even sure that
there should exist public session variables at all...

(MySQL has nothing similar, I don't know if MSSQL has some, but probably
not). Ok. We have different priorities. For you is not usual so in one
session there can be more more times switch of secure context. It is usual
for me, and for applications what I write.

I have added a section in the wiki to present succintely existing stuff in
other products.

Could you put your ideal (final) design proposition on the wiki page?

yes, I'll do it.

Good!

--
Fabien.

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

2016-12-29 11:42 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

yes, I'll do it.

But I'll remove some strange ideas.

Why?

I rather expect that you would comment that you find them strange and
argue why: there is no reason to "remove" a concept idea as such at least
early in a discussion process...

Why persistent variables?

Because *you* want persistent session variables... I did not invent it, I
just removed the "session" word and generalized the concept.

I newer talked about persistent data. I talked about persistent metadata. I
really don't propose any possible substitution of tables (relations). I
newer did it.

The used terminology is not 100% clean and natural - maybe better name is
"global temporary unshared untransactional unrelational storage" - when I
use a terminology related to temporary tables.

I don't see any sense to have two similar storages or two redundant access
methods - not in PostgreSQL level.

Sometimes one wants to store sets, sometimes one wants to only store value.

Please, one argument. We have tables. What is wrong on tables?

Nothing is wrong as such. Cons arguments are: the syntax is verbose just
for one scalar, and the one-row property is currently not easily enforced
by pg, AFAIK.

Note that I'm not claiming that it should be implemented, but if some kind
of half-persistent variables are implemented, I think it should be
consistent with possibly fully-persistent variable as well, even if they
are not implemented immediately, or ever.

There is small language barrier :) - I am thinking about features, what we
should to implement, not what can be implemented. There is lot of
possibilities, that we can find in universe. But only few has
technical/practical sense.

SQL is verbose language - I like it - because it different - the developer
have to know when he is working with database, with persistent data. The
cost of access to data is significantly higher. When this information is
hidden from developer, then the result is terrible slow.

Regards

Pavel

Show quoted text

Anything what will be persistent will have similar performance like tables.

Yes, sure. It is a different use case. Argue in the wiki!

--
Fabien.

I newer talked about persistent data. I talked about persistent metadata.

Sure, I finally understood that detail. Now if I hear "persistent
variable", I by default understand that both metadata and data are
persistent... It requires some effort to understand the subtelty.

I really don't propose any possible substitution of tables (relations).
I newer did it.

Sure.

The used terminology is not 100% clean and natural - maybe better name is
"global temporary unshared untransactional unrelational storage" -

Hmmm. Too long:-) But these properties need to be spelled out.

[...] I don't see any sense to have two similar storages or two
redundant access methods - not in PostgreSQL level.

Then say so in the wiki in the cons.

Personnaly, I'm not sure. Maybe having a clean way of declaring a one-row
"singleton" table enforced by postgresql would be enough.

--
Fabien.

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

2016-12-29 14:25 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

I newer talked about persistent data. I talked about persistent metadata.

Sure, I finally understood that detail. Now if I hear "persistent
variable", I by default understand that both metadata and data are
persistent... It requires some effort to understand the subtelty.

I really don't propose any possible substitution of tables (relations). I

newer did it.

Sure.

The used terminology is not 100% clean and natural - maybe better name is

"global temporary unshared untransactional unrelational storage" -

Hmmm. Too long:-) But these properties need to be spelled out.

[...] I don't see any sense to have two similar storages or two redundant

access methods - not in PostgreSQL level.

Then say so in the wiki in the cons.

Personnaly, I'm not sure. Maybe having a clean way of declaring a one-row
"singleton" table enforced by postgresql would be enough.

There is a singleton table :)

create table foo(x integer unique not null default 1 check(x = 1), y
integer);
insert into foo(y) values(100);
analyze foo;

The storage is not important and is not interesting - any different behave
for persistent objects different than MVCC can be big surprise for users.

What is interesting are getter functions - they can be volatile or
stable/immutable - what can be interesting, because then the value can be
used by planner.

For example - MySQL @var is volatile - can be changed in query - that's
mean, you cannot use it as const for planner :( - the behave will be same
(with same risks to performance) like using plpgsql variable in query.

With getter functions you can do bigger game.

Regards

Pavel

Show quoted text

--
Fabien.

2016-12-29 14:41 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2016-12-29 14:25 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

I newer talked about persistent data. I talked about persistent metadata.

Sure, I finally understood that detail. Now if I hear "persistent
variable", I by default understand that both metadata and data are
persistent... It requires some effort to understand the subtelty.

I really don't propose any possible substitution of tables (relations). I

newer did it.

Sure.

The used terminology is not 100% clean and natural - maybe better name is

"global temporary unshared untransactional unrelational storage" -

Hmmm. Too long:-) But these properties need to be spelled out.

[...] I don't see any sense to have two similar storages or two redundant

access methods - not in PostgreSQL level.

Then say so in the wiki in the cons.

Personnaly, I'm not sure. Maybe having a clean way of declaring a one-row
"singleton" table enforced by postgresql would be enough.

There is a singleton table :)

create table foo(x integer unique not null default 1 check(x = 1), y
integer);
insert into foo(y) values(100);
analyze foo;

The storage is not important and is not interesting - any different behave
for persistent objects different than MVCC can be big surprise for users.

our sequences - simple, persistent, and not ACID - I found lot of people
that cannot accept it - not quickly

Show quoted text

What is interesting are getter functions - they can be volatile or
stable/immutable - what can be interesting, because then the value can be
used by planner.

For example - MySQL @var is volatile - can be changed in query - that's
mean, you cannot use it as const for planner :( - the behave will be same
(with same risks to performance) like using plpgsql variable in query.

With getter functions you can do bigger game.

Regards

Pavel

--
Fabien.

There is a singleton table :)

create table foo(x integer unique not null default 1 check(x = 1), y integer);
insert into foo(y) values(100);
analyze foo;

I know this one. It can be empty, which a singleton cannot be. For a
singleton table, you should have one and only one row, you cannot insert
or delete, so this is only part of the real thing.

For example - MySQL @var is volatile - can be changed in query - that's
mean, you cannot use it as const for planner :(

Indeed, because of the ":=" within in a SELECT query, the variable is
updated at each round. Yuk.

--
Fabien.

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

2016-12-29 20:23 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

There is a singleton table :)

create table foo(x integer unique not null default 1 check(x = 1), y
integer);
insert into foo(y) values(100);
analyze foo;

I know this one. It can be empty, which a singleton cannot be. For a
singleton table, you should have one and only one row, you cannot insert or
delete, so this is only part of the real thing.

subselect is "singleton" - it returns one row every time.

Not sure if term "singleton" is valid in relation database.

Show quoted text

For example - MySQL @var is volatile - can be changed in query - that's

mean, you cannot use it as const for planner :(

Indeed, because of the ":=" within in a SELECT query, the variable is
updated at each round. Yuk.

--
Fabien.

On 12/29/2016 02:23 PM, Fabien COELHO wrote:

There is a singleton table :)

create table foo(x integer unique not null default 1 check(x = 1), y
integer);
insert into foo(y) values(100);
analyze foo;

I know this one. It can be empty, which a singleton cannot be. For a
singleton table, you should have one and only one row, you cannot
insert or delete, so this is only part of the real thing.

Surely we can do a bit better than that, if that's what you really want.
Create the table with an initial row and add a trigger preventing
anything except update.

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Not sure if term "singleton" is valid in relation database.

The term is valid in mathematics: The relational model is an extension of
set theory, eg the algebra includes set operators such as union,
intersection, difference... In the set theory, a singleton designate is a
exactly-one-element set. At least Wikipedia and my dwindling memories tell
me so:-)

--
Fabien.

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

I know this one. It can be empty, which a singleton cannot be. For a
singleton table, you should have one and only one row, you cannot insert or
delete, so this is only part of the real thing.

Surely we can do a bit better than that, if that's what you really want.
Create the table with an initial row and add a trigger preventing anything
except update.

Yes.

I just meant that this is not available easily "out of the box", but it is
obviously doable with some effort... which people would seldom do.

--
Fabien.

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

On 30 December 2016 at 14:50, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

I know this one. It can be empty, which a singleton cannot be. For a
singleton table, you should have one and only one row, you cannot insert or
delete, so this is only part of the real thing.

Surely we can do a bit better than that, if that's what you really want.
Create the table with an initial row and add a trigger preventing anything
except update.

Yes.

I just meant that this is not available easily "out of the box", but it is
obviously doable with some effort... which people would seldom do.

Sure... but every feature has a cost too, in maintenance and reliability.

This needs to have compelling use cases, and it's not free to add
multiple similar-ish/overlapping features. So some agreement on what
we should actually have is needed, along with a compelling case for
what it delivers that we can't already do.

Pavel's personal requirements include that it be well suited for
static analysis of plpgsql using his plpgsql_check tool. So he wants
persistent definitions.

You expect different things from variables, to the point where it's a
different feature.

It's unlikely two unrelated variable implementations will be accepted.

I think progress can only be achieved by setting out requirements, a
feature matrix, and which proposed implementations can deliver which
desired features. Then go from there.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Pavel's personal requirements include that it be well suited for
static analysis of plpgsql using his plpgsql_check tool. So he wants
persistent definitions.

I've been in static analysis for the last 25 years, and the logic of this
statement fails me.

Pavel wants that the plpgsql_check tool can statically analyse session
variables, which is fine with me.

It does not fellow that the definition must be "persistent" as such, it
fellows that it should be available in the PL/pgSQL script so that a tool
which reads it can check it by looking at the codes. IMO this is a lesser
requirement.

I do not think that a feature should be designed around the current
limitations of a particular external tool, esp. if said tool can be
improved at a reasonable cost.

I think progress can only be achieved by setting out requirements, a
feature matrix, and which proposed implementations can deliver which
desired features. Then go from there.

Yep. I've bootstapped a wiki page, feel free to improve it as you see fit:

https://wiki.postgresql.org/wiki/Variable_Design

--
Fabien.

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

On 30 December 2016 at 16:46, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Pavel's personal requirements include that it be well suited for
static analysis of plpgsql using his plpgsql_check tool. So he wants
persistent definitions.

I've been in static analysis for the last 25 years, and the logic of this
statement fails me.

I have no opinion here, as I've not seen plpgsql_check nor do I
understand the issues Pavel perceives with having dynamic definitions
of variables.

All I'm saying is that you two are talking around in circles by
repeating different requirements to each other, and it's not going to
get anywhere unless you both change your approach. It sounds like
you're already trying to do that.

I do not think that a feature should be designed around the current
limitations of a particular external tool, esp. if said tool can be improved
at a reasonable cost.

Not arguing there.

I was initially inclined to favour Pavel's proposal because it fits a
RLS use case I was somewhat interested in. But so would dynamic
variables resolved at runtime so long as they were fast.

Personally I don't much care what the result is, so long as it can
satisfy some kind of reasonable security isolation, such that role A
can set it, B can read it but not set it, and role C can do neither.
Preferably without resorting to creating SECURITY DEFINER accessors,
since they're messy and slow. Support for data typing would also be
nice too.

If it doesn't deliver security controls then IMO there's not much
advantage over (ab)using GUCs with current_setting(...).

Exploring the other areas discussed:

Personally I think MVCC, persistent variables are a totally
unnecessary idea that solves a problem we don't have. But maybe I
don't understand your use cases. I expect anything like that would
land up using a pg_catalog relation as a k/v-like store with different
columns for different types or something of the like, which is really
something the user can do well enough for themselves. I don't see the
point at all.

Non-MVCC persistent variables would probably be prohibitively
expensive to make crash-safe, and seem even more pointless.

Now, I can see shared variables whose state is visible across backends
but is neither MVCC nor persistent being a fun toy, albeit not one I
find likely to be particularly useful personally. But we can probably
already do that in extensions, we've got most if not all of the needed
infrastructure. Because we're a shared-nothing-by-default system, such
variables will probably need shared memory segments that need to be
allocated and, if new vars are added or their values grow too much,
re-allocated. Plus locks to control access. All of which we can
already do. Most of the uses I can think of for such things are met
reasonably well by advisory locking already, and I expect most of the
rest would be met by autonomous commit, so it feels a bit like a
feature looking for a use-case.

So .... lets take a step back or eight and ask "why?"

Pavel:

* Why is it so necessary for plpgsql variables to be implemented as
persistent entities that are in the catalogs in order for you to
achieve the static checking you want to? Is this due to limitations of
your approach in plpgsql_check, or more fundamental issues? Explain.

Fabien:

* What use do you have for persistent-data variables? Please set out
some use cases where they solve problems that are currently hard to to
solve or greatly improve on the existing solutions.

* On what basis do you _oppose_ persistently defining variables in the
catalogs as their own entities? (My own objection is that "temporary
variables" would make our existing catalog bloat issues for temp
objects even worse).

* Do you expect temporary/transient session variables to come into
existence when first set, or to require some form of explicit
definition?

Everyone:

* Does anyone care about or want variables whose value is shared
across sessions? If so, why? Set out use cases.

* Does anyone care about or want variables whose value becomes visible
as soon as set, i.e. non-MVCC? If so, why? Set out use cases.

* Does anyone care about or want variables whose value is persistent
on-disk across restarts and/or crashes, maybe recorded in WAL for
replication, etc? If so, justify how this is better than a relation in
real-world practical terms.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

On 30 December 2016 at 17:29, Craig Ringer <craig@2ndquadrant.com> wrote:

So .... lets take a step back or eight and ask "why?"

Oh, and speaking of, I see Pavel's approach as looking for a
PostgreSQL-adapted way to do something like Oracle's PL/SQL package
variables. Right Pavel?

If so, their properties are, as far as I as a non-Oracle-person can tell:

* Can be package-private or public. If public, can be both got and set
by anyone. If private, can be got and set directly only by code in
package. (Our equivalent is "by the owner"). As far as I can tell
outside access to package-private variables still uses the variable
get/set syntax, but is automatically proxied via getter/setter methods
defined in the package, if defined, otherwise inaccessible.

* Value not visible across sessions. Ever.

* Can have an initialiser / DEFAULT value.

* Non-persistent, value lost at session end.

A typical example, where package variables are init'd from a table:

http://www.dba-oracle.com/plsql/t_plsql_global_data.htm

which relies on package initializers, something we don't have (but can
work around easily enough with a little verbosity).

This shows both public vars and package-private ones.

See also https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/constantvar_declaration.htm

I believe these package variable properties are the properties Pavel
seeks to model/emulate. Declared statically, value persistent only
within the same session, non-transactional, can be private.

Certainly there's nothing here that requires us to allow GRANTs.
Simple ownership tests would supply us with similar functionality to
what Oracle users have, allowing for our lack of packages and
inability to hide the _existence_ of an object, only its contents.

My views:

I am personally overwhelmingly opposed to variables that automagically
create themselves when dereferenced, a-la Perl. Write $serialised
(english spelling) not $serialized (US spelling) and you get a silent
null. Fun! Hell. No. This is why failure to "use strict" in Perl is a
near-criminal offense.

I'd also strongly prefer to require vars to be declared before first
use. Again, like "use strict", and consistent with how Pg behaves
elsewhere. Otherwise we need some kind of magic syntax to say "this is
a variable", plus vars that get created on first assignment suck
almost as badly as ones that're null on undefined deference. Spend
half an hour debugging and figure out that you typo'd an assignment.
Again, "use strict".

I fail to see any real utility to cross-session vars, persistent or
otherwise, at this point. Use a normal or unlogged relation.

I don't see the point of untyped variables with no ownership or rights
controls. (ab)use a GUC. Note that you can achieve both xact-scoped
and session-scoped that way, with xact-scoped vars assigned using SET
LOCAL being unwound on xact end.

Unless we also propose to add ON CONNECT triggers, I think some kind
of persistency of declaration is useful but not critical. We'll land
up with apps sending preambles of declarations on session start
otherwise. But the most compelling use cases are for things where
there'll be a procedure invoked by the user or app on connect anyway,
so it can declare stuff there. I'm utterly unconvinced that it's
necessary to have them in the catalogs to achieve static checking.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

2016-12-30 10:29 GMT+01:00 Craig Ringer <craig@2ndquadrant.com>:

On 30 December 2016 at 16:46, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Pavel's personal requirements include that it be well suited for
static analysis of plpgsql using his plpgsql_check tool. So he wants
persistent definitions.

I've been in static analysis for the last 25 years, and the logic of this
statement fails me.

I have no opinion here, as I've not seen plpgsql_check nor do I
understand the issues Pavel perceives with having dynamic definitions
of variables.

All I'm saying is that you two are talking around in circles by
repeating different requirements to each other, and it's not going to
get anywhere unless you both change your approach. It sounds like
you're already trying to do that.

I do not think that a feature should be designed around the current
limitations of a particular external tool, esp. if said tool can be

improved

at a reasonable cost.

Not arguing there.

I was initially inclined to favour Pavel's proposal because it fits a
RLS use case I was somewhat interested in. But so would dynamic
variables resolved at runtime so long as they were fast.

Personally I don't much care what the result is, so long as it can
satisfy some kind of reasonable security isolation, such that role A
can set it, B can read it but not set it, and role C can do neither.
Preferably without resorting to creating SECURITY DEFINER accessors,
since they're messy and slow. Support for data typing would also be
nice too.

If it doesn't deliver security controls then IMO there's not much
advantage over (ab)using GUCs with current_setting(...).

Exploring the other areas discussed:

Personally I think MVCC, persistent variables are a totally
unnecessary idea that solves a problem we don't have. But maybe I
don't understand your use cases. I expect anything like that would
land up using a pg_catalog relation as a k/v-like store with different
columns for different types or something of the like, which is really
something the user can do well enough for themselves. I don't see the
point at all.

Non-MVCC persistent variables would probably be prohibitively
expensive to make crash-safe, and seem even more pointless.

Now, I can see shared variables whose state is visible across backends
but is neither MVCC nor persistent being a fun toy, albeit not one I
find likely to be particularly useful personally. But we can probably
already do that in extensions, we've got most if not all of the needed
infrastructure. Because we're a shared-nothing-by-default system, such
variables will probably need shared memory segments that need to be
allocated and, if new vars are added or their values grow too much,
re-allocated. Plus locks to control access. All of which we can
already do. Most of the uses I can think of for such things are met
reasonably well by advisory locking already, and I expect most of the
rest would be met by autonomous commit, so it feels a bit like a
feature looking for a use-case.

So .... lets take a step back or eight and ask "why?"

Pavel:

* Why is it so necessary for plpgsql variables to be implemented as
persistent entities that are in the catalogs in order for you to
achieve the static checking you want to? Is this due to limitations of
your approach in plpgsql_check, or more fundamental issues? Explain.

There are few reasons:

1. plpgsql_check cannot to know a order of calls of functions. So any
dynamic created object and related operations are not checkable by
plpgsql_check (or any tool). If you create variable in one function, then
this information is not available in other function.

2. You can write some hints - like Fabien proposal - it is not vulnerable
against typo. It is much more safer to have one "created" variable, then
more "declared" variables and believe so all declarations are valid. The
created variable is only on one place - you can do simple check if
reference to variable is valid or not. With query to system catalogue, you
can get the list of all variables - you can see very simply if some
variables are redundant, obsolete, wrong.

3. The catalogue objects allow to create well granularity of access
rights. without it, you have to introduce only "PRIVATE" variables - and
then you have to GRANT rights via security definer functions. There is not
simple reply to question who can work with this variable, who has access,
... you have to check a rights to getter functions. When variables are
catalogue objects, then this reply is simple. Catalogue objects allows to
have "declared" security. Without catalogue objects we have to construct
the security. For me, a declared security is stronger.

Regards

Pavel

Fabien:

* What use do you have for persistent-data variables? Please set out
some use cases where they solve problems that are currently hard to to
solve or greatly improve on the existing solutions.can

* On what basis do you _oppose_ persistently defining variables in the
catalogs as their own entities? (My own objection is that "temporary
variables" would make our existing catalog bloat issues for temp
objects even worse).

* Do you expect temporary/transient session variables to come into
existence when first set, or to require some form of explicit
definition?

Everyone:

* Does anyone care about or want variables whose value is shared
across sessions? If so, why? Set out use cases.

* Does anyone care about or want variables whose value becomes visible
as soon as set, i.e. non-MVCC? If so, why? Set out use cases.

* Does anyone care about or want variables whose value is persistent
on-disk across restarts and/or crashes, maybe recorded in WAL for
replication, etc? If so, justify how this is better than a relation in
real-world practical terms.

Thank you for this summary

Pavel

Show quoted text

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hello Craig,

A long mail with many questions, that I tried to answered clearly, the
result is too long...

[...] I have no opinion here, as I've not seen plpgsql_check nor do I
understand the issues Pavel perceives with having dynamic definitions of
variables.

I understand that Pavel assumes that a static analysis tool cannot take
into account a dynamic variable, hence is reserve.

All I'm saying is that you two are talking around in circles by
repeating different requirements to each other, and it's not going to
get anywhere unless you both change your approach. It sounds like
you're already trying to do that.

Yep, that is why I have created the wiki page, so at least a argument
should not be repeated cyclically, it should be written once.

[...] I was initially inclined to favour Pavel's proposal because it
fits a RLS use case I was somewhat interested in. But so would dynamic
variables resolved at runtime so long as they were fast.

Fitting the need of use cases is the key point, obviously.

[...] Preferably without resorting to creating SECURITY DEFINER
accessors, since they're messy and slow.

I'm not sure what you mean about "messy", but if you can objectivate this
it can be an argument. Please feel free to explain it on the wiki.

As for "slow", I have just tested overheads with pgbench, comparing a
direct arithmetic operation (as a proxy to a fast session variable
consultation) to constant returning plpgsql functions with security
definer and security invoker, on a direct socket connection, with prepared
statements:

select 1 + 0 : 0.020 ms
select one_sd() : 0.024 ms
select one_si() : 0.024 ms

I do not think that there is a significant "slowness" issue with using
function calls.

Exploring the other areas discussed:

Personally I think MVCC, persistent variables are a totally unnecessary
[...] But maybe I don't understand your use cases.

I've done a survey about the schema of projects based on databases, mysql
or pgsql. A significant number of them use a common pattern based on a
one-row table, essentially to hold some scalar information about the
application version and facilitate upgrades.

However the one-row property is just hoped for, and on principle a
database is about declaring constraints that are enforced afterwards.

I see two clean solutions to this use case: declaring tables as one row,
or having scalar objects.

Now, I can see shared variables whose state is visible across backends
but is neither MVCC nor persistent being a fun toy, albeit not one I
find likely to be particularly useful personally.

Yep, I'm skeptical as well. I would like to see a convincing use case.

Pavel:

* Why is it so necessary for plpgsql variables to be implemented as
persistent entities that are in the catalogs in order for you to
achieve the static checking you want to? Is this due to limitations of
your approach in plpgsql_check, or more fundamental issues? Explain.

Note about this question not addressed to me: currently "plpgsql_check"
cannot analyze any session variables as no such concept exists, whether
with or without persistent declarations.

Fabien:

* What use do you have for persistent-data variables? Please set out
some use cases where they solve problems that are currently hard to to
solve or greatly improve on the existing solutions.

It is about the often seen one-row pattern, that I think should be
enforced either with some singleton table declaration, or scalar objects.

* On what basis do you _oppose_ persistently defining variables in the
catalogs as their own entities?

In understand that you are speaking of "persistent session variables".

For me a database is about persistence (metadata & data) with safety
(transactions) and security (permissions)... and maybe performance:-)

Pavel's proposal creates a new object with 2 (secure metadata-persistence)
out of 4 properties... I'm not a ease with introducting a new
half-database concept in a database. I could accept it for a convincing
use case that absolutely require that for deep reasons.

On the other hand there are dynamic session variables (mysql, mssql,
oracle have some variants) which are useful on their own without
pretending to be database objects (no CREATE/ALTER/DROP, GRANT/REVOKE). If
I can make these to handle the special case and avoid a new special
half-database concept, I would prefer it.

The key point about all that is to discuss, understand and evaluate the
involved use cases.

(My own objection is that "temporary variables" would make our existing
catalog bloat issues for temp objects even worse).

I do agree that inefficient temporary variables are worthless, but ISTM
that Pavel's proposal is not exactly about temporary variables, it is
about temporary-valued permanent-variables. So there is no temporary (on
the fly) variable as such, and if it is extended for this purpose then
indeed the catalog costs look expensive.

* Do you expect temporary/transient session variables to come into
existence when first set, or to require some form of explicit
definition?

It is still an open question in the proposal I have written in the wiki.
Opinions are welcome.

A declaration is required for typing if a variable it set, but it may not
be necessary for consulting the variable if the default value of non
existing variables is NULL, but then typos are not errors, although they
could be warnings...

MySQL does on-assignment limited typing, eg "SET @foo = (...)::UNSIGNED".
I prefer a declaration, but I could live with that kind of thing, although
I do not like it because what happens on a subsequent "SET @foo =
'17'::TEXT" is unclear... is the type changed or is the value cast to
unsigned anyway? MySQL does the later.

* Does anyone care about or want variables whose value is shared
across sessions? If so, why? Set out use cases.

Rather add them to the wiki, please!

* Does anyone care about or want variables whose value becomes visible
as soon as set, i.e. non-MVCC? If so, why? Set out use cases.

Idem, in the wiki!

(1) Having some kind of variable, especially in interactive mode, allows
to manipulate previous results and reuse them later, without having to
resort to repeated sub-queries or to retype non trivial values.

Client side psql :-variables are untyped and unescaped, thus not very
convenient for this purpose.

(2) Writing a simple psql script which can be parameterized to some degree
by setting some variables and then include the script.

* Does anyone care about or want variables whose value is persistent
on-disk across restarts and/or crashes, maybe recorded in WAL for
replication, etc? If so, justify how this is better than a relation in
real-world practical terms.

IMO it is *not* better, but I wish that I could declare a table as a
singleton. See the wiki.

--
Fabien.

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

2016-12-30 11:03 GMT+01:00 Craig Ringer <craig@2ndquadrant.com>:

On 30 December 2016 at 17:29, Craig Ringer <craig@2ndquadrant.com> wrote:

So .... lets take a step back or eight and ask "why?"

Oh, and speaking of, I see Pavel's approach as looking for a
PostgreSQL-adapted way to do something like Oracle's PL/SQL package
variables. Right Pavel?

It was main motivation - the question was - how to share (in one session)
secure some information between function calls.

The PostgreSQL is specific in multi language support - but purpose is same.

If so, their properties are, as far as I as a non-Oracle-person can tell:

* Can be package-private or public. If public, can be both got and set
by anyone. If private, can be got and set directly only by code in
package. (Our equivalent is "by the owner"). As far as I can tell
outside access to package-private variables still uses the variable
get/set syntax, but is automatically proxied via getter/setter methods
defined in the package, if defined, otherwise inaccessible.

* Value not visible across sessions. Ever.

* Can have an initialiser / DEFAULT value.

* Non-persistent, value lost at session end.

A typical example, where package variables are init'd from a table:

http://www.dba-oracle.com/plsql/t_plsql_global_data.htm

which relies on package initializers, something we don't have (but can
work around easily enough with a little verbosity).

This shows both public vars and package-private ones.

See also https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/
constantvar_declaration.htm

I believe these package variable properties are the properties Pavel
seeks to model/emulate. Declared statically, value persistent only
within the same session, non-transactional, can be private.

Certainly there's nothing here that requires us to allow GRANTs.
Simple ownership tests would supply us with similar functionality to
what Oracle users have, allowing for our lack of packages and
inability to hide the _existence_ of an object, only its contents.

The packages has own scope - so any access from packages is allowed. I
cannot do it in Postgres without explicitly written setter/getter
functions. So GRANTS reduces a requirement to write security definer
envelop functions.

Sure - owner doesn't need it. If your application is one user, or if you
are owner, then you don't need to use GRANT.

Show quoted text

My views:

I am personally overwhelmingly opposed to variables that automagically
create themselves when dereferenced, a-la Perl. Write $serialised
(english spelling) not $serialized (US spelling) and you get a silent
null. Fun! Hell. No. This is why failure to "use strict" in Perl is a
near-criminal offense.

I'd also strongly prefer to require vars to be declared before first
use. Again, like "use strict", and consistent with how Pg behaves
elsewhere. Otherwise we need some kind of magic syntax to say "this is
a variable", plus vars that get created on first assignment suck
almost as badly as ones that're null on undefined deference. Spend
half an hour debugging and figure out that you typo'd an assignment.
Again, "use strict".

I fail to see any real utility to cross-session vars, persistent or
otherwise, at this point. Use a normal or unlogged relation.

I don't see the point of untyped variables with no ownership or rights
controls. (ab)use a GUC. Note that you can achieve both xact-scoped
and session-scoped that way, with xact-scoped vars assigned using SET
LOCAL being unwound on xact end.

Unless we also propose to add ON CONNECT triggers, I think some kind
of persistency of declaration is useful but not critical. We'll land
up with apps sending preambles of declarations on session start
otherwise. But the most compelling use cases are for things where
there'll be a procedure invoked by the user or app on connect anyway,
so it can declare stuff there. I'm utterly unconvinced that it's
necessary to have them in the catalogs to achieve static checking.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Please Pavel, could you avoid citing a whole long mail just for commenting
one point?

* Why is it so necessary for plpgsql variables to be implemented as
persistent entities that are in the catalogs in order for you to
achieve the static checking you want to? Is this due to limitations of
your approach in plpgsql_check, or more fundamental issues? Explain.

There are few reasons:

1. plpgsql_check cannot to know a order of calls of functions.

Indeed.

So any dynamic created object and related operations are not checkable
by plpgsql_check (or any tool).

NO. Your first sentence does not imply this very general statement.

Some things that I think can be statically proved within a session, that
would cover some security concerns:

(1) For statically named private dynamic variables declared/used at
different points it can be checked without relying on the function order
that all declarations are consistent, i.e. the same type, same default
value if any.

(2) Then the value of the variable is either the default value (eg NULL)
or the assigned value at points of assignement, which must be a valid
value for the type, otherwise the assignement would have failed.

(3) If there is only one assignment in the code, then you know that the
variable can only have been assigned a non default value from this point.
Probably nice to have in a security context, but it requires you to be
sure that you have access to all the code...

(4) For a session boolean, then for code "IF @var IS NOT NULL AND NOT @var
THEN RAISE 'cannot access'; END", in a sequence, then one can prove that
for any pl code after this point in the sequence @var has been previously
assigned to true, otherwise the exception would have been raised.

AFAICS, for "static" session variables the only difference is that the
declaration consistency (1) is slightly more built-in, although you still
have to check that no function DROP/re-CREATE the session variable with a
different type, which is quite close to checking (1) for a dynamic session
variable.

Other properties (2), (3), (4) are exactly the same.

2. [...] 3.

Please could you put your pros & cons in the wiki as well?

Or don't you want to use it?

--
Fabien.

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

2016-12-30 14:45 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Please Pavel, could you avoid citing a whole long mail just for commenting
one point?

* Why is it so necessary for plpgsql variables to be implemented as

persistent entities that are in the catalogs in order for you to
achieve the static checking you want to? Is this due to limitations of
your approach in plpgsql_check, or more fundamental issues? Explain.

There are few reasons:

1. plpgsql_check cannot to know a order of calls of functions.

Indeed.

So any dynamic created object and related operations are not checkable by

plpgsql_check (or any tool).

NO. Your first sentence does not imply this very general statement.

If you have not unique definition, you cannot to it. There is not
possibility different between typo and decision. We are talking about
static analyze - so code should not be executed - and you don't know what
function will be started first.

Some things that I think can be statically proved within a session, that
would cover some security concerns:

(1) For statically named private dynamic variables declared/used at
different points it can be checked without relying on the function order
that all declarations are consistent, i.e. the same type, same default
value if any.

what is "static" private dynamic variable ? You are using new terminology.
Static variables like Clang static variables are not usable - you would to
share content between different functions.

(2) Then the value of the variable is either the default value (eg NULL)
or the assigned value at points of assignement, which must be a valid value
for the type, otherwise the assignement would have failed.

(3) If there is only one assignment in the code, then you know that the
variable can only have been assigned a non default value from this point.
Probably nice to have in a security context, but it requires you to be
sure that you have access to all the code...

(4) For a session boolean, then for code "IF @var IS NOT NULL AND NOT @var
THEN RAISE 'cannot access'; END", in a sequence, then one can prove that
for any pl code after this point in the sequence @var has been previously
assigned to true, otherwise the exception would have been raised.

You are speaking about runtime check.There is not a problem to define some
rules for runtime check.

What is not possible in your design

1.

BEGIN
RAISE NOTICE '%', @var;
END;

Without "execution", you cannot to say if usage of @var is correct or not

ok, we can use a DECLARE var

DECLARE @var EXTERNAL
BEGIN
RAISE NOTICE '%', @var;
END;

ok, I can do static check - but

1. anytime I have to repeat DECLARE statement
2. there is not possible to find typo in DECLARE statement

Regards

Pavel

Show quoted text

AFAICS, for "static" session variables the only difference is that the
declaration consistency (1) is slightly more built-in, although you still
have to check that no function DROP/re-CREATE the session variable with a
different type, which is quite close to checking (1) for a dynamic session
variable.

Other properties (2), (3), (4) are exactly the same.

2. [...] 3.

Please could you put your pros & cons in the wiki as well?

Or don't you want to use it?

--
Fabien.

2016-12-30 12:01 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2016-12-30 10:29 GMT+01:00 Craig Ringer <craig@2ndquadrant.com>:

On 30 December 2016 at 16:46, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Pavel's personal requirements include that it be well suited for
static analysis of plpgsql using his plpgsql_check tool. So he wants
persistent definitions.

I've been in static analysis for the last 25 years, and the logic of

this

statement fails me.

I have no opinion here, as I've not seen plpgsql_check nor do I
understand the issues Pavel perceives with having dynamic definitions
of variables.

All I'm saying is that you two are talking around in circles by
repeating different requirements to each other, and it's not going to
get anywhere unless you both change your approach. It sounds like
you're already trying to do that.

I do not think that a feature should be designed around the current
limitations of a particular external tool, esp. if said tool can be

improved

at a reasonable cost.

Not arguing there.

I was initially inclined to favour Pavel's proposal because it fits a
RLS use case I was somewhat interested in. But so would dynamic
variables resolved at runtime so long as they were fast.

Personally I don't much care what the result is, so long as it can
satisfy some kind of reasonable security isolation, such that role A
can set it, B can read it but not set it, and role C can do neither.
Preferably without resorting to creating SECURITY DEFINER accessors,
since they're messy and slow. Support for data typing would also be
nice too.

If it doesn't deliver security controls then IMO there's not much
advantage over (ab)using GUCs with current_setting(...).

Exploring the other areas discussed:

Personally I think MVCC, persistent variables are a totally
unnecessary idea that solves a problem we don't have. But maybe I
don't understand your use cases. I expect anything like that would
land up using a pg_catalog relation as a k/v-like store with different
columns for different types or something of the like, which is really
something the user can do well enough for themselves. I don't see the
point at all.

Non-MVCC persistent variables would probably be prohibitively
expensive to make crash-safe, and seem even more pointless.

Now, I can see shared variables whose state is visible across backends
but is neither MVCC nor persistent being a fun toy, albeit not one I
find likely to be particularly useful personally. But we can probably
already do that in extensions, we've got most if not all of the needed
infrastructure. Because we're a shared-nothing-by-default system, such
variables will probably need shared memory segments that need to be
allocated and, if new vars are added or their values grow too much,
re-allocated. Plus locks to control access. All of which we can
already do. Most of the uses I can think of for such things are met
reasonably well by advisory locking already, and I expect most of the
rest would be met by autonomous commit, so it feels a bit like a
feature looking for a use-case.

So .... lets take a step back or eight and ask "why?"

Pavel:

* Why is it so necessary for plpgsql variables to be implemented as
persistent entities that are in the catalogs in order for you to
achieve the static checking you want to? Is this due to limitations of
your approach in plpgsql_check, or more fundamental issues? Explain.

There are few reasons:

1. plpgsql_check cannot to know a order of calls of functions. So any
dynamic created object and related operations are not checkable by
plpgsql_check (or any tool). If you create variable in one function, then
this information is not available in other function.

2. You can write some hints - like Fabien proposal - it is not vulnerable
against typo. It is much more safer to have one "created" variable, then
more "declared" variables and believe so all declarations are valid. The
created variable is only on one place - you can do simple check if
reference to variable is valid or not. With query to system catalogue, you
can get the list of all variables - you can see very simply if some
variables are redundant, obsolete, wrong.

3. The catalogue objects allow to create well granularity of access
rights. without it, you have to introduce only "PRIVATE" variables - and
then you have to GRANT rights via security definer functions. There is not
simple reply to question who can work with this variable, who has access,
... you have to check a rights to getter functions. When variables are
catalogue objects, then this reply is simple. Catalogue objects allows to
have "declared" security. Without catalogue objects we have to construct
the security. For me, a declared security is stronger.

My concept is similar to "global" variables in C language. Without header
files you can use it - but maybe linker fails, maybe runtime fails. For me
- information in catalogue is exactly what we do by declaration in header
file.

Regards

Pavel

Show quoted text

Regards

Pavel

Fabien:

* What use do you have for persistent-data variables? Please set out
some use cases where they solve problems that are currently hard to to
solve or greatly improve on the existing solutions.can

* On what basis do you _oppose_ persistently defining variables in the
catalogs as their own entities? (My own objection is that "temporary
variables" would make our existing catalog bloat issues for temp
objects even worse).

* Do you expect temporary/transient session variables to come into
existence when first set, or to require some form of explicit
definition?

Everyone:

* Does anyone care about or want variables whose value is shared
across sessions? If so, why? Set out use cases.

* Does anyone care about or want variables whose value becomes visible
as soon as set, i.e. non-MVCC? If so, why? Set out use cases.

* Does anyone care about or want variables whose value is persistent
on-disk across restarts and/or crashes, maybe recorded in WAL for
replication, etc? If so, justify how this is better than a relation in
real-world practical terms.

Thank you for this summary

Pavel

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hello again,

So any dynamic created object and related operations are not checkable by

plpgsql_check (or any tool).

NO. Your first sentence does not imply this very general statement.

If you have not unique definition, you cannot to it. There is not
possibility different between typo and decision. We are talking about
static analyze - so code should not be executed - and you don't know
what function will be started first.

Yes, I assure you that I really know how static analysis works... All the
properties I described below may be proved without executing the code,
that was my point...

Some things that I think can be statically proved within a session, that
would cover some security concerns:

(1) For statically named private dynamic variables declared/used at
different points it can be checked without relying on the function order
that all declarations are consistent, i.e. the same type, same default
value if any.

what is "static" private dynamic variable ? You are using new terminology.

They are my session variable, I just spelled out some properties to be
precise about what I am stating, otherwise it is a mess. The name of the
variable is "static" (statically-named), i.e. it is known directly in the
code. However the variable creation and value are "dynamic".

Static variables like Clang static variables are not usable - you would to
share content between different functions.

Sure. I mean static as in "static analysis", i.e. by looking at the code
without executing it, as you put it.

(2) (3) (4) [...]

You are speaking about runtime check.

Not really, I was speaking about properties statically provable, which I
understood was your concern. Now the properties proved may imply a runtime
assumption, for instance that the code has executed without error up to
some point in the program, which is basically impossible to prove
statically.

BEGIN
RAISE NOTICE '%', @var;
END;

Without "execution", you cannot to say if usage of @var is correct or not

It depends about your definition of "correct".

For this very instance it would not matter: if the variable was not
created beforehand, then an error is raised because it does not exist, if
it was created before hand, then an error is raised because that is what
the code is doing... So an error is always raised if the variable is not
right.

ok, we can use a DECLARE var

DECLARE @var EXTERNAL

I do not know what you mean by 'EXTERNAL'.

BEGIN
RAISE NOTICE '%', @var;
END;

ok, I can do static check - but

1. anytime I have to repeat DECLARE statement

Yes, twice in the complete use case: one for the function which checks the
credentials, one for the getter function.

2. there is not possible to find typo in DECLARE statement

It is possible to find "some" typos, depending on the code: you can check
that a variable is both assigned and used somewhere, otherwise it is very
probably a typo. Perl does that *statically*, before executing a script.

--
Fabien.

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

2016-12-30 15:34 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello again,

So any dynamic created object and related operations are not checkable by

plpgsql_check (or any tool).

NO. Your first sentence does not imply this very general statement.

If you have not unique definition, you cannot to it. There is not
possibility different between typo and decision. We are talking about
static analyze - so code should not be executed - and you don't know what
function will be started first.

Yes, I assure you that I really know how static analysis works... All the
properties I described below may be proved without executing the code, that
was my point...

Some things that I think can be statically proved within a session, that

would cover some security concerns:

(1) For statically named private dynamic variables declared/used at
different points it can be checked without relying on the function order
that all declarations are consistent, i.e. the same type, same default
value if any.

what is "static" private dynamic variable ? You are using new terminology.

They are my session variable, I just spelled out some properties to be
precise about what I am stating, otherwise it is a mess. The name of the
variable is "static" (statically-named), i.e. it is known directly in the
code. However the variable creation and value are "dynamic".

Static variables like Clang static variables are not usable - you would to

share content between different functions.

Sure. I mean static as in "static analysis", i.e. by looking at the code
without executing it, as you put it.

(2) (3) (4) [...]

You are speaking about runtime check.

Not really, I was speaking about properties statically provable, which I
understood was your concern. Now the properties proved may imply a runtime
assumption, for instance that the code has executed without error up to
some point in the program, which is basically impossible to prove
statically.

BEGIN

RAISE NOTICE '%', @var;
END;

Without "execution", you cannot to say if usage of @var is correct or not

It depends about your definition of "correct".

For this very instance it would not matter: if the variable was not
created beforehand, then an error is raised because it does not exist, if
it was created before hand, then an error is raised because that is what
the code is doing... So an error is always raised if the variable is not
right.

ok, we can use a DECLARE var

DECLARE @var EXTERNAL

I do not know what you mean by 'EXTERNAL'.

it means "used as shared in session"

BEGIN

RAISE NOTICE '%', @var;
END;

ok, I can do static check - but

1. anytime I have to repeat DECLARE statement

Yes, twice in the complete use case: one for the function which checks the
credentials, one for the getter function.

2. there is not possible to find typo in DECLARE statement

It is possible to find "some" typos, depending on the code: you can check
that a variable is both assigned and used somewhere, otherwise it is very
probably a typo. Perl does that *statically*, before executing a script.

"Before execution" .. I am speaking "without execution".

theoretically, if you check all possible functions, you can find some
issues - but you have to check all function on server. Elsewhere you cannot
to find typo in DECLARE statement.

Regards

Pavel

Show quoted text

--
Fabien.

DECLARE @var EXTERNAL

I do not know what you mean by 'EXTERNAL'.

it means "used as shared in session"

Shared by whom? There is no such thing in the proposal I have made on the
wiki or in mails. In the proposal variables are private to the role which
creates them.

It is possible to find "some" typos, depending on the code: you can check
that a variable is both assigned and used somewhere, otherwise it is very
probably a typo. Perl does that *statically*, before executing a script.

"Before execution" .. I am speaking "without execution".

Before execution is also without execution. You can run "perl -c" to get
the warning.

theoretically, if you check all possible functions, you can find some
issues - but you have to check all function on server.

Yes, sure. It seems to be the same with your proposal: if a hidden
function drops and recreates a session variable with a different type, or
changes its permission, then some static checks are void as well, that is
life. Also, a SQL function may access and modify the variables
unexpectedly, that would be missed by a PL/pgSQL analysis tool... There is
no miracle.

Basically, there may be issues even if static analysis tools says that all
is well.

Elsewhere you cannot to find typo in DECLARE statement.

Indeed, probably there exists some class of typos that may not be found by
some static analysis implementations on PL/pgSQL functions which uses
basic session variables.

By the way, are you planing to contribute to the wiki?

https://wiki.postgresql.org/wiki/Variable_Design

--
Fabien.

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

2016-12-30 18:39 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

DECLARE @var EXTERNAL

I do not know what you mean by 'EXTERNAL'.

it means "used as shared in session"

Shared by whom? There is no such thing in the proposal I have made on the
wiki or in mails. In the proposal variables are private to the role which
creates them.

shared by functions in session.

It is possible to find "some" typos, depending on the code: you can check

that a variable is both assigned and used somewhere, otherwise it is very
probably a typo. Perl does that *statically*, before executing a script.

"Before execution" .. I am speaking "without execution".

Before execution is also without execution. You can run "perl -c" to get
the warning.

theoretically, if you check all possible functions, you can find some

issues - but you have to check all function on server.

Yes, sure. It seems to be the same with your proposal: if a hidden
function drops and recreates a session variable with a different type, or
changes its permission, then some static checks are void as well, that is
life. Also, a SQL function may access and modify the variables
unexpectedly, that would be missed by a PL/pgSQL analysis tool... There is
no miracle.

No - metadata, in my design, are persistent - like tables - so you don't
calculate so any functions can drop a variables. The deployment of secure
variables is same like table deployment. No dynamic there.

Basically, there may be issues even if static analysis tools says that all
is well.

Elsewhere you cannot to find typo in DECLARE statement.

Indeed, probably there exists some class of typos that may not be found by
some static analysis implementations on PL/pgSQL functions which uses basic
session variables.

yes, and I would not to append any new case that cannot be covered by
plpgsql check. Dynamic SQL and our temporal tables are enough issues
already.

By the way, are you planing to contribute to the wiki?

https://wiki.postgresql.org/wiki/Variable_Design

I wrote my notes there.

Show quoted text

--
Fabien.

On 30 December 2016 at 21:00, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

As for "slow", I have just tested overheads with pgbench, comparing a direct
arithmetic operation (as a proxy to a fast session variable consultation) to
constant returning plpgsql functions with security definer and security
invoker, on a direct socket connection, with prepared statements:

select 1 + 0 : 0.020 ms
select one_sd() : 0.024 ms
select one_si() : 0.024 ms

That's one call per executor run. Not really an effective test.

Consider cases like row security where you're testing 10000 rows.
Hopefully the planner will inline the test if it's a function declared
stable, but it may not.

However the one-row property is just hoped for, and on principle a database
is about declaring constraints that are enforced afterwards.

I see two clean solutions to this use case: declaring tables as one row, or
having scalar objects.

I agree that's a common issue.

The unique partial index on 1 hack in postgres works, though it's ugly.

Adding a whole new different storage concept seems like massive
overkill for this problem, which is minor and already easily solved.
Someone could make 1-row tables prettier with a new constraint type
instead maybe, if it's really considered that ugly. Personally I'd
just document the unique expression index hack.

CREATE UNIQUE INDEX onerow ON mytable((1));

* On what basis do you _oppose_ persistently defining variables in the
catalogs as their own entities?

In understand that you are speaking of "persistent session variables".

For me a database is about persistence (metadata & data) with safety
(transactions) and security (permissions)... and maybe performance:-)

Pavel's proposal creates a new object with 2 (secure metadata-persistence)
out of 4 properties... I'm not a ease with introducting a new half-database
concept in a database.

I strongly disagree. If you want "all-database" properties ... use tables.

We generally add new features when that's not sufficient to achieve
something. Most notably SEQUENCEs, which deliberately violate
transaction isolation and atomicity in order to deliver a compelling
benefit not otherwise achieveable.

Similarly for advisory locking.

On the other hand there are dynamic session variables (mysql, mssql, oracle
have some variants) which are useful on their own without pretending to be
database objects (no CREATE/ALTER/DROP, GRANT/REVOKE).

We have precent here for sequences. Yes, they do confuse users, but
they're also VERY useful, and the properties of variables would be
clearer IMO.

I'm not especially attached to doing them as database objects; I'm
just as happy with something declared at session start by some
function that then intends to set and use the variable. But I don't
think your argument against a DDL-like approach holds water.

(My own objection is that "temporary variables" would make our existing
catalog bloat issues for temp objects even worse).

I do agree that inefficient temporary variables are worthless, but ISTM that
Pavel's proposal is not exactly about temporary variables, it is about
temporary-valued permanent-variables. So there is no temporary (on the fly)
variable as such, and if it is extended for this purpose then indeed the
catalog costs look expensive.

I meant that we'd certainly want CREATE TEMPORARY VARIABLE for ones
that go away at end of session, if we were going to have
catalog-object-like variables. Which would result in catalog bloat.

(1) Having some kind of variable, especially in interactive mode, allows to
manipulate previous results and reuse them later, without having to resort
to repeated sub-queries or to retype non trivial values.

Client side psql :-variables are untyped and unescaped, thus not very
convenient for this purpose.

You can currently (ab)use user defined GUCs for this. Ugly, but
effective, and honestly something we could bless into general use if
we decided to. It's not that bad.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

2016-12-31 1:16 GMT+01:00 Craig Ringer <craig@2ndquadrant.com>:

On 30 December 2016 at 21:00, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

As for "slow", I have just tested overheads with pgbench, comparing a

direct

arithmetic operation (as a proxy to a fast session variable

consultation) to

constant returning plpgsql functions with security definer and security
invoker, on a direct socket connection, with prepared statements:

select 1 + 0 : 0.020 ms
select one_sd() : 0.024 ms
select one_si() : 0.024 ms

That's one call per executor run. Not really an effective test.

Consider cases like row security where you're testing 10000 rows.
Hopefully the planner will inline the test if it's a function declared
stable, but it may not.

However the one-row property is just hoped for, and on principle a

database

is about declaring constraints that are enforced afterwards.

I see two clean solutions to this use case: declaring tables as one row,

or

having scalar objects.

I agree that's a common issue.

The unique partial index on 1 hack in postgres works, though it's ugly.

Adding a whole new different storage concept seems like massive
overkill for this problem, which is minor and already easily solved.
Someone could make 1-row tables prettier with a new constraint type
instead maybe, if it's really considered that ugly. Personally I'd
just document the unique expression index hack.

CREATE UNIQUE INDEX onerow ON mytable((1));

* On what basis do you _oppose_ persistently defining variables in the
catalogs as their own entities?

In understand that you are speaking of "persistent session variables".

For me a database is about persistence (metadata & data) with safety
(transactions) and security (permissions)... and maybe performance:-)

Pavel's proposal creates a new object with 2 (secure

metadata-persistence)

out of 4 properties... I'm not a ease with introducting a new

half-database

concept in a database.

I strongly disagree. If you want "all-database" properties ... use tables.

We generally add new features when that's not sufficient to achieve
something. Most notably SEQUENCEs, which deliberately violate
transaction isolation and atomicity in order to deliver a compelling
benefit not otherwise achieveable.

Similarly for advisory locking.

On the other hand there are dynamic session variables (mysql, mssql,

oracle

have some variants) which are useful on their own without pretending to

be

database objects (no CREATE/ALTER/DROP, GRANT/REVOKE).

We have precent here for sequences. Yes, they do confuse users, but
they're also VERY useful, and the properties of variables would be
clearer IMO.

I'm not especially attached to doing them as database objects; I'm
just as happy with something declared at session start by some
function that then intends to set and use the variable. But I don't
think your argument against a DDL-like approach holds water.

(My own objection is that "temporary variables" would make our existing
catalog bloat issues for temp objects even worse).

I do agree that inefficient temporary variables are worthless, but ISTM

that

Pavel's proposal is not exactly about temporary variables, it is about
temporary-valued permanent-variables. So there is no temporary (on the

fly)

variable as such, and if it is extended for this purpose then indeed the
catalog costs look expensive.

I meant that we'd certainly want CREATE TEMPORARY VARIABLE for ones
that go away at end of session, if we were going to have
catalog-object-like variables. Which would result in catalog bloat.

Because our catalog is MVCC, then bloating is unremovable - but if we
implement global temporary tables, then metadata of temporary objects can
be stored there - the main catalogue can be stable.

But the question? When you would to use local temporary variables? When you
cannot to use global variables? Probably in adhoc scripts, in interactive
work, ... It is minimal impact on catalogue.

The performance problems can be in PL usage, or intensive application usage
- and there can be used global variables.

Analogy with our temporary tables - if we can use global temporary tables
in critical PL, then local temporary tables can be nice feature perfect for
interactive work, and nobody have to fix a catalogue bloat.

Design of possibility to do local temporary variable is minimal work. I
don't afraid about performance when developers can use global variables as
option

Regards

Pavel

Show quoted text

(1) Having some kind of variable, especially in interactive mode, allows

to

manipulate previous results and reuse them later, without having to

resort

to repeated sub-queries or to retype non trivial values.

Client side psql :-variables are untyped and unescaped, thus not very
convenient for this purpose.

You can currently (ab)use user defined GUCs for this. Ugly, but
effective, and honestly something we could bless into general use if
we decided to. It's not that bad.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

unexpectedly, that would be missed by a PL/pgSQL analysis tool... There is
no miracle.

No - metadata, in my design, are persistent - like tables - so you don't
calculate so any functions can drop a variables. The deployment of secure
variables is same like table deployment. No dynamic there.

You are missing my point: Static analysis is about proving properties. If
you need metadata to be persistent, then you should check that it is the
case, i.e. the static analysis must check that there is no metadata
changes anywhere. For instance, an analysis tool should reject a function
which contains:

GRANT UPDATE ON VARIABLE super_secret_variable TO PUBLIC;

Or does:

DROP VARIABLE super_secret;
CREATE VARIABLE super_secret ...;

If a static analysis tool is specific to one language, then it can only
checks that all is well in functions in those languages, but as there may
be functions written in other languages as well then the check is somehow
partial. This is not a bad thing, it just illustrate that you cannot check
everything. That is quality ensurance.

[...] Indeed, probably there exists some class of typos that may not be
found by some static analysis implementations on PL/pgSQL functions
which uses basic session variables.

yes, and I would not to append any new case that cannot be covered by
plpgsql check. Dynamic SQL and our temporal tables are enough issues
already.

I'm not sure that I understand these sentences.

I wrote my notes there.

Great! I restructured a little bit and tried to improve the English. I
also added questions when some statement that I think are too optimistic,
or are unclear to me.

--
Fabien.

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

2016-12-31 17:51 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

unexpectedly, that would be missed by a PL/pgSQL analysis tool... There is

no miracle.

No - metadata, in my design, are persistent - like tables - so you don't
calculate so any functions can drop a variables. The deployment of secure
variables is same like table deployment. No dynamic there.

You are missing my point: Static analysis is about proving properties. If
you need metadata to be persistent, then you should check that it is the
case, i.e. the static analysis must check that there is no metadata changes
anywhere. For instance, an analysis tool should reject a function which
contains:

GRANT UPDATE ON VARIABLE super_secret_variable TO PUBLIC;

It doesn't need to reject this functions - but this information is not
visible in any other functions. But this tasks you are do in deployment
scripts - not in functions.

Or does:

DROP VARIABLE super_secret;
CREATE VARIABLE super_secret ...;

But you don't do it in functions - these variables are persistent - you
don't create it or drop inside functions. The content is secure, so you
don't need to hide this variable against other.

If a static analysis tool is specific to one language, then it can only
checks that all is well in functions in those languages, but as there may
be functions written in other languages as well then the check is somehow
partial. This is not a bad thing, it just illustrate that you cannot check
everything. That is quality ensurance.

[...] Indeed, probably there exists some class of typos that may not be

found by some static analysis implementations on PL/pgSQL functions which
uses basic session variables.

yes, and I would not to append any new case that cannot be covered by
plpgsql check. Dynamic SQL and our temporal tables are enough issues
already.

I'm not sure that I understand these sentences.

so I don't prefer any design that increase a area where plpgsql_check
should not work.

I wrote my notes there.

Great! I restructured a little bit and tried to improve the English. I
also added questions when some statement that I think are too optimistic,
or are unclear to me.

we have just different perspectives

Regards

Pavel

Show quoted text

--
Fabien.

Hello Craig,

As for "slow", I have just tested overheads with pgbench, comparing a direct
arithmetic operation (as a proxy to a fast session variable consultation) to
constant returning plpgsql functions with security definer and security
invoker, on a direct socket connection, with prepared statements:

select 1 + 0 : 0.020 ms
select one_sd() : 0.024 ms
select one_si() : 0.024 ms

That's one call per executor run. Not really an effective test.

I really did 10 calls per transaction. For one call it was 24 ms vs 28 ms.
However I'm not sure of the respective overheads of the protocol, planer
and executor, though.

Consider cases like row security where you're testing 10000 rows.

Another test: calling 1,000,000 times one_sd() or one_si() in a plpgsql
loops seems to cost about 1.1 seconds on my laptop. I'd say that the
function call is about 2/3 of that time, the rest is on the loop and exit
test.

SELECT NOW();
DO LANGUAGE plpgsql $$
DECLARE count INT DEFAULT 0;
BEGIN
LOOP count := count + ONE_SD() ;
EXIT WHEN count = 1000000;
END LOOP;
END; $$;
SELECT NOW();

Based on these evidences, I continue to think that there is no significant
performance issue with calling simple security definer functions.

Hopefully the planner will inline the test if it's a function declared
stable, but it may not.

Indeed they are, so the planner should factor out the test when possible.

* On what basis do you _oppose_ persistently defining variables in the
catalogs as their own entities?

In understand that you are speaking of "persistent session variables".

For me a database is about persistence (metadata & data) with safety
(transactions) and security (permissions)... and maybe performance:-)

Pavel's proposal creates a new object with 2 (secure metadata-persistence)
out of 4 properties... I'm not a ease with introducting a new half-database
concept in a database.

I strongly disagree. If you want "all-database" properties ... use tables.

Sure. I am not sure about what are you disagreeing with, as I'm just
describing Pavel's proposal...

We generally add new features when that's not sufficient to achieve
something. Most notably SEQUENCEs, which deliberately violate
transaction isolation and atomicity in order to deliver a compelling
benefit not otherwise achieveable.

Yes, sure.

On the other hand there are dynamic session variables (mysql, mssql, oracle
have some variants) which are useful on their own without pretending to be
database objects (no CREATE/ALTER/DROP, GRANT/REVOKE).

We have precent here for sequences. Yes, they do confuse users, but
they're also VERY useful, and the properties of variables would be
clearer IMO.

Yep. But my point is that before adding a new strange object type I would
prefer that there is no other solution.

I'm not especially attached to doing them as database objects; I'm
just as happy with something declared at session start by some
function that then intends to set and use the variable. But I don't
think your argument against a DDL-like approach holds water.

I have expectations about objects hold by a database, and these new object
fails them.

If you do not have expectations, then all is fine.

(1) Having some kind of variable, especially in interactive mode, allows to
manipulate previous results and reuse them later, without having to resort
to repeated sub-queries or to retype non trivial values.

Client side psql :-variables are untyped and unescaped, thus not very
convenient for this purpose.

You can currently (ab)use user defined GUCs for this.

How? It seems that I have missed the syntax to assign the result of a
query to a user-defined guc, and to reuse it simply in a query.

--
Fabien.

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

DROP VARIABLE super_secret;
CREATE VARIABLE super_secret ...;

But you don't do it in functions - these variables are persistent - you
don't create it or drop inside functions. The content is secure, so you
don't need to hide this variable against other.

ISTM that you are still missing my point.

I understood that you want a static analysis tool to re-assure you about
how your session variables are manipulated. I do not see how such a tool
can give any assurance without checking that the variable meta-data are
not changed by some malicious code inserted in a function.

I'm not sure that I understand these sentences.

so I don't prefer any design that increase a area where plpgsql_check
should not work.

My assumption is that plpgsql_check can be improved. For instance, I
assume that if "secure session variables" are added, then it will be
enhanced to do some checking about these and take them into account. If
"simple session variables" are added, I assume that it would also be
updated accordingly.

I wrote my notes there.

Great! I restructured a little bit and tried to improve the English. I
also added questions when some statement that I think are too optimistic,
or are unclear to me.

we have just different perspectives

I'm trying to have sentences that are both clear and true. If I think that
a sentence is imprecise because it is missing a key hypothesis, then I try
to improve it, whether it is mine or someone else.

--
Fabien.

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

If you do not have expectations, then all is fine.

(1) Having some kind of variable, especially in interactive mode, allows to

manipulate previous results and reuse them later, without having to
resort
to repeated sub-queries or to retype non trivial values.

Client side psql :-variables are untyped and unescaped, thus not very
convenient for this purpose.

You can currently (ab)use user defined GUCs for this.

How? It seems that I have missed the syntax to assign the result of a
query to a user-defined guc, and to reuse it simply in a query.

postgres=# select set_config('myvar.text', (select
current_timestamp::text), false);
+-------------------------------+
| set_config |
+-------------------------------+
| 2016-12-31 18:56:42.894246+01 |
+-------------------------------+
(1 row)

Time: 0,448 ms
postgres=# select current_setting('myvar.text');
+-------------------------------+
| current_setting |
+-------------------------------+
| 2016-12-31 18:56:42.894246+01 |
+-------------------------------+
(1 row)

--

Show quoted text

Fabien.

2016-12-31 18:46 GMT+01:00 Fabien COELHO <coelho@cri.ensmp.fr>:

DROP VARIABLE super_secret;

CREATE VARIABLE super_secret ...;

But you don't do it in functions - these variables are persistent - you
don't create it or drop inside functions. The content is secure, so you
don't need to hide this variable against other.

ISTM that you are still missing my point.

I understood that you want a static analysis tool to re-assure you about
how your session variables are manipulated. I do not see how such a tool
can give any assurance without checking that the variable meta-data are not
changed by some malicious code inserted in a function.

if you afraid this, then just use grep to verify functions that have this
code. It is same like tables - you can generate it dynamicly, but is risks
- similar to use dynamic SQL. Sure, there is a exceptions - but there are
rules for PL - don't use dynamic SQL if it is not deadly necessary, use SQL
security, not own, ...

I'm not sure that I understand these sentences.

so I don't prefer any design that increase a area where plpgsql_check
should not work.

My assumption is that plpgsql_check can be improved. For instance, I
assume that if "secure session variables" are added, then it will be
enhanced to do some checking about these and take them into account. If
"simple session variables" are added, I assume that it would also be
updated accordingly.

in simple session variables there are not any safe point - any
authoritative point. Sure I can do some - I can introduce some hints, etc -
but it is workaround - nothing more - it like C development without header
files.

Show quoted text

I wrote my notes there.

Great! I restructured a little bit and tried to improve the English. I
also added questions when some statement that I think are too optimistic,
or are unclear to me.

we have just different perspectives

I'm trying to have sentences that are both clear and true. If I think that
a sentence is imprecise because it is missing a key hypothesis, then I try
to improve it, whether it is mine or someone else.

--
Fabien.