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

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

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

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.

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

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

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.

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.

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

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