Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

po 18. 11. 2019 v 19:47 odesílatel Pavel Stehule 
napsal:

>
>
> ne 3. 11. 2019 v 17:27 odesílatel Pavel Stehule 
> napsal:
>
>>
>>
>> čt 10. 10. 2019 v 11:41 odesílatel Pavel Stehule 
>> napsal:
>>
>>> Hi
>>>
>>> minor change - replace heap_tuple_fetch_attr by detoast_external_attr.
>>>
>>>
>> similar update - heap_open, heap_close was replaced by table_open,
>> table_close
>>
>
> fresh rebase
>

only rebase

Regards

Pavel


> Regards
>
> Pavel
>
>
>> Regards
>>
>> Pavel
>>
>


schema-variables-20191214.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

ne 3. 11. 2019 v 17:27 odesílatel Pavel Stehule 
napsal:

>
>
> čt 10. 10. 2019 v 11:41 odesílatel Pavel Stehule 
> napsal:
>
>> Hi
>>
>> minor change - replace heap_tuple_fetch_attr by detoast_external_attr.
>>
>>
> similar update - heap_open, heap_close was replaced by table_open,
> table_close
>

fresh rebase

Regards

Pavel


> Regards
>
> Pavel
>


schema-variables-20191118.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

čt 10. 10. 2019 v 11:41 odesílatel Pavel Stehule 
napsal:

> Hi
>
> minor change - replace heap_tuple_fetch_attr by detoast_external_attr.
>
>
similar update - heap_open, heap_close was replaced by table_open,
table_close

Regards

Pavel


schema_variables-20191103.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

minor change - replace heap_tuple_fetch_attr by detoast_external_attr.

Regards

Pavel

pá 4. 10. 2019 v 6:12 odesílatel Pavel Stehule 
napsal:

> Hi
>
> so 10. 8. 2019 v 9:10 odesílatel Pavel Stehule 
> napsal:
>
>> Hi
>>
>> just rebase
>>
>
> fresh rebase
>
> Regards
>
> Pavel
>
>
>> Regards
>>
>> Pavel
>>
>


schema_variables-20191010.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

so 10. 8. 2019 v 9:10 odesílatel Pavel Stehule 
napsal:

> Hi
>
> just rebase
>

fresh rebase

Regards

Pavel


> Regards
>
> Pavel
>


schema-variables-20191004.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

just rebase

Regards

Pavel


schema-variables-rebase-20190810.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

ne 30. 6. 2019 v 5:10 odesílatel Pavel Stehule 
napsal:

>
>
> pá 24. 5. 2019 v 19:12 odesílatel Pavel Stehule 
> napsal:
>
>> Hi
>>
>> čt 9. 5. 2019 v 6:34 odesílatel Pavel Stehule 
>> napsal:
>>
>>> Hi
>>>
>>> rebased patch
>>>
>>
>> rebase after pgindent
>>
>
> fresh rebase
>

just rebase again

Regards

Pavel



> Regards
>
> Pavel
>
>
>> Regards
>>
>> Pavel
>>
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>


schema-variables-20190716.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

pá 24. 5. 2019 v 19:12 odesílatel Pavel Stehule 
napsal:

> Hi
>
> čt 9. 5. 2019 v 6:34 odesílatel Pavel Stehule 
> napsal:
>
>> Hi
>>
>> rebased patch
>>
>
> rebase after pgindent
>

fresh rebase

Regards

Pavel


> Regards
>
> Pavel
>
>>
>> Regards
>>
>> Pavel
>>
>>
>>


schema-variables-20190630.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

čt 9. 5. 2019 v 6:34 odesílatel Pavel Stehule 
napsal:

> Hi
>
> rebased patch
>

rebase after pgindent

Regards

Pavel

>
> Regards
>
> Pavel
>
>
>


schema-variables-20190524.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

rebased patch

Regards

Pavel


schema-variables-20190509.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

fresh rebase

Regards

Pavel


schema-variables-20180419.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

út 26. 3. 2019 v 6:40 odesílatel Pavel Stehule 
napsal:

> Hi
>
> ne 24. 3. 2019 v 6:57 odesílatel Pavel Stehule 
> napsal:
>
>> Hi
>>
>> rebase against current master
>>
>
>
> fixed issue IF NOT EXISTS & related regress tests
>

another rebase

Regards

Pavel


> Regards
>
> Pavel
>
>
>> Regards
>>
>> Pavel
>>
>


schema-variables-20190402.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

po 25. 3. 2019 v 20:40 odesílatel Erik Rijkers  napsal:

> On 2019-03-24 10:32, Pavel Stehule wrote:
> > ne 24. 3. 2019 v 10:25 odesílatel Erik Rijkers  napsal:
> >
> >> On 2019-03-24 06:57, Pavel Stehule wrote:
> >> > Hi
> >> >
> >> > rebase against current master
> >>
> >> I ran into this:
> >>
> >> (schema 'varschema2' does not exist):
> >>
> >> drop variable varschema2.testv cascade;
> >> ERROR:  schema "varschema2" does not exist
> >> create variable if not exists testv as text;
> >> server closed the connection unexpectedly
> >> This probably means the server terminated abnormally
> >> before or while processing the request.
> >> connection to server was lost
> >>
> >>
> >> (both statements are needed to force the crash)
> >>
> >
> > I cannot to reproduce it.
> >  [backtrace and stuff]
>
> Sorry, I don't have the wherewithal to get more info but I have repeated
> this now on 4 different machines (debian jessie/stretch; centos).
>
> I did notice that sometimes those two offending lines
> "
>drop variable varschema2.testv cascade;
>create variable if not exists testv as text;
> "
> have to be repeated a few times (never more than 4 or 5 times) before
> the crash occurs (signal 11: Segmentation fault).
>

Should be fixed now.

Thank you for report

Pavel


>
> Erik Rijkers
>
>
>

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

ne 24. 3. 2019 v 6:57 odesílatel Pavel Stehule 
napsal:

> Hi
>
> rebase against current master
>


fixed issue IF NOT EXISTS & related regress tests

Regards

Pavel


> Regards
>
> Pavel
>


schema-variables-20190326.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Erik Rijkers]

On 2019-03-24 10:32, Pavel Stehule wrote:

ne 24. 3. 2019 v 10:25 odesílatel Erik Rijkers  napsal:


On 2019-03-24 06:57, Pavel Stehule wrote:
> Hi
>
> rebase against current master

I ran into this:

(schema 'varschema2' does not exist):

drop variable varschema2.testv cascade;
ERROR:  schema "varschema2" does not exist
create variable if not exists testv as text;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost


(both statements are needed to force the crash)



I cannot to reproduce it.
 [backtrace and stuff]


Sorry, I don't have the wherewithal to get more info but I have repeated 
this now on 4 different machines (debian jessie/stretch; centos).


I did notice that sometimes those two offending lines
"
  drop variable varschema2.testv cascade;
  create variable if not exists testv as text;
"
have to be repeated a few times (never more than 4 or 5 times) before 
the crash occurs (signal 11: Segmentation fault).



Erik Rijkers

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

ne 24. 3. 2019 v 10:25 odesílatel Erik Rijkers  napsal:

> On 2019-03-24 06:57, Pavel Stehule wrote:
> > Hi
> >
> > rebase against current master
> >
>
> I ran into this:
>
> (schema 'varschema2' does not exist):
>
> drop variable varschema2.testv cascade;
> ERROR:  schema "varschema2" does not exist
> create variable if not exists testv as text;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> connection to server was lost
>
>
> (both statements are needed to force the crash)
>

I cannot to reproduce it.

please, try compilation with "make distclean"; configure ..

or if the problem persists, please send test case, or backtrace

Regards

Pavel

>
>
> thanks,
>
> Erik Rijkers
>
>
>

Re: [HACKERS] proposal: schema variables

[Erik Rijkers]

On 2019-03-24 06:57, Pavel Stehule wrote:

Hi

rebase against current master



I ran into this:

(schema 'varschema2' does not exist):

drop variable varschema2.testv cascade;
ERROR:  schema "varschema2" does not exist
create variable if not exists testv as text;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost


(both statements are needed to force the crash)


thanks,

Erik Rijkers

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

rebase against current master

Regards

Pavel


schema-variables-20190324.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[David Steele]

On 3/7/19 10:10 AM, Fabien COELHO wrote:


Anyway, the patch is non trivial and very large, so targetting v12 now 
is indeed out of reach.


Agreed.  I have set the target version to PG13.

Regards,
--
-David
da...@pgmasters.net

Re: Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi


>> My strong opinion based on the underlying use case is that it that such
>> session variables should be transactional by default, and Pavel strong
>> opinion is that they should not, to be closer to Oracle comparable
>> feature.
>
>
It is closer to any known database Oracle, DB2, Firebird, MSSQL, MySQL,

Regards

Pavel

Re: Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

čt 7. 3. 2019 v 9:10 odesílatel Fabien COELHO  napsal:

>
> Hello David,
>
> > This patch hasn't receive any review in a while and I'm not sure if
> that's
> > because nobody is interested or the reviewers think it does not need any
> more
> > review.
> >
> > It seems to me that this patch as implemented does not quite satisfy any
> one.
> >
> > I think we need to hear something from the reviewers soon or I'll push
> this
> > patch to PG13 as Andres recommends [1].
>
> I have discussed the feature extensively with Pavel on the initial thread.
>
> My strong opinion based on the underlying use case is that it that such
> session variables should be transactional by default, and Pavel strong
> opinion is that they should not, to be closer to Oracle comparable
> feature.
>
> According to the documentation, the current implementation does provide a
> transactional feature. However, it is not the default behavior, so I'm in
> disagreement on a key feature, although I do really appreciate that Pavel
> implemented the transactional behavior.
>
> Otherwise, ISTM that they could be named "SESSION VARIABLE" because the
> variable only exists in memory, in a session, and we could thing of adding
> other kind of variables later on.
>
> I do intend to review it in depth when it is transactional by default.
>

I am sorry. I cannot to support this request. Variables are not
transactional. My opinion is strong in this part.

I would not to repeat this discussion from start. I am sorry.

Regards

Pavel


> Anyway, the patch is non trivial and very large, so targetting v12 now is
> indeed out of reach.
>
> --
> Fabien.
>
>

Re: Re: [HACKERS] proposal: schema variables

[Fabien COELHO]

Hello David,

This patch hasn't receive any review in a while and I'm not sure if that's 
because nobody is interested or the reviewers think it does not need any more 
review.


It seems to me that this patch as implemented does not quite satisfy any one.

I think we need to hear something from the reviewers soon or I'll push this 
patch to PG13 as Andres recommends [1].


I have discussed the feature extensively with Pavel on the initial thread.

My strong opinion based on the underlying use case is that it that such 
session variables should be transactional by default, and Pavel strong 
opinion is that they should not, to be closer to Oracle comparable 
feature.


According to the documentation, the current implementation does provide a 
transactional feature. However, it is not the default behavior, so I'm in 
disagreement on a key feature, although I do really appreciate that Pavel

implemented the transactional behavior.

Otherwise, ISTM that they could be named "SESSION VARIABLE" because the 
variable only exists in memory, in a session, and we could thing of adding 
other kind of variables later on.


I do intend to review it in depth when it is transactional by default.

Anyway, the patch is non trivial and very large, so targetting v12 now is 
indeed out of reach.


--
Fabien.

Re: Re: [HACKERS] proposal: schema variables

[David Steele]

On 3/3/19 10:27 PM, Pavel Stehule wrote:


rebase and fix compilation due changes related pg_dump


This patch hasn't receive any review in a while and I'm not sure if 
that's because nobody is interested or the reviewers think it does not 
need any more review.


It seems to me that this patch as implemented does not quite satisfy any 
one.


