út 23. 7. 2024 v 23:41 odesílatel Laurenz Albe <laurenz.a...@cybertec.at> napsal:
> On Tue, 2024-07-23 at 16:34 +0200, Laurenz Albe wrote: > > CREATE VARIABLE command: > > > > This is buggy: > > > > CREATE VARIABLE str AS text NOT NULL DEFAULT NULL; > > > > Ugh. > > > > SELECT str; > > ERROR: null value is not allowed for NOT NULL session variable > "laurenz.str" > > DETAIL: The result of DEFAULT expression is NULL. > > > > Perhaps that is a leftover from the previous coding, but I think there > need be > > no check upon SELECT. It should be enough to check during CREATE > VARIABLE and > > LET. > > I'm having second thoughts about that. > > I was thinking of a variable like of a table column, but there is a > fundamental > difference: there is a clear moment when a tuple is added (INSERT or > UPDATE), > which is the point where a column can be checked for NULL values. > > A variable can be SELECTed without having been LET before, in which case it > has the default value. But there is no way to test the default value > before > the variable is SELECTed. So while DEFAULT NULL for a non-nullable > variable > seems weird, it is no worse than DEFAULT somefunc() for a function that > returns > NULL. > > So perhaps the behavior I complained about above is actually the right one. > In the view of that, it doesn't seem necessary to enforce a DEFAULT value > for > a NOT NULL variable: NOT NULL might just as well mean "you have to LET it > before > you can SELECT it". > exactly > > > IMMUTABLE variables: > > > > + <varlistentry id="sql-createvariable-immutable"> > > + <term><literal>IMMUTABLE</literal></term> > > + <listitem> > > + <para> > > + The assigned value of the session variable can not be changed. > > + Only if the session variable doesn't have a default value, a > single > > + initialization is allowed using the <command>LET</command> > command. Once > > + done, no further change is allowed until end of transaction > > + if the session variable was created with clause <literal>ON > TRANSACTION > > + END RESET</literal>, or until reset of all session variables > by > > + <command>DISCARD VARIABLES</command>, or until reset of all > session > > + objects by command <command>DISCARD ALL</command>. > > + </para> > > + </listitem> > > + </varlistentry> > > > > I can see the usefulness of IMMUTABLE variables, but I am surprised > that > > they are reset by DISCARD. What is the use case you have in mind? > > The use case I can envision is an application that sets a value right > after > > authentication, for use with row-level security. But then it would be > harmful > > if the user could reset the variable with DISCARD. > > I'm beginning to be uncertain about that as well. You might want to use a > connection pool, and you LET the variable when you take it out of the pool. > When the session is returned to the pool, variables get DISCARDed. > > Sure, a user can call DISCARD, but only if he or she is in an interactive > session. > > So perhaps it is good as it is. > I think this design should work. There are a lot of scenarios, where session variables can be used well, and sure, there will be scenarios where it doesn't work well, but now, I think it is a good balance between usability, complexity and code complexity. There are a lot of lines, but the code is almost very simple. Regards Pavel > > Yours, > Laurenz Albe >
