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 ]

jjohn (22)

jjohn
  (email not shown publicly)
http://taskboy.com/
AOL IM: taskboy3000 (Add Buddy, Send Message)

Perl hack/Linux buff/OSS junkie.

Journal of jjohn (22)

Monday August 05, 2002
08:34 PM

Postgres Ahoy!

[ #6915 ]

Today I installed postgres. I've been using MySQL for several years now and have been generally happy with it. Lately, I've been looking lustily at such features as foreign key support and subselects -- things that MySQL is slowly beginning to think about maybe implementing possibly Real Soon Now. For once, I'm not going to be partisan about my software. To put it plainly, I need what I need. MySQL is still a great engine for read-only, infrequently changing data. Postgres promises to be better at supporting complex referential relationships between tables.

Postgres is not without failings.

  • The command line client psql works slightly differently than mysql. I didn't expect a learning curve there.
  • The quoting rules for the SQL postgres expects is, er, unexpected. That double quotes denote column names and single quotes denote strings is an unexpected wrinkle.
  • I'm not fond the postgres authorization system. I prefer the MySQL style of internal SQL tables to manage access to the databases rather than an external, UNIXy text file database. SIGHUPs to postmaster for authorization changes? Eek.
  • I noticed that the maximum number of TCP/IP connections was a compile time setting. I assume this can be overridden by a runtime configuration option.

These aren't showstopper problems; this is merely my travelog as I pass through the unknown country of a new RDBMS.

Why am I bothering? Apache::Session supports both MySQL and Postgresql. What's the difference? Apache::Session::MySQL locks the tables during updates. Apache::Session::Postgres uses transactions and no locking. I think the ideal solution is row-level locking. The MySQL solution quickly falls down in the application I'm developing because every web page paint requires accessing the session table.

Transactions aren't a silver bullet either. One process can be altering a row while another is reading and acting on the soon-to-be-changed data. For Apache::Session applications, this implies that two users would be trying to alter the same row (with the same session), which isn't suppose to happen except for extremely pathological cases. In any case, the application's performance will handled a bigger load with the table locking millstone.

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.
  • Alternatives (Score:3, Interesting)

    by djberg96 (2603) on 2002.08.05 21:05 (#11436) Journal
    I saw sap-db [sapdb.org] for the first time today, and it looks interesting. Thought I'd pass it along. It may have what you want.
    • Re:Alternatives (Score:2, Informative)

      First, thank you for the tip.

      Saw that /. item too. SAP doesn't look that enticing to me. It looks like an ailing product that wanted some opensource fairy dust sprinkled on it. I read a fair amount of the comments and looked around at the docs on the site.

      • The code is a weird blend of pascal and C++. It uses a proprietary "transpiler" to compile the pascal into C. Jesus.
      • It supports Perl through ODBC. Ick.
      • Despite marketing hype, many users reported a fairly negative experience on install.

      Postg

  • This is a run time setting, in /var/lib/pgsql/data/postgresql.conf. You can configure a number of useful things in there. All of them require a server restart, though.

    There's good documentation on that in the postgresql manual somewhere.

    It does kind of suck that you can't change the host based access from inside the database, but hopefully, it's not something you'll be changing too often anyway...

    -Dom

  • I'm not fond the postgres authorization system. I prefer the MySQL style of internal SQL tables to manage access to the databases rather than an external, UNIXy text file database. SIGHUPs to postmaster for authorization changes? Eek.

    That there needs to be any notification of the server at all seems suboptimal to me. I can't tell you how many times in the past few years I've been bitten by forgetting to do a FLUSH PRIVILEGES in MySQL after updating permissions. :-(

    --

    --
    xoa

    • I've been bitten by forgetting to do a FLUSH PRIVILEGES in MySQL after updating permissions

      As have I. I believe that using the GRANT statement eliminates the need for a FLUSH or mysqladmin reload.

      But, yes. It's annoying. Does Oracle handle this more gracefully?