I think we need to hear something from the reviewers soon or I'll push 
this patch to PG13 as Andres recommends [1].


--
-David
da...@pgmasters.net

[1] 
https://www.postgresql.org/message-id/20190216054526.zss2cufdxfeudr4i%40alap3.anarazel.de

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

just rebase

regards

Pavel


schema-variables-20190131.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

just rebase

Regards

Pavel


schema-variables-20190130.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

fresh rebased patch, no other changes

Pavel


schema-variables-20190122-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Erik Rijkers]

On 2018-12-31 14:23, Pavel Stehule wrote:
st 21. 11. 2018 v 8:24 odesílatel Pavel Stehule 




[schema-variables-20181231-01.patch.gz]


Hi Pavel,

I gave this a quick try-out with the script I had from previous 
versions,

and found these two errors:


drop schema if exists schema1 cascade;
create schema if not exists schema1;
drop variable if exists schema1.myvar1;  --> error 49
create variable schema1.myvar1 as text ;
select schema1.myvar1;
let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
alter variable schema1.myvar1 rename to myvar2;
select schema1.myvar2;
create variable schema1.myvar1 as text ;
let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
alter variable schema1.myvar1 rename to myvar2; --> error 4287
select schema1.myvar2;



The above, ran with   psql -qXa  gives the following output:

drop schema if exists schema1 cascade;
create schema if not exists schema1;
drop variable if exists schema1.myvar1;  --> error 49
ERROR:  unrecognized object type: 49
create variable schema1.myvar1 as text ;
select schema1.myvar1;
 myvar1


(1 row)

let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
  myvar1
---
 variable value ""
(1 row)

alter variable schema1.myvar1 rename to myvar2;
select schema1.myvar2;
  myvar2
---
 variable value ""
(1 row)

create variable schema1.myvar1 as text ;
let schema1.myvar1 = 'variable value ""';
select schema1.myvar1;
  myvar1
---
 variable value ""
(1 row)

alter variable schema1.myvar1 rename to myvar2; --> error 4287
ERROR:  unsupported object class 4287
select schema1.myvar2;
  myvar2
---
 variable value ""
(1 row)



thanks,


Erik Rijkers

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

st 21. 11. 2018 v 8:24 odesílatel Pavel Stehule 
napsal:

> Hi
>
> just rebase
>
>
rebase

Regards

Pavel


>
> Regards
>
> Pavel
>


schema-variables-20181231-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

so 1. 12. 2018 v 0:16 odesílatel Dmitry Dolgov <9erthali...@gmail.com>
napsal:

> > On Wed, Nov 21, 2018 at 8:25 AM Pavel Stehule 
> wrote:
> >
> > just rebase
>
> Thanks for working on this patch.
>
> I'm a bit confused, but cfbot again says that there are some conflicts.
> Probably they are the minor one, from src/bin/psql/help.c
>

rebased again

Regards

Pavel


> For now I'm moving it to the next CF.
>


schema-variables-20181201-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Dmitry Dolgov]

> On Wed, Nov 21, 2018 at 8:25 AM Pavel Stehule  wrote:
>
> just rebase

Thanks for working on this patch.

I'm a bit confused, but cfbot again says that there are some conflicts.
Probably they are the minor one, from src/bin/psql/help.c

For now I'm moving it to the next CF.

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

just rebase


Regards

Pavel


schema-variables-20181121-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

út 23. 10. 2018 v 14:50 odesílatel Erik Rijkers  napsal:

> > [schema-variables-20181007-01.patch.gz]
>
> Hi,
>
> I tried to test your schema-variables patch but got stuck here instead
> (after applying succesfully on top of e6f5d1acc):
>
> make[2]: *** No rule to make target
> '../../../src/include/catalog/pg_variable.h', needed by 'bki-stamp'.
> Stop.
> make[1]: *** [submake-catalog-headers] Error 2
> make[1]: *** Waiting for unfinished jobs
> make: *** [submake-generated-headers] Error 2
> Makefile:141: recipe for target 'submake-catalog-headers' failed
> src/Makefile.global:370: recipe for target 'submake-generated-headers'
> failed
>
>
Unfortunately previous patch was completely broken. I am sorry

Please, check this patch.

Regards

Pavel


> thanks,
>
> Erik Rijkers
>


schema-variables-20181023-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Erik Rijkers]

[schema-variables-20181007-01.patch.gz]


Hi,

I tried to test your schema-variables patch but got stuck here instead 
(after applying succesfully on top of e6f5d1acc):


make[2]: *** No rule to make target 
'../../../src/include/catalog/pg_variable.h', needed by 'bki-stamp'.  
Stop.

make[1]: *** [submake-catalog-headers] Error 2
make[1]: *** Waiting for unfinished jobs
make: *** [submake-generated-headers] Error 2
Makefile:141: recipe for target 'submake-catalog-headers' failed
src/Makefile.global:370: recipe for target 'submake-generated-headers' 
failed



thanks,

Erik Rijkers

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

ne 30. 9. 2018 v 0:19 odesílatel Pavel Stehule 
napsal:

>
>
> so 29. 9. 2018 v 10:34 odesílatel Pavel Stehule 
> napsal:
>
>>
>>
>> so 22. 9. 2018 v 8:00 odesílatel Pavel Stehule 
>> napsal:
>>
>>> Hi
>>>
>>> rebased against yesterday changes in tab-complete.c
>>>
>>
>> rebased against last changes in master
>>
>
> + using content of schema variable for estimation
> + subtransaction support
>
> I hope so now, there are almost complete functionality. Please, check it.
>

new update

minor white space issue
one more regress test and 2 pg_dump tests

Regards

Pavel


>
> Regards
>
> Pavel
>
>
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>> Regards
>>>
>>> Pavel
>>>
>>


schema-variables-20181007-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

st 3. 10. 2018 v 1:01 odesílatel Thomas Munro 
napsal:

> On Sun, Sep 30, 2018 at 11:20 AM Pavel Stehule 
> wrote:
> > I hope so now, there are almost complete functionality. Please, check it.
>
> Hi Pavel,
>
> FYI there is a regression test failure on Windows:
>
> plpgsql ... FAILED
>
> *** 4071,4077 
> end;
> $$ language plpgsql;
> select stacked_diagnostics_test();
> - NOTICE: sqlstate: 22012, message: division by zero, context:
> [PL/pgSQL function zero_divide() line 4 at RETURN <- SQL statement
> "SELECT zero_divide()" <- PL/pgSQL function stacked_diagnostics_test()
> line 6 at PERFORM]
> + NOTICE: sqlstate: 42702, message: column reference "v" is ambiguous,
> context: [PL/pgSQL function zero_divide() line 4 at RETURN <- SQL
> statement "SELECT zero_divide()" <- PL/pgSQL function
> stacked_diagnostics_test() line 6 at PERFORM]
>
> https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.15234


please, check attached patch

Thank you for report

Pavel


>
> --
> Thomas Munro
> http://www.enterprisedb.com
>


schema-variables-20181004-02.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Thomas Munro]

On Sun, Sep 30, 2018 at 11:20 AM Pavel Stehule  wrote:
> I hope so now, there are almost complete functionality. Please, check it.

Hi Pavel,

FYI there is a regression test failure on Windows:

plpgsql ... FAILED

*** 4071,4077 
end;
$$ language plpgsql;
select stacked_diagnostics_test();
- NOTICE: sqlstate: 22012, message: division by zero, context:
[PL/pgSQL function zero_divide() line 4 at RETURN <- SQL statement
"SELECT zero_divide()" <- PL/pgSQL function stacked_diagnostics_test()
line 6 at PERFORM]
+ NOTICE: sqlstate: 42702, message: column reference "v" is ambiguous,
context: [PL/pgSQL function zero_divide() line 4 at RETURN <- SQL
statement "SELECT zero_divide()" <- PL/pgSQL function
stacked_diagnostics_test() line 6 at PERFORM]

https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.15234

-- 
Thomas Munro
http://www.enterprisedb.com

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

so 29. 9. 2018 v 10:34 odesílatel Pavel Stehule 
napsal:

>
>
> so 22. 9. 2018 v 8:00 odesílatel Pavel Stehule 
> napsal:
>
>> Hi
>>
>> rebased against yesterday changes in tab-complete.c
>>
>
> rebased against last changes in master
>

+ using content of schema variable for estimation
+ subtransaction support

I hope so now, there are almost complete functionality. Please, check it.

Regards

Pavel


>
> Regards
>
> Pavel
>
>
>
>> Regards
>>
>> Pavel
>>
>


schema-variables-20180929-02.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

so 22. 9. 2018 v 8:00 odesílatel Pavel Stehule 
napsal:

> Hi
>
> rebased against yesterday changes in tab-complete.c
>

rebased against last changes in master

Regards

Pavel



> Regards
>
> Pavel
>


schema-variables-20180929-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

rebased against yesterday changes in tab-complete.c

Regards

Pavel


schema-variables-20180922-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

pá 21. 9. 2018 v 21:46 odesílatel Arthur Zakirov 
napsal:

> On Wed, Sep 19, 2018 at 04:36:40PM +0200, Pavel Stehule wrote:
> > ON COMMIT DROP is used only for temp variables (transaction or not
> > transaction). The purpose is same like for tables. Sometimes you can to
> > have object with shorter life than is session.
> >
> > ON TRANSACTION END RESET has sense mainly for not transaction variables.
> I
> > see two use cases.
> >
> > 1. protect some sensitive data - on transaction end guaranteed reset and
> > cleaning on end transaction. So you can be sure, so variable is not
> > initialized (has default value), or you are inside transaction.
> >
> > 2. automatic initialization - ON TRANSACTION END RESET ensure so variable
> > is in init state for any transaction.
> >
> > Both cases has sense for transaction or not transaction variables.
> >
> > I am thinking so transaction life time for content has sense. Is cheaper
> to
> > reset variable than drop it (what ON COMMIT DROP does)
> >
> > What do you think?
>
> Thanks, I understood the cases.
>
> But I think there is more sense to use these options only with
> transactional
> variables. It is more consistent and simple for me.
>

I agree so it can be hard to imagine - and if I return back to start
discussion about schema variables - it can be hard because it joins some
concepts - variables has persistent transactional metadata, but the content
is not transactional.

I don't think so the variability is a issue in this case. There is a lot of
examples, so lot of combinations are possible - global temp tables and
package variables (Oracle), local temp tables and local variables
(Postgres), session variables and memory tables (MSSQL). Any combination of
feature has cases where can be very practical and useful.

ON TRANSACTION END RESET can be useful, because we have not a session event
triggers (and in this moment I am not sure if it is necessary and practical
- their usage can be very fragile). But some work can do ON xxx clauses,
that should not to have negative impact on performance or fragility.

ON TRANSACTION END RESET ensure cleaned and initialized to default value
for any transaction. Other possibility is ON COMMAND END RESET (but I would
not to implement it now), ...


> As a summary, it is 1 voice vs 1 voice :) So it is better to leave the
> syntax as is without changes for now.
>

:) now is enough time to think about syntax. Some features can be removed
and returned back later, where this concept will be more absorbed.

Regards

Pavel


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

Re: [HACKERS] proposal: schema variables

[Arthur Zakirov]

On Wed, Sep 19, 2018 at 04:36:40PM +0200, Pavel Stehule wrote:
> ON COMMIT DROP is used only for temp variables (transaction or not
> transaction). The purpose is same like for tables. Sometimes you can to
> have object with shorter life than is session.
> 
> ON TRANSACTION END RESET has sense mainly for not transaction variables. I
> see two use cases.
> 
> 1. protect some sensitive data - on transaction end guaranteed reset and
> cleaning on end transaction. So you can be sure, so variable is not
> initialized (has default value), or you are inside transaction.
> 
> 2. automatic initialization - ON TRANSACTION END RESET ensure so variable
> is in init state for any transaction.
> 
> Both cases has sense for transaction or not transaction variables.
> 
> I am thinking so transaction life time for content has sense. Is cheaper to
> reset variable than drop it (what ON COMMIT DROP does)
> 
> What do you think?

