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.
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
Re:PostgreSQL
entropic on 2002-12-15T20:56:35
I thought I'd add a link for anybody else interested.
http://gborg.postgresql.org/project/dbdpg/projdisplay.php
Re:PostgreSQL
Dom2 on 2002-12-16T10:11:47
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
Re:PostgreSQL
Theory on 2002-12-16T20:44:24
If you build your database with
--enable-multibyteand create the database with-E UNICODE, doesn't it just work?--David
Re:PostgreSQL
Dom2 on 2002-12-17T14:24:24
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 = $dbh->prepare(
'INSERT INTO test (name) VALUES (?)' );
foreach ('fred', 'barney',
"\N{LATIN CAPITAL LETTER A WITH MACRON}dam" ) {
print "Inserting '$_'\n";
$sth->execute( $_ );
}
} elsif ($ARGV[0] && $ARGV[0] eq 'clean' ) {
$dbh->do( 'DROP TABLE test' );
} else {
my $sth = $dbh->prepare(
'SELECT name FROM test' );
$sth->execute;
while ( my ($name) = $sth->fetchrow_array ) {
print "name: $name\n";
}
}
$dbh->disconnect;(Please excuse the odd formatting...)
It's particularly noticeable if you look at the output in something like less -Kascii or od -c.
-Dom
Re:PostgreSQL
Theory on 2002-12-17T19:15:52
This works perfectly for me:
mercury% try
name: fred
name: barney
name: ÄdamAre you sure that PostgreSQL is compiled with
--enable-multibyte? Try addinguse 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 because it is nice to people that don't like RDBMS -- and the relational model. Trees, trees, givus sum trees!!!
Re:Bah
autarch on 2002-12-16T18:06:48
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 is the classic round peg in a square hole. I guess those who don't know history are doomed to repeat it. You might want to check out Database Debunkings, which talks about both of these issues.
I do agree that SQL sucks however, and I'd love to see a truly relational database.Re:Bah
darobin on 2002-12-16T18:18:38
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.
Re:Bah
autarch on 2002-12-16T18:25:31
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.Re:Bah
darobin on 2002-12-16T18:47:02
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
:) Re:Bah
Matts on 2002-12-16T23:32:22
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.Re:Bah
darobin on 2002-12-16T23:52:10
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?
