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.

use Perl

All the Perl that's Practical to Extract and Report

use Perl 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 August 25, 2002

05:19 AM

Open Source Databases

[ #7290 ]

For a few years now I've been a huge fan of Postgresql. It always worked the way I wanted it to, and when they added MVCC it just got even better.

However this week I've had some rather extreme performance requirements from Pg. So I've been doing as much as I possibly can in plpgsql (Pg's variant on Oracle's Pl/sql). In fact I've got it to the point now where I can just pass in a chunk of text to Pg, and get back the result I need, all without doing any prepare/execute/loop stuff whatsoever.

However, the stuff I've been doing involves temporary tables. Each time in I need to stuff the data I've got into this temporary table, do some manipulation on it, aggregate some results, and return a single floating point number. Easy enough, and pretty standard fodder for anyone who's got a strong background in RDBMS' like I do.

Well no, it's not quite that simple. You see I was struggling with a very strange error message for quite some time - the symptoms being that everything worked perfectly the first time through, but failed horribly the second time. Eventually I turned to google, which came up with the goods: in plpgsql when you create and use a temporary table, it compiles the table name in all your sql into a direct tableid reference for speed, but since next time around that temporary table is different, it's pointing at a non-existant table. Hence the error. This is a bug in Postgresql. Argh!

So what are my options? I've tried a few things now - Interbase/Firebird doesn't yet have temporary tables. MySQL doesn't have stored procedures. SAPDB was just a bitch to install and I couldn't figure it out.

What's left? It seems that Oracle or one of the other commercial db's is all there is :-(((

List all Journal entries

Open Source Databases More | Login | Reply

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Without JavaScript enabled, you might want to use the classic discussion system instead. If you login, you can remember this preference.

Bug in PostgreSQL (Score:2, Insightful)

by bart (450)

This is a bug in Postgresql. Argh!
So what are my options?
Get the bug fixed. This is the advantage of using open source stuff: once developers know about a bug and have a good clue about what causes it, you often don't have to wait long for a fix.

So, report it.
Couldn't you... (Score:3, Insightful)

by jordan (120) on 2002.08.25 7:08 (#12149) Homepage Journal

Just permanent table for this use? I've done this when I wanted something I was doing repeatedly to go the fastest possible. I found there was some overhead to creating the temporary table, so I just created a permanent table and reused it.

Reply to This
- Re:Couldn't you... (Score:2)
  
  by Matts (1087)
  
  That's the work around that I've done for now. Unfortunately it's a fair bit slower than the temporary table system, because it has to have an extra column (pid), which has to be permanently indexed, thus inserts are slower, and selects and updates are slower because they have to use the index. Nuts.
  - Re:Couldn't you... (Score:1)
    
    by jordan (120)
    
    It's been a few years since I did that app where I replaced a temporary table with a permanent one and I recall that there were other advantages to making the data (semi-)permanent.
    I recall now that the real speedup was that the data was time-tagged. I had been performing big selects against the database for data, inserting data from a time range in the temporary table and performing reports off of this. I was able to take advantage of the fact that subsequent runs would need data mostly in the same time
Alternative Language (Score:1)

by Dom2 (2981)

PlPGSQL isn't the only embedded language. There's PL/TCL, PL/Python and PL/Perl as well. These may not have the same limitations as PL/PGSQL.
-Dom
- Re:Alternative Language (Score:1)
  
  by Dom2 (2981)
  
  Hmmm, looking at the docs [postgresql.org], you'd have to use the CPAN module DBD::PgSPI to do stuff. Might be worth examining that module...
  -Dom