Thanks, I understood the cases.

But I think there is more sense to use these options only with transactional
variables. It is more consistent and simple for me.

As a summary, it is 1 voice vs 1 voice :) So it is better to leave the
syntax as is without changes for now.

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

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

st 19. 9. 2018 v 13:23 odesílatel Arthur Zakirov 
napsal:

> Hello,
>
> On Wed, Sep 19, 2018 at 10:30:31AM +0200, Pavel Stehule wrote:
> > Hi
> >
> > new update:
> >
> > I fixed pg_restore, and I cleaned a code related to transaction
> processing
> >
> > There should be a full functionality now.
>
> I reviewed a little bit the patch. I have a few comments.
>
> > pg_views Columns
>
> I think there is a typo here. It should be "pg_variable".
>

fixed


> > - ONCOMMIT_DROP   /* ON COMMIT DROP */
> > + ONCOMMIT_DROP,  /* ON COMMIT DROP */
> > } OnCommitAction;
>
> There is the extra comma here after ONCOMMIT_DROP.
>

fixed

Thank you for comments

attached updated patch




> 1 - https://www.postgresql.org/docs/current/static/sql-createtable.html
>
> --
> Arthur Zakirov
> Postgres Professional: http://www.postgrespro.com
> Russian Postgres Company
>


schema-variables-20180920-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

st 19. 9. 2018 v 14:53 odesílatel Arthur Zakirov 
napsal:

> On Wed, Sep 19, 2018 at 02:08:04PM +0200, Pavel Stehule wrote:
> > Unfortunately we cannot to use standard
> > "SET" command, because it is used in Postgres for different purpose.
> > READ|WRITE are totally clear, and for user it is another signal so
> > variables are different than tables (so it is not one row table).
> >
> > I prefer current state, but if common opinion will be different, I have
> not
> > problem to change it.
>
> I see. I grepped the thread before writhing this but somehow missed the
> discussion.
>
> > The content of variables is not transactional (by default). It is not
> > destroyed by rollback. So I have to calculate with rollback too. So the
> > most correct syntax should be "ON COMMIT ON ROLLBACK RESET" what is
> little
> > bit messy and I used "ON TRANSACTION END". It should be signal, so this
> > event is effective on rollback event and it is valid for not transaction
> > variable. This logic is not valid to transactional variables, where ON
> > COMMIT RESET has sense. But this behave is not default and then I prefer
> > more generic syntax.
> > ...
> > So I see two different cases - work with catalog (what is transactional)
> > and work with variable value, what is (like other variables in
> programming
> > languages) not transactional. "ON TRANSACTION END RESET" means - does
> reset
> > on any transaction end.
> >
> > I hope so I explained it cleanly - if not, please, ask.
>
> I understood what you mean, thank you. I thought that
> { ON COMMIT DROP | ON TRANSACTION END RESET } parameters are used only
> for transactional variables in the first place. But is there any sense
> in using this parameters with non-transactional variables? That is when
> we create non-transactional variable we don't want that the variable
> will rollback or reset its value after the end of a transaction.
>

ON COMMIT DROP is used only for temp variables (transaction or not
transaction). The purpose is same like for tables. Sometimes you can to
have object with shorter life than is session.

ON TRANSACTION END RESET has sense mainly for not transaction variables. I
see two use cases.

1. protect some sensitive data - on transaction end guaranteed reset and
cleaning on end transaction. So you can be sure, so variable is not
initialized (has default value), or you are inside transaction.

2. automatic initialization - ON TRANSACTION END RESET ensure so variable
is in init state for any transaction.

Both cases has sense for transaction or not transaction variables.

I am thinking so transaction life time for content has sense. Is cheaper to
reset variable than drop it (what ON COMMIT DROP does)

What do you think?

Pavel



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

Re: [HACKERS] proposal: schema variables

[Arthur Zakirov]

On Wed, Sep 19, 2018 at 02:08:04PM +0200, Pavel Stehule wrote:
> Unfortunately we cannot to use standard
> "SET" command, because it is used in Postgres for different purpose.
> READ|WRITE are totally clear, and for user it is another signal so
> variables are different than tables (so it is not one row table).
> 
> I prefer current state, but if common opinion will be different, I have not
> problem to change it.

I see. I grepped the thread before writhing this but somehow missed the
discussion.

> The content of variables is not transactional (by default). It is not
> destroyed by rollback. So I have to calculate with rollback too. So the
> most correct syntax should be "ON COMMIT ON ROLLBACK RESET" what is little
> bit messy and I used "ON TRANSACTION END". It should be signal, so this
> event is effective on rollback event and it is valid for not transaction
> variable. This logic is not valid to transactional variables, where ON
> COMMIT RESET has sense. But this behave is not default and then I prefer
> more generic syntax.
> ...
> So I see two different cases - work with catalog (what is transactional)
> and work with variable value, what is (like other variables in programming
> languages) not transactional. "ON TRANSACTION END RESET" means - does reset
> on any transaction end.
> 
> I hope so I explained it cleanly - if not, please, ask.

I understood what you mean, thank you. I thought that
{ ON COMMIT DROP | ON TRANSACTION END RESET } parameters are used only
for transactional variables in the first place. But is there any sense
in using this parameters with non-transactional variables? That is when
we create non-transactional variable we don't want that the variable
will rollback or reset its value after the end of a transaction.

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

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi
st 19. 9. 2018 v 13:23 odesílatel Arthur Zakirov 
napsal:

> Hello,
>
> On Wed, Sep 19, 2018 at 10:30:31AM +0200, Pavel Stehule wrote:
> > Hi
> >
> > new update:
> >
> > I fixed pg_restore, and I cleaned a code related to transaction
> processing
> >
> > There should be a full functionality now.
>
> I reviewed a little bit the patch. I have a few comments.
>
> > pg_views Columns
>
> I think there is a typo here. It should be "pg_variable".
>

I'll fix it.

>
> > GRANT { READ | WRITE | ALL [ PRIVILEGES ] }
>
> Shouldn't we use here GRANT { SELECT | LET | ... } syntax for the
> constistency. Same for REVOKE. I'm not experienced syntax developer
> though. But we use SELECT and LET commands when working with variables.
> So we should GRANT and REVOKE priveleges for this commands.
>

I understand to your proposal, - and I have not strong opinion. Originally
I proposed {SELECT|UPDATE), but some people prefer {READ|WRITE}. Now I
prefer Peter's proposal (what is implemented now) - READ|WRITE, because it
is very illustrative - and the mentioned difference is good because the
variables are not tables (by default), are not persistent, so different
rights are good for me. I see "GRANT LET" like very low clear, because
nobody knows what LET command does. Unfortunately we cannot to use standard
"SET" command, because it is used in Postgres for different purpose.
READ|WRITE are totally clear, and for user it is another signal so
variables are different than tables (so it is not one row table).

I prefer current state, but if common opinion will be different, I have not
problem to change it.


> > [ { ON COMMIT DROP | ON TRANSACTION END RESET } ]
>
> I think we may join them and have the syntax { ON COMMIT DROP | RESET }
> to get more simpler syntax. If we create a variable with ON COMMIT
> DROP, PostgreSQL will drop it regardless of whether transaction was
> committed or rollbacked:
>

I though about it too. I'll try to explain my idea. Originally I was
surprised so postgres uses "ON COMMIT" syntax, but in documentation is used
term "at transaction end". But it has some sense. ON COMMIT DROP is allowed
only for temporary tables and ON COMMIT DELETE ROWS is allowed for tables.
With these clauses the PostgreSQL is more aggressive in cleaning. It
doesn't need to calculate with rollback, because the rollback does cleaning
by self. So syntax "ON COMMIT" is fully correct it is related only for
commit event. It has not sense on rollback event (and doesn't change a
behave on rollback event).

The content of variables is not transactional (by default). It is not
destroyed by rollback. So I have to calculate with rollback too. So the
most correct syntax should be "ON COMMIT ON ROLLBACK RESET" what is little
bit messy and I used "ON TRANSACTION END". It should be signal, so this
event is effective on rollback event and it is valid for not transaction
variable. This logic is not valid to transactional variables, where ON
COMMIT RESET has sense. But this behave is not default and then I prefer
more generic syntax.

Again I have not a problem to change it, but I am thinking so current
design is logically correct.


> =# ...
> =# begin;
> =# create variable int1 int on commit drop;
> =# rollback;
> =# -- There is no variable int1
>
>
PostgreSQL catalog is transactional (where the metadata is stored), so when
I am working with metadata, then I use ON COMMIT syntax, because the behave
of  ON ROLLBACK cannot be changed.

So I see two different cases - work with catalog (what is transactional)
and work with variable value, what is (like other variables in programming
languages) not transactional. "ON TRANSACTION END RESET" means - does reset
on any transaction end.

I hope so I explained it cleanly - if not, please, ask.

CREATE TABLE syntax has similar options [1]. ON COMMIT controls
> the behaviour of temporary tables at the end a transaction block,
> whether it was committed or rollbacked. But I'm not sure is this a good
> example of precedence.
>
> > - ONCOMMIT_DROP   /* ON COMMIT DROP */
> > + ONCOMMIT_DROP,  /* ON COMMIT DROP */
> > } OnCommitAction;
>
> There is the extra comma here after ONCOMMIT_DROP.
>

I'll fix it.

Regards

Pavel

>
> 1 - https://www.postgresql.org/docs/current/static/sql-createtable.html
>
> --
> Arthur Zakirov
> Postgres Professional: http://www.postgrespro.com
> Russian Postgres Company
>

Re: [HACKERS] proposal: schema variables

[Arthur Zakirov]

Hello,

On Wed, Sep 19, 2018 at 10:30:31AM +0200, Pavel Stehule wrote:
> Hi
> 
> new update:
> 
> I fixed pg_restore, and I cleaned a code related to transaction processing
> 
> There should be a full functionality now.

I reviewed a little bit the patch. I have a few comments.

> pg_views Columns

I think there is a typo here. It should be "pg_variable".

> GRANT { READ | WRITE | ALL [ PRIVILEGES ] }

Shouldn't we use here GRANT { SELECT | LET | ... } syntax for the
constistency. Same for REVOKE. I'm not experienced syntax developer
though. But we use SELECT and LET commands when working with variables.
So we should GRANT and REVOKE priveleges for this commands.

> [ { ON COMMIT DROP | ON TRANSACTION END RESET } ]

I think we may join them and have the syntax { ON COMMIT DROP | RESET }
to get more simpler syntax. If we create a variable with ON COMMIT
DROP, PostgreSQL will drop it regardless of whether transaction was
committed or rollbacked:

=# ...
=# begin;
=# create variable int1 int on commit drop;
=# rollback;
=# -- There is no variable int1

CREATE TABLE syntax has similar options [1]. ON COMMIT controls
the behaviour of temporary tables at the end a transaction block,
whether it was committed or rollbacked. But I'm not sure is this a good
example of precedence.

> - ONCOMMIT_DROP   /* ON COMMIT DROP */
> + ONCOMMIT_DROP,  /* ON COMMIT DROP */
> } OnCommitAction;

There is the extra comma here after ONCOMMIT_DROP.

1 - https://www.postgresql.org/docs/current/static/sql-createtable.html

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

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

new update:

I fixed pg_restore, and I cleaned a code related to transaction processing

There should be a full functionality now.

Regards

Pavel


schema-variables-20180919-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

so 15. 9. 2018 v 18:06 odesílatel Pavel Stehule 
napsal:

