Re: PostgreSQL Gotchas

Tom Lane wrote:

>Stephen Frost <sfrost@snowman.net> writes:
>
>
>>Unquoted object names fold to lower case
>>  I don't really see this as too much of an issue, personally, but I do
>>  know some people have run into it.  The example they give seems a bit
>>  off tho, as I thought Oracle just folded to upper-case (in which case
>>  "table" != table in Oracle, but "table" = table in Postgres, kind of
>>  depends on how you look at it).  I'm not sure the spec says one way or
>>  another.
>>
>>
>
>Yeah, it does, and the spec sides with Oracle.  This is explained
>exactly in the manual:
>
>: Quoting an identifier also makes it case-sensitive, whereas unquoted
>: names are always folded to lower case. For example, the identifiers FOO,
>: foo, and "foo" are considered the same by PostgreSQL, but "Foo" and
>: "FOO" are different from these three and each other. (The folding of
>: unquoted names to lower case in PostgreSQL is incompatible with the SQL
>: standard, which says that unquoted names should be folded to upper
>: case. Thus, foo should be equivalent to "FOO" not "foo" according to the
>: standard. If you want to write portable applications you are advised to
>: always quote a particular name or never quote it.)
>
>
Any chance of adding a configuration option for future versions in order
to set case folding behavior?  It seems that PostgreSQL has really
attempted to be as standards-compliant as possible and this is one area
where improvement could be made without breaking backward compatibility...

We already have configuration options for setting whether NULL = NULL
returns TRUE or NULL, and a few other legacy options for preventing past
non-standard behavior from breaking compatibility with current versions.

>
>
>
>>RANDOM() failures
>>  This looks like an interesting problem, and might be a bug to be
>>  fixed.  I think it depends on the what the SQL specification says for
>>  this case, if it says anything at all, as to if it should be
>>  considered a bug or not.  On the surface it certainly does look like
>>  an oddity though.
>>
>>
>
>Well, it's surely legitimate material as a "gotcha".  The example is
>taken from
>http://archives.postgresql.org/pgsql-general/2004-11/msg01375.php
>and the "previous discussion" referred to is this thread:
>http://archives.postgresql.org/pgsql-hackers/2004-10/msg00082.php
>
>As you can see, there wasn't a lot of agreement that we ought to change
>it.  Arguably, if we did change it we'd get ripped for the "gotcha" of
>poor optimization when the user forgets to mark nonvolatile functions
>properly.  (Personally, though, I'm in favor of tightening it up.)
>
>
It all depends on which is worse?  Incorrect results or bad performance
on poorly written functions?  I would side with bad performance any day
if it was user error and could be fixed with the help of the people on
the PERFORM list.  If we didn't believe that, we would all be using
MySQL, right?

Best Wishes,
Chris Travers
Metatron Technology Consulting