Skip site navigation (1) Skip section navigation (2)

Re: bug in on_error_rollback !?

From: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
To: "Andrew Dunstan" <andrew(at)dunslane(dot)net>
Cc: "Bernd Helmle" <mailings(at)oopsware(dot)de>, "PGSQL Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: bug in on_error_rollback !?
Date: 2006-10-27 09:50:48
Message-ID: 65937bea0610270250k11f39c9amc5c15a75eaa16368@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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
>
>

In response to

Responses

pgsql-hackers by date

Next:From: dakotali kasapDate: 2006-10-27 09:57:57
Subject: Re: printing the query plan?
Previous:From: Zeugswetter Andreas ADI SDDate: 2006-10-27 09:30:52
Subject: Re: New CRC algorithm: Slicing by 8

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group