>
>
>
>
>> The code is more cleaner now, there are more tests, and documentation is
>> mostly complete. I am sorry - my English is not good.
>> New features:
>>
>> o ON COMMIT DROP and ON TRANSACTION END RESET -- remove temp variable on
>> commit, reset variable on transaction end (commit, rollback)
>> o LET var = DEFAULT -- reset specified variable
>>
>>
> fix some forgotten warnings and dependency issue
> few more tests
>
>
new update:

o support NOT NULL check
o implementation limited transaction variables - these variables doesn't
respects subtransactions(this is much more complex), drop variable drops
content although the drop can be reverted (maybe this limit will be
removed).

CREATE TRANSACTION VARIABLE fx AS int;

LET fx = 10;
BEGIN
  LET fx = 20;
ROLLBACK;

SELECT fx;

Regards

Pavel


> Regards
>
> Pavel
>
>
>> Regards
>>
>> Pavel
>>
>>
>>> Regards,
>>> Dean
>>>
>>


schema-variables-20180917-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

> The code is more cleaner now, there are more tests, and documentation is
> mostly complete. I am sorry - my English is not good.
> New features:
>
> o ON COMMIT DROP and ON TRANSACTION END RESET -- remove temp variable on
> commit, reset variable on transaction end (commit, rollback)
> o LET var = DEFAULT -- reset specified variable
>
>
fix some forgotten warnings and dependency issue
few more tests

Regards

Pavel


> Regards
>
> Pavel
>
>
>> Regards,
>> Dean
>>
>


schema-variables-20180915-01.patch.gz
Description: application/gzip

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

2018-09-07 14:34 GMT+02:00 Fabien COELHO :

>
> Hello Pavel,
>
> here is updated patch - I wrote some transactional support
>>
>> I am not sure how these new features are understandable and if these
>> features does it better or not.
>>
>
> There are possibility to reset to default value when
>>
>> a) any transaction is finished - the scope of value is limited by
>> transaction
>>
>> CREATE VARIABLE foo int ON TRANSACTION END RESET;
>>
>
> With this option I understand that it is a "within a transactionnal"
> variable, i.e. when the transaction ends, whether commit or rollback, the
> variable is reset to a default variable. It is not really a "session"
> variable anymore, each transaction has its own value.
>

yes, the correct name should be "schema variable with transaction scope". I
think it can be useful like short life global variable. These variables can
works like transaction caches.


>  -- begin session
>  -- foo has default value, eg NULL
>  BEGIN;
> LET foo = 1;
>  COMMIT/ROLLBACK;
>  -- foo has default value again, NULL
>
> b) when transaction finished by rollback
>>
>> CREATE VARIABLE foo int ON ROLLBACK RESET
>>
>
> That is a little bit safer and you are back to a SESSION-scope variable,
> which is reset to the default value if the (any) transaction fails?
>
>   -- begin session
>   -- foo has default value, eg NULL
>   BEGIN;
> LET foo = 1;
>   COMMIT;
>   -- foo has value 1
>   BEGIN;
> -- foo has value 1...
>   ROLLBACK;
>   -- foo has value NULL
>
> c) A more logical (from a transactional point of view - but not necessary
> simple to implement, I do not know) feature/variant would be to reset the
> value to the one it had at the beginning of the transaction, which is not
> necessarily the default.
>
>   -- begin session
>   -- foo has default value, eg NULL
>   BEGIN;
> LET foo = 1;
>   COMMIT;
>   -- foo has value 1
>   BEGIN;
> LET foo = 2; (*)
> -- foo has value 2
>   ROLLBACK;
>   -- foo has value 1 back, change (*) has been reverted
>
> Now, when I am thinking about it, the @b is simple, but not too practical -
>> when some fails, then we lost a value (any transaction inside session can
>> fails).
>>
>
> Indeed.
>
> The @a has sense - the behave is global value (what is not possible
>> in Postgres now), but this value is destroyed by any unhandled exceptions,
>> and it cleaned on transaction end. The @b is just for information and for
>> discussion, but I'll remove it - because it is obscure.
>>
>
> Indeed.
>
> The open question is syntax. PostgreSQL has already ON COMMIT xxx . It is
>> little bit unclean, because it has semantic "on transaction end", but if I
>> didn't implement @b, then ON COMMIT syntax can be used.
>>
>
> I was more arguing on the third (c) option, i.e. on rollback the value is
> reverted to its value at the beginning of the rollbacked transaction.
>

> At the minimum, ISTM that option (b) is enough to implement the audit
> pattern, but it would mean that any session which has a rollback, for any
> reason (deadlock, serialization...), would have to be reinitialized, which
> would be a drawback.
>
> The to options could be non-transactional session variables "ON ROLLBACK
> DO NOT RESET/DO NOTHING", and somehow transactional session variables "ON
> ROLLBACK RESET TO DEFAULT" (b) or "ON ROLLBACK RESET TO INITIAL" (c).
>

@b is hardly understandable for not trained people, because any rollback in
session does reset. But people expecting @c, or some near @c.

I understand so you talked about @c. Now I think so it is possible to
implement, but it is not trivial. The transactional behave have to
calculate not only with transactions, but with SAVEPOINTS and ROLLBACK TO
savepoints. On second hand, the implementation will be relatively compact.

I'll hold it in my memory, but there are harder issues (support for
parallelism).

Regards

Pavel



> --
> Fabien.
>
>

Re: [HACKERS] proposal: schema variables

[Fabien COELHO]

Hello Pavel,


here is updated patch - I wrote some transactional support

I am not sure how these new features are understandable and if these
features does it better or not.



There are possibility to reset to default value when

a) any transaction is finished - the scope of value is limited by
transaction

CREATE VARIABLE foo int ON TRANSACTION END RESET;


With this option I understand that it is a "within a transactionnal" 
variable, i.e. when the transaction ends, whether commit or rollback, the 
variable is reset to a default variable. It is not really a "session" 
variable anymore, each transaction has its own value.


 -- begin session
 -- foo has default value, eg NULL
 BEGIN;
LET foo = 1;
 COMMIT/ROLLBACK;
 -- foo has default value again, NULL


b) when transaction finished by rollback

CREATE VARIABLE foo int ON ROLLBACK RESET


That is a little bit safer and you are back to a SESSION-scope variable, 
which is reset to the default value if the (any) transaction fails?


  -- begin session
  -- foo has default value, eg NULL
  BEGIN;
LET foo = 1;
  COMMIT;
  -- foo has value 1
  BEGIN;
-- foo has value 1...
  ROLLBACK;
  -- foo has value NULL

c) A more logical (from a transactional point of view - but not necessary 
simple to implement, I do not know) feature/variant would be to reset the 
value to the one it had at the beginning of the transaction, which is not 
necessarily the default.


  -- begin session
  -- foo has default value, eg NULL
  BEGIN;
LET foo = 1;
  COMMIT;
  -- foo has value 1
  BEGIN;
LET foo = 2; (*)
-- foo has value 2
  ROLLBACK;
  -- foo has value 1 back, change (*) has been reverted


Now, when I am thinking about it, the @b is simple, but not too practical -
when some fails, then we lost a value (any transaction inside session can
fails).


Indeed.


The @a has sense - the behave is global value (what is not possible
in Postgres now), but this value is destroyed by any unhandled exceptions,
and it cleaned on transaction end. The @b is just for information and for
discussion, but I'll remove it - because it is obscure.


Indeed.


The open question is syntax. PostgreSQL has already ON COMMIT xxx . It is
little bit unclean, because it has semantic "on transaction end", but if I
didn't implement @b, then ON COMMIT syntax can be used.


I was more arguing on the third (c) option, i.e. on rollback the value is 
reverted to its value at the beginning of the rollbacked transaction.


At the minimum, ISTM that option (b) is enough to implement the audit 
pattern, but it would mean that any session which has a rollback, for any 
reason (deadlock, serialization...), would have to be reinitialized, which 
would be a drawback.


The to options could be non-transactional session variables "ON ROLLBACK 
DO NOT RESET/DO NOTHING", and somehow transactional session variables "ON 
ROLLBACK RESET TO DEFAULT" (b) or "ON ROLLBACK RESET TO INITIAL" (c).


--
Fabien.

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

2018-09-04 9:21 GMT+02:00 Dean Rasheed :

> AFAICS this patch does nothing to consider parallel safety -- that is,
> as things stand, a variable is allowed in a query that may be
> parallelised, but its value is not copied to workers, leading to
> incorrect results. For example:
>
> create table foo(a int);
> insert into foo select * from generate_series(1,100);
> create variable zero int;
> let zero = 0;
>
> explain (costs off) select count(*) from foo where a%10 = zero;
>
>   QUERY PLAN
> ---
>  Finalize Aggregate
>->  Gather
>  Workers Planned: 2
>  ->  Partial Aggregate
>->  Parallel Seq Scan on foo
>  Filter: ((a % 10) = zero)
> (6 rows)
>
> select count(*) from foo where a%10 = zero;
>
>  count
> ---
>  38037-- Different random result each time, should be 100,000
> (1 row)
>
> Thoughts?
>

The query use copy of values of variables now - but unfortunately, these
values are not passed to workers.  Should be fixed.

Thank you for test case.

Pavel


> Regards,
> Dean
>

Re: [HACKERS] proposal: schema variables

[Dean Rasheed]

AFAICS this patch does nothing to consider parallel safety -- that is,
as things stand, a variable is allowed in a query that may be
parallelised, but its value is not copied to workers, leading to
incorrect results. For example:

create table foo(a int);
insert into foo select * from generate_series(1,100);
create variable zero int;
let zero = 0;

explain (costs off) select count(*) from foo where a%10 = zero;

  QUERY PLAN
---
 Finalize Aggregate
   ->  Gather
 Workers Planned: 2
 ->  Partial Aggregate
   ->  Parallel Seq Scan on foo
 Filter: ((a % 10) = zero)
(6 rows)

select count(*) from foo where a%10 = zero;

 count
---
 38037-- Different random result each time, should be 100,000
(1 row)

Thoughts?

Regards,
Dean

Re: [HACKERS] proposal: schema variables

[Fabien COELHO]

Hello Pavel L.

I do not understand your point, as usual. I raise a factual issue about 
security, and you do not answer how this can be solved with your proposal, 
but appeal to argument of authority and declare your "strong opinion".


I do not see any intrinsic opposition between having session objects and 
transactions. Nothing prevents a session object to be transactional beyond 
your willingness that it should not be.


Now, I do expect all PostgreSQL features to be security-wise, whatever 
their scope.


I do not think that security should be traded for "cheap & fast", esp as 
the sole use case for a feature is a security pattern that cannot be 
implemented securely with it. This appears to me as a huge contradiction, 
hence my opposition against this feature as proposed.


I can't to agree with your position.

Consider this example. I want to record some inappropriate user actions 
to audit table and rollback transaction. But aborting transaction will 
also abort record to audit table. So, do not use tables, becouse they 
have security implications.


Indeed, you cannot record a transaction failure from a transaction.


This is very similar to your approach.


I understand that your point is that some use case could require a non 
transactional session variable. I'm not sure of how the use case would go 
on though, because once the "attacker" disconnects, the session variable 
disappears, so it does not record that there was a problem.


Anyway, I'm not against having session variables per se. I'm argumenting 
that there is a good case to have them transactional by default, and 
possibly an option to have them non transactional if this is really needed 
by some use case to provide.


The only use case put forward by Pavel S. is the security audit one 
where a session variable stores that audit checks have been performed, 
which AFAICS cannot be implemented securely with the proposed non 
transactional session variables.


--
Fabien.

Re: [HACKERS] proposal: schema variables

[Pavel Luzanov]

