articles [was: mysql-pgsql comparison]

> 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
make sense.

- 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 have credit 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 -
the article 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.