All the Perl that's Practical to Extract and Report

Postgres Ahoy!

jjohn on 2002-08-06T01:34:14

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.

Alternatives

djberg96 on 2002-08-06T02:05:52

I saw sap-db for the first time today, and it looks interesting. Thought I'd pass it along. It may have what you want.

Re:Alternatives

jjohn on 2002-08-06T02:36:38

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.

Postgres seems like a good fit for now. At some point, I'll bit the bullet and take the Oracle plunge. But, not until I have to.

max_connections

Dom2 on 2002-08-06T11:01:12

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

Auth changes

petdance on 2002-08-06T14:00:31

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. :-(

Re:Auth changes

jjohn on 2002-08-06T15:36:52

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?

Re:Auth changes

petdance on 2002-08-06T17:02:30

I'm pretty sure that GRANT does not force a flush. Maybe it does.