Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Matts (1087)

Matts
  (email not shown publicly)

I work for MessageLabs [messagelabs.com] in Toronto, ON, Canada. I write spam filters, MTA software, high performance network software, string matching algorithms, and other cool stuff mostly in Perl and C.

Journal of Matts (1087)

Sunday December 15, 2002
01:51 PM

SQL D'oh!

[ #9470 ]

SQL is wonderful. It's so powerful in such a DWIMMY way. It's probably my favourite language after Perl (no, you're not allowed to disagree with this ;-).

However sometimes it's just a little *too* powerful.

For two days now I've been trying to debug why my trigger was deadlocking. It all seemed sensible. But it was doing nasties with selecting from the same table that the trigger was acting on, so I assumed I was causing deadlocks. I cleared out all that code, but alas, that didn't help things!

Then I discovered it:

-- part of my trigger code
UPDATE foo SET foodoo = newfoodoo;

Yes, I forgot the WHERE clause. So I was successfully updating about 2 million rows for every other row being updated/inserted. Needless to say this made my query a little slow.

This has yet again taught me to not assume that the database is broken. My first thought (after removing what could possibly cause a deadlock) was "The database is broken! I found a bug!" [1]. Of course it wasn't, it was doing exactly what I asked it to. Grrrr ;-)

On a plus note, postgresql rocks. Aside from one annoying bug (temp tables are useless because plpgsql pre-compiles the table's internal id, thus rendering the fact that the table is temporary completely pointless), it's probably the best database I've ever used (and I've used all the serious ones - Oracle, Sybase, MS SQL Server, DB2, MySQL, SQLite, SAPDB, Firebird, Watcom/SybaseASA).

[1] Often seen in perl land as "The regexp engine is broken! I found a bug!".

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • On a plus note, postgresql rocks.

    Ooh, on that note, you might be just the guy to help me write a new PostgreSQL driver. I haven't had the time to work on it in a few weeks, but I'm hoping to get back to it soon...

    --David

    • I thought I'd add a link for anybody else interested.

      http://gborg.postgresql.org/project/dbdpg/projdisplay.php [postgresql.org]

    • I noticed you'd just released the new DBD::Pg. Any idea when it'll support setting the UTF-8 flag on returned data? This was and is a large annoyance...

      Alternatively, any idea how hard it would be to add in myself? I haven't looked at the source yet...

      -Dom

      • If you build your database with --enable-multibyte and create the database with -E UNICODE, doesn't it just work?

        --David

        • Nope, I get doubly-encoded UTF8 because the data returned from the fetch isn't marked with Perl's UTF8 flag. Here's a test script:

          #!/usr/bin/perl -w

          use strict;
          use warnings;

          use 5.008;

          use charnames ':full';

          use DBI;

          my $dbh =
              DBI->connect( 'dbi:Pg:dbname=dom', 'dom', 'dom',
              { AutoCommit => 1, RaiseError => 1 } );

          binmode( STDOUT, ':utf8' );
          if ($ARGV[0] && $ARGV[0] eq 'setup' ) {
              $dbh->do( 'CREATE TABLE test ( name text )' );
              my $sth

          • This works perfectly for me:

            mercury% try
            name: fred
            name: barney
            name: Ädam

            Are you sure that PostgreSQL is compiled with --enable-multibyte? Try adding use utf8;. (Perhaps we should move this discussion to dbi-users@perl.org.)

            --David

  • I always had the feeling (having made the exact same mistake you describe) that the world might be safer if the absence of WHERE implied "WHERE false".

    But then that's a mooo point because SQL SUCKS. I am so delighted I don't have to touch it anymore. It's worse than Java. It's worse than INTERCAL. It's worse than BrianFuck (if I misspelt that one correctly). SQL should be dragged out in the backyard and shot. The only things that sucks more than SQL are RDBMS -- all of them, except perhaps SQLite b

    --

    -- Robin Berjon [berjon.com]

    • There are no RDBMS's on the market. There are only SQL DBMS's, which are loosely based on relational theory, but don't actually implement it properly. So to blame relational theory for the failures of SQL DBMS's is rather ridiculous.

      As for trees, that's called the hierarchical model, and it was dragged out in the backyard and shot back in the 70's. The fact is that with relational theory, creating a schema for hierarchical data is easy. However, creating a hierarchical schema for non-hierarchical data
      • Humour, it's called humour. I sometimes use it on monday mornings when I'm bored, or feel facetious, or am still drunk from the week-end.

        I know DB Debunking, I know about RDBMS, and I know about the hierarchical model. A lot of stuff is inherently hierarchical, with cyclic or acyclic graphs all over. I like the latter best, it fits my brain better. And there are lots of cool tools to use it. But then I'm a document guy and I don't care much about the needs that usually drive people to want DBMSs.

        --

        -- Robin Berjon [berjon.com]

        • Ah, I've heard of this humor thing.

          It seemed there were some serious parts in there though. I for one do seriously think SQL should be dragged into the backyard and shot.
          • Yes, there were indeed some serious parts, but they were seriously exaggerated (sorry if that was unclear). I do dislike SQL much, and used to think that it should be shot, especially as it's so balkanised. Now that I don't have to use it anymore, I really don't care much (though I'm always interested in hearing about better options).

            Much of the rest was mostly flamebait for Matt, with whom I've had this conversation a dozen times :)

            --

            -- Robin Berjon [berjon.com]

            • Here's a scary thought for you then. Of all the computer languages created more than 20 years ago, I can only think of two in wide spread use today (SQL and C).

              This says neither about the quality of those languages, but I do think it says one thing: they get the job done.
              • they get the job done

                As do contract killers. I'm not scared. Yet.

                Now, of the four schema languages for XML that you can name off the top of your head, which two make sense and which two are most widely used, and despite general dislike, do in fact get the job done? Would you recommend them? Do you think they should be replaced with a better solution?

                --

                -- Robin Berjon [berjon.com]