Thread: youtube video on pgsql integrity

youtube video on pgsql integrity

From
Ray Stell
Date:
turn the vol down a bit:
http://www.youtube.com/watch?v=1PoFIohBSM4
the answer is d)


Re: youtube video on pgsql integrity

From
"Kevin Grittner"
Date:
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


Re: youtube video on pgsql integrity

From
Thomas Kellerer
Date:
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 ;) )

Re: youtube video on pgsql integrity

From
Ray Stell
Date:
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.... 

Re: youtube video on pgsql integrity

From
Chris Angelico
Date:
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


Re: youtube video on pgsql integrity

From
Zdeněk Bělehrádek
Date:
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


Re: youtube video on pgsql integrity

From
Shaun Thomas
Date:
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


Re: youtube video on pgsql integrity

From
Gavin Flower
Date:
On 30/11/12 04:30, Chris Angelico wrote:
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 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
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

Re: youtube video on pgsql integrity

From
Mike Christensen
Date:

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. 

Re: youtube video on pgsql integrity

From
Gavan Schneider
Date:
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 :)



Re: youtube video on pgsql integrity

From
Tom Lane
Date:
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


Re: youtube video on pgsql integrity

From
Gavan Schneider
Date:
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