Undefined psql variables

On Mon, Jan 23, 2017 at 10:34 AM, Corey Huinker <corey.huinker@gmail.com>
wrote:

I was giving some thought to how psql handles undefined variables.

I would like an option where either psql can provide an alternate value
when an undefined variable is referenced, or a way to detect that a
specific variable is undefined and replace it with a defined variable.

My first thought thought was to have a

\set_if_undefined var_name 'default_value'

​\coalesce var_name [maybe other var names?] 'default_value' --sets the
value of var_name to itself or the first defined/non-null result of the
subsequent items​. Probably only useful if you allow expressions. I have
followed the "\if" thread that closely but IIRC that was part of the
discussion there.

Another idea adding a \pset parameter that would return a specific value
when an undefined psql variable is referenced instead of raising an error.
Like this:

# select :'x' as value_of_x;
ERROR: syntax error at or near ":"
LINE 1: select :'x' as value_of_x;
^

# \pset variable_default ''
analytics=# select :'x' as value_of_x;
value_of_x
------------

(1 row)

​-1​

I unconvinced that setting this in the global environment is a good thing.

David J.

On Mon, Jan 23, 2017 at 12:49 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

I have followed the "\if" thread that closely but IIRC that was part of
the discussion there.

Yes, I'm trying to split some of those side-thoughts into their own threads.

Corey Huinker <corey.huinker@gmail.com> writes:

I was giving some thought to how psql handles undefined variables.
I would like an option where either psql can provide an alternate value
when an undefined variable is referenced, or a way to detect that a
specific variable is undefined and replace it with a defined variable.

This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps

\if not defined(x)
\set x y
\fi

Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Corey Huinker <corey.huinker@gmail.com> writes:

I was giving some thought to how psql handles undefined variables.
I would like an option where either psql can provide an alternate value
when an undefined variable is referenced, or a way to detect that a
specific variable is undefined and replace it with a defined variable.

This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps

\if not defined(x)
\set x y
\fi

Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.

regards, tom lane

That'd work too, if \if and defined(psql_var_name) come to fruition.

A use case we have now is where a script has several values that are almost
always a default value.

Instead of

PGSERVICE=foo psql -f script.sql --set var1=a --set var2=b --set var3=d ...
--set varN=n

I can just specify the non-default values:

PGSERVCE=foo psql -f script.sql --set var3=unusual_value

and the code then shows the unusual needle in the default haystack.

2017-01-23 18:53 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

Corey Huinker <corey.huinker@gmail.com> writes:

I was giving some thought to how psql handles undefined variables.
I would like an option where either psql can provide an alternate value
when an undefined variable is referenced, or a way to detect that a
specific variable is undefined and replace it with a defined variable.

This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps

\if not defined(x)
\set x y
\fi

Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.

Some form of DEFINED has more sense than COALESCE in this case. We can
introduce some predefined client side functions available only in \if
\elsif statements.

Regards

Pavel

[...] Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.

I agree, a more generic solution seems better than an ad-hoc one.

