Thread: youtube video on pgsql integrity
turn the vol down a bit: http://www.youtube.com/watch?v=1PoFIohBSM4 the answer is d)
Ray Stell wrote: > http://www.youtube.com/watch?v=1PoFIohBSM4 I really don't want to get into bashing another product or starting a flame war, but I'm curious -- is everything shown there really the behavior of the MySQL database itself? It's not the front-end tool or something? It's hard to believe that anything with the behavior shown could gain any traction. The emphasis in the software's behavior seems to be in not throwing an error, even if you need to store something completely different from what was specified. -Kevin
Kevin Grittner, 29.11.2012 15:27: > Ray Stell wrote: > >> http://www.youtube.com/watch?v=1PoFIohBSM4 > > I really don't want to get into bashing another product or starting > a flame war, but I'm curious -- is everything shown there really > the behavior of the MySQL database itself? Yes, it is. It *is* the default behaviour but you can configure it to behave more sanely though. Thomas (But the author of the video does not seem to know Postgres that well either, because he spelled in wrong ;) )
On Nov 29, 2012, at 9:27 AM, Kevin Grittner wrote: > is everything shown there really > the behavior of the MySQL database itself? Good question. I intend to install mysql one day to explore, but just can't find the time. The particular engine is notdisclosed and I've read some are better than others....
On Fri, Nov 30, 2012 at 2:00 AM, Ray Stell <stellr@vt.edu> wrote: > > On Nov 29, 2012, at 9:27 AM, Kevin Grittner wrote: >> is everything shown there really >> the behavior of the MySQL database itself? > > Good question. I intend to install mysql one day to explore, but just can't find the time. The particular engine is notdisclosed and I've read some are better than others.... Far as I can see, none of the behaviour show there is the front end (other than UI features like autocomplete). You should be able to replicate everything demonstrated in that vid using any MySQL client. I like his quoting of the error messages. MySQL: now()/0 -> NULL; PostgreSQL: now()/0 -> "dude, what are you doing". With at least some of the invalid-data-gets-modified examples, you can tell MySQL to be more strict about things. However, the typical use of MySQL is with those sorts of settings at their defaults. See: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html and note that an application is always free to violate any of those rules it likes. PostgreSQL puts the responsibility on the database schema designer and the database admin; MySQL puts the responsibility on the application developer. PostgreSQL builds a database and lets applications talk to it; MySQL lets an application store its data. There's a huge philosophical difference there. ChrisA
Dne Thu, 29 Nov 2012 16:00:55 +0100 Ray Stell <stellr@vt.edu> napsal(a): > > On Nov 29, 2012, at 9:27 AM, Kevin Grittner wrote: >> is everything shown there really >> the behavior of the MySQL database itself? > > Good question. I intend to install mysql one day to explore, but just > can't find the time. The particular engine is not disclosed and I've > read some are better than others.... > It is behaviour of SQL parser, independent of storage engine. I tried it with MySQL in default settings, results are at http://pastebin.com/mbavfdj9 As you can see, your data will be changed on both mainstream engines. There is some "funny" effects created by this architecture (SQL parser and multiple storage engines), my favourite are the ones when InnoDB refuses to start (e. g. when you change innodb_log_file_size and forget to erase old innodb logs). Rest of MySQL starts happily, and even lets you drop tables belonging to unknown engine (InnoDB). When you realize your mistake and restart MySQL with functional InnoDB, you won't see the table (because SQL metadata are gone), but you can't create it either (because table metadata are still stored in InnoDB tablespace). -- Zdeněk Bělehrádek
On 11/29/2012 07:48 AM, Ray Stell wrote: > http://www.youtube.com/watch?v=1PoFIohBSM4 To be fair to MySQL, you can turn all that "helpfulness" off if you set the SQL mode: https://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html Amusingly enough, they actually have a POSTGRESQL mode. ;) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
On 30/11/12 04:30, Chris Angelico wrote:
$ psqlOn Fri, Nov 30, 2012 at 2:00 AM, Ray Stell <stellr@vt.edu> wrote:On Nov 29, 2012, at 9:27 AM, Kevin Grittner wrote:is everything shown there really the behavior of the MySQL database itself?Good question. I intend to install mysql one day to explore, but just can't find the time. The particular engine is not disclosed and I've read some are better than others....Far as I can see, none of the behaviour show there is the front end (other than UI features like autocomplete). You should be able to replicate everything demonstrated in that vid using any MySQL client. I like his quoting of the error messages. MySQL: now()/0 -> NULL; PostgreSQL: now()/0 -> "dude, what are you doing". With at least some of the invalid-data-gets-modified examples, you can tell MySQL to be more strict about things. However, the typical use of MySQL is with those sorts of settings at their defaults. See: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html and note that an application is always free to violate any of those rules it likes. PostgreSQL puts the responsibility on the database schema designer and the database admin; MySQL puts the responsibility on the application developer. PostgreSQL builds a database and lets applications talk to it; MySQL lets an application store its data. There's a huge philosophical difference there. ChrisA
psql (9.2.1)
Type "help" for help.
gavin=> SELECT now()/0;
ERROR: operator does not exist: timestamp with time zone / integer
LINE 1: SELECT now()/0;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
gavin=>
Cheers,
Gavin
On Thu, Nov 29, 2012 at 9:41 AM, Shaun Thomas <sthomas@optionshouse.com> wrote:
On 11/29/2012 07:48 AM, Ray Stell wrote:http://www.youtube.com/watch?v=1PoFIohBSM4
To be fair to MySQL, you can turn all that "helpfulness" off if you set the SQL mode:
https://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
Amusingly enough, they actually have a POSTGRESQL mode. ;)
My favorite part was "D) Postgres reaches over and slaps mySql".
Can we make pgAdmin detect if you're running a mySql admin tool, and display an animated hand-slapping graphic? I'll buy dinner for the person who implements this.
On Friday, November 30, 2012 at 02:30, czisg0sgmt@sneakemail.com
(Chris Angelico rosuav-at-gmail.com |pg-gts/Basic|) wrote:
>I like his quoting of the error messages. MySQL: now()/0 -> NULL;
>PostgreSQL: now()/0 -> "dude, what are you doing".
>
Hmmmm... that looked amusing in a perverse way, and a diversion
from test cases, so I gave it a try mid psql session, as follows:
pendari=> select '2149Q1'::text similar to '((19)|(20))[0-9]{2}Q[1-4]'::text;
?column?
----------
f
(1 row)
pendari=> select now()/0;
Bus error: 10
pendari:~ gavan$ psql --user=book_keeper --host=localhost pendari
psql: could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1)
and accepting
TCP/IP connections on port 5434?
could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5434?
could not connect to server: Connection refused
Is the server running on host "localhost" (fe80::1) and accepting
TCP/IP connections on port 5434?
pendari:~ gavan$
Ouch! That is a strange way to say "dude, what are you doing",
even if it is totally accurate.
PostreSQL: 9.2.1
System: Mac OS X Server Lion 10.7.5 (11G63)
Processor: 2.66 GHz Intel Core 2 Duo
RAM: 8 GB 1067 MHz DDR3
Nothing got to the log files
On the assumption that stupid user input should not crash the
server I consider this to be a bug, but also assume there must
be some details in my configuration that have brought this to
light. So what extra information is needed to complete the picture?
Since I did the build myself I have the ./configure output
files, and will see if a core dump has turned up somewhere.
Anything else to add? and, what is the next step?
Regards
Gavan Schneider
PS. Of course I will smile bravely if this is the postgresql
equivalent of being Rick rolled :)
Gavan Schneider <pg-gts@snkmail.com> writes:
> pendari=> select now()/0;
> Bus error: 10
[ scratches head... ] I get the expected error report on my own OS X
10.7.5 machine (though it's just plain Lion not Server).
This looks like it's a psql-side problem, not a server-side problem,
particularly since you say nothing showed up in the server log.
I'm wondering about psql-to-libpq version mismatches and such, since
Lion comes with a 9.0-era libpq in /usr/lib. You might investigate
which library is being picked up using "otool -L". Also, Apple's
version of libedit is known to be pretty buggy, so if you're using
that it might be worth your trouble to install GNU readline instead.
regards, tom lane
Tom, thanks for the reply
On Thursday, November 29, 2012 at 12:58, Tom Lane wrote:
>Gavan Schneider writes:
>>pendari=> select now()/0;
>>Bus error: 10
>
>[ scratches head... ] I get the expected error report on my own OS X
>10.7.5 machine (though it's just plain Lion not Server).
>
As of Lion the Server functionality is a simple add-on to the
base system. Seems the underlying stuff is always there just you
get the management tools and extra bell'n'whistles to configure.
>This looks like it's a psql-side problem, not a server-side problem,
>particularly since you say nothing showed up in the server log.
>
Agree that psql had a problem since it left crash logs for both
of the instances I had running in separate shells. Impressive!
almost quantum (aka spooky action at a distance) effect. Execute
stupid statement in one shell and kill your own and the other
process as well. I have found an instance of my server process
still hanging on but it is not accepting connections from psql
or pgadmin3, and at almost zero CPU time, is likely detritus
from a failed startup test.
So the psql "problem" also took the server down... to me this
all seems to point to a common piece of code with poor
interprocess separation so the misaligned libraries idea seems
to have merit.
From the psql crash log:
Process: psql [94318]
Path: /Volumes/VOLUME/*/psql
Identifier: psql
Version: ??? (???)
Code Type: X86-64 (Native)
Parent Process: bash [69168]
Date/Time: 2012-11-30 09:48:53.886 +1100
OS Version: Mac OS X Server 10.7.5 (11G63)
Report Version: 9
Crashed Thread: 0 Dispatch queue: com.apple.main-thread
Exception Type: EXC_BAD_ACCESS (SIGBUS)
Exception Codes: 0x000000000000000a, 0x0000000100938622
VM Regions Near 0x100938622:
--> mapped file 0000000100930000-000000010098c000 [
368K] r-x/rwx SM=COW
Object_id=270c5ca0
VM_ALLOCATE 000000010098c000-0000000100993000 [
28K] rw-/rwx SM=PRV
Application Specific Information:
objc[94318]: garbage collection is OFF
Thread 0 Crashed:: Dispatch queue: com.apple.main-thread
0 ??? 0x0000000100938622 0 + 4304635426
1 ??? 0x0000000100939807 0 + 4304640007
2 ??? 0x0000000100940df6 0 + 4304670198
3 ??? 0x0000000100931124 0 + 4304605476
Thread 0 crashed with X86 Thread State (64-bit): ...
Logical CPU: 0
Binary Images:
... +psql (??? - ???) /Volumes/VOLUME/*/psql
... libssl.0.9.8.dylib (44.0.0 - compatibility 0.9.8)
.... /usr/lib/libssl.0.9.8.dylib
*==> ... libedit.3.dylib (3.0.0 - compatibility 2.0.0) ... /usr/lib/libedit.3.dylib
...
From this can I conclude:
-- the Apple "buggy" version is the one in use?
-- specifically, that my postgres build would not normally have
installed this library in this location? and,
-- do you have the GNU readline installed on your system?
(While I am having so many adventures doing the "normal" thing,
I hope you can understand why I don't want to be a pioneer as
well. :)
Regards
Gavan Schneider