On 23.08.2018 12:46, Fabien COELHO wrote:
I do not understand your point, as usual. I raise a factual issue 
about security, and you do not answer how this can be solved with your 
proposal, but appeal to argument of authority and declare your "strong 
opinion".


I do not see any intrinsic opposition between having session objects 
and transactions. Nothing prevents a session object to be 
transactional beyond your willingness that it should not be.


Now, I do expect all PostgreSQL features to be security-wise, whatever 
their scope.


I do not think that security should be traded for "cheap & fast", esp 
as the sole use case for a feature is a security pattern that cannot 
be implemented securely with it. This appears to me as a huge 
contradiction, hence my opposition against this feature as proposed.


I can't to agree with your position.

Consider this example.
I want to record some inappropriate user actions to audit table and 
rollback transaction.

But aborting transaction will also abort record to audit table.
So, do not use tables, becouse they have security implications.

This is very similar to your approach.

Schema variables is a very needed and important feature, but for others 
purposes.


-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: [HACKERS] proposal: schema variables

[Fabien COELHO]

Security vs "good enough in some cases" looks bad to me.


We don't find a agreement, because you are concentrated on transation, 
me on session. And we have different expectations.


I do not understand your point, as usual. I raise a factual issue about 
security, and you do not answer how this can be solved with your proposal, 
but appeal to argument of authority and declare your "strong opinion".


I do not see any intrinsic opposition between having session objects and 
transactions. Nothing prevents a session object to be transactional beyond 
your willingness that it should not be.


Now, I do expect all PostgreSQL features to be security-wise, whatever 
their scope.


I do not think that security should be traded for "cheap & fast", esp as 
the sole use case for a feature is a security pattern that cannot be 
implemented securely with it. This appears to me as a huge contradiction, 
hence my opposition against this feature as proposed.


The good news is that I'm a nobody: if a committer is happy with your 
patch, it will get committed, you do not need my approval.


--
Fabien.

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

2018-08-23 10:17 GMT+02:00 Fabien COELHO :

>
> Hello Pavel,
>
> 2. holding some session based informations, that can be used in security
>> definer functions.
>>
>
> Hmmm, I see our disagreement. My point is that this feature is *NOT* fit
> for security-related uses because if the transaction fails the variable
> would keep the value it had if the transaction had not failed...
>
> 3. Because it is not transactional, then it allows write operation on read
>>
>
> It is not transactional safe, but it is secure in sense a possibility to
>> set a access rights.
>>
>
> This is a misleading play on words. It is secure wrt to access right, but
> unsecure wrt security purposes which is the only point for having such a
> feature in the first place.
>
> I understand, so some patterns are not possible, but when you need hold
>> some keys per session, then this simply solution can be good enough.
>>
>
> Security vs "good enough in some cases" looks bad to me.
>

We don't find a agreement, because you are concentrated on transation, me
on session. And we have different expectations.


> I think it is possible for some more complex patterns,
>>
>
> I'm not sure of any pattern which would be correct wrt security if it
> depends on the success of a transaction.
>
> but then developer should be smarter, and should to enocode state result
>> to content of variable.
>>
>
> I do not see how the developer can be smarter if they need a transactional
> for security but they do not have it.
>
> There is strong benefit - read write access to variables is very cheap and
>> fast.
>>
>
> I'd say that PostgreSQL is about "ACID & security" first, not "cheap &
> fast" first.
>
> I invite any patch to doc (or everywhere) with explanation and about
>> possible risks.
>>
>
> Hmmm... You are the one proposing the feature...
>
> Here is something, thanks for adjusting it to the syntax you are proposing
> and inserting it where appropriate. Possibly in the corresponding CREATE
> doc?
>
> """
> 
> 
> Beware that session variables are not transactional.
> This is a concern in a security context where the variable must be set to
> some trusted value depending on the success of the transaction:
> if the transaction fails, the variable keeps its trusted value unduly.
> 
>
> 
> For instance, the following pattern does NOT work:
>
> 
> CREATE USER auditer;
> SET ROLE auditer;
> CREATE SESSION VARIABLE is_audited BOOLEAN DEFAULT FALSE ...;
> -- ensure that only "auditer" can write "is_audited":
> REVOKE ... ON SESSION VARIABLE is_audited FROM ...;
>
> -- create an audit function
> CREATE FUNCTION audit_session(...) SECURITY DEFINER AS $$
>   -- record the session and checks in some place...
>   -- then tell it was done:
>   LET is_audited = TRUE;
> $$;
>
> -- the intention is that other security definier functions can check that
> -- the session is audited by checking on "is_audited", eg:
> CREATE FUNCTION only_for_audited(...) SECURITY DEFINER AS $$
>   IF NOT is_audited THEN RAISE "security error";
>   -- do protected stuff here.
> $$;
> 
>
> The above pattern can be attacked with the following approach:
> 
> BEGIN;
> SELECT audit_session(...);
> -- success, "is_audited" is set...
> ROLLBACK;
> -- the audit login has been reverted, but "is_audited" retains its value.
>
> -- any subsequent operation believes wrongly that the session is audited,
> -- but its logging has really been removed by the ROLLBACK.
>
> -- ok but should not:
> SELECT only_for_audited(...);
> 
> 
> 
> """
>
>
It is good example of not supported pattern. It is not designed for this.
I'll merge this doc.

Note: I am not sure, if I have all relations to described issue, but if I
understand well, then solution can be reset on transaction end, maybe reset
on rollback. This is solvable, I'll look how it is complex.


>
> For the record, I'm "-1" on this feature as proposed, for what it's worth,
> because of the misleading security implications. This feature would just
> help people have their security wrong.
>

I respect your opinion - and I hope so integration of your proposed doc is
good warning for users that would to use not transactional variable like
transactional source.

Regards

Pavel


>
> --
> Fabien.
>
>

Re: [HACKERS] proposal: schema variables

[Fabien COELHO]

Hello Pavel,


2. holding some session based informations, that can be used in security
definer functions.


Hmmm, I see our disagreement. My point is that this feature is *NOT* fit 
for security-related uses because if the transaction fails the variable 
would keep the value it had if the transaction had not failed...



3. Because it is not transactional, then it allows write operation on read



It is not transactional safe, but it is secure in sense a possibility to
set a access rights.


This is a misleading play on words. It is secure wrt to access right, but 
unsecure wrt security purposes which is the only point for having such a 
feature in the first place.


I understand, so some patterns are not possible, but when you need hold 
some keys per session, then this simply solution can be good enough.


Security vs "good enough in some cases" looks bad to me.


I think it is possible for some more complex patterns,


I'm not sure of any pattern which would be correct wrt security if it 
depends on the success of a transaction.


but then developer should be smarter, and should to enocode state result 
to content of variable.


I do not see how the developer can be smarter if they need a transactional 
for security but they do not have it.


There is strong benefit - read write access to variables is very cheap 
and fast.


I'd say that PostgreSQL is about "ACID & security" first, not "cheap & 
fast" first.



I invite any patch to doc (or everywhere) with explanation and about
possible risks.


Hmmm... You are the one proposing the feature...

Here is something, thanks for adjusting it to the syntax you are proposing 
and inserting it where appropriate. Possibly in the corresponding CREATE 
doc?


"""


Beware that session variables are not transactional.
This is a concern in a security context where the variable must be set to
some trusted value depending on the success of the transaction:
if the transaction fails, the variable keeps its trusted value unduly.



For instance, the following pattern does NOT work:


CREATE USER auditer;
SET ROLE auditer;
CREATE SESSION VARIABLE is_audited BOOLEAN DEFAULT FALSE ...;
-- ensure that only "auditer" can write "is_audited":
REVOKE ... ON SESSION VARIABLE is_audited FROM ...;

-- create an audit function
CREATE FUNCTION audit_session(...) SECURITY DEFINER AS $$
  -- record the session and checks in some place...
  -- then tell it was done:
  LET is_audited = TRUE;
$$;

-- the intention is that other security definier functions can check that
-- the session is audited by checking on "is_audited", eg:
CREATE FUNCTION only_for_audited(...) SECURITY DEFINER AS $$
  IF NOT is_audited THEN RAISE "security error";
  -- do protected stuff here.
$$;


The above pattern can be attacked with the following approach:

BEGIN;
SELECT audit_session(...);
-- success, "is_audited" is set...
ROLLBACK;
-- the audit login has been reverted, but "is_audited" retains its value.

-- any subsequent operation believes wrongly that the session is audited,
-- but its logging has really been removed by the ROLLBACK.

-- ok but should not:
SELECT only_for_audited(...);



"""


For the record, I'm "-1" on this feature as proposed, for what it's worth, 
because of the misleading security implications. This feature would just 
help people have their security wrong.



--
Fabien.

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

2018-08-22 9:00 GMT+02:00 Fabien COELHO :

>
> Hello Pavel,
>
> AFAICR, I had an objection on such new objects when you first proposed
>>> something similar in October 2016.
>>>
>>> Namely, if session variables are not transactional, they cannot be used
>>> to
>>> implement security related auditing features which were advertised as the
>>> motivating use case: an the audit check may fail on a commit because of a
>>> differed constraint, but the variable would keep its "okay" value unduly,
>>> which would create a latent security issue, the audit check having failed
>>> but the variable saying the opposite.
>>>
>>> So my point was that they should be transactional by default, although I
>>> would be ok with an option for having a voluntary non transactional
>>> version.
>>>
>>> Is this issue addressed somehow with this ?
>>>
>>
>>
>> 1. I respect your opinion, but I dont agree with it. Oracle, db2 has
>> similar or very similar feature non transactional, and I didnt find any
>> requests to change it.
>>
>
> The argument of authority that "X does it like that" is not a valid answer
> to my technical objection about security implications of this feature.
>
> 2. the prototype implementation was based on relclass items, and some
>> transactional behave was possible. Peter E. had objections to this design
>> and proposed own catalog table. I did it. Now, the transactional behave is
>> harder to implement, although it is not impossible. This patch is not
>> small
>> now, so I didnt implement it.
>>
>
> "It is harder to implement" does not look like a valid answer either.
>
> I have a strong opinion so default behave have to be non transactional.
>>
>
> The fact that you have a "strong opinion" does not really answer my
> objection. Moreover, I said that I would be ok with a non transactional
> option, provided that a default transactional is available.
>
> Transactional variables significantly increases complexity of this patch,
>> now is simple, because we can reset variable on drop variable command.
>> Maybe I miss some simply implementation, but I spent on it more than few
>> days. Still, any cooperation are welcome.
>>
>
> "It is simpler to implement this way" is not an answer either, especially
> as you said that it could have been on point 2.
>
>
> As I do not see any clear answer to my objection about security
> implications, I understand that it is not addressed by this patch.
>
>
> At the bare minimum, if this feature ever made it as is, I think that a
> clear caveat must be included in the documentation about not using it for
> any security-related purpose.
>
> Also, I'm not really sure how useful such a non-transactional object can
> be for other purposes: the user should take into account that the
> transaction may fail and the value of the session variable be inconsistent
> as a result. Sometimes it may not matter, but if it matters there is no
> easy way around the fact.
>

I agree, so it should be well documented to be clear, what is possible,
what not, and to be correct expectations.

This feature has two (three) purposes

1. global variables for PL language
2. holding some session based informations, that can be used in security
definer functions.
3. Because it is not transactional, then it allows write operation on read
only hot stand by instances.

It is not transactional safe, but it is secure in sense a possibility to
set a access rights. I understand, so some patterns are not possible, but
when you need hold some keys per session, then this simply solution can be
good enough. The variables are clean after session end.

I think it is possible for some more complex patterns, but then developer
should be smarter, and should to enocode state result to content of
variable. There is strong benefit - read write access to variables is very
cheap and fast.

I invite any patch to doc (or everywhere) with explanation and about
possible risks.

Regards

Pavel