Currently the value of a non existing psql-variable is... its own
reference:-(

psql> \echo :x

:x

I'm not sure of the rational, apart from the probable lexer implementation
point of view. Maybe an empty string or 0 or some configurable value would
provide better alternative.

PGSERVICE=foo psql -f script.sql --set var1=a --set var2=b --set var3=d ...
--set varN=n

How are the variable typically used in the script?

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 23, 2017 at 11:16 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

[...] Obviously the \if stuff is things we don't have yet either, but it

seems less likely to have surprising side-effects.

I agree, a more generic solution seems better than an ad-hoc one.

Currently the value of a non existing psql-variable is... its own
reference:-(

psql> \echo :x

:x

I'm not sure of the rational, apart from the probable lexer implementation
point of view. Maybe an empty string or 0 or some configurable value would
provide better alternative.

​The fundamental problem is that:

SELECT 'testing' AS ":tablename"

is perfectly valid SQL code. psql, by design, attempts to resolve all
strings of the form <:['"]?\w> in a purely textual manner.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Mon, Jan 23, 2017 at 11:16 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Currently the value of a non existing psql-variable is... its own
reference:-(

psql> \echo :x
:x

I'm not sure of the rational, apart from the probable lexer implementation
point of view. Maybe an empty string or 0 or some configurable value would
provide better alternative.

The fundamental problem is that:
SELECT 'testing' AS ":tablename"
is perfectly valid SQL code.

Yeah, but psql does know not to try to resolve :something inside a quoted
literal or identifier. The actual problem is with constructs like

SELECT somearray[lower:upper] FROM ...

If the user is thinking that's an array subscript not a variable
reference, we don't want to break their query when we don't even have
a useful thing to contribute.

Back in the day, PG allowed ":" as a generic operator name, making
this even worse; but I think the only remaining SQL syntax that could
include a colon is array slicing.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Back in the day, PG allowed ":" as a generic operator name, making
this even worse; but I think the only remaining SQL syntax that could
include a colon is array slicing.

Ok, so the behavior of replacing ":unknown" by same cannot be changed.

Some fun:

\set 1 1
SELECT ('{1,2,3,4,5,6,7,8,9,10,11,12}'::INT[])[1:1];
-- yields 11

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Corey Huinker <corey.huinker@gmail.com> writes:

I was giving some thought to how psql handles undefined variables.
I would like an option where either psql can provide an alternate value
when an undefined variable is referenced, or a way to detect that a
specific variable is undefined and replace it with a defined variable.

This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps

\if not defined(x)
\set x y
\fi

Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.

regards, tom lane

In light of the backticks variable expansion thread, I'm reviving this
thread in the hopes that a defined()-ish psql function can make it into v10.
It's something that cannot be solved with a query and \gset, so adding it
to psql boolean expressions is the only option I can see.

Corey Huinker <corey.huinker@gmail.com> writes:

On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps
\if not defined(x)

In light of the backticks variable expansion thread, I'm reviving this
thread in the hopes that a defined()-ish psql function can make it into v10.
It's something that cannot be solved with a query and \gset, so adding it
to psql boolean expressions is the only option I can see.

I'm fairly hesitant to add stuff in advance of having a fairly clear
sketch of the boolean expression language we want. I don't mind
implementing such a language piece-by-piece, but if we just throw in
one or two features that seem like good ideas, I'm afraid we'll be
painting ourselves into a corner.

The only thing that seems locked down so far is that "a single argument
is a simple boolean value". If we were hot to support expr-style
comparison behavior, we could define cases with exactly three arguments
as being "\if value operator value". But I'm afraid that that would
cause problems because there would be other desirable behaviors (like
"\if not defined varname") that would also involve three arguments,
creating ambiguity.

I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions. So you could imagine

\if defined varname
\if sql boolean expression to send to server
\if compare value operator value

It would be easy to allow "not" in front of any one of these, but
it's less clear how to do AND or OR combinations. You can always
fake AND with nested \if's, but OR is a bit more of a problem.
Maybe we don't need it.

Other ideas about how to design this?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Tom,

I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions. So you could imagine

\if defined varname
\if sql boolean expression to send to server
\if compare value operator value

It would be easy to allow "not" in front of any one of these, but
it's less clear how to do AND or OR combinations. You can always
fake AND with nested \if's, but OR is a bit more of a problem.
Maybe we don't need it.

Other ideas about how to design this?

My 0.02 ￜ:

I have convinced myself that, unlike pgbench, psql does not really need an
advanced client-side-implemented language, so the smaller the better. What
I mean by this is that from psql point of view it is ok that the actual
expression evaluation is performed server-side. From a user experience
point of view it would look similar to pgbench, just the evaluator does
not need to be client-side.

So I would suggest something close but maybe simpler than what you suggest
above. If there is just one thing, it is true or false, checked client
side, well, this is already implemented:-).

\if something

If there are more than one argument, or maybe if previous true/false
evaluation failed, then:

\if sql expression to be evaluated server side

Then the result is checked for true or false client-side. It would be
equivalent to:

SELECT sql expression to be evaluted server side AS is_ok \gset
\if :is_ok

Finally I would suggest that client to server would only communicate by
variable substitution, as the backtick patch with external processes.

For checking variable definition, I would suggest to extend the variable
access syntax so that there is no exception to the one thing rule between
client side and server side evaluation:

\if :?variable

the :?... is subsituted by true or false depending on whether the variable
exists.

\if NOT :?variable

would work by executing "NOT ..." on the server. No need for "defined"
which would not look like SQL function calls anyway, no need for any
operator client side or clumsy rules.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2017-04-02 18:29 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:

Corey Huinker <corey.huinker@gmail.com> writes:

On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

This seems pretty bizarre. What's the use case? Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps
\if not defined(x)

In light of the backticks variable expansion thread, I'm reviving this
thread in the hopes that a defined()-ish psql function can make it into

v10.

It's something that cannot be solved with a query and \gset, so adding it
to psql boolean expressions is the only option I can see.

I'm fairly hesitant to add stuff in advance of having a fairly clear
sketch of the boolean expression language we want. I don't mind
implementing such a language piece-by-piece, but if we just throw in
one or two features that seem like good ideas, I'm afraid we'll be
painting ourselves into a corner.

The only thing that seems locked down so far is that "a single argument
is a simple boolean value". If we were hot to support expr-style
comparison behavior, we could define cases with exactly three arguments
as being "\if value operator value". But I'm afraid that that would
cause problems because there would be other desirable behaviors (like
"\if not defined varname") that would also involve three arguments,
creating ambiguity.

I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions. So you could imagine

\if defined varname
\if sql boolean expression to send to server
\if compare value operator value

These possibilities looks well.

if defined varname is perfectly intuitive

Maybe it can be shorter - def, undef

\if def var, \if undef var

Regards

Pavel

2017-04-02 18:56 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello Tom,

I'm inclined to suggest that we should require all extensions beyond the

boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions. So you could imagine

\if defined varname
\if sql boolean expression to send to server
\if compare value operator value

It would be easy to allow "not" in front of any one of these, but
it's less clear how to do AND or OR combinations. You can always
fake AND with nested \if's, but OR is a bit more of a problem.
Maybe we don't need it.

Other ideas about how to design this?

My 0.02 €:

I have convinced myself that, unlike pgbench, psql does not really need an
advanced client-side-implemented language, so the smaller the better. What
I mean by this is that from psql point of view it is ok that the actual
expression evaluation is performed server-side. From a user experience
point of view it would look similar to pgbench, just the evaluator does not
need to be client-side.

I am sorry - I disagree - I don't expect hard scripting in psql too. But
psql is much more widely used than pgbench - and scripting should be
intuitive.

Regards

Pavel

Hello Pavel,

I have convinced myself that, unlike pgbench, psql does not really need an
advanced client-side-implemented language, so the smaller the better. What
I mean by this is that from psql point of view it is ok that the actual
expression evaluation is performed server-side. From a user experience
point of view it would look similar to pgbench, just the evaluator does not
need to be client-side.

I am sorry - I disagree - I don't expect hard scripting in psql too. But
psql is much more widely used than pgbench - and scripting should be
intuitive.

I am ok with that objective.

I'm just arguing that for pgbench the evaluator needs to be on the client
side, which implies a lexer, parser and executor. For psql, it does not
really matter where the evaluator is, thus relying on the server should be
fine and simpler and also powerful, provided the necessary information can
be transfered from the client, eg through variable expansion, and maybe
back in the form of special variables to test for errors for instance.

It would not change anything from a syntactic point of view, i.e. it
should indeed be intuitive as you put it, i.e. SQL-like, for instance:

\if current_setting('something') = 'whatever' AND :VERSION_NUM >= 100000
...

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Fabien COELHO <coelho@cri.ensmp.fr> writes:

I'm just arguing that for pgbench the evaluator needs to be on the client
side, which implies a lexer, parser and executor. For psql, it does not
really matter where the evaluator is, thus relying on the server should be
fine and simpler and also powerful, provided the necessary information can
be transfered from the client, eg through variable expansion, and maybe
back in the form of special variables to test for errors for instance.

I don't really buy this. Certainly it'd be fine for many use-cases,
but there will be cases where what you're trying to script around
is server-side errors. An expression evaluation facility that goes
belly-up as soon as the server is in an aborted transaction is not
going to be very useful in that scenario.

I think that we need just a relatively primitive facility in order
to meet that sort of need, but we do need something.

So my view of this is that "send the expression to the server" ought
to be just one option for \if, not the only way to do it. Hence my
suggestion of "\if sql ...text to send to server...". Probably someone
can think of a better keyword than "sql" for that.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Fabien COELHO <coelho@cri.ensmp.fr> writes:

For checking variable definition, I would suggest to extend the variable
access syntax so that there is no exception to the one thing rule between
client side and server side evaluation:

\if :?variable

Don't like that one bit; you're going to run out of namespace there
in no time. And you don't have a very good way to say "if not defined",
either.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Tom,

I'm just arguing that for pgbench the evaluator needs to be on the client
side, which implies a lexer, parser and executor. For psql, it does not
really matter where the evaluator is, thus relying on the server should be
fine and simpler and also powerful, provided the necessary information can
be transfered from the client, eg through variable expansion, and maybe
back in the form of special variables to test for errors for instance.

I don't really buy this. Certainly it'd be fine for many use-cases, but
there will be cases where what you're trying to script around is
server-side errors. An expression evaluation facility that goes belly-up
as soon as the server is in an aborted transaction is not going to be
very useful in that scenario.

"Going belly-up" suggests testing/checking for errors, which could be
eased through special variables ￜ la errno and more than simplistic
client-side expression evaluation.

I think that we need just a relatively primitive facility in order
to meet that sort of need, but we do need something.

Hmmm. Yes, I was thinking of that kind of thing. The question is how large
the necessary "something". I'm arguying for the smallest possible
solution. Maybe handling direct booleans (as already implemented) and the
NOT operator could be enough (clear enough to understand for the user,
would cover needed cases, and would be easy to implement)? i.e.

\if NOT :IS_CONNECTED
...

SELECT ... \gset
\if :SQL_ERROR_OCCURED
...

\if :CURRENT_TRANSACTION_ABORTED
...

So my view of this is that "send the expression to the server" ought
to be just one option for \if, not the only way to do it. Hence my
suggestion of "\if sql ...text to send to server...". Probably someone
can think of a better keyword than "sql" for that.

That is the kind of (ugly) thing I would really like to avoid, if
possible. As pavel argued, it should be "intuitive", and having a explicit
syntactic marker and/or possibly two distinct syntaxes does not strike me
as a desirable user-experience.

--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello,

For checking variable definition, I would suggest to extend the variable
access syntax so that there is no exception to the one thing rule between
client side and server side evaluation:

\if :?variable

Don't like that one bit;

Possibly:-)

This is kind of a shell-like hack ${VAR:?error-message-if-not-defined},
or ${#VAR} to get a length.

They are not likable but they do the job.

you're going to run out of namespace there in no time.

I do not undestand where there would be a namespace issue. Is that under
the assumption that ":?xxx" is frequently used in SQL?

And you don't have a very good way to say "if not defined", either.

Indeed. I'm afraid that handling "NOT" client-side would be necessary with
this approach, so the decision would be 1 thing or 2 things where the
first one is "NOT" would be handled client-side.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers