Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-29 Thread Peter Eisentraut
On 7/29/16 3:13 PM, David Fetter wrote:
> I expect this kind of blather from MySQL, but you've brought up
> something that's been bothering me for awhile.  PostgreSQL's response
> should look more like this:
> 
> ERROR:  month field value out of range: "2016-99-99"
> LINE 1: select cast('2016-99-99' as date);
>   ^
> Any idea how much effort that would be?

This particular case is probably not hard, but the problem is that that
would raise the bar about error pointer precision, and you then should
also update a bunch of other places to give similar precision.  That
could be a lot of work.

I am, however, of the opinion, that these kinds of things can never be
helpful enough.  The latest trend is start and end pointers, which would
be nice.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-29 Thread David Fetter
On Fri, Jul 29, 2016 at 09:20:12PM +0300, Nikolay Samokhvalov wrote:
> On Fri, Jul 29, 2016 at 5:28 PM, Jim Nasby  wrote:
> >
> > The way I sum up MySQL vs PG for people that ask is to recount how they
> > "fixed" the Feb. 31st bug when they released strict mode (something that
> > they actually called out in the release PR). With strict mode enabled, Feb.
> > 30th and 31st would give you an error. Feb 35th was still silently
> > converted to March whatever. *That was the MySQL mentality: data quality
> > doesn't matter compared to "ease of use".*
> >
> > They've done this throughout their history... when presented with a hard
> > problem, they skip around it or plaster over it, and then they promote that
> > their solution is the only right way to solve the problem. (Their docs
> > actually used to say that anything other that table-level locking was a bad
> > idea.)
> 
> 
> This is exactly what I mean saying MySQL speaks different language than I
> know, and that's why I simply cannot use it:
> 
> (mysql 5.7.12)
> 
> mysql> select cast('2016-99-99' as date);
> ++
> | cast('2016-99-99' as date) |
> ++
> | NULL   |
> ++
> 1 row in set, 1 warning (0.00 sec)
> 
> 
> In Postgres:
> 
> test=#  select cast('2016-99-99' as date);
> ERROR:  date/time field value out of range: "2016-99-99"
> LINE 1: select cast('2016-99-99' as date);
> ^

I expect this kind of blather from MySQL, but you've brought up
something that's been bothering me for awhile.  PostgreSQL's response
should look more like this:

ERROR:  month field value out of range: "2016-99-99"
LINE 1: select cast('2016-99-99' as date);
  ^
Any idea how much effort that would be?

Best,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-29 Thread Nikolay Samokhvalov
On Fri, Jul 29, 2016 at 5:28 PM, Jim Nasby  wrote:
>
> The way I sum up MySQL vs PG for people that ask is to recount how they
> "fixed" the Feb. 31st bug when they released strict mode (something that
> they actually called out in the release PR). With strict mode enabled, Feb.
> 30th and 31st would give you an error. Feb 35th was still silently
> converted to March whatever. *That was the MySQL mentality: data quality
> doesn't matter compared to "ease of use".*
>
> They've done this throughout their history... when presented with a hard
> problem, they skip around it or plaster over it, and then they promote that
> their solution is the only right way to solve the problem. (Their docs
> actually used to say that anything other that table-level locking was a bad
> idea.)


This is exactly what I mean saying MySQL speaks different language than I
know, and that's why I simply cannot use it:

(mysql 5.7.12)

mysql> select cast('2016-99-99' as date);
++
| cast('2016-99-99' as date) |
++
| NULL   |
++
1 row in set, 1 warning (0.00 sec)


In Postgres:

test=#  select cast('2016-99-99' as date);
ERROR:  date/time field value out of range: "2016-99-99"
LINE 1: select cast('2016-99-99' as date);
^


Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-29 Thread Jim Nasby

On 7/29/16 8:17 AM, Kevin Grittner wrote:

On Thu, Jul 28, 2016 at 8:39 PM, Tatsuo Ishii  wrote:


> BTW, is there any opposite information, i.e. showing the
> limitation of MySQL comparing with PostgreSQL?

