Thread: Re: Variable substitution in psql backtick expansion

Bonjour Daniel,

> I think that users would rather have the option to just put
> an SQL expression behind \if.

Note that this is already available indirectly, as show in the 
documentation.
  SELECT some-boolean-expression AS okay \gset  \if :okay    \echo boolean expression was true  \else    \echo boolean
expressionwas false  \endif
 

-- 
Fabien.

Hello Pavel,

> For this case can be nice to have function that returns server version 
> as number some like version_num() .. 10000

The server side information can be queried:

   SELECT current_setting(‘server_version_num’)
     AS server_version_num \gset
   -- 90602

However client side is not so clean:

   \echo :VERSION
   PostgreSQL 10devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

Probably some :VERSION_NUM would make some sense. See attached PoC patch.
Would it make sense?

-- 
Fabien.

> For this case can be nice to have function that returns server version as
> number
>
> some like version_num() .. 10000

Another possible trick to get out of a script which does not support \if,
relying on the fact that the unexpected command is simply ignored:
  -- exit version below 10  \if false    \echo 'script requires version 10 or better'    \q  \endif

Also possible but less informative on errors:
  \set ON_ERROR_STOP on  \if false \endif

-- 
Fabien.

Hello Pavel,

>>   \echo :VERSION
>>   PostgreSQL 10devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
>> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
>>
>> Probably some :VERSION_NUM would make some sense. See attached PoC patch.
>> Would it make sense?
>
> Maybe better name for you CLIENT_VERSION_NUM

If it was starting from nothing I would tend to agree with you, but there 
is already an existing :VERSION variable, so it seemed logical to keep on 
and create variants with the same prefix.

> Can be SERVER_VERSION_NUM taken from connection info?

Probably it could. It seems a little less straightforward than defining a 
client-side string at compile time. The information is displayed when the 
connection is established, so the information is there somewhere.
 psql (10devel, server 9.6.2)

-- 
Fabien.

> Can be SERVER_VERSION_NUM taken from connection info?

Here is a version with that, quite easy as well as the information was 
already there for other checks.

I have not updated "help.c" because the initial VERSION was not presented 
there in the first place.

-- 
Fabien.

>> Can be SERVER_VERSION_NUM taken from connection info?
>
> Here is a version with that, quite easy as well as the information was 
> already there for other checks.
>
> I have not updated "help.c" because the initial VERSION was not presented 
> there in the first place.

Here is a v3 to fix the documentation example. Sorry for the noise.

-- 
Fabien.

    Fabien COELHO wrote:

> Note that this is already available indirectly, as show in the
> documentation.
>
>   SELECT some-boolean-expression AS okay \gset
>   \if :okay
>     \echo boolean expression was true
>   \else
>     \echo boolean expression was false
>   \endif

Yes, the question was whether we leave it as that for v10,
or if it's worth a last-minute improvement for usability,
assuming it's doable, similarly to what $subject does to backtick
expansion for external evaluation.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Hello Daniel,

>>   SELECT some-boolean-expression AS okay \gset
>>   \if :okay
>
> Yes, the question was whether we leave it as that for v10,
> or if it's worth a last-minute improvement for usability,
> assuming it's doable, similarly to what $subject does to backtick
> expansion for external evaluation.

My 0.02 € about server-side expressions: ISTM that there is nothing 
obvious/easy to do to include these:
 - how would it work, both with \set ... and \if ...?
 - should it be just simple expressions or may it allow complex   queries?
 - how would error detection and handling work from a script?
 - should it have some kind of continuation, as expressions are   likely to be longer than a constant?
 - how would they interact with possible client-side expressions?
   (on this point, I think that client-side is NOT needed for "psql".    It makes sense for "pgbench" in a benchmarking
contextwhere the    client must interact with the server in some special meaningful    way, but for simple scripting
theperformance requirement and    logic is not the same, so server-side could be enough).
 

Basically quite a few questions which would not find an instantaneous 
answer and associated patch.

However I agree with you that there may be minimal usability things to add 
before 10, similar to Tom's backtick variable substitution.

Having some access to the client version as suggested by Pavel looks like 
a good idea for the kind of script which may rely on conditionals...

Maybe other things, not sure what, though. Maybe other client settings
could be exported as variables, but the version looks like the main which 
is currently missing.

Maybe a way to know what is the client current status? eg in transaction,
transaction has aborted, things like that?

-- 
Fabien.

