Thread: psql variables

Recently, I was surprised to learn that psql variables are case
sensitive. Furthermore, there is no error when one attempts to '\set'
a non-existent variable (arguably for good reason: I suppose it's too
late now to get rid of user-defined psql variables). That leads to
confusing situations like the following:

nconway=# \set autocommit off
nconway=# create table def (a int, b int);
CREATE TABLE
nconway=# abort;
WARNING:  there is no transaction in progress
ROLLBACK

One possible justification for this behavior is that it allows a
simple naming convention to distinguish user-defined variables from
psql-internal variables. Is that the case?

If so, can't we achieve that aim via some other means? For example, by
requiring a common prefix for psql-internal variables, akin to 'pg_*'
for system catalogs prior to 7.3.

While we're on the subject, there are some other design choices in
this area that seem a bit unfortunate. For example, we don't error out
on references to undefined variables (so "\echo :x" could be an
undefined variable or a variable that is set to the empty string, it
isn't possible to tell). Also, the user can go ahead and overwrite the
value of built-in variables like HOST and PORT, which doesn't seem
like a very useful feature.

Now that 7.4's psql includes some genuinely useful psql variables
like AUTOCOMMIT and VERBOSITY, it seems likely this feature will see
more usage, so it would be nice to clean it up a bit.

-Neil

Neil Conway <neilc@samurai.com> writes:
> Recently, I was surprised to learn that psql variables are case
> sensitive. Furthermore, there is no error when one attempts to '\set'
> a non-existent variable (arguably for good reason: I suppose it's too
> late now to get rid of user-defined psql variables).

[ blinks... ]  This is historical revisionism.  Psql variables were
invented to provide user-defined variables; it is the predefined ones
that are a wart added to the mechanism, not vice versa.

You could argue it either way about whether their names should be
case-sensitive or not.  I think it's too late to revisit that choice
given that it's not 100% wrong.

> While we're on the subject, there are some other design choices in
> this area that seem a bit unfortunate. For example, we don't error out
> on references to undefined variables (so "\echo :x" could be an
> undefined variable or a variable that is set to the empty string, it
> isn't possible to tell).

That one bothers me a bit too; perhaps Peter can give a justification.

> Also, the user can go ahead and overwrite the
> value of built-in variables like HOST and PORT, which doesn't seem
> like a very useful feature.

Again, you're letting the tail wag the dog.  If we did that then any
addition of a new built-in variable would risk breaking existing user
scripts that happened to use that name as an ordinary variable.

It wouldn't be a bad idea to document some restriction on which names
might become predefined variables in future.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:
> [ blinks... ]  This is historical revisionism.  Psql variables were
> invented to provide user-defined variables; it is the predefined
> ones that are a wart added to the mechanism, not vice versa.

The historical origins of the feature are no excuse for its
deficiencies.

> Again, you're letting the tail wag the dog.  If we did that then any
> addition of a new built-in variable would risk breaking existing user
> scripts that happened to use that name as an ordinary variable.

A name conflict with a newly-added variable is very likely to cause
problems in any case, if the new variable does anything at all. For
example, if your pre-7.4 used "AUTOCOMMIT" to mean something other
than what 7.4 thinks it means, your script is probably broken.

On second thought, there's another alternative. Rather than improving
\set, we could invent a new mechanism for setting psql-internal
variables, and leave the \set stuff to user-defined variables. That
way we can define whatever semantics we'd like for the new command
without hindering backward compatibility. We'd need to ensure that
using \set to modify existing psql variable names still works, but
that shouldn't be too difficult.

One downside is that we'd now have *three* different sets of variables
in psql alone (\set, \pset, and \sys_set or whatever it might be
named). We might improve that by amalgamating \pset into \sys_set,
because AFAICS there is no reason for the former to exist.

Comments?

-Neil

Neil Conway <neilc@samurai.com> writes:
> The historical origins of the feature are no excuse for its
> deficiencies.

On the other hand, the alleged deficiencies are not bad enough to
justify making non-backwards-compatible changes.  If we were getting
routine complaints from the field I might be willing to break things in
order to address the issue.  But we're not --- AFAIR you are the first
to raise the point at all.

> On second thought, there's another alternative. Rather than improving
> \set, we could invent a new mechanism for setting psql-internal
> variables, and leave the \set stuff to user-defined variables.

I was toying with the idea of inventing a "\declare foo" command
(which would error out if the variable foo already exists), along with
an optional setting that makes psql complain about either use of or
assignment to an undeclared variable.  As long as latter setting is
false the behavior is backwards-compatible.  By setting it true you get
the sort of error checking you're after.
        regards, tom lane

Neil Conway wrote:
> Recently, I was surprised to learn that psql variables are case
> sensitive.

like shell variables

> Furthermore, there is no error when one attempts to '\set'
> a non-existent variable

Well, how are you going to set a new variable if not this way?

> One possible justification for this behavior is that it allows a
> simple naming convention to distinguish user-defined variables from
> psql-internal variables. Is that the case?

Yes, upper-case names are reserved.  This is documented.

> While we're on the subject, there are some other design choices in
> this area that seem a bit unfortunate. For example, we don't error
> out on references to undefined variables

like shell variables

> Also, the user can go ahead and overwrite
> the value of built-in variables like HOST and PORT

like shell variables

Peter Eisentraut <peter_e@gmx.net> writes:
> Neil Conway wrote:
>> Recently, I was surprised to learn that psql variables are case
>> sensitive.
>
> like shell variables

What relevance does that have? Shell variables may or may not share
the same design flaws that psql variables do, but I don't see that as
a justification for not changing or improving the current behavior of
psql variables.

Perhaps you're suggesting shell variables were used as the design
model for psql's variables (although I can't be sure, you didn't
elaborate). If so, what I'm saying is that this model is not very
friendly for setting psql-internal options, and we'd be better
changing it as I've elaborated on elsewhere. Do you agree?

-Neil

Neil Conway wrote:
> Perhaps you're suggesting shell variables were used as the design
> model for psql's variables (although I can't be sure, you didn't
> elaborate). If so, what I'm saying is that this model is not very
> friendly for setting psql-internal options, and we'd be better
> changing it as I've elaborated on elsewhere. Do you agree?

Shell variables did serve as a design model, mostly because I found it 
better to use *some* model rather than inventing behavior out of thin 
air.  Consequently, I am sort of biased on this.

Peter Eisentraut <peter_e@gmx.net> writes:
> Shell variables did serve as a design model, mostly because I found it 
> better to use *some* model rather than inventing behavior out of thin 
> air.  Consequently, I am sort of biased on this.

It does seem worth pointing out that shell variables have acted the way
they do for a long time.  I haven't heard all that many people
complaining about it, so I'm unconvinced that the behavior is broken.
I'm definitely against breaking backwards compatibility to improve it
in psql.

I might be willing to support an optional (not-default) mode with more
error checking, though.  What did you think of the "\declare foo" idea?
        regards, tom lane