> --
> Fabien.
>

Re: [HACKERS] proposal: schema variables

[Fabien COELHO]

Hello Pavel,


AFAICR, I had an objection on such new objects when you first proposed
something similar in October 2016.

Namely, if session variables are not transactional, they cannot be used to
implement security related auditing features which were advertised as the
motivating use case: an the audit check may fail on a commit because of a
differed constraint, but the variable would keep its "okay" value unduly,
which would create a latent security issue, the audit check having failed
but the variable saying the opposite.

So my point was that they should be transactional by default, although I
would be ok with an option for having a voluntary non transactional
version.

Is this issue addressed somehow with this ?



1. I respect your opinion, but I dont agree with it. Oracle, db2 has
similar or very similar feature non transactional, and I didnt find any
requests to change it.


The argument of authority that "X does it like that" is not a valid answer 
to my technical objection about security implications of this feature.



2. the prototype implementation was based on relclass items, and some
transactional behave was possible. Peter E. had objections to this design
and proposed own catalog table. I did it. Now, the transactional behave is
harder to implement, although it is not impossible. This patch is not small
now, so I didnt implement it.


"It is harder to implement" does not look like a valid answer either.


I have a strong opinion so default behave have to be non transactional.


The fact that you have a "strong opinion" does not really answer my 
objection. Moreover, I said that I would be ok with a non transactional 
option, provided that a default transactional is available.



Transactional variables significantly increases complexity of this patch,
now is simple, because we can reset variable on drop variable command.
Maybe I miss some simply implementation, but I spent on it more than few
days. Still, any cooperation are welcome.


"It is simpler to implement this way" is not an answer either, especially 
as you said that it could have been on point 2.



As I do not see any clear answer to my objection about security 
implications, I understand that it is not addressed by this patch.



At the bare minimum, if this feature ever made it as is, I think that a 
clear caveat must be included in the documentation about not using it for 
any security-related purpose.


Also, I'm not really sure how useful such a non-transactional object can 
be for other purposes: the user should take into account that the 
transaction may fail and the value of the session variable be inconsistent 
as a result. Sometimes it may not matter, but if it matters there is no 
easy way around the fact.


--
Fabien.

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi Fabien

Dne út 21. 8. 2018 19:56 uživatel Fabien COELHO 
napsal:

>
> Hello Pavel,
>
> AFAICR, I had an objection on such new objects when you first proposed
> something similar in October 2016.
>
> Namely, if session variables are not transactional, they cannot be used to
> implement security related auditing features which were advertised as the
> motivating use case: an the audit check may fail on a commit because of a
> differed constraint, but the variable would keep its "okay" value unduly,
> which would create a latent security issue, the audit check having failed
> but the variable saying the opposite.
>
> So my point was that they should be transactional by default, although I
> would be ok with an option for having a voluntary non transactional
> version.
>
> Is this issue addressed somehow with this ?



1. I respect your opinion, but I dont agree with it. Oracle, db2 has
similar or very similar feature non transactional, and I didnt find any
requests to change it.

2. the prototype implementation was based on relclass items, and some
transactional behave was possible. Peter E. had objections to this design
and proposed own catalog table. I did it. Now, the transactional behave is
harder to implement, although it is not impossible. This patch is not small
now, so I didnt implement it. I have a strong opinion so default behave
have to be non transactional.

Transactional variables significantly increases complexity of this patch,
now is simple, because we can reset variable on drop variable command.
Maybe I miss some simply implementation, but I spent on it more than few
days. Still, any cooperation are welcome.

Regards

Pavel




> --
> Fabien.
>

Re: [HACKERS] proposal: schema variables

[Fabien COELHO]

Hello Pavel,

AFAICR, I had an objection on such new objects when you first proposed 
something similar in October 2016.


Namely, if session variables are not transactional, they cannot be used to 
implement security related auditing features which were advertised as the 
motivating use case: an the audit check may fail on a commit because of a 
differed constraint, but the variable would keep its "okay" value unduly, 
which would create a latent security issue, the audit check having failed 
but the variable saying the opposite.


So my point was that they should be transactional by default, although I 
would be ok with an option for having a voluntary non transactional 
version.


Is this issue addressed somehow with this version?

--
Fabien.

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

2018-06-27 19:15 GMT+02:00 Gilles Darold :

> Le 27/06/2018 à 13:22, Pavel Stehule a écrit :
>
> Hi
>
> 2018-06-27 12:21 GMT+02:00 Gilles Darold :
>
>> Hi,
>>
>> I'm reviewing the patch as it was flagged in the current commit fest.
>> Here are my feedback:
>>
>>  - The patch need to be rebased due to changes in file
>> src/sgml/catalogs.sgml
>>
>>  - Some compilation warning must be fixed:
>>
>> analyze.c: In function ‘transformLetStmt’:
>> analyze.c:1568:17: warning: variable ‘rte’ set but not used
>> [-Wunused-but-set-variable]
>>   RangeTblEntry *rte;
>>  ^~~
>> tab-complete.c:1268:21: warning: initialization from incompatible pointer
>> type [-Wincompatible-pointer-types]
>>   {"VARIABLE", NULL, _for_list_of_variables},
>>
>> In the last warning a NULL is missing, should be written: {"VARIABLE",
>> NULL, NULL, _for_list_of_variables},
>>
>>
>>  - How about Peter's suggestion?:
>> "In DB2, the privileges for variables are named READ and WRITE. That
>> would make more sense to me than reusing the privilege names for tables.
>>
> The patch use SELECT and UPDATE which make sense too for SELECT but
>> less for UPDATE.
>>
>>  - The implementation of "ALTER VARIABLE varname SET SCHEMA schema_name;"
>> is missing
>>
>>  - ALTER VARIABLE var1 OWNER TO gilles; ok but not documented and missing
>> in regression test
>>
>>  - ALTER VARIABLE var1 RENAME TO var2; ok but not documented and missing
>> in regression test
>>
>> More generally I think that some comments must be rewritten, especially
>> those talking about a PoC. In documentation there is HTML comments that can
>> be removed.
>>
>> Comment at end of file src/backend/commands/schemavar.c generate some
>> "indent with spaces" errors with git apply but perhaps the comment can be
>> entirely removed or undocumented details moved to the right place.
>>
>> Otherwise all regression tests passed without issue and especially your
>> new regression tests about schema variables.
>>
>> I have a patch rebased, let me known if you want me to post the new diff.
>>
>
> I plan significant refactoring of this patch for next commitfest. There
> was anotherstrong Peter's and Robert comments
>
> 1. The schema variables should to have own system table
> 2. The composite schema variables should to use explicitly defined
> composite type
> 3. The memory management is not nice - transactional drop table with
> content is implemented ugly.
>
> I hope, so I can start on these issues next month.
>
> Thank you for review - I'll recheck ALTER commands.
>
>>
>> Otherwise all regression tests passed without issue and especially your
>> new regression tests about schema variables.
>>
>> I have a patch rebased, let me known if you want me to post the new diff.
>>
>
> I plan significant refactoring of this patch for next commitfest. There
> was anotherstrong Peter's and Robert c
> Regards
>
>
> Ok Pavel, I've changed the status to "Waiting for authors" so that no one
> will make an other review until you send a new patch.
>
sure

Thank you

Pavel


>
> --
> Gilles Darold
> Consultant PostgreSQLhttp://dalibo.com - http://dalibo.org
>
>

Re: [HACKERS] proposal: schema variables

[Gilles Darold]

Le 27/06/2018 à 13:22, Pavel Stehule a écrit :

Hi

2018-06-27 12:21 GMT+02:00 Gilles Darold >:


Hi,

I'm reviewing the patch as it was flagged in the current commit
fest. Here are my feedback:

 - The patch need to be rebased due to changes in file
src/sgml/catalogs.sgml

 - Some compilation warning must be fixed:

analyze.c: In function ‘transformLetStmt’:
analyze.c:1568:17: warning: variable ‘rte’ set but not used
[-Wunused-but-set-variable]
  RangeTblEntry *rte;
 ^~~
tab-complete.c:1268:21: warning: initialization from
incompatible pointer type [-Wincompatible-pointer-types]
  {"VARIABLE", NULL, _for_list_of_variables},

In the last warning a NULL is missing, should be written:
{"VARIABLE", NULL, NULL, _for_list_of_variables},


 - How about Peter's suggestion?:
    "In DB2, the privileges for variables are named READ and
WRITE. That would make more sense to me than reusing the privilege
names for tables.

    The patch use SELECT and UPDATE which make sense too for
SELECT but less for UPDATE.

 - The implementation of "ALTER VARIABLE varname SET SCHEMA
schema_name;" is missing

 - ALTER VARIABLE var1 OWNER TO gilles; ok but not documented and
missing in regression test

 - ALTER VARIABLE var1 RENAME TO var2; ok but not documented and
missing in regression test

More generally I think that some comments must be rewritten,
especially those talking about a PoC. In documentation there is
HTML comments that can be removed.

Comment at end of file src/backend/commands/schemavar.c generate
some "indent with spaces" errors with git apply but perhaps the
comment can be entirely removed or undocumented details moved to
the right place.

Otherwise all regression tests passed without issue and especially
your new regression tests about schema variables.

I have a patch rebased, let me known if you want me to post the
new diff.


I plan significant refactoring of this patch for next commitfest. 
There was anotherstrong Peter's and Robert comments


1. The schema variables should to have own system table
2. The composite schema variables should to use explicitly defined 
composite type
3. The memory management is not nice - transactional drop table with 
content is implemented ugly.


I hope, so I can start on these issues next month.

Thank you for review - I'll recheck ALTER commands.


Otherwise all regression tests passed without issue and especially
your new regression tests about schema variables.

I have a patch rebased, let me known if you want me to post the
new diff.


I plan significant refactoring of this patch for next commitfest. 
There was anotherstrong Peter's and Robert c

Regards



Ok Pavel, I've changed the status to "Waiting for authors" so that no 
one will make an other review until you send a new patch.



--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

2018-06-27 12:21 GMT+02:00 Gilles Darold :

> Hi,
>
> I'm reviewing the patch as it was flagged in the current commit fest. Here
> are my feedback:
>
>  - The patch need to be rebased due to changes in file
> src/sgml/catalogs.sgml
>
>  - Some compilation warning must be fixed:
>
> analyze.c: In function ‘transformLetStmt’:
> analyze.c:1568:17: warning: variable ‘rte’ set but not used
> [-Wunused-but-set-variable]
>   RangeTblEntry *rte;
>  ^~~
> tab-complete.c:1268:21: warning: initialization from incompatible pointer
> type [-Wincompatible-pointer-types]
>   {"VARIABLE", NULL, _for_list_of_variables},
>
> In the last warning a NULL is missing, should be written: {"VARIABLE",
> NULL, NULL, _for_list_of_variables},
>
>
>  - How about Peter's suggestion?:
> "In DB2, the privileges for variables are named READ and WRITE. That
> would make more sense to me than reusing the privilege names for tables.
>
The patch use SELECT and UPDATE which make sense too for SELECT but
> less for UPDATE.
>
>  - The implementation of "ALTER VARIABLE varname SET SCHEMA schema_name;"
> is missing
>
>  - ALTER VARIABLE var1 OWNER TO gilles; ok but not documented and missing
> in regression test
>
>  - ALTER VARIABLE var1 RENAME TO var2; ok but not documented and missing
> in regression test
>
> More generally I think that some comments must be rewritten, especially
> those talking about a PoC. In documentation there is HTML comments that can
> be removed.
>
> Comment at end of file src/backend/commands/schemavar.c generate some
> "indent with spaces" errors with git apply but perhaps the comment can be
> entirely removed or undocumented details moved to the right place.
>
> Otherwise all regression tests passed without issue and especially your
> new regression tests about schema variables.
>
> I have a patch rebased, let me known if you want me to post the new diff.
>

