Thread: bug in on_error_rollback !?

bug in on_error_rollback !?

From
"Gurjeet Singh"
Date:
<span style="font-family: courier new,monospace;">I was thinking of recommending this to someone, but wanted to try it
onmy own first; good thing that I did. I think it is broken as of now.<br /><br />I assume that the error thrown for
'select1', inside a transaction, with 'on_error_rollback on', is not supposed to raise it's head !!! </span><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">Or am I missing something?</span><br style="font-family: courier
new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">postgres=#select version();</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">                                                 version</span><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier
new,monospace;">--------------------------------------------------------------------------------------------------------
</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> PostgreSQL
8.2beta1on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw special 20030504-1)</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">(1 row)</span><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">postgres=# begin; </span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;">BEGIN</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">postgres=# \set on_error_rollback on </span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">postgres=# select a;</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;"> ERROR:  column "a" does not exist</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">LINE 1: select
a;</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">
              ^</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">postgres=#select 1;</span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;"> ERROR:  current transaction is aborted, commands ignored until end of transaction
block</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">postgres=#end;</span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;">ROLLBACK</span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;">postgres=# select a;</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">ERROR:  column "a" does not exist</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">LINE 1: select a;</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;">               ^</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">postgres=# select 1;
</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> ?column?</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">----------</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">       1</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">(1row) </span><br style="font-family: courier new,monospace;" /><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">postgres=#</span><br style="font-family: courier
new,monospace;"/><br clear="all" style="font-family: courier new,monospace;" /><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">-- </span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">gurjeet[.singh]@ EnterpriseDB.com</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">singh.gurjeet@{ gmail |
hotmail| yahoo }.com </span> 

Re: bug in on_error_rollback !?

From
Bernd Helmle
Date:
--On Freitag, Oktober 27, 2006 11:00:07 +0530 Gurjeet Singh 
<singh.gurjeet@gmail.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@[local]:bernd #= BEGIN;
BEGIN
bernd@[local]:bernd #*= \set ON_ERROR_ROLLBACK
bernd@[local]:bernd #*= SELECT a;
ERROR:  column "a" does not exist
LINE 1: SELECT a;              ^
bernd@[local]:bernd #*= SELECT 1;?column?
----------       1
(1 row)

--  Thanks
                   Bernd


Re: bug in on_error_rollback !?

From
"Gurjeet Singh"
Date:
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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

On 10/27/06, Bernd Helmle <mailings@oopsware.de> wrote:
You have to specify the parameter in upper case:

bernd@[local]:bernd #*= \set ON_ERROR_ROLLBACK

Re: bug in on_error_rollback !?

From
"Andrew Dunstan"
Date:
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



Re: bug in on_error_rollback !?

From
"Gurjeet Singh"
Date:
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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

On 10/27/06, Andrew Dunstan <andrew@dunslane.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




Re: bug in on_error_rollback !?

From
"Greg Sabino Mullane"
Date:
-----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@turnstep.com
PGP Key: 0x14964AC8 200610270914
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

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

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




Re: bug in on_error_rollback !?

From
Neil Conway
Date:
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@postgresql.org/msg41273.html




Re: bug in on_error_rollback !?

From
Peter Eisentraut
Date:
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/


Re: bug in on_error_rollback !?

From
"Gurjeet Singh"
Date:


On 10/27/06, Peter Eisentraut <peter_e@gmx.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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

Re: bug in on_error_rollback !?

From
Richard Troy
Date:
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.gurjeet@gmail.com>
> To: Peter Eisentraut <peter_e@gmx.net>
> Cc: pgsql-hackers@postgresql.org, Andrew Dunstan <andrew@dunslane.net>,
>      Bernd Helmle <mailings@oopsware.de>
> Subject: Re: [HACKERS] bug in on_error_rollback !?
>
> On 10/27/06, Peter Eisentraut <peter_e@gmx.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@ScienceTools.com, http://ScienceTools.com/



Re: bug in on_error_rollback !?

From
Robert Treat
Date:
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


Re: bug in on_error_rollback !?

From
"Gurjeet Singh"
Date:
On 10/29/06, Richard Troy <rtroy@sciencetools.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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

Re: bug in on_error_rollback !?

From
"Jim C. Nasby"
Date:
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@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)