Thread: We aren't a relational database ... ?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
"How many open source relational databases can you name? My friend Gabrielle
recently sent me the links to two of them. However, if you?re like most
technical people, you probably don?t know any ? just as I didn?t until
recently. I can already imagine many of you saying ?bulls**t?, what about MySQL
and PostgreSQL?? (to name just two), but those are just databases, not
relational databases."
-
<http://www.oreillynet.com/onlamp/blog/2007/10/open_source_relational_databas.html?CMP=OTC-3W3B95036222&ATT=Open+Source+Relational+Databases>
- ----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . scrappy@hub.org MSN . scrappy@hub.org
Yahoo . yscrappy Skype: hub.org ICQ . 7615664
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4 (FreeBSD)
iD8DBQFHCY6s4QvfyHIvDvMRAiYVAJ0UptZBrFW81R0IccdVwOKGiyrAggCgmNrQ
Y7mmbBMVgb16bpgjg1j8srM=
=1tJ1
-----END PGP SIGNATURE-----
On Sun, 2007-10-07 at 22:58 -0300, Marc G. Fournier wrote:
> "How many open source relational databases can you name? My friend Gabrielle
> recently sent me the links to two of them. However, if you?re like most
> technical people, you probably don?t know any ? just as I didn?t until
> recently. I can already imagine many of you saying ?bulls**t?, what about MySQL
> and PostgreSQL?? (to name just two), but those are just databases, not
> relational databases."
SQL does have some glaring violations of the relational model, the
duplicate rows are just one example. You can't even compare two relation
values in SQL; I'm not sure whether that's required by the relational
model or not, but it certainly seems to show that relations were not the
real design focus of the language.
I think PostgreSQL is a good representative of the spirit of the
relational model in other ways though. It's easy to define your own
types that are every bit as powerful as built-in types. And the rule
system is specifically designed to help separate the logical design from
the physical storage (although the rule system could probably be better
at this), which is an important benefit of relational theory. And we
shouldn't forget that SQL is a lot closer to a relational language than
any other practical alternative, which may be why it's so successful.
That being said, I'd like to see some group really make an effort to
build a truly relational database. If they are able to achieve the
theoretical benefits of the relational model, that would be very
worthwhile.
And the benefits aren't too far-fetched. It's much easier for the
optimizer to transform relational expressions than SQL expressions. And
if you write declarative constraints as relational expressions (rather
than triggers), the optimizer is able to use those constraints as part
of the optimization process. PostgreSQL is able to do that for simple
things like unique attributes or constraint exclusion, but for more
complex constraints you need to use triggers, which the optimizer can't
see.
Regards,
Jeff Davis
On Sun, Oct 07, 2007 at 07:53:14PM -0700, Jeff Davis wrote: > On Sun, 2007-10-07 at 22:58 -0300, Marc G. Fournier wrote: > > "How many open source relational databases can you name? My friend > > Gabrielle recently sent me the links to two of them. However, if > > you?re like most technical people, you probably don?t know any ? > > just as I didn?t until recently. I can already imagine many of you > > saying ?bulls**t?, what about MySQL and PostgreSQL?? (to name just > > two), but those are just databases, not relational databases." > > SQL does have some glaring violations of the relational model, Nope. SQL doesn't conform with *a* relational model espoused by Darwen, Date and Pascal, hereinafter DDP, who are about as connected to database management as Christian Identity is to Christianity. It conforms pretty well to Codd's relational model, and he's the guy who invented the thing. > the duplicate rows are just one example. You can't even compare two > relation values in SQL; I'm not sure whether that's required by the > relational model or not, but it certainly seems to show that > relations were not the real design focus of the language. > > I think PostgreSQL is a good representative of the spirit of the > relational model in other ways though. It's easy to define your own > types that are every bit as powerful as built-in types. And the rule > system is specifically designed to help separate the logical design > from the physical storage (although the rule system could probably > be better at this), which is an important benefit of relational > theory. And we shouldn't forget that SQL is a lot closer to a > relational language than any other practical alternative, which may > be why it's so successful. > > That being said, I'd like to see some group really make an effort to > build a truly relational database. If they are able to achieve the > theoretical benefits of the relational model, that would be very > worthwhile. It is instructive to note that no one in academia, industry, government or open source, has managed so much as a crude prototype of DDP's fantasy model despite huge available resources and several decades over which to apply them. However, even if someone were to manage such a thing tomorrow, there would be quite large practical problems such as the myriad of byzantine nonsense DDP proposes rather than allow NULLs anywhere. > And the benefits aren't too far-fetched. Yes, they are. > It's much easier for the optimizer to transform relational > expressions than SQL expressions. And if you write declarative > constraints as relational expressions (rather than triggers), the > optimizer is able to use those constraints as part of the > optimization process. PostgreSQL is able to do that for simple > things like unique attributes or constraint exclusion, but for more > complex constraints you need to use triggers, which the optimizer > can't see. DDP's is a fascinating, elegant theory, as is Phlogiston theory, but there is one little problem: no evidence thus far for either of them and much against. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
David Fetter wrote:
> On Sun, Oct 07, 2007 at 07:53:14PM -0700, Jeff Davis wrote:
>> On Sun, 2007-10-07 at 22:58 -0300, Marc G. Fournier wrote:
> DDP's is a fascinating, elegant theory, as is Phlogiston theory, but
> there is one little problem: no evidence thus far for either of them
> and much against.
The phlogiston theory (from the Ancient Greek φλογιστόν phlŏgistón
"burnt up," from φλόξ phlóx "fire") is an obsolete scientific theory,
stated initially in 1667 by Johann Joachim Becher, whereby in addition
to the classical four elements of the Greeks, there was an additional
fire-like element called “phlogiston” that was contained within
combustible bodies, and released, to lesser or greater degrees, during
combustion. The theory was an attempt to explain oxidation processes,
such as combustion and the rusting of metals.
Wow... you learn something everyday. (source Wikipedia)
Joshua D. Drake
>
> Cheers,
> David.
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFHClbtATb/zqfZUUQRAjlhAJ9GG11IlpnHVoqFywOtnXDzEfOlZwCgh7rj
Eq+SBnuxu+vYydEV1+g2uWM=
=2noE
-----END PGP SIGNATURE-----
On Mon, 2007-10-08 at 07:55 -0700, David Fetter wrote:
> On Sun, Oct 07, 2007 at 07:53:14PM -0700, Jeff Davis wrote:
> > On Sun, 2007-10-07 at 22:58 -0300, Marc G. Fournier wrote:
> > > "How many open source relational databases can you name? My friend
> > > Gabrielle recently sent me the links to two of them. However, if
> > > you?re like most technical people, you probably don?t know any ?
> > > just as I didn?t until recently. I can already imagine many of you
> > > saying ?bulls**t?, what about MySQL and PostgreSQL?? (to name just
> > > two), but those are just databases, not relational databases."
> >
> > SQL does have some glaring violations of the relational model,
>
> Nope. SQL doesn't conform with *a* relational model espoused by
> Darwen, Date and Pascal, hereinafter DDP, who are about as connected
> to database management as Christian Identity is to Christianity. It
> conforms pretty well to Codd's relational model, and he's the guy who
> invented the thing.
I haven't heard anyone say before that duplicate tuples were part of any
relational model.
I'm not saying SQL is bad; it's certainly the best practical data
language we have.
The problem I see is that it's the _only_ practical data language in
existence, and it is (in my opinion) imperfect.
Regards,
Jeff Davis
On Mon, 2007-10-08 at 09:24 -0700, Joshua D. Drake wrote:
> > I haven't heard anyone say before that duplicate tuples were part of any
> > relational model.
> >
> > I'm not saying SQL is bad; it's certainly the best practical data
> > language we have.
> >
> > The problem I see is that it's the _only_ practical data language in
> > existence, and it is (in my opinion) imperfect.
>
> Jeff, that is fair but I must of course counter point. What language,
> regardless of implementation or purpose is perfect?
>
> And no Perl, is not perfect.
>
For something that's the only option available, and it is too far from
perfect in my opinion. There are many options for procedural
programming, functional programming, and object-oriented programming,
but only one practical option for a data language.
In SQL, "=" is both an assignment operator (e.g. UPDATE) and a
comparison operator (e.g. WHERE clause). There are nondeterministic
updates (if you have a FROM in your UPDATE) that produce different
results depending on the order rows are read from the heap (which might
depend on insertion order). There are all kinds of special words for
special cases, like "INTERVAL".
I think SQL could use some healthy competition. Perl may not be perfect,
but at least it's got plenty of viable alternatives.
Regards,
Jeff Davis
> The phlogiston theory (from the Ancient Greek φλογιστόν phlŏgistón > "burnt up," from φλόξ phlóx "fire") is an obsolete scientific theory, > stated initially in 1667 by Johann Joachim Becher, whereby in addition Phlogiston was a reasonable theory at the time, with the knowledge they had. Now it seems ridiculus in hindsigt. But perhaps people in the 24th century will have a laugh over wormholes and n dimensions. Just to stay on topic :-) -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: kaare@jasonic.dk
> In SQL, "=" is both an assignment operator (e.g. UPDATE) and a > comparison operator (e.g. WHERE clause). There are nondeterministic Why would that be a problem when they occur in different parts of the statement? -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: kaare@jasonic.dk
On Mon, Oct 08, 2007 at 09:15:29AM -0700, Jeff Davis wrote: > On Mon, 2007-10-08 at 07:55 -0700, David Fetter wrote: > > On Sun, Oct 07, 2007 at 07:53:14PM -0700, Jeff Davis wrote: > > > On Sun, 2007-10-07 at 22:58 -0300, Marc G. Fournier wrote: > > > > "How many open source relational databases can you name? My > > > > friend Gabrielle recently sent me the links to two of them. > > > > However, if you?re like most technical people, you probably > > > > don?t know any ? just as I didn?t until recently. I can > > > > already imagine many of you saying ?bulls**t?, what about > > > > MySQL and PostgreSQL?? (to name just two), but those are just > > > > databases, not relational databases." > > > > > > SQL does have some glaring violations of the relational model, > > > > Nope. SQL doesn't conform with *a* relational model espoused by > > Darwen, Date and Pascal, hereinafter DDP, who are about as > > connected to database management as Christian Identity is to > > Christianity. It conforms pretty well to Codd's relational model, > > and he's the guy who invented the thing. > > I haven't heard anyone say before that duplicate tuples were part of > any relational model. There is at least one relational model--the one every SQL DBMS is based on--which uses multisets instead of sets. Multiset theory and practice turn out to be extremely handy in databases, as they allow things we take for granted like aggregates and arithmetic on same. > I'm not saying SQL is bad; it's certainly the best practical data > language we have. POSTQUEL was quite a nice language, too. Check out the pre-SQL sources of POSTGRES. > The problem I see is that it's the _only_ practical data language in > existence, and it is (in my opinion) imperfect. Perfection isn't a human attribute, and there's a lot of evidence to suggest it isn't a divine one either. How about striving for excellence instead? That is definitely achievable. :) Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
On Tue, 2007-10-09 at 09:44 +0200, Kaare Rasmussen wrote:
> > In SQL, "=" is both an assignment operator (e.g. UPDATE) and a
> > comparison operator (e.g. WHERE clause). There are nondeterministic
>
>
> Why would that be a problem when they occur in different parts of the
> statement?
>
It can actually be in the same part of the statement:
UPDATE foo SET b = a = 0;
is legal in PostgreSQL. How weird is that? At a glance, can you tell
what it's doing?
Regards,
Jeff Davis
On Tue, Oct 09, 2007 at 11:48:35AM -0700, Jeff Davis wrote: > On Tue, 2007-10-09 at 09:44 +0200, Kaare Rasmussen wrote: > > > In SQL, "=" is both an assignment operator (e.g. UPDATE) and a > > > comparison operator (e.g. WHERE clause). There are > > > nondeterministic > > > > Why would that be a problem when they occur in different parts of > > the statement? > > It can actually be in the same part of the statement: > > UPDATE foo SET b = a = 0; Don't Do That(TM). It wouldn't even be wise to do it if there were separate assignment and comparison operators because it's too easy to mess up even then. > is legal in PostgreSQL. How weird is that? At a glance, can you tell > what it's doing? Use parentheses, which is what you'd be wise to do even if there were separate assignment and comparison operators. Watch out for phony "elegance." :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
On Tue, 2007-10-09 at 12:13 -0700, David Fetter wrote:
> On Tue, Oct 09, 2007 at 11:48:35AM -0700, Jeff Davis wrote:
> > On Tue, 2007-10-09 at 09:44 +0200, Kaare Rasmussen wrote:
> > > > In SQL, "=" is both an assignment operator (e.g. UPDATE) and a
> > > > comparison operator (e.g. WHERE clause). There are
> > > > nondeterministic
> > >
> > > Why would that be a problem when they occur in different parts of
> > > the statement?
> >
> > It can actually be in the same part of the statement:
> >
> > UPDATE foo SET b = a = 0;
>
> Don't Do That(TM).
Right, I don't; it was completely contrived. I am not making any attempt
to be elegant with such a statement.
I'm just trying to point out that, if there's a complex update statement
it might do something unexpected if you have a bug. Other languages have
different assignment and comparison operators to avoid these situations,
and I think it helps people catch bugs (and makes it easier to parse,
I'm sure).
Regards,
Jeff Davis
> UPDATE foo SET b = a = 0; > > is legal in PostgreSQL. How weird is that? At a glance, can you tell > what it's doing? Is it legal in standard SQL? -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: kaare@jasonic.dk
David, > Perfection isn't a human attribute, and there's a lot of evidence to > suggest it isn't a divine one either. Heh! Is that an *original* Fetter-ism? --Josh
On Wed, Oct 10, 2007 at 06:49:59PM -0700, Josh Berkus wrote: > David, > > >Perfection isn't a human attribute, and there's a lot of evidence to > >suggest it isn't a divine one either. > > Heh! Is that an *original* Fetter-ism? It is as far as I know ;) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate