Re: set_config() documentation clarification
> > > >> SET >> g = year % 19, >> c = year / 100, >> h = (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30, >> i = h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)), >> j = year + year/4 + i + 2 - c + c/4) % 7, >> p = i - j, >> easter_month = 3 + (p + 26)/30, >> easter_day = 1 + (p + 27 + (p + 6)/40) % 31 >> SELECT make_date(year, easter_month, easter_day) >> >> or maybe even WITH like this: >> >> WITH >> year % 19 AS g , >> year / 100 AS c, >> (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h, >> h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i, >> year + year/4 + i + 2 - c + c/4) % 7 AS j, >> i - j AS p, >> 3 + (p + 26)/30 AS easter_month, >> 1 + (p + 27 + (p + 6)/40) % 31 AS easter_day >> SELECT make_date(year, easter_month, easter_day) >> > > I do not think this clause is necessary (because we have PLpgSQL or C), > but other people can have different opinions (and it is true, so this > feature can have some performance benefit - because it enhances the > possibilities of inlined expressions and custom (own) extensions are > prohibited in cloud environments (and will be) ). Theoretically the > implementation of this feature should not be hard, because these variables > are very local only (the scope is just row), so this is just a game for > parser and for expression's interpreter. But if you introduce this feature, > then it is better to use syntax that is used by some other well known > systems (Oracle or others). > The name for this feature can be "row scope variables" and yes, in OLAP queries there are repeated expressions where this feature can be useful. postgres=# explain verbose select make_date(year, easter_month, easter_day) from (select year, 3 + (p + 26)/30 AS easter_month, 1 + (p + 27 + (p + 6)/40) % 31 AS easter_day from ( select year, i - j AS p from (select year, i, (year + year/4 + i + 2 - c + c/4) % 7 AS j from (select year, c, h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i from (select year, g, c, (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h from (select year, year % 19 as g, year / 100 as c from generate_series(2019,2020) g(year) offset 0) s1 offset 0) s2 offset 0) s3 offset 0) s4 offset 0) s5 offset 0) s6; ┌─┐ │ QUERY PLAN │ ╞═╡ │ Subquery Scan on s6 (cost=0.00..0.35 rows=2 width=4) │ │ Output: make_date(s6.year, s6.easter_month, s6.easter_day) │ │ -> Subquery Scan on s5 (cost=0.00..0.33 rows=2 width=12) │ │ Output: s5.year, (3 + ((s5.p + 26) / 30)), (1 + (((s5.p + 27) + ((s5.p + 6) / 40)) % 31)) │ │ -> Subquery Scan on s4 (cost=0.00..0.26 rows=2 width=8) │ │ Output: s4.year, (s4.i - s4.j) │ │ -> Subquery Scan on s3 (cost=0.00..0.24 rows=2 width=12) │ │ Output: s3.year, s3.i, ((s3.year + (s3.year / 4)) + s3.i) + 2) - s3.c) + (s3.c / 4)) % 7) │ │ -> Subquery Scan on s2 (cost=0.00..0.18 rows=2 width=12) │ │ Output: s2.year, s2.c, (s2.h - ((s2.h / 28) * (1 - (((s2.h / 28) * (29 / (s2.h + 1))) * ((21 - s2.g) / 11)│ │ -> Subquery Scan on s1 (cost=0.00..0.10 rows=2 width=16)│ │ Output: s1.year, s1.g, s1.c, (s1.c - (s1.c / 4)) - (((8 * s1.c) + 13) / 25)) + (19 * s1.g)) + 15) % 30) │ │ -> Function Scan on pg_catalog.generate_series g (cost=0.00..0.03 rows=2 width=12) │ │ Output: g.year, (g.year % 19), (g.year / 100) │ │ Function Call: generate_series(2019, 2020) │ └─┘ (15 rows) Pavel
Re: set_config() documentation clarification
út 5. 1. 2021 v 22:15 odesílatel Joel Jacobson napsal: > On Tue, Jan 5, 2021, at 21:34, Pavel Stehule wrote: > > yes, it is supported. More the schema variables supports RESET to > default on transaction end, > > and supports access rights for usage in security definer functions. > > Nice. > > > Maybe - I don't know what "Statement variables" :). Other databases do > not have similarly named features. > > I know, I made that name up just to make the connection, > the name used by other databases is "LET clause", > and in functional languages such as OCaml and Haskell, > this concept is called "let expressions". > > > There are two concepts (these concepts can be mixed). Maybe - you can > see there how non tabular objects can be > > accessed in queries with. > > ... > > Thank you for a detailed explanation, very useful. > > >> Also, do you know if Schema variables are part of the SQL standard? > > ANSI SQL defines modules, and some variables can be declared in module > scope. Modules are like our schemas with the > > possibility to define private objects. But I don't know any > implementation of this part of the standard in some widely used > > database . It is like a mix of package concepts (Oracle) with schemas, > because modules can hold private database objects > > like tables or temporary tables. So my proposed schema variables are not > part of SQL standard, because related features > > depend on modules. Functionally it is similar +/-. Personally I don't > like concepts of modules (or packages) too much. The > > schemas are a good replacement for 90% and the current system of > qualified names and search path, that is same for > > tables and same for procedures, is very simple and good enough). So > instead of introducing modules, I prefer enhanced > > schemas about some features like private objects. But it is in the > category "nice to have" rather than a necessary feature. > > This is encouraging to hear, then I will pray there might be hope for LET > clauses I need, > even though not being part of the SQL standard. > > In another attempt to sell the LET clause feature, imagine OCaml/Haskell > *without* let expressions, > where users would be advised to write functions in a different language > like C, > to do their complex computations reused at many places, and then return > the result back to OCaml/Haskell. > That wouldn't be a very nice user-experience to the OCaml/Haskell user. > > I really think a lot of real-life complex SQL code could be simplified a > lot > and written much more clear and concise with LET clauses. > I have no idea - all my life I use procedural languages, when this case is not a problem. > Since using "SET" as the command for Schema variables, > maybe using SET for LET clause would make the idea less controversial: > The schema variables (my patch) introduced the LET statement, because SET (SET keyword) is already used in Postgres for GUC setting and works with GUC. But this fact doesn't block using LET as a new clause. > SET > g = year % 19, > c = year / 100, > h = (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30, > i = h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)), > j = year + year/4 + i + 2 - c + c/4) % 7, > p = i - j, > easter_month = 3 + (p + 26)/30, > easter_day = 1 + (p + 27 + (p + 6)/40) % 31 > SELECT make_date(year, easter_month, easter_day) > > or maybe even WITH like this: > > WITH > year % 19 AS g , > year / 100 AS c, > (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h, > h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i, > year + year/4 + i + 2 - c + c/4) % 7 AS j, > i - j AS p, > 3 + (p + 26)/30 AS easter_month, > 1 + (p + 27 + (p + 6)/40) % 31 AS easter_day > SELECT make_date(year, easter_month, easter_day) > I do not think this clause is necessary (because we have PLpgSQL or C), but other people can have different opinions (and it is true, so this feature can have some performance benefit - because it enhances the possibilities of inlined expressions and custom (own) extensions are prohibited in cloud environments (and will be) ). Theoretically the implementation of this feature should not be hard, because these variables are very local only (the scope is just row), so this is just a game for parser and for expression's interpreter. But if you introduce this feature, then it is better to use syntax that is used by some other well known systems (Oracle or others). > I will study SQL code in the wild on Github written by other users to see > how many % > that could benefit from this feature. > I am sure, so it can be very good task for learning PostgresSQL internals - parser and executor, and it can be funny work (when I started with Postgres, I had to modify same parts). Regards Pavel > Maybe I'm wrong, but my gut feeling says this would be a really good thing, > and just like like Schema variables, I didn't really know I needed them > before I saw them. > > Best regards, > > Joel >
Re: set_config() documentation clarification
On Tue, Jan 5, 2021, at 20:28, Bruce Momjian wrote: > Oh, I know it is useful. My pg_cryptokey uses it: > https://momjian.us/download/pgcryptokey/ Looks like a nice extension I could use sometimes myself, thanks for sharing. /Joel
Re: set_config() documentation clarification
On 01/05/21 16:14, Joel Jacobson wrote: > or maybe even WITH like this: > > WITH > year % 19 AS g , > year / 100 AS c, > (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h, > h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i, > year + year/4 + i + 2 - c + c/4) % 7 AS j, > i - j AS p, > 3 + (p + 26)/30 AS easter_month, > 1 + (p + 27 + (p + 6)/40) % 31 AS easter_day > SELECT make_date(year, easter_month, easter_day) The standard indeed has such a syntax ... not for entirely arbitrary variables like that (at least not that I've seen) but definitely for lexically-scoped settings of things like XMLOPTION or XMLBINARY. Regards, -Chap
Re: set_config() documentation clarification
Bruce Momjian writes: > On Tue, Jan 5, 2021 at 08:23:15PM +0100, Joel Jacobson wrote: >> út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian napsal: >>> I think I tried to make this feature more visible a few years ago and >>> some people said we might remove it someday, so don't do that. If that >>> is false, I think we can make it more prominent. >> I think it's false. > Oh, I know it is useful. My pg_cryptokey uses it: > https://momjian.us/download/pgcryptokey/ People use it because we don't have a better substitute. But I think the way forward is to create a better substitute, not to encourage abuse of custom GUCs ... and this is an abuse. Two good reasons why are * there's no way to declare a data type for such a variable from SQL * guc.c is not designed to scale to large numbers of variables, nor to heavy update activity. I've not looked at Pavel's "schema variables" patch lately, but maybe people who are interested in this should be pushing that forward. regards, tom lane
Re: set_config() documentation clarification
On Tue, Jan 5, 2021, at 21:34, Pavel Stehule wrote: > yes, it is supported. More the schema variables supports RESET to default on > transaction end, > and supports access rights for usage in security definer functions. Nice. > Maybe - I don't know what "Statement variables" :). Other databases do not > have similarly named features. I know, I made that name up just to make the connection, the name used by other databases is "LET clause", and in functional languages such as OCaml and Haskell, this concept is called "let expressions". > There are two concepts (these concepts can be mixed). Maybe - you can see > there how non tabular objects can be > accessed in queries with. > ... Thank you for a detailed explanation, very useful. >> Also, do you know if Schema variables are part of the SQL standard? > ANSI SQL defines modules, and some variables can be declared in module scope. > Modules are like our schemas with the > possibility to define private objects. But I don't know any implementation of > this part of the standard in some widely used > database . It is like a mix of package concepts (Oracle) with schemas, > because modules can hold private database objects > like tables or temporary tables. So my proposed schema variables are not part > of SQL standard, because related features > depend on modules. Functionally it is similar +/-. Personally I don't like > concepts of modules (or packages) too much. The > schemas are a good replacement for 90% and the current system of qualified > names and search path, that is same for > tables and same for procedures, is very simple and good enough). So instead > of introducing modules, I prefer enhanced > schemas about some features like private objects. But it is in the category > "nice to have" rather than a necessary feature. This is encouraging to hear, then I will pray there might be hope for LET clauses I need, even though not being part of the SQL standard. In another attempt to sell the LET clause feature, imagine OCaml/Haskell *without* let expressions, where users would be advised to write functions in a different language like C, to do their complex computations reused at many places, and then return the result back to OCaml/Haskell. That wouldn't be a very nice user-experience to the OCaml/Haskell user. I really think a lot of real-life complex SQL code could be simplified a lot and written much more clear and concise with LET clauses. Since using "SET" as the command for Schema variables, maybe using SET for LET clause would make the idea less controversial: SET g = year % 19, c = year / 100, h = (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30, i = h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)), j = year + year/4 + i + 2 - c + c/4) % 7, p = i - j, easter_month = 3 + (p + 26)/30, easter_day = 1 + (p + 27 + (p + 6)/40) % 31 SELECT make_date(year, easter_month, easter_day) or maybe even WITH like this: WITH year % 19 AS g , year / 100 AS c, (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h, h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i, year + year/4 + i + 2 - c + c/4) % 7 AS j, i - j AS p, 3 + (p + 26)/30 AS easter_month, 1 + (p + 27 + (p + 6)/40) % 31 AS easter_day SELECT make_date(year, easter_month, easter_day) I will study SQL code in the wild on Github written by other users to see how many % that could benefit from this feature. Maybe I'm wrong, but my gut feeling says this would be a really good thing, and just like like Schema variables, I didn't really know I needed them before I saw them. Best regards, Joel
Re: set_config() documentation clarification
út 5. 1. 2021 v 21:34 odesílatel Pavel Stehule
napsal:
>
>
> út 5. 1. 2021 v 20:23 odesílatel Joel Jacobson napsal:
>
>> út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian napsal:
>> > I think I tried to make this feature more visible a few years ago and
>> > some people said we might remove it someday, so don't do that. If that
>> > is false, I think we can make it more prominent.
>>
>> I think it's false.
>>
>> I'll try to give you a real-life context on how set_config() was useful
>> to me
>> yesterday when implementing application-level Role-Based Access Control
>> built on top of PostgREST.
>>
>> In the postgrest.conf I'm using the "pre-request" feature to call an
>> auth() function
>> that will raise an exception if the user is not authorized to access the
>> resource.
>>
>> Before, I had to execute the code to authenticate the user by verifying a
>> UUID token in current_setting('request.cookie.access_token', TRUE)::uuid
>> in a query in a helper-function user_id() by looking it up in an
>> access_tokens table.
>>
>> Since functions as well as security_definer views might restrict access
>> to rows based on application-level user_id, this user_id() function is
>> called
>> from multiple different places possibly lots of times.
>>
>> Now, using set_config(), I instead verify the access_token only once,
>> in my auth() function, and set the user_id there, and modified user_id()
>> to use current_setting() to read it.
>>
>> Maybe it's not an improvement performance-wise since user_id() is marked
>> STABLE
>> so maybe its query would only be executed once per transaction anyway.
>> But I think it's cleaner to do all the authenticate and authorize
>> operations
>> at one place, make a decision, and then use the constant result of that
>> decision,
>> instead of relying on caching of functions.
>>
>> Here is the code for the scenario described:
>>
>> https://github.com/truthly/uniphant/commit/da12465818a62e69355dad126648c15a11871c12#diff-fe0f50327c9772bdf4baa8944856716da16706f0998e844820b51fc6077a7c7aR21
>>
>> On Tue, Jan 5, 2021, at 18:59, Pavel Stehule wrote:
>> > Schema variables are designed specially for described purposes
>> https://commitfest.postgresql.org/31/1608/.
>>
>> Many thanks Pavel for working on Schema variables, looks like a very nice
>> feature.
>>
>> Is it possible to get the behaviour of set_config(..., ..., TRUE) i.e.
>> "the new value will only apply for the current transaction" by using CREATE
>> TEMP VARIABLE .. ON TRANSACTION END?
>> This is what I need for my purpose, I don't want the value to survive the
>> transaction.
>>
>
> yes, it is supported. More the schema variables supports RESET to default
> on transaction end, and supports access rights for usage in security
> definer functions.
>
> So some users can set this value and others can only read.
>
>
>> I noted "LET" has been suggested as an alternative name for the command.
>> This reminds me of what I brought up in the other thread "LET clause". But
>> instead of "Schema variables" I guess a descriptive sentence for what I
>> talked about would be "Statement variables" i.e. variables that are
>> declared and exists on a per-statement level. Do you think the "Schema
>> variables" code would be useful to look at if I would try to implement a
>> PoC of "Statement variables"?
>>
>
> Maybe - I don't know what "Statement variables" :). Other databases do not
> have similarly named features. There are two concepts (these concepts can
> be mixed). Maybe - you can see there how non tabular objects can be
> accessed in queries with.
>
> 1. using some global temporary objects - the object is persistent, but
> data not (and data are not shared). These objects should be assigned to
> some persistent space - Oracle has packages, I propose schema, DB2 has
> schemas, ANSI SQL has modules. It's designed primarily for stored
> procedures as global variables (some are shared, some not). These objects
> can be locally temporal - like our temporary tables. I prefer the name
> "schema variables" due similarity with DB2 schema variables. Oracle's
> package variables are just ADA language package variables.
>
> 2. session variables - these exist dynamically only in session, and are
> not assigned with some persistent space. Usually they are created by
> DECLARE statement (MSSQL) or are created by first usage (MySQL). In this
> concept, the session variables are very dynamic objects - in MSSQL or MySQL
> very primitive without possibility to set access rights (but it can be
> implementation detail). The little bit strange thing is fact, so these
> objects are in specific address space - MSSQL, MySQL uses special notation
> - it starts by @. This script language in PgAdmin III supports this syntax
> too. The open question is stability of values stored in these variables.
> MySQL variables are volatile, MSSQL I don't know. It has an impact on
> behaviour. MySQL variables can be used for implementation of a row counter,
> but because they are not
Re: set_config() documentation clarification
út 5. 1. 2021 v 20:23 odesílatel Joel Jacobson napsal:
> út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian napsal:
> > I think I tried to make this feature more visible a few years ago and
> > some people said we might remove it someday, so don't do that. If that
> > is false, I think we can make it more prominent.
>
> I think it's false.
>
> I'll try to give you a real-life context on how set_config() was useful to
> me
> yesterday when implementing application-level Role-Based Access Control
> built on top of PostgREST.
>
> In the postgrest.conf I'm using the "pre-request" feature to call an
> auth() function
> that will raise an exception if the user is not authorized to access the
> resource.
>
> Before, I had to execute the code to authenticate the user by verifying a
> UUID token in current_setting('request.cookie.access_token', TRUE)::uuid
> in a query in a helper-function user_id() by looking it up in an
> access_tokens table.
>
> Since functions as well as security_definer views might restrict access
> to rows based on application-level user_id, this user_id() function is
> called
> from multiple different places possibly lots of times.
>
> Now, using set_config(), I instead verify the access_token only once,
> in my auth() function, and set the user_id there, and modified user_id()
> to use current_setting() to read it.
>
> Maybe it's not an improvement performance-wise since user_id() is marked
> STABLE
> so maybe its query would only be executed once per transaction anyway.
> But I think it's cleaner to do all the authenticate and authorize
> operations
> at one place, make a decision, and then use the constant result of that
> decision,
> instead of relying on caching of functions.
>
> Here is the code for the scenario described:
>
> https://github.com/truthly/uniphant/commit/da12465818a62e69355dad126648c15a11871c12#diff-fe0f50327c9772bdf4baa8944856716da16706f0998e844820b51fc6077a7c7aR21
>
> On Tue, Jan 5, 2021, at 18:59, Pavel Stehule wrote:
> > Schema variables are designed specially for described purposes
> https://commitfest.postgresql.org/31/1608/.
>
> Many thanks Pavel for working on Schema variables, looks like a very nice
> feature.
>
> Is it possible to get the behaviour of set_config(..., ..., TRUE) i.e.
> "the new value will only apply for the current transaction" by using CREATE
> TEMP VARIABLE .. ON TRANSACTION END?
> This is what I need for my purpose, I don't want the value to survive the
> transaction.
>
yes, it is supported. More the schema variables supports RESET to default
on transaction end, and supports access rights for usage in security
definer functions.
So some users can set this value and others can only read.
> I noted "LET" has been suggested as an alternative name for the command.
> This reminds me of what I brought up in the other thread "LET clause". But
> instead of "Schema variables" I guess a descriptive sentence for what I
> talked about would be "Statement variables" i.e. variables that are
> declared and exists on a per-statement level. Do you think the "Schema
> variables" code would be useful to look at if I would try to implement a
> PoC of "Statement variables"?
>
Maybe - I don't know what "Statement variables" :). Other databases do not
have similarly named features. There are two concepts (these concepts can
be mixed). Maybe - you can see there how non tabular objects can be
accessed in queries with.
1. using some global temporary objects - the object is persistent, but data
not (and data are not shared). These objects should be assigned to some
persistent space - Oracle has packages, I propose schema, DB2 has schemas,
ANSI SQL has modules. It's designed primarily for stored procedures as
global variables (some are shared, some not). These objects can be locally
temporal - like our temporary tables. I prefer the name "schema variables"
due similarity with DB2 schema variables. Oracle's package variables are
just ADA language package variables.
2. session variables - these exist dynamically only in session, and are not
assigned with some persistent space. Usually they are created by DECLARE
statement (MSSQL) or are created by first usage (MySQL). In this concept,
the session variables are very dynamic objects - in MSSQL or MySQL very
primitive without possibility to set access rights (but it can be
implementation detail). The little bit strange thing is fact, so these
objects are in specific address space - MSSQL, MySQL uses special notation
- it starts by @. This script language in PgAdmin III supports this syntax
too. The open question is stability of values stored in these variables.
MySQL variables are volatile, MSSQL I don't know. It has an impact on
behaviour. MySQL variables can be used for implementation of a row counter,
but because they are not stable, the query cannot be optimized well (or
this optimization can be not correct).
Schema variables are designed for usage for stored procedures and for usage
from applications. Session
Re: set_config() documentation clarification
On Tue, Jan 5, 2021 at 08:23:15PM +0100, Joel Jacobson wrote: > út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian napsal: > > I think I tried to make this feature more visible a few years ago and > > some people said we might remove it someday, so don't do that. If that > > is false, I think we can make it more prominent. > > I think it's false. Oh, I know it is useful. My pg_cryptokey uses it: https://momjian.us/download/pgcryptokey/ -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: set_config() documentation clarification
út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian napsal:
> I think I tried to make this feature more visible a few years ago and
> some people said we might remove it someday, so don't do that. If that
> is false, I think we can make it more prominent.
I think it's false.
I'll try to give you a real-life context on how set_config() was useful to me
yesterday when implementing application-level Role-Based Access Control
built on top of PostgREST.
In the postgrest.conf I'm using the "pre-request" feature to call an auth()
function
that will raise an exception if the user is not authorized to access the
resource.
Before, I had to execute the code to authenticate the user by verifying a
UUID token in current_setting('request.cookie.access_token', TRUE)::uuid
in a query in a helper-function user_id() by looking it up in an access_tokens
table.
Since functions as well as security_definer views might restrict access
to rows based on application-level user_id, this user_id() function is called
from multiple different places possibly lots of times.
Now, using set_config(), I instead verify the access_token only once,
in my auth() function, and set the user_id there, and modified user_id()
to use current_setting() to read it.
Maybe it's not an improvement performance-wise since user_id() is marked STABLE
so maybe its query would only be executed once per transaction anyway.
But I think it's cleaner to do all the authenticate and authorize operations
at one place, make a decision, and then use the constant result of that
decision,
instead of relying on caching of functions.
Here is the code for the scenario described:
https://github.com/truthly/uniphant/commit/da12465818a62e69355dad126648c15a11871c12#diff-fe0f50327c9772bdf4baa8944856716da16706f0998e844820b51fc6077a7c7aR21
On Tue, Jan 5, 2021, at 18:59, Pavel Stehule wrote:
> Schema variables are designed specially for described purposes
> https://commitfest.postgresql.org/31/1608/.
Many thanks Pavel for working on Schema variables, looks like a very nice
feature.
Is it possible to get the behaviour of set_config(..., ..., TRUE) i.e. "the new
value will only apply for the current transaction" by using CREATE TEMP
VARIABLE .. ON TRANSACTION END?
This is what I need for my purpose, I don't want the value to survive the
transaction.
I noted "LET" has been suggested as an alternative name for the command. This
reminds me of what I brought up in the other thread "LET clause". But instead
of "Schema variables" I guess a descriptive sentence for what I talked about
would be "Statement variables" i.e. variables that are declared and exists on a
per-statement level. Do you think the "Schema variables" code would be useful
to look at if I would try to implement a PoC of "Statement variables"?
Also, do you know if Schema variables are part of the SQL standard?
/Joel
Re: set_config() documentation clarification
út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian napsal: > On Mon, Jan 4, 2021 at 09:46:32AM -0700, David G. Johnston wrote: > > On Mon, Jan 4, 2021 at 8:26 AM Joel Jacobson wrote: > > > > In the documentation at https://www.postgresql.org/docs/current/ > > functions-admin.html > > this behaviour is not mentioned anywhere as far as I can see: > > > > > > > > https://www.postgresql.org/docs/current/runtime-config-custom.html > > > > I do think "Customized Options" is an accurate description. One needs to > > consider their usage of such variables as "modular" even though there is > no > > library code involved. i.e., the description is possibly overly > specific but > > the variables created in this manner are simply namespaced but otherwise > usable > > in any manner you deem fit. > > I think I tried to make this feature more visible a few years ago and > some people said we might remove it someday, so don't do that. If that > is false, I think we can make it more prominent. > This feature is frequently used as a workaround for missing global package variables. And a lot of scripts use this feature as only one possibility to push parameters to the DO statement. Unfortunately - there is not any guard against typo errors, are untyped, and access is slow - it is designed for different purposes. Schema variables are designed specially for described purposes https://commitfest.postgresql.org/31/1608/. Regards Pavel > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee > > > >
Re: set_config() documentation clarification
On Mon, Jan 4, 2021 at 09:46:32AM -0700, David G. Johnston wrote: > On Mon, Jan 4, 2021 at 8:26 AM Joel Jacobson wrote: > > In the documentation at https://www.postgresql.org/docs/current/ > functions-admin.html > this behaviour is not mentioned anywhere as far as I can see: > > > > https://www.postgresql.org/docs/current/runtime-config-custom.html > > I do think "Customized Options" is an accurate description. One needs to > consider their usage of such variables as "modular" even though there is no > library code involved. i.e., the description is possibly overly specific but > the variables created in this manner are simply namespaced but otherwise > usable > in any manner you deem fit. I think I tried to make this feature more visible a few years ago and some people said we might remove it someday, so don't do that. If that is false, I think we can make it more prominent. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: set_config() documentation clarification
On Mon, Jan 4, 2021 at 8:26 AM Joel Jacobson wrote: > In the documentation at > https://www.postgresql.org/docs/current/functions-admin.html > this behaviour is not mentioned anywhere as far as I can see: > > https://www.postgresql.org/docs/current/runtime-config-custom.html I do think "Customized Options" is an accurate description. One needs to consider their usage of such variables as "modular" even though there is no library code involved. i.e., the description is possibly overly specific but the variables created in this manner are simply namespaced but otherwise usable in any manner you deem fit. David J.
Re: set_config() documentation clarification
On 01/04/21 10:25, Joel Jacobson wrote: > I just learned from a pg friend, it's possible to set your own made up > setting_name using set_config(), > if the setting_name contains at least one dot ".". It works that way so you can set a config variable needed by an extension, if necessary before the extension is loaded, so PostgreSQL doesn't know yet what variable names with that prefix are or aren't valid. Once the extension that defines the prefix (before the first dot) is loaded, it will handle any variables with that prefix that it recognizes, and errors will be reported for any others. I'm not sure how much of that behavior needs to be documented for set_config() itself; it gets a little deep into the weeds of extension development. Is it documented there? In that case, maybe the briefest of mentions at set_config() would be appropriate, such as "names starting with a prefix and a dot can be treated specially, as described at [link]". Regards, -Chap
set_config() documentation clarification
I just learned from a pg friend, it's possible to set your own made up
setting_name using set_config(),
if the setting_name contains at least one dot ".".
I didn't know about this and have always thought it's not possible,
due to early in my career having run into the error message
you get when not having a dot in the name:
SELECT set_config('foobar','test',true);
ERROR: unrecognized configuration parameter "foobar"
If instead having a dot in the name, it works:
SELECT set_config('foo.bar','test',true);
set_config
test
(1 row)
In the documentation at
https://www.postgresql.org/docs/current/functions-admin.html
this behaviour is not mentioned anywhere as far as I can see:
"set_config ( setting_name text, new_value text, is_local boolean ) → text
Sets the parameter setting_name to new_value, and returns that value. If
is_local is true, the new value will only apply for the current transaction. If
you want the new value to apply for the current session, use false instead.
This function corresponds to the SQL command SET."
Perhaps a paragraph where this is clarified should be added?
Kind regards,
Joel