I plan significant refactoring of this patch for next commitfest. There was
anotherstrong Peter's and Robert comments

1. The schema variables should to have own system table
2. The composite schema variables should to use explicitly defined
composite type
3. The memory management is not nice - transactional drop table with
content is implemented ugly.

I hope, so I can start on these issues next month.

Thank you for review - I'll recheck ALTER commands.

Regards

Pavel

>
> --
> Gilles Darold
> Consultant PostgreSQLhttp://dalibo.com - http://dalibo.org
>
>

Re: [HACKERS] proposal: schema variables

[Gilles Darold]

Hi,

I'm reviewing the patch as it was flagged in the current commit fest. 
Here are my feedback:


 - The patch need to be rebased due to changes in file 
src/sgml/catalogs.sgml


 - Some compilation warning must be fixed:

   analyze.c: In function ‘transformLetStmt’:
   analyze.c:1568:17: warning: variable ‘rte’ set but not used
   [-Wunused-but-set-variable]
  RangeTblEntry *rte;
 ^~~
   tab-complete.c:1268:21: warning: initialization from incompatible
   pointer type [-Wincompatible-pointer-types]
  {"VARIABLE", NULL, _for_list_of_variables},

   In the last warning a NULL is missing, should be written:
   {"VARIABLE", NULL, NULL, _for_list_of_variables},


 - How about Peter's suggestion?:
    "In DB2, the privileges for variables are named READ and WRITE. 
That would make more sense to me than reusing the privilege names for 
tables.
    The patch use SELECT and UPDATE which make sense too for SELECT but 
less for UPDATE.


 - The implementation of "ALTER VARIABLE varname SET SCHEMA 
schema_name;" is missing


 - ALTER VARIABLE var1 OWNER TO gilles; ok but not documented and 
missing in regression test


 - ALTER VARIABLE var1 RENAME TO var2; ok but not documented and 
missing in regression test


More generally I think that some comments must be rewritten, especially 
those talking about a PoC. In documentation there is HTML comments that 
can be removed.


Comment at end of file src/backend/commands/schemavar.c generate some 
"indent with spaces" errors with git apply but perhaps the comment can 
be entirely removed or undocumented details moved to the right place.


Otherwise all regression tests passed without issue and especially your 
new regression tests about schema variables.


I have a patch rebased, let me known if you want me to post the new diff.


--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

2018-05-01 3:56 GMT+02:00 Peter Eisentraut :

> On 4/20/18 13:45, Pavel Stehule wrote:
> > I dunno, it seems awfully different to me.  There's only one
> "column",
> > right?  What code is really shared here?  Are constraints and
> triggers
> > even desirable feature for variables?  What would be the use case?
> >
> >
> > The schema variable can hold composite value. The patch allows to use
> > any composite type or adhoc composite values
> >
> > DECLARE x AS compositetype;
> > DECLARE x AS (a int, b int, c int);
>
> I'm not sure that this anonymous composite type thing is such a good
> idea.  Such a variable will then be incompatible with anything else,
> because it's of a different type.
>

Using anonymous composite type variable is just shortcut for situations
when mentioned feature is not a problem. These variables are global, so
there can be only one variable of some specific composite type, and
incompatibility with others is not a issue.

This feature can be interesting for short live temp variables - these
variables can be used for parametrization of anonymous block.

But this feature is not significant, and can be removed from patch.


> In any case, I find that a weak argument for storing this in pg_class.
> You could just as well create these pg_class entries implicitly and link
> them from "pg_variable", same as composite types have a main entry in
> pg_type and additional stuff in pg_class.
>
> > I think stuffing this into pg_class is pretty strange.
> >
> > It will be if variable is just scalar value without any possibilities.
> > But then there is only low benefit
> >
> > The access rights implementation is shared with other from pg_class too.
>
> In DB2, the privileges for variables are named READ and WRITE.  That
> would make more sense to me than reusing the privilege names for tables.
>
>
good idea

Regards

Pavel

> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [HACKERS] proposal: schema variables

[Peter Eisentraut]

On 4/20/18 13:45, Pavel Stehule wrote:
> I dunno, it seems awfully different to me.  There's only one "column",
> right?  What code is really shared here?  Are constraints and triggers
> even desirable feature for variables?  What would be the use case?
> 
> 
> The schema variable can hold composite value. The patch allows to use
> any composite type or adhoc composite values
> 
> DECLARE x AS compositetype;
> DECLARE x AS (a int, b int, c int);

I'm not sure that this anonymous composite type thing is such a good
idea.  Such a variable will then be incompatible with anything else,
because it's of a different type.

In any case, I find that a weak argument for storing this in pg_class.
You could just as well create these pg_class entries implicitly and link
them from "pg_variable", same as composite types have a main entry in
pg_type and additional stuff in pg_class.

> I think stuffing this into pg_class is pretty strange.
> 
> It will be if variable is just scalar value without any possibilities.
> But then there is only low benefit
> 
> The access rights implementation is shared with other from pg_class too.

In DB2, the privileges for variables are named READ and WRITE.  That
would make more sense to me than reusing the privilege names for tables.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

2018-04-20 17:32 GMT+02:00 Robert Haas :

> On Tue, Apr 17, 2018 at 12:28 PM, Pavel Stehule 
> wrote:
> > It true, so there are lot of "unused" attributes for this purpose, but
> there
> > is lot of shared attributes, and lot of shared code. Semantically, I see
> > variables in family of sequences, tables, indexes, views. Now, it shares
> > code, and I hope in next steps more code can be shared - constraints,
> > triggers.
>
> I dunno, it seems awfully different to me.  There's only one "column",
> right?  What code is really shared here?  Are constraints and triggers
> even desirable feature for variables?  What would be the use case?
>

The schema variable can hold composite value. The patch allows to use any
composite type or adhoc composite values

DECLARE x AS compositetype;
DECLARE x AS (a int, b int, c int);

Constraints are clear, no.

Triggers are strange maybe, but why not - it can be used like enhanced
constraints, can be used for some value calculations, ..


> I think stuffing this into pg_class is pretty strange.
>

It will be if variable is just scalar value without any possibilities. But
then there is only low benefit

The access rights implementation is shared with other from pg_class too.

Regards

Pavel


>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [HACKERS] proposal: schema variables

[Robert Haas]

On Tue, Apr 17, 2018 at 12:28 PM, Pavel Stehule  wrote:
> It true, so there are lot of "unused" attributes for this purpose, but there
> is lot of shared attributes, and lot of shared code. Semantically, I see
> variables in family of sequences, tables, indexes, views. Now, it shares
> code, and I hope in next steps more code can be shared - constraints,
> triggers.

I dunno, it seems awfully different to me.  There's only one "column",
right?  What code is really shared here?  Are constraints and triggers
even desirable feature for variables?  What would be the use case?

I think stuffing this into pg_class is pretty strange.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] proposal: schema variables

[Arthur Zakirov]

On Tue, Apr 17, 2018 at 06:28:19PM +0200, Pavel Stehule wrote:
> I though about it, and I am inclined to prefer pg_class instead separate
> tables.
> 
> It true, so there are lot of "unused" attributes for this purpose, but
> there is lot of shared attributes, and lot of shared code. Semantically, I
> see variables in family of sequences, tables, indexes, views. Now, it
> shares code, and I hope in next steps more code can be shared -
> constraints, triggers.
> 
> There are two objective arguments for using pg_class:
> 
> 1. unique name in schema - it reduces risk of collisions
> 2. sharing lot of code
> 
> So in this case I don't see well benefits of separate table.

Understood. I haven't strong opinion here though. But I thought that
pg_class approach may limit extensibility of variables.

BTW:
- there is unitialized variable 'j' in pg_dump.c:15422
- in tab-complete.c:1268 initialization needs extra NULL before
  _for_list_of_variables

Also I think makeRangeVarForTargetOfSchemaVariable() has non friendly
argument names 'field1', 'field2', 'field2'.

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

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

2018-04-17 16:14 GMT+02:00 Arthur Zakirov :

> Hello Pavel,
>
> On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote:
> > I hope so this proposal is good enough and simple.
> >
> > Comments, notes?
>
> As I understood variables are stored in pg_class table. Did you consider
> storing variables in a special catalog table? It can be named as
> pg_variable.
>
> pg_variable table requires more code of course, but it would fix the
> issues:
> - pg_class has a lot attributes which are not related with variables,
>   and I think variables don't need many of them
> - in a future variables might want to have some additional attributes
>   which are not needed for relations, you can easily add them to
>   pg_variable
>
> What do you think?
>

I though about it, and I am inclined to prefer pg_class instead separate
tables.

It true, so there are lot of "unused" attributes for this purpose, but
there is lot of shared attributes, and lot of shared code. Semantically, I
see variables in family of sequences, tables, indexes, views. Now, it
shares code, and I hope in next steps more code can be shared -
constraints, triggers.

There are two objective arguments for using pg_class:

1. unique name in schema - it reduces risk of collisions
2. sharing lot of code

So in this case I don't see well benefits of separate table.

Regards

Pavel



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

Re: [HACKERS] proposal: schema variables

[Arthur Zakirov]

Hello Pavel,

On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote:
> I hope so this proposal is good enough and simple.
> 
> Comments, notes?

As I understood variables are stored in pg_class table. Did you consider
storing variables in a special catalog table? It can be named as
pg_variable.

pg_variable table requires more code of course, but it would fix the issues:
- pg_class has a lot attributes which are not related with variables,
  and I think variables don't need many of them
- in a future variables might want to have some additional attributes
  which are not needed for relations, you can easily add them to
  pg_variable

What do you think?

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

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

2018-03-21 6:24 GMT+01:00 Pavel Stehule :

>
>
> 2018-03-20 18:38 GMT+01:00 Pavel Stehule :
>
>> Hi
>>
>> I am sending new update. The code is less ugly, and the current
>> functionality is +/- final for first stage. It should be good enough for
>> playing and testing this concept.
>>
>> What is supported:
>>
>> 1. scalar, composite and array variables
>> 2. composite can be defined on place or some composite type can be used
>> 3. variable, or any field of variable, can have defined default value
>> 4. variable is database object - the access rights are required
>> 5. the values are stored in binary form with defined typmod
>>
>> An usage is very simple:
>>
>> postgres=# create variable foo as numeric default 0;
>> CREATE VARIABLE
>> postgres=# select foo;
>> ┌─┐
>> │ foo │
>> ╞═╡
>> │   0 │
>> └─┘
>> (1 row)
>>
>> postgres=# let foo = pi();
>> LET
>> postgres=# select foo;
>> ┌──┐
>> │   foo│
>> ╞══╡
>> │ 3.14159265358979 │
>> └──┘
>> (1 row)
>>
>> postgres=# create variable boo as (x numeric default 0, y numeric default
>> 0);
>> CREATE VARIABLE
>> postgres=# let boo.x = 100;
>> LET
>> postgres=# select boo;
>> ┌─┐
>> │   boo   │
>> ╞═╡
>> │ (100,0) │
>> └─┘
>> (1 row)
>>
>> postgres=# select boo.x;
>> ┌─┐
>> │  x  │
>> ╞═╡
>> │ 100 │
>> └─┘
>> (1 row)
>>
>> Please try it.
>>
>
> small fix - support for SQL functions
>
>

the patch is in commit fest list https://commitfest.postgresql.org/18/1608/

Regards

Pavel


>
>> Regards
>>
>> Pavel
>>
>
>

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

2018-03-12 16:38 GMT+01:00 Pavel Luzanov :

