Thread: The "Why Postgres not MySQL" bullet list
Just a brief note to say I am still working on it but it's taking longer than I anticipated. There's a lot of involved detail and checking, and I'm up to 500 lines of text already without even having started on transaction support. I think this will develop into a longer technical document which will serve as the background for a shorter bullet-list type paper. In the meantime here's some reference material I put together: http://sql-info.de/mysql/gotchas.html Ian Barwick barwick@gmx.net
Very interesting read -- and I'm only through section 1. Having used MySQL for a while some are known to me, many others are new. One particularly interesting item is Section 1.7, DEFAULT 'now()' as this is also a PostgreSQL gotcha. See 'nowtest' attached. Anyway, I noticed a few times the spec has been mentioned as being broken, ignored or otherwise, but the relevant sections not pointed to. Would you like a hand in tracing down quotes where spec in these areas? On Sun, 2003-08-31 at 14:47, Ian Barwick wrote: > Just a brief note to say I am still working on it but it's > taking longer than I anticipated. There's a lot of involved > detail and checking, and I'm up to 500 lines of text > already without even having started on transaction support. > I think this will develop into a longer technical document > which will serve as the background for a shorter > bullet-list type paper. > > In the meantime here's some reference material I > put together: > http://sql-info.de/mysql/gotchas.html > > > Ian Barwick > barwick@gmx.net > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Attachment
On Sunday 31 August 2003 21:32, Rod Taylor wrote: > Very interesting read -- and I'm only through section 1. Having used > MySQL for a while some are known to me, many others are new. > > One particularly interesting item is Section 1.7, DEFAULT 'now()' as > this is also a PostgreSQL gotcha. See 'nowtest' attached. Interesting. Though I think this is subtly different - in MySQL the default is ignored, you could put anything in you want... > Anyway, I noticed a few times the spec has been mentioned as being > broken, ignored or otherwise, but the relevant sections not pointed to. > > Would you like a hand in tracing down quotes where spec in these areas? Yup. I'm interested in facts not FUD ;-). On that note I do have vague intentions of compiling a list of PostgreSQL gotchas. The only other one I have right now is PostgreSQL's mandatory AS as in SELECT col AS whatever instead of the alternative SELECT col whatever which IIRC is standard-compliant (there is a thread on this somewhere). Ian Barwick barwick@gmx.net
> On that note I do have vague intentions of compiling a list > of PostgreSQL gotchas. The only other one I have right now is > PostgreSQL's mandatory AS as in > SELECT col AS whatever > instead of the alternative > SELECT col whatever > > which IIRC is standard-compliant (there is a thread on this > somewhere). I believe in the spec AS is optional. Peter had a proposal for allowing optional AS in PostgreSQL, but I don't remember why it wasn't implemented.
Attachment
On Sun, 2003-08-31 at 16:21, Ian Barwick wrote:
> On Sunday 31 August 2003 21:32, Rod Taylor wrote:
> > Very interesting read -- and I'm only through section 1. Having used
> > MySQL for a while some are known to me, many others are new.
> >
> > One particularly interesting item is Section 1.7, DEFAULT 'now()' as
> > this is also a PostgreSQL gotcha. See 'nowtest' attached.
>
> Interesting. Though I think this is subtly different - in MySQL the default is
> ignored, you could put anything in you want...
>
> > Anyway, I noticed a few times the spec has been mentioned as being
> > broken, ignored or otherwise, but the relevant sections not pointed to.
> >
> > Would you like a hand in tracing down quotes where spec in these areas?
>
> Yup. I'm interested in facts not FUD ;-).
>
> On that note I do have vague intentions of compiling a list
> of PostgreSQL gotchas. The only other one I have right now is -
Implicit from items (toggle in 7.4, but allows them by default).
Constraint names are unique to a table rather than a schema.
Folds identities to lowercase rather than uppercase when they are not
quoted.
Unique constraints are evaluated immediately. This can make UPDATE ..
SET col = col + 1 evaluation difficult.
That is all I have for PostgreSQL at the moment.
For Gotcha 1.8 (INSERT INTO .. SELECT)
Self referencing operations are an advanced feature which MySQL does not
support.
Conformance Rules (Section 14.8)
1) Without Feature F781, Self-referencing operations, no leaf
generally underlying table of T shall be generally contained in
the <query expression> immediately contained in the <insert
columns and source> except as the <table or query name> or
<correlation name> of a column reference.
Attachment
> > Would you like a hand in tracing down quotes where spec in these areas?
>
> Yup. I'm interested in facts not FUD ;-).
-- style comments
Notice that there is nothing mentioned in section 5.2 about an
alternative to treating --[{-}...] as a comment.
Side note:
A PostgreSQL gotcha includes nested block comments /* */, which
the spec does not support. Consider:
SELECT 'TEST' || /* My field goes with this "/*" */ '*/';
PostgreSQL believes the '; to be part of a string. Spec says
this is a complete statement.
Section 5.2
Format:
<simple comment> ::= <simple comment introducer> [ <comment
character>... ] <newline>
<simple comment introducer> ::= <minus sign><minus sign>[<minus
sign>...]
<comment character> ::= <nonquote character> | <quote>
Syntax Rules:
18) SQL text containing one or more instances of <comment> is equivalent
to the same SQL text with the <comment> replaced with <newline>.
Attachment
Rod Taylor wrote:
-- Start of PGP signed section.
> > > Would you like a hand in tracing down quotes where spec in these areas?
> >
> > Yup. I'm interested in facts not FUD ;-).
>
> -- style comments
>
> Notice that there is nothing mentioned in section 5.2 about an
> alternative to treating --[{-}...] as a comment.
>
> Side note:
> A PostgreSQL gotcha includes nested block comments /* */, which
> the spec does not support. Consider:
>
> SELECT 'TEST' || /* My field goes with this "/*" */ '*/';
>
> PostgreSQL believes the '; to be part of a string. Spec says
> this is a complete statement.
>
>
> Section 5.2
>
> Format:
>
> <simple comment> ::= <simple comment introducer> [ <comment
> character>... ] <newline>
>
> <simple comment introducer> ::= <minus sign><minus sign>[<minus
> sign>...]
> <comment character> ::= <nonquote character> | <quote>
>
>
> Syntax Rules:
>
> 18) SQL text containing one or more instances of <comment> is equivalent
> to the same SQL text with the <comment> replaced with <newline>.
I thought a newer SQL spec allows C comments --- am I remembering
correctly?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
> I thought a newer SQL spec allows C comments --- am I remembering > correctly? Yes, single layer block comments /* */ are allowed as well. This has to do with MySQL treating SELECT 1 -- 1; as a functional statement returning 2.