On 02/01/2011 03:36 AM, Robert Haas wrote:
On Mon, Jan 31, 2011 at 5:40 PM, Nick Rudnick<joerg.rudn...@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
-- 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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