Hello Corey,

> I'm anxious to help with these patches, but they seem a bit of a moving
> target. Happy to jump in and review as soon as we've settled on what should
> be done.

The "v3" I sent basically adds both client & server version numbers in 
client-side variables, basically same code as suggested by Pavel for the 
server version, and some documentation.

The questions are:
 - which version should be provided (10.0 100000 ...)
 - how should they be named?
   In v3 there is VERSION{,_NAME,_NUM} for client and   SERVER_VERSION_{NUM,NAME} or SVERSION_NUM suggested   by Pavel
forserver.
 
 - how desirable/useful is it to have this in 10?
   Despite that this was not submitted in the relevant CF...   I created an entry in the July one, but this is for 11.

-- 
Fabien.

Corey Huinker <corey.huinker@gmail.com> writes:
> Looking at #define STRINGIFY(), I got curious where else STRINGIFY was used:
> Without digging too deep, it seems like the redefinition wouldn't be
> harmful, but it might make sense to not use the name STRINGIFY() if only to
> avoid confusion with Solution.pm.

More to the point, we already have that.  See c.h:

#define CppAsString(identifier) #identifier
#define CppAsString2(x)            CppAsString(x)
        regards, tom lane

> src/tools/msvc/Solution.pm:s{PG_VERSION_STR "[^"]+"}{__STRINGIFY(x)
> #x\n#define __STRINGIFY2(z) __STRINGIFY(z)\n#define PG_VERSION_STR
> "PostgreSQL $self->{strver}$extraver, compiled by Visual C++ build "
> __STRINGIFY2(_MSC_VER) ", $bits-bit"};

Well, this is the same hack.

> Without digging too deep, it seems like the redefinition wouldn't be
> harmful, but it might make sense to not use the name STRINGIFY() if only to
> avoid confusion with Solution.pm.

It is the usual name for these macro. What would you suggest?

>>  - how desirable/useful is it to have this in 10?
>
> Extensions and extension-ish packages will love the _NUM vars.

Hmmmm. I'm afraid pg extension scripts (for CREATE EXTENSION) are not 
executed through psql, but server side directly, so there is not much 
backslash-command support.

> There's a lesser need for the _NAME vars.

I put them more for error reporting, eg:
  \if :VERSION_NUM < 110000    \echo :VERSION_NAME is not supported, should be at least 11.0    \q  \endif

-- 
Fabien.

> More to the point, we already have that.  See c.h:
>
> #define CppAsString(identifier) #identifier
> #define CppAsString2(x)            CppAsString(x)

Thanks for the pointer. I grepped for them without success. Here is a v4.

-- 
Fabien

Fabien COELHO <coelho@cri.ensmp.fr> writes:
>>> - how desirable/useful is it to have this in 10?

>> Extensions and extension-ish packages will love the _NUM vars.

> Hmmmm. I'm afraid pg extension scripts (for CREATE EXTENSION) are not 
> executed through psql, but server side directly, so there is not much 
> backslash-command support.

Yeah.

>> There's a lesser need for the _NAME vars.

> I put them more for error reporting, eg:

>    \if :VERSION_NUM < 110000
>      \echo :VERSION_NAME is not supported, should be at least 11.0
>      \q
>    \endif

I kinda feel like we're getting ahead of ourselves here, in that
the above is not going to do what you want until we have some kind
of expression eval capability built into psql.  You pointed out that
"\if false" can be used to reject pre-v10 psqls, but what about
rejecting v10?  ISTM that if we leave this out until there's something
that can do something useful with it, then something along the lines of

\if false -- pre-v10, complain and die
\endif
\if not defined VERSION_NUM -- pre-v11, complain and die
\endif

would do the trick.  Of course, there are probably other ways to
do that, but my point is that you haven't made a case why we need
to put this in now rather than later.
        regards, tom lane

Hello Tom,

> about version numbers [...] Of course, there are probably other ways to 
> do that, but my point is that you haven't made a case why we need to put 
> this in now rather than later.

Indeed, I have not. What about having the ability to test for minor 
versions?
  \if false    -- pre 10.0    \q  \endif  SELECT :VERSION_NUM < 100002 AS minor_not_ok \gset  \if :minor_not_ok
\echoscript requires at least pg 10.2    \q  \endif
 

Otherwise it will wait for next CF. Note that the patch is pretty minor 
and straightforward, no need to spend much time on it.

-- 
Fabien.