bug in on_error_rollback !?

I was thinking of recommending this to someone, but wanted to try it on my
own first; good thing that I did. I think it is broken as of now.

I assume that the error thrown for 'select 1', inside a transaction, with
'on_error_rollback on', is not supposed to raise it's head !!!

Or am I missing something?

postgres=# select version();
version

--------------------------------------------------------------------------------------------------------
PostgreSQL 8.2beta1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.2.3(mingw special 20030504-1)
(1 row)

postgres=# begin;
BEGIN
postgres=# \set on_error_rollback on
postgres=# select a;
ERROR: column "a" does not exist
LINE 1: select a;
^
postgres=# select 1;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
postgres=# end;
ROLLBACK
postgres=# select a;
ERROR: column "a" does not exist
LINE 1: select a;
^
postgres=# select 1;
?column?
----------
1
(1 row)

postgres=#

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com

--On Freitag, Oktober 27, 2006 11:00:07 +0530 Gurjeet Singh
<singh(dot)gurjeet(at)gmail(dot)com> wrote:

> I was thinking of recommending this to someone, but wanted to try it on
> my own first; good thing that I did. I think it is broken as of now.
>
> I assume that the error thrown for 'select 1', inside a transaction, with
> 'on_error_rollback on', is not supposed to raise it's head !!!
>
> Or am I missing something?
>

[...]

You have to specify the parameter in upper case:

bernd(at)[local]:bernd #= BEGIN;
BEGIN
bernd(at)[local]:bernd #*= \set ON_ERROR_ROLLBACK
bernd(at)[local]:bernd #*= SELECT a;
ERROR: column "a" does not exist
LINE 1: SELECT a;
^
bernd(at)[local]:bernd #*= SELECT 1;
?column?
----------
1
(1 row)

--
Thanks

Bernd

Thanks ...

but case-sensitivity (even without quotes or d-quotes) is the last thing I'd
expect in a SQL compliant software. This was highly unexpected. May I dare
to raise a bug to eliminate case-sensitivity in psql variables? Will I get
support from the community?

Regards,

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com

On 10/27/06, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>
> You have to specify the parameter in upper case:
>
> bernd(at)[local]:bernd #*= \set ON_ERROR_ROLLBACK
>

Gurjeet Singh wrote:
> Thanks ...
>
> but case-sensitivity (even without quotes or d-quotes) is the last thing
> I'd
> expect in a SQL compliant software. This was highly unexpected. May I dare
> to raise a bug to eliminate case-sensitivity in psql variables? Will I get
> support from the community?
>

psql variables and commands are not SQL, and are case sensitive. For
example, \ds and \dS are not at all the same.

This is documented clearly on the psql man page, so it is simply not a
bug, and changing this would probably break lots of legacy scripts.

cheers

andrew

I understand that psql commands are not SQL, but since psql is used to
interact with SQL database, then the assumption of case-insensitivity in
psql also comes naturally to the user.

\ds and \dS are commands (first token on the line) so it is acceptable that
they be case-sensitive. But a command's parameters/arguments should not be
case sensitive, unless quoted.

If it is documented that psql commands are case sensitive, then I would like
to point to an ambiguity:

I have two tables:
postgres=# create table "t"( a int);
CREATE TABLE
postgres=# create table "T"( b int);
CREATE TABLE

Now, according to your statement, the \d command should report about two
different tables in the following first two commands:

postgres=# \d t
Table "public.t"
Column | Type | Modifiers
--------+---------+-----------
a | integer |

postgres=# \d T
Table "public.t"
Column | Type | Modifiers
--------+---------+-----------
a | integer |

postgres=# \d "T"
Table "public.T"
Column | Type | Modifiers
--------+---------+-----------
b | integer |

postgres=#

But, as you can see, I had to d-quote T to get the intended result. IMHO, \d
is behaving correctly, and other '\' commands should treat their
parameters/arguments likewise.