I'm not aware of a general list on the topic, but in reviewing
academic papers regarding transaction isolation I did find (and
confirm) that MySQL InnoDB relaxes the "strict" aspect of the
Strict 2 Phase Locking they use for implementing serializable
transactions.  "For performance reasons" they drop the locks

...

The way I sum up MySQL vs PG for people that ask is to recount how they 
"fixed" the Feb. 31st bug when they released strict mode (something that 
they actually called out in the release PR). With strict mode enabled, 
Feb. 30th and 31st would give you an error. Feb 35th was still silently 
converted to March whatever. *That was the MySQL mentality: data quality 
doesn't matter compared to "ease of use".*


They've done this throughout their history... when presented with a hard 
problem, they skip around it or plaster over it, and then they promote 
that their solution is the only right way to solve the problem. (Their 
docs actually used to say that anything other that table-level locking 
was a bad idea.)

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-29 Thread Kevin Grittner
On Thu, Jul 28, 2016 at 8:39 PM, Tatsuo Ishii  wrote:

> BTW, is there any opposite information, i.e. showing the
> limitation of MySQL comparing with PostgreSQL?

I'm not aware of a general list on the topic, but in reviewing
academic papers regarding transaction isolation I did find (and
confirm) that MySQL InnoDB relaxes the "strict" aspect of the
Strict 2 Phase Locking they use for implementing serializable
transactions.  "For performance reasons" they drop the locks
acquired during the transaction *before* ensuring crash/recovery
persistence.  This is more-or-less equivalent to always running
with synchronous_commit = off as well as allowing a small window
for serialization anomalies in corner cases.  The PostgreSQL
synchronous_commit option allows a similar performance benefit
(where the trade-off is deemed justified) without risking data
integrity in the same way.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-28 Thread Nikolay Samokhvalov
On Fri, Jul 29, 2016 at 4:39 AM, Tatsuo Ishii  wrote:
>
> Great translation.
>
> BTW, is there any opposite information, i.e. showing the limitation of
> MySQL comparing with PostgreSQL? I'm not familiar with MySQL, but
> occasionally hearing surprising (as a PostgreSQL user) limitation of
> MySQL and wondering if there's any summary of the info.
>

Sorry cannot help with that :-) I stopped using MySQL in 2005, when
discovered that we speak different languages (I learned standard ISO/ANSI
SQL in university, and then easily communicated with Oracle, SQL Server,
but failed to do so with MySQL; that's why I switched to Postgres).

During last years, all the focus of "let's compare Postres to ..." activity
was switched from MySQL to MongoDB and Oracle.
Maybe it's time to refresh the data -- for those who works with both
Postgres and MySQL.


Re: [HACKERS] "Strong sides of MySQL" talk from PgDay16Russia, translated

2016-07-28 Thread Tatsuo Ishii
> Following Uber's case discussion, I found this talk by Alexey Kopytov to be
> really interesting:
>   http://kaamos.me/talks/pgday16/strongmysql/strongmysql.html (online html,
> in Russian)
> 
> I translated it to English:
> 
> https://www.dropbox.com/s/t6a15s66jxg50tg/mysqlstrong_pgday16russia.pdf?dl=0
> (pdf)
> 
> The slides deck contains a lot of details. The author claims that during
> recent years, MySQL made a lot of progress in defending and advancing its
> position as a "most popular database for the web", he provides detailed
> reasoning for that, and then concludes that PostgreSQL will need years and
> maybe even decades to close gaps in the certain fields which are very
> sensitive for large companies:
>  - replication
>  - storage engines / compression / direct IO / etc
>  - partitioning,
> etc.
> 
> Of course this information is biased (Alexey works at Percona) but IMO it's
> much more detailed, qualitative and useful analysis compared to the Uber's
> recent article.

Great translation.

BTW, is there any opposite information, i.e. showing the limitation of
MySQL comparing with PostgreSQL? I'm not familiar with MySQL, but
occasionally hearing surprising (as a PostgreSQL user) limitation of
MySQL and wondering if there's any summary of the info.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers