[lug] BLUG Poll

[Rob Nagler]

dio2002 writes:
> 1) What benefits (performance, extra programming capability, scalability,
> functionality, etc) if any do I get moving from MySQL to Postgres and
> possibly vice versa if any exist?

I think quality is the biggest issue.  MySQL was not designed as a
transactional database whereas Postgres evolved from Ingres which was
a transactional db many decades ago.  The architectural focus of MySQL
is about speed.  Postgres focuses on handling transactions, including
low-level locking.

Consider that with Postgres, DDL is transactional.  This is extremely
important to us, because we continually upgrade our applications, and
that often involves DDL.  If we make a mistake (shudders!) in the DDL,
it's no big deal, the transaction rollsback, and we fix the bug in the
upgrade, and start over.  Here's some discussion on the Postgres site: 

http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

And, here's a rant why transactional DDL sucks:

http://postgresqldbnews.blogspot.com/2008/04/postgres-ddl-transaction-control-sucks.html

Having performed hundreds of db upgrades over the last decade
on live servers, I disagree with this guy.  He didn't know how his
system works so he blames the system.  Transactional DDL is extremely
important, and as the author says, testing your upgrades is also
important.  Just because you test an upgrade, doesn't mean it is going
to work in production.

> accesses the CLI to do a common task like create db, create db user, set
> password, create table, etc would suffice.

Well, depends on what you mean. :-)  We build databases all the time,
and these use our extensive toolset, which is open source.  However,
you have to load bOP, which means you are probably biting off more
than you need.  There's some stuff to learn in Bivio::Util::SQL if you
haven't done programmatic upgrades and the like:

http://petshop.bivio.biz/src?s=Bivio::Util::SQL

Rob