All the Perl that's Practical to Extract and Report
use Perl Log In
jjohn (22)
(email not shown publicly)
http://taskboy.com/
AOL IM: taskboy3000 (Add Buddy, Send Message)
Perl hack/Linux buff/OSS junkie.
Postgres Ahoy!
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.
--Larry Wall in stab.c from the perl source code
Alternatives (Score:3, Interesting)
Reply to This
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.
Postg
max_connections (Score:1)
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 (Score:1)
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
Re:Auth changes (Score:1)
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 (Score:1)
--
xoa