For the more inquisitive (I know you'll go and try the -E switch to psql),
here's what's sent to the backend for the three different \d commands:

postgres=# \d t
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^t$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

postgres=# \d T
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^t$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

postgres=# \d "T"
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^T$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

postgres=#

Regards,

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com

On 10/27/06, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> Gurjeet Singh wrote:
> > Thanks ...
> >
> > but case-sensitivity (even without quotes or d-quotes) is the last thing
> > I'd
> > expect in a SQL compliant software. This was highly unexpected. May I
> dare
> > to raise a bug to eliminate case-sensitivity in psql variables? Will I
> get
> > support from the community?
> >
>
>
> psql variables and commands are not SQL, and are case sensitive. For
> example, \ds and \dS are not at all the same.
>
> This is documented clearly on the psql man page, so it is simply not a
> bug, and changing this would probably break lots of legacy scripts.
>
> cheers
>
> andrew
>
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> This is documented clearly on the psql man page, so it is simply not a
> bug, and changing this would probably break lots of legacy scripts.

In a general sense, perhaps, but in this *particular* case, I don't
see what harm allowing "\set on_error_rollback" would be: it certainly
won't break any existing scripts. I wrote this feature (but someone else
chose the name!) and I still occasionally write it lowercase and wonder
why it isn't working. :)

Perhaps even allowing all of the \set commands to be case-insensitive
may be a good idea?

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200610270914
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFFQgawvJuQZxSWSsgRAvBKAKDb4gcpRsjpbxo7SKJwmu5y7Bo48QCeIMao
DZFuEIPJaPNyv/TRGNH6shc=
=/n5U
-----END PGP SIGNATURE-----

On Fri, 2006-10-27 at 03:50 -0500, Andrew Dunstan wrote:
> psql variables and commands are not SQL, and are case sensitive. For
> example, \ds and \dS are not at all the same.
>
> This is documented clearly on the psql man page, so it is simply not a
> bug

It may be documented, but \set still has a terrible user interface.
There is no feedback about whether a variable has been successfully
changed. Updates to non-existent variables are silently accepted, and
references to non-existent variables do not result in an error. I've
complained about this before[1], but there wasn't a consensus on how to
improve this behavior (given the need for backward compatibility).

-Neil

[1] http://mail-archive.com/pgsql-hackers(at)postgresql(dot)org/msg41273.html

Gurjeet Singh wrote:
> \ds and \dS are commands (first token on the line) so it is
> acceptable that they be case-sensitive. But a command's
> parameters/arguments should not be case sensitive, unless quoted.

This distinction has not basis in SQL syntax.

> If it is documented that psql commands are case sensitive, then I
> would like to point to an ambiguity:

If it helps, compare this to ECPG: The C parts follow the syntax rules
of C, the SQL parts follow the syntax rules of SQL. In psql, the psql
parts follow the syntax rules of psql, the SQL parts follow the syntax
rules of SQL. The syntax rules of psql in turn are inspired by Unix
shells, sort of because psql is used that way. (Surely one wouldn't
want the argument to \i be case-insensitive?)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 10/27/06, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>
> In psql, the psql
> parts follow the syntax rules of psql, the SQL parts follow the syntax
> rules of SQL. The syntax rules of psql in turn are inspired by Unix
> shells, sort of because psql is used that way. (Surely one wouldn't
> want the argument to \i be case-insensitive?)

A very good reasoning... I completely agree...

But you'd also agree that since the psql variables can (and most often they
are) used in SQL satements, we should consider making atleast \set case
insensitive!

postgres=# \set x 1
postgres=# select :x;
?column?
----------
1
(1 row)

postgres=# select :X;
ERROR: syntax error at or near ":"
LINE 1: select :X;
^
postgres=#

<Greg>
what harm allowing "\set on_error_rollback" would be: it certainly
won't break any existing scripts.
...
I wrote this feature (but someone else
chose the name!) and I still occasionally write it lowercase and wonder
why it isn't working. :)
</Greg>

I agree, we can't make every '\' command case-insensitive, but a few,
where it makes absolute sense, should be subject to reconsideration. We have
the choice of making it more user-friendly, and less confusing.

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com

Gurjeet,

I see that the question of case sensitivity in psql is still being
discussed. "I don't have a dog in that fight," but thought I might make a
suggestion. To wit:

