Re: [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)

On 02/01/2011 03:36 AM, Robert Haas wrote:
> On Mon, Jan 31, 2011 at 5:40 PM, Nick Rudnick<joerg.rudnick@t-online.de>  wrote:
>> * In this regard it is of interest in how far there are principal efficiency
>> problems with the support of (deeply nested) object like structure by the
>> backend, or if the backend may be expected to do this job not terribly worse
>> then more specialized OODMS -- of course, I would be interested in any
>> discussions of these topics...
> I simply don't know what a more-specialized OODBMS would do that is
> similar to or different than what PostgreSQL does, so it's hard to
> comment.  I don't immediately see why we'd be any less efficient, but
> without knowing what algorithms are in use on the other side, it's a
> bit hard to say.
>
I assume this is a questions for experts in DB optimization -- I am
afraid that the indices or the query optimization might be suboptimal
for deeply nested structures -- on the other hand, it might be possible
that somebody would say that, with some WHISKY indices (;-)) or the
like, PostgreSQL would do good. After all, PostgreSQL (and I guess the
backend, too) is a very modular piece of software...
>> * The same question for doing rule bases on top of the PostgreSQL backend...
> I'm not sure if you're referring to the type of rules added by the SQL
> command CREATE RULE here, or some other kind of rule.  But the rules
> added by CREATE RULE are generally not too useful.  Most serious
> server programming is done using triggers.
For the kind usage of I am interested in please look:
http://en.wikipedia.org/wiki/Expert_system
http://en.wikipedia.org/wiki/Inference_engine
http://en.wikipedia.org/wiki/Deductive_database
http://en.wikipedia.org/wiki/Datalog
http://en.wikipedia.org/wiki/Forward_chaining

And yes, this can be done -- here an inelegant example (with many
obvious todos), demonstrating the simple «Colonel West example» of
Artificial Intelligence, a Modern Approach by Russell/Norvig in plain
PostgreSQL RULEs (in attachment, too):

= 8< ==========================================
-- for primordial facts:
CREATE TABLE american(person text);
CREATE TABLE missile(thing text);
CREATE TABLE owns(owner text, property text);
CREATE TABLE enemy(person text, target text);

-- for derived facts:
CREATE TABLE weapon(thing text);
CREATE TABLE sells(seller text, thing text, buyer text);
CREATE TABLE hostile(person text);
CREATE TABLE criminal(person text);

-- rules:
CREATE RULE missile_is_a_weapon AS
        ON INSERT TO missile
        DO ALSO
        INSERT INTO weapon SELECT NEW.thing;

CREATE RULE enemy_of_america_is_hostile AS
        ON INSERT TO enemy WHERE NEW.target = 'America'
        DO ALSO
        INSERT INTO hostile SELECT NEW.person;

-- nono_can_get_missiles_only_from_west
CREATE RULE nono_can_get_missiles_only_from_west__missile AS
        ON INSERT TO missile
        DO ALSO
        INSERT INTO sells
        SELECT 'West' AS seller, NEW.thing, 'Nono' AS buyer
        FROM owns WHERE owner='Nono' AND property=NEW.thing;

CREATE RULE nono_can_get_missiles_only_from_west__owns AS
        ON INSERT TO owns WHERE NEW.owner='Nono'
        DO ALSO
        INSERT INTO sells
        SELECT 'West' AS seller, NEW.property, 'Nono' AS buyer
        FROM missile WHERE thing=NEW.property;

-- americans_selling_weapons_to_hostiles_are_criminal
CREATE RULE americans_selling_weapons_to_hostiles_are_criminal__hostile AS
        ON INSERT TO hostile
        DO ALSO
        INSERT INTO criminal
        SELECT seller FROM sells, weapon, american
        WHERE sells.buyer=NEW.person
                 AND sells.thing=weapon.thing
              AND sells.seller=american.person;

CREATE RULE americans_selling_weapons_to_hostiles_are_criminal__weapon AS
        ON INSERT TO weapon
        DO ALSO
        INSERT INTO criminal
        SELECT seller FROM sells, hostile, american
        WHERE sells.buyer=hostile.person
                 AND sells.thing=NEW.thing
              AND sells.seller=american.person;


CREATE RULE americans_selling_weapons_to_hostiles_are_criminal__american AS
        ON INSERT TO american
        DO ALSO
        INSERT INTO criminal
        SELECT seller FROM sells, hostile, weapon
        WHERE sells.buyer=hostile.person
                 AND sells.thing=weapon.thing
              AND sells.seller=NEW.person;

CREATE RULE americans_selling_weapons_to_hostiles_are_criminal__sells AS
        ON INSERT TO sells
        DO ALSO
        INSERT INTO criminal
        SELECT NEW.seller FROM american, hostile, weapon
        WHERE NEW.buyer=hostile.person
                 AND NEW.thing=weapon.thing
              AND NEW.seller=american.person;


-- entering some facts now:
INSERT INTO missile VALUES('M1');
INSERT INTO enemy VALUES('Nono','America');
INSERT INTO owns VALUES('Nono','M1');
INSERT INTO american VALUES('West');

-- querying the database:
SELECT * FROM criminal;
= 8< ==========================================

If this could be done efficiently, it would allow many interesting
applications -- I guess that e.g., in combination with the XML
functionality, a big part of semantic web engine functionality might be
given. I am also more optimistic in this case, as I guess relational
algebra is much closer related to Datalog logic programming (which seems
to be gaining more interest lately) than to OO.

>> * For teaching at university courses, on the other hand, efficiency would be
>> of lower interest, so there was an idea that there might be some (possibly
>> toy example like) efforts to tune the frontend into this direction.
> You're still being awfully vague about what you mean by "this direction".
>
Please excuse -- I cannot speak for this professor... his other option
is using Oracle for teaching, which might support ORDBMS functionality
slightly more -- anything more interesting (for teaching purposes!!!)
would speak for PostgreSQL.

Cheers, Nick