On 10/16/2005 12:40 PM, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
Actually, perhaps an even more restricted version would be better.
Lowercase quoted identifiers only if they are all uppercase. So then:
No, I think the original proposal was better. This one doesn't fix
Jan Wieck [EMAIL PROTECTED] writes:
What is bad about leaving pg_catalog all lower case and expect everyone
to query the catalog quoted?
The fact that it will break every nontrivial client currently in
existence. Those quotes aren't there in the clients and we can't
suddenly mandate them to
On 10/17/2005 10:16 AM, Tom Lane wrote:
Jan Wieck [EMAIL PROTECTED] writes:
What is bad about leaving pg_catalog all lower case and expect everyone
to query the catalog quoted?
The fact that it will break every nontrivial client currently in
existence. Those quotes aren't there in the
Jan Wieck wrote:
On 10/17/2005 10:16 AM, Tom Lane wrote:
Jan Wieck [EMAIL PROTECTED] writes:
What is bad about leaving pg_catalog all lower case and expect
everyone to query the catalog quoted?
The fact that it will break every nontrivial client currently in
existence. Those quotes
Tom Lane wrote:
Chris Travers [EMAIL PROTECTED] writes:
I make the following assumptions:
1) All backend references to identifiers are treated as quoted by the
backend as mentioned in a previous thread.
2) Same with official clients like psql.
3) We don't guarantee complete
* Tom Lane ([EMAIL PROTECTED]) wrote:
Matthew matthew@zeut.net writes:
Is it worth having a GUC variable that enables / disable this?
That's a given, I think. We're certainly not going to make smash-to-
lower-case the only available behavior.
A GUC variable for this would be quite nice..
At 11:43 AM 10/15/2005 -0400, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
Seems to me we'd be better off creating an option
lowercase_quoted_anyway which solves everything, at the expense of
being even less compliant.
I think that'll be a good option to have.
paying
On Sun, Oct 16, 2005 at 10:00:25PM +0800, Lincoln Yeoh wrote:
Martijn van Oosterhout kleptog@svana.org writes:
Seems to me we'd be better off creating an option
lowercase_quoted_anyway which solves everything, at the expense of
being even less compliant.
I think that'll be a good option
For the record, I am trying to flesh out my thoughts on this issue. I
am not asking Tom to do this BTW :-) because I am sure he has more
pressing contributions to make. However, because it can be an issue for
those migrating from, say, Oracle, it would be good to have a discussion
on what
Jan Wieck wrote:
On 10/13/2005 2:40 PM, Tom Lane wrote:
Chris Travers [EMAIL PROTECTED] writes:
So basically, the problem is that any fix for case folding would
touch a fair bit of code and possibly cause other problems.
However, I haven't seen anyone worry about performance issues in
Martijn van Oosterhout kleptog@svana.org writes:
Actually, perhaps an even more restricted version would be better.
Lowercase quoted identifiers only if they are all uppercase. So then:
No, I think the original proposal was better. This one doesn't fix
things for the lusers who think MixedCase
On Sat, Oct 15, 2005 at 05:46:03PM -0700, Chris Travers wrote:
This is largely what I have been suggesting. However, you still have
two (small) problems that could be solved fairly easily I think. Take
Tom's example:
SELECT MAX(relpages) FROM pg_class;
Now, I am perfectly happy to
On Sun, Oct 16, 2005 at 12:40:25PM -0400, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
Actually, perhaps an even more restricted version would be better.
Lowercase quoted identifiers only if they are all uppercase. So then:
No, I think the original proposal was better.
Tom Lane wrote:
Although ... it's true that there seem to be very few apps relying on
case sensitivity per se, ie, expecting Foo and foo to be different.
The complaints that I can remember were about programs that expected
FOO and FOO (not quoted) to be the same. So always-smash-to-lower-
case
Tom Lane wrote:
Note to implementor: In 'SELECT 1 as Title', the quoted string
should not be lowercased, even if you are lowercasing everything
else...
You don't get to have that, I think, because the lexer is not context
aware. It's not clear to me why it's a good idea anyway.
This is
Martijn van Oosterhout kleptog@svana.org writes:
Okay, how do we refer to the RI_FKey_cascade_del function then? Or
trigger RI_ConstraintTrigger_2204908. Using $$ quoting? Invent
another quoting style?
Yeah, I was wondering about those myself. Probably we could just change
their names to
Peter Eisentraut [EMAIL PROTECTED] writes:
I guess you could get around that if you leave the case-folding in the
lexer as is but instead make the nameeq function case insensitive.
If you do that then you have to nail down the behavior at initdb time
(else flipping the switch will corrupt
Matthew wrote:
Tom Lane wrote:
Although ... it's true that there seem to be very few apps relying on
case sensitivity per se, ie, expecting Foo and foo to be different.
The complaints that I can remember were about programs that expected
FOO and FOO (not quoted) to be the same. So
Matthew matthew@zeut.net writes:
Is it worth having a GUC variable that enables / disable this?
That's a given, I think. We're certainly not going to make smash-to-
lower-case the only available behavior.
One issue we might want to reflect on is how easy it should be to change
the variable's
Martijn van Oosterhout wrote:
On Sat, Oct 15, 2005 at 05:46:03PM -0700, Chris Travers wrote:
This is largely what I have been suggesting. However, you still have
two (small) problems that could be solved fairly easily I think. Take
Tom's example:
SELECT MAX(relpages) FROM pg_class;
Chris Travers [EMAIL PROTECTED] writes:
I make the following assumptions:
1) All backend references to identifiers are treated as quoted by the
backend as mentioned in a previous thread.
2) Same with official clients like psql.
3) We don't guarantee complete backward compatibility in the
Since nobody has told me why my proposal doesn't work, I figure I will
point out that it does solve this problem nicely. However, it makes the
assumptions that we treat the identifiers as quoted when the backend
uses them to look them up in the system catalogs. Also I am rethinking
a few
On Fri, Oct 14, 2005 at 10:34:53AM -0400, Jan Wieck wrote:
On 10/13/2005 2:40 PM, Tom Lane wrote:
Since the end reward for all this work would be having to read CATALOGS
WRITTEN IN ALL UPPER CASE, none of the key developers seem very
interested ...
Just an idea ...
if the release that
Martijn van Oosterhout kleptog@svana.org writes:
The main problem I see is that the most people who are complaining are
people who create their tables as MixedCaseQuoted and then refer to
them as Unquoted. Even following the SQL standard precisely will not
fix this, given that behaviour is
Tom Lane wrote:
Although ... it's true that there seem to be very few apps relying on
case sensitivity per se, ie, expecting Foo and foo to be
different. The complaints that I can remember were about programs
that expected FOO and FOO (not quoted) to be the same. So
always-smash-to-lower-
Greg Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
If you write, say,
select max(relpages) from pg_class;
and the lexer thinks that it should fold unquoted identifiers to upper
case, then the catalog entries defining these names had better read
PG_CLASS, RELPAGES, and MAX, not
Tom Lane [EMAIL PROTECTED] writes:
If you write, say,
select max(relpages) from pg_class;
and the lexer thinks that it should fold unquoted identifiers to upper
case, then the catalog entries defining these names had better read
PG_CLASS, RELPAGES, and MAX, not the lower-case
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
and the lexer thinks that it should fold unquoted identifiers to upper
case, then the catalog entries defining these names had better read
PG_CLASS, RELPAGES, and MAX, not the lower-case names they contain
today.
Well
Tom Lane [EMAIL PROTECTED] writes:
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
and the lexer thinks that it should fold unquoted identifiers to upper
case, then the catalog entries defining these names had better read
PG_CLASS, RELPAGES, and MAX, not the
On 10/13/2005 2:40 PM, Tom Lane wrote:
Chris Travers [EMAIL PROTECTED] writes:
So basically, the problem is that any fix for case folding would touch a
fair bit of code and possibly cause other problems. However, I haven't
seen anyone worry about performance issues in such a fix, just that it
Greg Stark [EMAIL PROTECTED] writes:
So two identifiers match if either is an unquoted identifier and they match
case insensitively. Or if both are quoted and they match case sensitively.
Which part of adhere to the standard are you failing to get? The
standard is 100% clear about what it
Tom Lane [EMAIL PROTECTED] writes:
Greg Stark [EMAIL PROTECTED] writes:
So two identifiers match if either is an unquoted identifier and they match
case insensitively. Or if both are quoted and they match case sensitively.
Which part of adhere to the standard are you failing to get? The
Tom Lane wrote:
Stephen Frost [EMAIL PROTECTED] 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
On Thu, 2005-10-13 at 13:09, Chris Travers wrote:
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
Chris Travers [EMAIL PROTECTED] writes:
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
Nikolay Samokhvalov wrote:
On 08/10/05, Martijn van Oosterhout kleptog@svana.org wrote:
On Sat, Oct 08, 2005 at 03:32:00PM +0400, Nikolay Samokhvalov wrote:
http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
Perhaps I'm wrong with some issues - any
Scott Marlowe wrote:
On Thu, 2005-10-13 at 13:09, Chris Travers wrote:
The problem, as explained to me when I floated this idea, is that the
CATALOGS are all in lower case, and many references to them are behind
the case folding mechanism (i.e. they get accessed by their lower case
names
Chris Travers [EMAIL PROTECTED] writes:
So basically, the problem is that any fix for case folding would touch a
fair bit of code and possibly cause other problems. However, I haven't
seen anyone worry about performance issues in such a fix, just that it
might be a fair bit of work.
More
Tom Lane wrote:
Chris Travers [EMAIL PROTECTED] writes:
So basically, the problem is that any fix for case folding would touch a
fair bit of code and possibly cause other problems. However, I haven't
seen anyone worry about performance issues in such a fix, just that it
might be a fair
Chris Travers [EMAIL PROTECTED] writes:
Tom Lane wrote:
Since the end reward for all this work would be having to read CATALOGS
WRITTEN IN ALL UPPER CASE, none of the key developers seem very
interested ...
Why would this be required?
If you write, say,
select max(relpages) from
On Thu, Oct 13, 2005 at 12:21:14PM -0700, Chris Travers wrote:
Since the end reward for all this work would be having to read CATALOGS
WRITTEN IN ALL UPPER CASE, none of the key developers seem very
interested ...
Why would this be required? If an individual developer wants to have a
Ok. here are some indepth thoughts after reviewing as many prior threads
as I could find on the archives.
Tom Lane wrote:
Chris Travers [EMAIL PROTECTED] writes:
Tom Lane wrote:
Since the end reward for all this work would be having to read CATALOGS
WRITTEN IN ALL UPPER CASE, none
Martijn van Oosterhout wrote:
On Thu, Oct 13, 2005 at 12:21:14PM -0700, Chris Travers wrote:
Since the end reward for all this work would be having to read CATALOGS
WRITTEN IN ALL UPPER CASE, none of the key developers seem very
interested ...
I think you're slightly missing the
I use PostgeSQL less than year. Before I worked with MS SQL Server
2000, MySQL 34, Oracle8i and Interbase. Also, I studied standards
SQL:1999 and SQL:2003. So, after switching to PostgreSQL I've
encountered with several things that seem strange to me. Of course,
several of them are simply not
On Sat, Oct 08, 2005 at 03:32:00PM +0400, Nikolay Samokhvalov wrote:
http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
Perhaps I'm wrong with some issues - any comments are welcome.
The only thing I can comment on is updatable views. You can make
updatable views
On 08/10/05, Martijn van Oosterhout kleptog@svana.org wrote:
On Sat, Oct 08, 2005 at 03:32:00PM +0400, Nikolay Samokhvalov wrote:
http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
Perhaps I'm wrong with some issues - any comments are welcome.
The only thing I
On Sat, Oct 08, 2005 at 06:05:29PM +0400, Nikolay Samokhvalov wrote:
On 08/10/05, Martijn van Oosterhout kleptog@svana.org wrote:
The only thing I can comment on is updatable views. You can make
updatable views using RULEs. The only thing is that they're not
*automatically* updateable.
Neil Conway wrote:
SELECT column alias, ...: this is a known issue. AFAIK it is not
easy to solve.
It's easy to solve. There's even a patch linked from the page.
I seem to remember that some (versions of some?) other SQL
implementation did not *allow* the AS there, complicating matters for
On 10/8/05, Nikolay Samokhvalov [EMAIL PROTECTED] wrote:
I use PostgeSQL less than year. Before I worked with MS SQL Server
2000, MySQL 34, Oracle8i and Interbase. Also, I studied standards
SQL:1999 and SQL:2003. So, after switching to PostgreSQL I've
encountered with several things that seem
On 10/7/05, Michael Fuhr [EMAIL PROTECTED] wrote:
On Thu, Oct 06, 2005 at 05:47:36PM -0500, Jeffrey Melloy wrote:
The only thing I could see actually being an issue is the random() one
and add missing from. The rest are trivial. The random() thing is
interesting, esoteric, and probably
CSN wrote:
Yep, I think the SQL spec says fold to uppercase. I'm
not sure why PostgreSQL folds to lowercase instead,
but if folding has to occur, I prefer lowercase.
I think preference was why lowercase was chosen many moons ago. It's
stayed that way because otherwise existing users would be
Gregory S. Williamson wrote:
[ re COUNT(*) ]
On Informix however it is blindingly fast, and can also be instantly
conjured with the dbaccess tool (Info/Table/Status). They might be
stashing this count somewhere, but it is not available when the table
is locked, as during a load. However
On Thu, Oct 06, 2005 at 07:29:44PM -0400, Tom Lane wrote:
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:
On Fri, Oct 07, 2005 at 08:44:34AM +0100, Richard Huxton wrote:
CSN wrote:
Yep, I think the SQL spec says fold to uppercase. I'm
not sure why PostgreSQL folds to lowercase instead,
but if folding has to occur, I prefer lowercase.
I think preference was why lowercase was chosen many moons
http://sql-info.de/postgresql/postgres-gotchas.html
Any comments from folks on the list ?
Cheers,
Aly.
--
Aly S.P Dharshi
[EMAIL PROTECTED]
A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject
Compared to MySQL ditching referential integrity because of a typo, I
would consider these 'gotchas' extremely minor, hence the reason I use
Postgresql not MySQL. Postgresql does what you expect from an
RDBMS system out of the box in 99.99% of cases. I don't have to
toggle things on special like,
This sure sounds like a flamewar bait email?
On Oct 6, 2005, at 11:07 AM, Aly S.P Dharshi wrote:
http://sql-info.de/postgresql/postgres-gotchas.html
Any comments from folks on the list ?
Cheers,
Aly.
--
Aly S.P Dharshi
[EMAIL PROTECTED]
A good speech is like a good dress
No flamewar here, I am just trying to see if opinions of others on this,
as Jim had posted a MySQL one, and that there was a PostgreSQL one, so I
wanted to see if these are valid, if they aren't then that site should be
updated to reflect this.
Cheers,
Aly.
On Thu, 6 Oct 2005, Gavin M. Roy
They're all valid, but most apply to versions 8.0 or 7.4 even, and
the others are pretty esoteric issues that you don't see often. The
missing from clause thing is likely to be the biggest surprise most
folks run into.
I find the supposed bad performance of aggregates is bunk. On my
Then wouldn't it be more appropriate to ask:
What's the status of the things listed on the PostgreSQL gotchas.
Are they bugs? Are they valid? Have the been resolved? What does
the community thing of those gotchas?
I personally don't take an open ended question like URL: Any
comments?
Okay that is a fair statement to make, hence restating:
- What is the status of those items listed on the PostgreSQL gotchas
- Are they bugs ?
- Are they valid statements ?
- If they are bugs are they resolved ?
- What does the PG community thing of this list of gotchas ?
On Thu, Oct 06, 2005 at 12:07:12PM -0600, Aly S.P Dharshi wrote:
http://sql-info.de/postgresql/postgres-gotchas.html
Any comments from folks on the list ?
The implicit from and mildly non-standard case folding are the only
two that approach being real issues people may stumble over, IMO. If
, 2005 11:40 AM
To: Aly S.P Dharshi
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL Gotchas
This sure sounds like a flamewar bait email?
On Oct 6, 2005, at 11:07 AM, Aly S.P Dharshi wrote:
http://sql-info.de/postgresql/postgres-gotchas.html
Any comments from folks
Sorry.
AlexOn 10/6/05, Gavin M. Roy [EMAIL PROTECTED] wrote:
This sure sounds like a flamewar bait email?On Oct 6, 2005, at 11:07 AM, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html
Any comments from folks on the list ? Cheers, Aly. -- Aly S.P Dharshi [EMAIL PROTECTED]
On 10/6/05, Aly S.P Dharshi aly ( dot ) dharshi (
at ) telus ( dot ) net wrote:
http://sql-info.de/postgresql/postgres-gotchas.html
Any comments from folks on the list ?
- It's a lot shorter than MySQL's gotchas list.
- 8 of the 13 are for versions of PostgreSQL = 8.1
- Of the
On Thu, Oct 06, 2005 at 12:07:12PM -0600, Aly S.P Dharshi wrote:
http://sql-info.de/postgresql/postgres-gotchas.html
Any comments from folks on the list ?
Several.
First, it looks to me like this is a pretty transparent attempt to
troll, so I'm not going to go there.
Second, if you actually
--- CSN [EMAIL PROTECTED] wrote:
- 8 of the 13 are for versions of PostgreSQL = 8.1
Doh!
- 8 of the 13 are for versions of PostgreSQL 8.1!
__
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
On Thu, 2005-06-10 at 12:07 -0600, Aly S.P Dharshi wrote:
http://sql-info.de/postgresql/postgres-gotchas.html
Any comments from folks on the list ?
SELECT column alias, ...: this is a known issue. AFAIK it is not easy
to solve.
Unquoted object names fold to lower case: this is intentional,
CSN wrote:
- I don't know enough about the UNICODE means
UTF-8 and RANDOM() failures to comment.
I'm hardly an expert, but I've done enough with unicode to know that you
can easily convert utf-8 to any other flavor of unicode you might want
to use. Though, why you'd want to use something
* Aly S.P Dharshi ([EMAIL PROTECTED]) wrote:
- What is the status of those items listed on the PostgreSQL gotchas
- Are they bugs ?
- Are they valid statements ?
- If they are bugs are they resolved ?
- What does the PG community thing of this list of gotchas ?
Stephen Frost wrote:
UNICODE means UTF-8
This is an interesting point. To be perfectly honest, it seems like
Postgres' UNICODE/UTF-8/etc support could stand to be somewhat better.
I've not used it much myself but I do see comments about it on the
lists from time to time. I think
On Thu, Oct 06, 2005 at 01:19:33PM -0600, Aly S.P Dharshi wrote:
Okay that is a fair statement to make, hence restating:
- What is the status of those items listed on the PostgreSQL gotchas
Some of them are for sure gotchas. For users of
tranditionally-non-toy (as opposed to
On Thu, Oct 06, 2005 at 04:18:03PM -0400, Stephen Frost wrote:
COUNT(*) very slow
As someone else has pointed out, it's only slow if you've got a large
dataset. There's certainly workarounds for this issue (generally
involving a couple of functions for keeping track of the number of
On Thu, Oct 06, 2005 at 12:29:51PM -0700, David Fetter wrote:
* Problems that will be fixed in the next version of PostgreSQL.
This means that problems get on developers' radar and get fixed. I
suppose by some extremely un-generous method of assessment, this
could be a gotcha.
*
On Thu, Oct 06, 2005 at 02:30:53PM -0400, Alex Turner wrote:
MySQL is to linux, what Jet is to Windows IMHO, oh wait - Jet has foreign
keys by default...
MySQL is the WindowsME of databases - first hit searching for MySQL on
CafePress. :)
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL
Neil Conway wrote:
COUNT(*) very slow: this is a known issue -- see the -hackers archives
for many prior discussions. MVCC makes this hard to solve effectively
(whether applications should actually be using COUNT(*) on large tables
with no WHERE clause is another matter...)
-Neil
And it's
On Thu, Oct 06, 2005 at 05:29:14PM -0500, Jim C. Nasby wrote:
On Thu, Oct 06, 2005 at 12:29:51PM -0700, David Fetter wrote:
* Problems that will be fixed in the next version of PostgreSQL.
This means that problems get on developers' radar and get fixed.
I suppose by some extremely
On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote:
- lowercase folding. I DO sometimes wish I could use
fieldID, etc. without quoting it.
I believe that may be against ANSI SQL. In any case, the only databases
I can think of that don't fold-case in some form are MySQL and Access.
--
Jim C.
Yep, I think the SQL spec says fold to uppercase. I'm
not sure why PostgreSQL folds to lowercase instead,
but if folding has to occur, I prefer lowercase.
CSN
--- Jim C. Nasby [EMAIL PROTECTED] wrote:
On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote:
- lowercase folding. I DO sometimes
Stephen Frost [EMAIL PROTECTED] 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
On Thu, Oct 06, 2005 at 05:47:36PM -0500, Jeffrey Melloy wrote:
The only thing I could see actually being an issue is the random() one
and add missing from. The rest are trivial. The random() thing is
interesting, esoteric, and probably has never been a problem in a real
situation. (Or
@postgresql.org
Subject:Re: [GENERAL] PostgreSQL Gotchas
Neil Conway wrote:
COUNT(*) very slow: this is a known issue -- see the -hackers archives
for many prior discussions. MVCC makes this hard to solve effectively
(whether applications should actually be using COUNT(*) on large tables
Gregory S. Williamson wrote:
On Informix however it is blindingly fast, and can also be instantly
conjured with the dbaccess tool (Info/Table/Status). They might be
stashing this count somewhere, but it is not available when the table
is locked, as during a load. However they do it,
Bruce Momjian pgman@candle.pha.pa.us writes:
Gregory S. Williamson wrote:
[ re COUNT(*) ]
On Informix however it is blindingly fast, and can also be instantly
conjured with the dbaccess tool (Info/Table/Status). They might be
stashing this count somewhere, but it is not available when the
84 matches
Mail list logo