I propose you adopt the standard that I personally have adopted eons ago -
literally perhaps 20 years ago - and has by now saved me many days of
time, I'm sure; ALWAYS presume case sensitivity and code _exactly_ that
way every time. (And develop you're own capitalization standard, too, so
you'll always know which way it goes.) You'll never be disappointed that
way and you won't create hidden bugs. If you want to keep arguing that
Postgres should change to meet your expectations, fine, and if it changes,
great for you, but you'll just have the same problem someday with some
other package - better you change your habits instead!

Richard

On Sat, 28 Oct 2006, Gurjeet Singh wrote:

> Date: Sat, 28 Oct 2006 20:01:00 +0530
> From: Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
> To: Peter Eisentraut <peter_e(at)gmx(dot)net>
> Cc: pgsql-hackers(at)postgresql(dot)org, Andrew Dunstan <andrew(at)dunslane(dot)net>,
> Bernd Helmle <mailings(at)oopsware(dot)de>
> Subject: Re: [HACKERS] bug in on_error_rollback !?
>
> On 10/27/06, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> >
> > In psql, the psql
> > parts follow the syntax rules of psql, the SQL parts follow the syntax
> > rules of SQL. The syntax rules of psql in turn are inspired by Unix
> > shells, sort of because psql is used that way. (Surely one wouldn't
> > want the argument to \i be case-insensitive?)
>
>
> A very good reasoning... I completely agree...
>
> But you'd also agree that since the psql variables can (and most often they
> are) used in SQL satements, we should consider making atleast \set case
> insensitive!
>
> postgres=# \set x 1
> postgres=# select :x;
> ?column?
> ----------
> 1
> (1 row)
>
> postgres=# select :X;
> ERROR: syntax error at or near ":"
> LINE 1: select :X;
> ^
> postgres=#
>
> <Greg>
> what harm allowing "\set on_error_rollback" would be: it certainly
> won't break any existing scripts.
> ...
> I wrote this feature (but someone else
> chose the name!) and I still occasionally write it lowercase and wonder
> why it isn't working. :)
> </Greg>
>
> I agree, we can't make every '\' command case-insensitive, but a few,
> where it makes absolute sense, should be subject to reconsideration. We have
> the choice of making it more user-friendly, and less confusing.
>
>
>
>

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy(at)ScienceTools(dot)com, http://ScienceTools.com/

On Friday 27 October 2006 09:19, Greg Sabino Mullane wrote:
> > This is documented clearly on the psql man page, so it is simply not a
> > bug, and changing this would probably break lots of legacy scripts.
>
> In a general sense, perhaps, but in this *particular* case, I don't
> see what harm allowing "\set on_error_rollback" would be: it certainly
> won't break any existing scripts. I wrote this feature (but someone else
> chose the name!) and I still occasionally write it lowercase and wonder
> why it isn't working. :)
>
> Perhaps even allowing all of the \set commands to be case-insensitive
> may be a good idea?

\typo

;-)

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On 10/29/06, Richard Troy <rtroy(at)sciencetools(dot)com> wrote:
>
> ALWAYS presume case sensitivity and code _exactly_ that
> way every time.

That makes a lot of sense for a C/C++/Java/(any other case-sens. lang.)
developer. And perhaps SQL developers too should adopt it; but sadly (for
nay sayers), the SQL standard doesn't require them to (and for good reason);
and as is said in the PG community very often, 'who are we to question the
standard?'.

(And develop you're own capitalization standard, too, so
> you'll always know which way it goes.) You'll never be disappointed that
> way and you won't create hidden bugs.

I'd be at the losing end all the time that way; 'coz every package has it's
own coding style, (take PG for example), and I'd hate to see myself, or
anyone else, to dirty the code by using a new, although consistent in
itself, coding style. In a package developed in a case-sensitive language,
I'd always want to code the way the earlier devils (I mean devels) had.

Seems like its time to crawl back under my rock.

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com

On Fri, Oct 27, 2006 at 01:19:25PM -0000, Greg Sabino Mullane wrote:
> > This is documented clearly on the psql man page, so it is simply not a
> > bug, and changing this would probably break lots of legacy scripts.
>
> In a general sense, perhaps, but in this *particular* case, I don't
> see what harm allowing "\set on_error_rollback" would be: it certainly
> won't break any existing scripts. I wrote this feature (but someone else
> chose the name!) and I still occasionally write it lowercase and wonder
> why it isn't working. :)
>
> Perhaps even allowing all of the \set commands to be case-insensitive
> may be a good idea?

The problem is that people my be depending on the case-sensitivity in
their scripts...

\set ACounter 1
\set aCounter 2

Of course, they're just asking for trouble, but suddenly making psql
variables case-insensitive would break that code.

Perhaps a good compromise would be adopting SQL case syntax (ie: you
have to wrap in double quotes to preserve case).
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)