Re: Proper relational database?

On Sat, Apr 23, 2016 at 2:04 PM, <david(at)andl(dot)org> wrote:
>> Why is starting at a low level important? A database is truly relational to
>> the extent that it implements the relational model. If you don't want the
>> database to allow tables without keys, or to allow null values, don't let
>> people create them. If the underlying machinery allows them, that seems like
>> a mere performance issue; worrying about that from the outset seems like a
>> perfect example of premature optimization. If PostgreSQL's performance is
>> acceptable now, why wouldn't it be acceptable with a different interface
>> language?
>
> Agreed.
>
>> There are other aspects of what would make a truly relational database, of
>> course. Codd's 0th rule, for instance, that the "system must be able to
>> manage data bases entirely through its relational capabilities" to me says
>> that there should be no data definition language, except as syntactic sugar
>> for relational operations. So you'd create users (thousands in one command,
>> if you liked) by adding tuples to a base relation.
>
> Yes, maybe, but that makes it not part of the language. You can't apply rule 0 to things like creating a type or operator in a language.
>
>> But which things are important? I think a good many of the things one might
>> would be lower-hanging fruit than that. Just having a clean query language
>> would alleviate a lot of (my) discomfort.
>
> Andl is that.
>
>> > I don't know if Postgres exposes the lower-level stuff to plugins or
>> > not — it would be nice if this could be an alternative query language
>> > for Postgres itself,
>>
>> Well, the parser doesn't, but as best I can tell it's also somewhat loosely
>> coupled from the system. It doesn't do table access, for instance. It builds
>> and returns a parse tree. There's no reason you couldn't parse a different
>> language and return a tree of the same type. Or you could just translate
>> your input language into SQL, and pass it along to the existing parser.
>
> I looked into that, and it's too hard as a starting place. There is too much of the tree and the subsequent query planning that is hooked into specific features of SQL. Instead, Andl generates a small subset of SQL. Once each query has been generated and parsed, the prepared statements can be cached and you get most of the benefits.
>
>> > but the assumptions about the two worlds (SQL vs a properly relational
>> > store) are probably too different.
>
>> Are there relational algebra expressions, or other operations necessary to a
>> truly relational database, which cannot be translated into SQL? I'm not
>> aware that there are, but I'd be interested to hear of it. If there were,
>> there's a good chance you wouldn't be able to translate them into the parse
>> tree, either.
>
> Absolutely not. SQL is a (nearly) full implementation of the relational algebra, plus other non-relational stuff. The only thing it really can't handle is a table with no columns! (I have to fake that)

FWIW standard SQL may not allow it but Postgres does, and it's even
possible to exclude duplicates by using an expression that references
the whole row.

postgres=# select;
┌──┐
├──┤
└──┘
(1 row)
postgres=# create table dum ();
CREATE TABLE
postgres=# select * from dum;
┌──┐
├──┤
└──┘
(0 rows)
postgres=# create unique index dum_unique on dum((dum));
CREATE INDEX
postgres=# insert into dum select;
INSERT 0 1
postgres=# select * from dum;
┌──┐
├──┤
└──┘
(1 row)
postgres=# insert into dum select;
ERROR: duplicate key value violates unique constraint "dum_unique"
DETAIL: Key ((dum.*))=(()) already exists.

--
Thomas Munro
http://www.enterprisedb.com