Thread: set_config() documentation clarification

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:

út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian <bruce@momjian.us> 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

út 5. 1. 2021 v 20:23 odesílatel Joel Jacobson <joel@compiler.org> napsal:

út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian <bruce@momjian.us> 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 variables are good for writing ad hoc queries, and adhoc interactive work. Although what I know, the other databases doesn support both concepts, I think so both concepts can be supported. There can be differences in syntax - session variables are not qualified,but the main difference is only in lifetime and constructor syntax CREATE or DECLARE - CREATE TEMP VARIABLE is functional equivalent of DECLARE var

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.

Regards

Pavel

 

/Joel

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

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