articles [was: mysql-pgsql comparison] - Mailing list pgsql-hackers

> Yeah. This article was probably written directly by the MySQL team. This is 
> what you learn in the computing business. If you want someting published .. 
> write it yourself and invite the journalist in a restaurant. I did it 2 or 3 
> times when working for a telecommunication company.

On a related note, I've been thinking about "Linux Journal" or
"Linux Magazine" level article on user-defined types and functions
and how they can make life much easier.  

A related topic are views and rules, again how they can be used
to eliminate otherwise difficult problems.

My PKIX stuff is a good example of this.  I defy anyone to implement
the following table definitions in MySQL:
 create table certs (   cert x509 not null,
   key text not null unique     constraint check (key = iands_hash(cert)),
   subject text not null unique     constraint check (subject = subject(cert)),
   issuer text not null     constraint check (issuer = issuer(cert)),
   primary key(key),
   foreign key(issuer) references certs(subject) deferrable );
 create view cert_insert as select cert from certs;
 create rule certi as on insert into cert_insert do instead   insert into certs (cert, key, subject, issuer)     values
(new.cert,iands_hash(new.cert), subject(new.cert),        issuer(new.cert));
 

There's a performance hit with all of these constraints,
of course, but that's more than offset by the confidence
that I (as database developer) can have in the database as
a whole.  A solid database means that I can keep my application
code thin.

(As an aside, I hope to get release 0.4 out this evening; it
documents all PKIX types and functions and includes support
for public key encryption.  But I digress....)

The problem with this example is that it's too obscure - it
gets people who understand this domain excited, but everyone
else gets sidetracked by it.

Can anyone think of a better example?  I've come up with
three possibilities, each with its own problems:
- geographical positions - latitude, longitude and possibly  elevation, with related functions.  But few constraints
makesense.
 
- credit cards.  Constraints would be the number of digits,  the check digit and the leading digit (4xxx is Visa, 5xxx
is Mastercard).  A "neat" feature would be a function that  masks the credit card information - you could use
views/rules so you could insert or update credit card info, but it would  be masked during access.
 
  But this strikes me as evil.  This information is still  in the datatabase, still accessible in any db dump.  If you
havecredit card info online, you *must* use  strong crypto.  (Such as libpkix 0.4 et seq.)
 
- email and netnews.  Create a new RFC822 type that understands  the RFC822 format and provides access to the headers
via accessor functions.
 
  The benefits are that this type has all of the features  you would want.  "Message-ID" should be unique.
"References" should give you referential integrity checks (although   it would not be possible to make it an actual
constraint in a live system.)  
 
  You could even illustrate advanced techniques by looking up  the sender's nominal email address with DNS.  If it's
not a valid address, it goes into the bit bucket as spam.  Even  if it is valid, a small configuration change and
you're checking RBL sites instead of the DNS servers and rejecting  mail from spammers.
 
  The downside is that, like PKIX, this is too heavyweight  for an introductory article.  But it may still be best -
thearticle would just need to gloss over the gory details.
 

> Don't worry about the results of such an article. It is simply not possible 
> to benchmark MySQL against PostgreSQL because MySQL lacks many PostgreSQL 
> features. A serious user immediatly understands this.

The problem is that there are a lot of people out there who want
to use a relational database, but don't understand just how much
they give up with MySQL.  Besides, if 4 of the 5 hosting companies
they considered suported MySQL instead of PostgreSQL it must be
better for their needs, right?

I've even caught friends making this mistake.  For simple
schemas and light loads MySQL looks soooo good.  The stuff
that I say is crucial for maintainability of even modestly
large databases (50 MB+) seems so abstract.

> Why don't you start a mailing list for "Propaganda" and "Public relations". 
> It should be possible to meet the main journalists and contact them on 
> regular basis with ready-to-publish articles.

An even better approach is to find a vict... person willing and
able to write a regular "Database Guru" column for LJ, LM, or the
like.  Most already have Kernel Korner, sysadmin, and similar columns.

Maybe 2 of 3 months will be basic.  How do you access the database
with Perl or JDBC?  Using ODBC to quietly replace SQL Server.
Why ESQL/C (ecpg, pro*c) is a tool every C/C++ developer should
have in their toolbox.  Even basic things like actually setting
up the configuration files for the first time.

That odd month... that's when you pull out the advanced topics
that separate the real databases from the toys.  Views and Rules.
User defined types.  Clustering.  Strong authentication of clients.
Crypto.

MySQL has mindspace for one reason alone - it's perceived as 
faster.  

PostgreSQL shouldn't try to compete in the same mindspace, it
should point out the many things that PostgreSQL supports but
MySQL doesn't... while quietly pointing out the folly in selecting
a system on the basis of single-user responsiveness.  MySQL handles
a single query faster, but PostgreSQL handles far more concurrent
queries and does not catastrophically fail under heavy loads.