PostgreSQL Gotchas

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, or
install additional modules, or check every time I create a foreign key that
the system will actualy enforce it.

In short Postgresql makes less work for a DBA than MySQL when you actualy
care about your data. It's cleaner, faster and easier to deal with in all
cases of real world database applications.

I'm not even gonna talk about the query planner in MySQL trying to deal with
an eight way join, let's just say that if you plan on using anything even
near third normal, MySQL will dog. Oh - yeah - and the fact that it doesn't
scale...

If those 'gotchas' are all one has against Postgresql... I don't know why
people are still even using Oracle or DB2 ;) MySQL you say, I thought we
were talking about RDBMSes here.

MySQL is to linux, what Jet is to Windows IMHO, oh wait - Jet has foreign
keys by default...

Alex

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
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

---------------------------(end of
broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Gavin M. Roy
800 Pound Gorilla
gmr@ehpg.net

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 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
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Gavin M. Roy
800 Pound Gorilla
gmr@ehpg.net

--
Aly S.P Dharshi
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

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
you look at the others that may be problematic in real use they're
mostly for archaic versions of PG.

The others that are still valid with 8.0 are... not so much problems
with PG as differences in the way PG works that users should be aware
of.

Cheers,
Steve

Sorry.

Alex

Don't think so.
The author sounds like a PostgreSQL proponent to me.

It also sounds like most of the issues have been addressed with recent
builds.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Gavin M. Roy
Sent: Thursday, October 06, 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 on the list ?

Cheers,

Aly.

--
Aly S.P Dharshi
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

---------------------------(end of
broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Gavin M. Roy
800 Pound Gorilla
gmr@ehpg.net

---------------------------(end of

broadcast)---------------------------

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?" as trying to be productive. But I could also just be
burnt out on the whole PgSQL vs MySQL flame war or reading too much
into what I appraised to be terse email. If so I apologize.

Regards,

Gavin

On Oct 6, 2005, at 12:01 PM, 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
- 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 ?

http://sql-info.de/postgresql/postgres-gotchas.html

On Thu, 6 Oct 2005, Gavin M. Roy wrote:

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?" as
trying to be productive. But I could also just be burnt out on the whole
PgSQL vs MySQL flame war or reading too much into what I appraised to be terse
email. If so I apologize.

Regards,

Gavin

--
Aly S.P Dharshi
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

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
workstation (not even a server, just my personal workstation with all
kinds of other crap running on it) I get a response time of about 1
second for a count(*) in version 7.4 of postgresql:

select count(*) from locators;
count
---------
1000000
(1 row)

takes anywhere from .75 to 1.0 second.

The same dataset, on oracle takes 0.75 seconds on a very fast oracle
server, and anywhere from 5 to 20 seconds on a slower server, like one
equivalent to my workstation.

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 go through that list, you find:

* Problems fixed in the last version or three of PostgreSQL. This is
an incentive to upgrade, not a gotcha.

* 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.

* Incomplete information, e.g. not mentioning Slony-I as an upgrade
path.

* One deviation from the standard that won't be changed: fold-to-lower.

* Nits so minor as not to be worth addressing (non-optional AS in FROM
clauses vs. SQL standard's making that AS optional there)

Third, there are real gotchas, but those are generally reported as
bugs on pgsql-bugs and acted upon as such.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

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 remaining, I consider "select as" to be
really trivial (and it appears a work-around can be
hacked).
- lowercase folding. I DO sometimes wish I could use
fieldID, etc. without quoting it.
- I've never found count(*) to be slow.
- I don't know enough about the "UNICODE means
"UTF-8"" and "RANDOM() failures" to comment.

CSN

__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

* Aly S.P Dharshi (aly.dharshi@telus.net) 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 ?

http://sql-info.de/postgresql/postgres-gotchas.html

There's only 6 things on there which apply to the current version of
PostgreSQL, and one of those will be fixed in 8.1.

The remaining items are:

SELECT column alias,...
This issue looks like it has probably been hashed over a few times and
at the end of the day the answer is "Best to use AS for clarity
anyway, and it is documented to be necessary, and Postgres upstream
has decided to require it instead of making alot of things reserved
which wouldn't otherwise need to be."

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. I've had problems with this when people were building things
against a MySQL database w/o proper quoting and assuming it was
case-sensitive when under Postgres it wasn't.

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.

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
rows). In this way, Postgres actually gives the user the flexibility
to choose the performance loss on insert/deletes which is necessary to
track the number of rows seperately, or not to.

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 there was some work done on this
for 8.1 and I expect support will only continue to improve in these
areas.

Just my 2c.

Thanks,

Stephen

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 other than utf-8 in the
first place I don't understand.

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, both
because the developers prefer this behavior and because it is consistent
with the behavior of prior PostgreSQL versions.

"Implicit FROM item and unintended cross joins": fixed in 8.1, as the
gotcha notes.

"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

--- CSN <cool_screen_name90001@yahoo.com> 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, 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 recently-non-toy) systems, I'd
think that there are three serious issues: the column alias
(because it strictly violates the SQL definition), the folding to
lower case (same reason), and the UNICODE==UTF-8 issue (because it
violates the UNICODE definition).

- Are they valid statements ?
- If they are bugs are they resolved ?

Some of them are valid and resolved (the site actually notes as much
in many cases).

One thing I do note is that there's a workaround for the have-to-dump
issue: you can use Slony to upgrade (by design). It won't work for
everyone, but it's a big step up in many cases.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler

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 jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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
rows). In this way, Postgres actually gives the user the flexibility
to choose the performance loss on insert/deletes which is necessary to
track the number of rows seperately, or not to.

Actually, I think the real issue here is that unlike every other
database I've used, PostgreSQL can't do covering index scans, where only
the index is read and not the base table. This is due to not having MVCC
visibility info in the index. But there has been discussion on -hackers
about a way to get close to this behavior; something along the lines of
setting a bit in the index once a tuple is visible to all running
transactions. This would be a win on larger indexes that don't have a
lot of insert/update/delete activity.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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.

* Incomplete information, e.g. not mentioning Slony-I as an upgrade
path.

From the few times I've emailed the author he seems happy to accept
better text. I'm sure he'd be happy to mention slony as an upgrade
option (though it would be best if there was a good doc describing this
we could point to). Likewise I believe he'd note things that will be
fixed in a given version (such as 8.1 or 8.2).

* One deviation from the standard that won't be changed: fold-to-lower.

Does the standard even specify which case you fold to?

I agree, this is only a gotcha if you're used to the very non-standard
behavior in MySQL.

* Nits so minor as not to be worth addressing (non-optional AS in FROM
clauses vs. SQL standard's making that AS optional there)

*shrug* I think it's useful to be able to see why something you might
expect to work doesn't.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461