Thread: bug in on_error_rollback !?
<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>
--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
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,
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:
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
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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
\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
-----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-----
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
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@gmx.net> wrote:
A very good reasoning... I completely agree...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?)
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
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/
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@sciencetools.com> wrote:
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?'.
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
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
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)