>
> On 12.03.2018 09:54, Pavel Stehule wrote:
>
>
> 2018-03-12 7:49 GMT+01:00 Pavel Luzanov :
>
>>
>> Is there any chances that it will work on replicas?
>>
> ...
>
> sure, it should to work. Now, I am try to solve a issues on concept level
> - the LET code is based on DML code base, so probably there is check for rw
> transactions. But it is useless for LET command.
>
>
> Very, very good!
>
> As I understand, the work on this patch now in progress and it not in
> commitfest.
> Please explain what features of schema variables I can review now.
>
> From first post of this thread the syntax of the CREATE VARIABLE command:
> CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
>   [ DEFAULT expression ] [[NOT] NULL]
>   [ ON TRANSACTION END { RESET | DROP } ]
>   [ { VOLATILE | STABLE } ];
>

Now, it is too early for review - it is in development. Some features are
not implemented yet - DEFAULTs, ON TRANSACTION END .., others has not sense
(what I know now VOLATILE, STABLE). Schema variables are passed as
parameters to query, so the behave is like any other params - it is STABLE
only.


>
> But in psql I see only:
> \h create variable
> Command: CREATE VARIABLE
> Description: define a new permissioned typed schema variable
> Syntax:
> CREATE VARIABLE [ IF NOT EXISTS ] name [ AS ] data_type ]
>
> I can include DEFAULT clause in CREATE VARIABLE command, but the value not
> used:
> postgres=# create variable i int default 0;
> CREATE VARIABLE
> postgres=# select i;
>  i
> ---
>
> (1 row)
>
> postgres=# \d+ i
>  schema variable "public.i"
>  Column |  Type   | Storage
> +-+-
>  i  | integer | plain
>
>
defaults are not implemented yet


>
> BTW, I found an error in handling of table aliases:
>
> postgres=# create variable x text;
> CREATE VARIABLE
> postgres=# select * from pg_class AS x where x.relname = 'x';
> ERROR:  type text is not composite
>
> It thinks that x.relname is an attribute of x variable instead of an alias
> for pg_class table.
>
>
It is not well handled collision. This should be detected and prohibited.
In this case, because x is scalar, then x.xx has not sense, and then it
should not be handled like variable. So the current design is not too
practical - it generates more collisions than it is necessary and still,
there are some errors.

Now, there is one important question - storage - Postgres stores all
objects to files - only memory storage is not designed yet. This is part,
where I need a help.

Regards

Pavel


>
> -
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>

Re: [HACKERS] proposal: schema variables

[Pavel Luzanov]

On 12.03.2018 09:54, Pavel Stehule wrote:


2018-03-12 7:49 GMT+01:00 Pavel Luzanov >:



Is there any chances that it will work on replicas?

...

sure, it should to work. Now, I am try to solve a issues on concept 
level - the LET code is based on DML code base, so probably there is 
check for rw transactions. But it is useless for LET command.


Very, very good!

As I understand, the work on this patch now in progress and it not in 
commitfest.

Please explain what features of schema variables I can review now.

From first post of this thread the syntax of the CREATE VARIABLE command:
CREATE [TEMP] VARIABLE [IF NOT EXISTS] name AS type
  [ DEFAULT expression ] [[NOT] NULL]
  [ ON TRANSACTION END { RESET | DROP } ]
  [ { VOLATILE | STABLE } ];

But in psql I see only:
\h create variable
Command: CREATE VARIABLE
Description: define a new permissioned typed schema variable
Syntax:
CREATE VARIABLE [ IF NOT EXISTS ] name [ AS ] data_type ]

I can include DEFAULT clause in CREATE VARIABLE command, but the value 
not used:

postgres=# create variable i int default 0;
CREATE VARIABLE
postgres=# select i;
 i
---

(1 row)

postgres=# \d+ i
 schema variable "public.i"
 Column |  Type   | Storage
+-+-
 i  | integer | plain


BTW, I found an error in handling of table aliases:

postgres=# create variable x text;
CREATE VARIABLE
postgres=# select * from pg_class AS x where x.relname = 'x';
ERROR:  type text is not composite

It thinks that x.relname is an attribute of x variable instead of an 
alias for pg_class table.



-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

2018-03-12 7:49 GMT+01:00 Pavel Luzanov :

> Hi,
>
> I plan to make usability and feature test review in several days.
>
> Is there any chances that it will work on replicas?
> Such possibility is very helpful in generating reports.
> Now, LET command produces an error:
>
> ERROR:  cannot execute LET in a read-only transaction
>
>

> But if we say that variables are non-transactional ?
>

sure, it should to work. Now, I am try to solve a issues on concept level -
the LET code is based on DML code base, so probably there is check for rw
transactions. But it is useless for LET command.

Regards

Pavel


>
> -
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
> On 08.03.2018 21:00, Pavel Stehule wrote:
>
> Hi
>
> 2018-02-07 7:34 GMT+01:00 Pavel Stehule :
>
>> Hi
>>
>> updated patch with your changes in documentation and pg_dump (initial)
>> support
>>
>> Main issue of this patch is storage. We can reuse local buffers used for
>> temp tables. But it does allocation by 8KB and it creates temp files for
>> every object. That is too big overhead. Storing just in session memory is
>> too simple - then there should be lot of new code used, when variable will
>> be dropped.
>>
>> I have ideas how to allow work with mix of scalar and composite types -
>> so it will be next step of this prototype.
>>
>> Regards
>>
>> Pavel
>>
>
> new update - rebased, + some initial support for composite values on right
> side and custom types, arrays are supported too.
>
> omega=# CREATE VARIABLE xx AS (a int, b numeric);
> CREATE VARIABLE
> omega=# LET xx = (10, 20)::xx;
> LET
> omega=# SELECT xx;
> +-+
> |   xx|
> +-+
> | (10,20) |
> +-+
> (1 row)
>
> omega=# SELECT xx.a + xx.b;
> +--+
> | ?column? |
> +--+
> |   30 |
> +--+
> (1 row)
>
> omega=# \d xx
> schema variable "public.xx"
> ++-+
> | Column |  Type   |
> ++-+
> | a  | integer |
> | b  | numeric |
> ++-+
>
>
> Regards
>
> Pavel
>
>
>
>

Re: [HACKERS] proposal: schema variables

[Pavel Luzanov]

Hi,

I plan to make usability and feature test review in several days.

Is there any chances that it will work on replicas?
Such possibility is very helpful in generating reports.
Now, LET command produces an error:

ERROR:  cannot execute LET in a read-only transaction

But if we say that variables are non-transactional ?

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 08.03.2018 21:00, Pavel Stehule wrote:

Hi

2018-02-07 7:34 GMT+01:00 Pavel Stehule >:


Hi

updated patch with your changes in documentation and pg_dump
(initial) support

Main issue of this patch is storage. We can reuse local buffers
used for temp tables. But it does allocation by 8KB and it creates
temp files for every object. That is too big overhead. Storing
just in session memory is too simple - then there should be lot of
new code used, when variable will be dropped.

I have ideas how to allow work with mix of scalar and composite
types - so it will be next step of this prototype.

Regards

Pavel


new update - rebased, + some initial support for composite values on 
right side and custom types, arrays are supported too.


omega=# CREATE VARIABLE xx AS (a int, b numeric);
CREATE VARIABLE
omega=# LET xx = (10, 20)::xx;
LET
omega=# SELECT xx;
+-+
|   xx    |
+-+
| (10,20) |
+-+
(1 row)

omega=# SELECT xx.a + xx.b;
+--+
| ?column? |
+--+
|   30 |
+--+
(1 row)

omega=# \d xx
schema variable "public.xx"
++-+
| Column |  Type   |
++-+
| a  | integer |
| b  | numeric |
++-+


Regards

Pavel

Re: [HACKERS] proposal: schema variables

[Pavel Stehule]

Hi

2018-02-03 1:48 GMT+01:00 David G. Johnston :

> ​I've done a non-compilation documentation review, the diff from the poc
> patch and the diff from master are attached.
>
> Comments are inter-twined in the patch in xml comment format; though I
> reiterate (some of?) them below.
>
> On Fri, Feb 2, 2018 at 3:06 PM, Pavel Stehule 
> wrote:
>
>> Hi
>>
>> I wrote proof concept of schema variables. The patch is not nice, but the
>> functionality is almost complete (for scalars only) and can be good enough
>> for playing with this concept.
>>
>> I recap a goals (the order is random):
>>
>> 1. feature like PL/SQL package variables (with similar content life cycle)
>> 2. available from any PL used by PostgreSQL, data can be shared between
>> different PL
>> 3. possibility to store short life data in fast secured storage
>>
>
> ​The generic use of the word secure here bothers me.  I'm taking it to be
> "protected by grant/revoke"-based privileges; plus session-locality.
>

I have not a problem with any other formulation.


>
> 4. possibility to pass parameters and results to/from anonymous blocks
>> 5. session variables with possibility to process static code check
>>
>
> What does "process static code check" means here?​
>

It mean the possibility to check validity of code without code execution.
You can use plpgsql_check for example.


>
>
>> 6. multiple API available from different environments - SQL commands, SQL
>> functions, internal functions
>>
>
> I made the public aspect of this explicit in the CREATE VARIABLE doc
> (though as noted below it probably belongs in section II)
> ​
>
>> 7. data are stored in binary form
>>
>
> Thoughts during my review:
>
> There is, for me, a cognitive dissonance between "schema variable" and
> "variable value" - I'm partial to the later.  Since we use "setting" for
> GUCs the term variable here hopefully wouldn't cause ambiguity...
>

The "schema" is important in this case. 1) it is a analogy to "package
variable", 2) not necessary, but probably often it will be used together
with PLpgSQL. There are variables too. "Session variables" doesn't well
specify the implementation. The session variables can be GUC, psql client
variables or some custom implementation in Postgres or package variables in
Oracle.


> I've noticed that we don't seem to have or enforce any policy on how to
> communicate "SQL standards compatibility" to the user...
>
> We are missing the ability to alter ownership (or at least its
> undocumented), and if that brings into existing ALTER VARIABLE we should
> probably add ALTER TYPE TO new_type USING (cast) for completeness.
>

good note. I didn't test it. I am not sure, what variants of ALTER should
be supported. Type of variables is interface. Probably we can allow to add
new field, but change type or remove field can break other object. So it
can be prohibited like we doesn't support ALTER on views. ALTERing is
another and pretty complex topic, and I don't think it is necessary to
solve it now. This feature can be valuable without ALTER support, and
nothing block later ALTER VARIABLE implementation.

This design allows lot of interesting features (that can be implemented
step by step)

1. support for default expression
2. support for constraints and maybe triggers
3. reset on transaction end
4. initialization of session start - via default expression or triggers it
can be way how to start code on session start.


>
> Its left for the reader to presume that because these are schema
> "relations" that namespace resolution via search_path works the same as any
> other relation.
>
> I think I've answered my own question regarding DISCARD in that
> "variables" discards values while if TEMP is in effect all temp variables
> are dropped.
>

DISCARD should to remove TEMP variables and should to remove content of all
variables.


>
> Examples abound though it doesn't feel like too much: but saying "The
> usage is very simple:" before giving the example in the function section
> seems to be outside of our general style.  A better preamble than "An
> example:" would be nice but the example is so simple I could not think of
> anything worth writing.
>

This doc is just design frame. I invite any enhancing because this feature
can be difficult for some people, because mix persistent object with
temporal/session content - and term "variable" can be used in relation
algebra in different semantic. It is natural for people with stored
procedures experience - mainly with Oracle, but for any other can be little
bit difficult. I believe so there should be more practical examples -
related to RLS for example.



>
> Its worth considering how both:
>
> https://www.postgresql.org/docs/10/static/ddl.html
> and
> https://www.postgresql.org/docs/10/static/queries.html
>
> could be updated to incorporate the broad picture of schema variables,
> with examples, and leave the reference (SQL and functions) sections mainly
>