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

Dear all,

for the sake academic teaching, a colleague asked me in how far
PostgreSQL does support object functionality these days.

I am afraid my web research was not very fruitful to him; the impression
is that hardly anybody is occupied in working on PostgreSQL object
functionality -- have ORM mappers grown so strong?

The docs report that the SQL/OLB ISO/IEC 9075-10 part of the SQL
standard have no implementation yet.

So I'd like to place my questions here:

* are there any people / projects known which are interested in ORDBMS /
OODBMS usage of PostgreSQL? Strict SQL standard conformance is less
important than the possibility to provide instructive and impressive
examples to students.

* are there any people / projects known which are interested in
extending PostgreSQL at a higher level (plpgsql, creating operators,
etc.) for the sake of ORDBMS / OODBMS functionality?

* are there any people / projects known which are interested in
extending PostgreSQL on the level of developing C code for the sake of
ORDBMS / OODBMS functionality?

* in how far does the backend support such efforts -- would it do fine,
or is rather to be expected that doing ORDBMS / OODBMS driven queries
would lead to disastrous performance?

* are there any people / projects known which are interested in using
the rule (?trigger?) system of PostgreSQL (maybe with extensions) to
achieve some kind of rule base / datalog type inference engines? In how
far does the backend constrain this in regard of performance?

Thanks a lot in advance,

Nick

Hello

What I know no body is working on SQL/OLB ISO/IEC 9075-10 now.

I proposed a 3 years ago a support of methods, but without success.
This propose was rejected. There isn't a real interest to implement it
from commiters. And I have to say - users doesn't request it too. And
there are a few issues with compatibility.

That's all what I know.

Regards

Pavel Stehule

2011/1/31 Jörg Roman Rudnick <joerg(dot)rudnick(at)t-online(dot)de>:
> Dear all,
>
> for the sake academic teaching, a colleague asked me in how far PostgreSQL
> does support object functionality these days.
>
> I am afraid my web research was not very fruitful to him; the impression is
> that hardly anybody is occupied in working on PostgreSQL object
> functionality -- have ORM mappers grown so strong?
>
> The docs report that the SQL/OLB ISO/IEC 9075-10 part of the SQL standard
> have no implementation yet.
>
> So I'd like to place my questions here:
>
> * are there any people / projects known which are interested in ORDBMS /
> OODBMS usage of PostgreSQL? Strict SQL standard conformance is less
> important than the possibility to provide instructive and impressive
> examples to students.
>
> * are there any people / projects known which are interested in extending
> PostgreSQL at a higher level (plpgsql, creating operators, etc.) for the
> sake of ORDBMS / OODBMS functionality?
>
> * are there any people / projects known which are interested in extending
> PostgreSQL on the level of developing C code for the sake of ORDBMS / OODBMS
> functionality?
>
> * in how far does the backend support such efforts -- would it do fine, or
> is rather to be expected that doing ORDBMS / OODBMS driven queries would
> lead to disastrous performance?
>
> * are there any people / projects known which are interested in using the
> rule (?trigger?) system of PostgreSQL (maybe with extensions) to achieve
> some kind of rule base / datalog type inference engines? In how far does the
> backend constrain this in regard of performance?
>
> Thanks a lot in advance,
>
>    Nick
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Mon, Jan 31, 2011 at 4:34 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> What I know no body is working on SQL/OLB ISO/IEC 9075-10 now.
>
> I proposed a 3 years ago a support of methods, but without success.
> This propose was rejected. There isn't a real interest to implement it
> from commiters. And I have to say - users doesn't request it too. And
> there are a few issues with compatibility.

It seems to me it's a bit unfair to say "there isn't real interest to
implement it from committers". Plenty of features get implemented
that no committer particularly cares about, because a number of
committers - including me - spend a good deal of time reviewing and
committing patches written by other people which they never would have
written themselves. It's true that patches sometimes get swatted down
because they are judged to be insufficiently useful or badly design or
because they create compatibility breaks, but that's not the same as
"not interested", which to me implies a sort of purely arbitrary
rejection that I try hard to avoid.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jan 31, 2011 at 3:32 AM, Jörg Roman Rudnick
<joerg(dot)rudnick(at)t-online(dot)de> wrote:
> * are there any people / projects known which are interested in ORDBMS /
> OODBMS usage of PostgreSQL? Strict SQL standard conformance is less
> important than the possibility to provide instructive and impressive
> examples to students.
>
> * are there any people / projects known which are interested in extending
> PostgreSQL at a higher level (plpgsql, creating operators, etc.) for the
> sake of ORDBMS / OODBMS functionality?
>
> * are there any people / projects known which are interested in extending
> PostgreSQL on the level of developing C code for the sake of ORDBMS / OODBMS
> functionality?
>
> * in how far does the backend support such efforts -- would it do fine, or
> is rather to be expected that doing ORDBMS / OODBMS driven queries would
> lead to disastrous performance?
>
> * are there any people / projects known which are interested in using the
> rule (?trigger?) system of PostgreSQL (maybe with extensions) to achieve
> some kind of rule base / datalog type inference engines? In how far does the
> backend constrain this in regard of performance?

I don't really know much about ORDBMS / OODBMS functionality; a quick
Google search suggests that SQL/OLB is mostly about Java language
bindings, and there's a separate project (pgsql-jdbc) which works on
PostgreSQL connectivity for Java. As far as changes to the core
database are concerned, user-defined functions and operators are not
hard to create, but I'm fuzzy on what specifically you want to do.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Interesting... I remember that some years ago, I fiddled around with
functions, operators etc. to allow a method like syntax -- but I ever
was worried this approach would have serious weaknesses -- are there any
principal hindrances to having methods, if no, can this be implemented
in a straightforward way?

Thank you in advance,

Nick

On 01/31/2011 03:19 PM, Robert Haas wrote:
> On Mon, Jan 31, 2011 at 4:34 AM, Pavel Stehule<pavel(dot)stehule(at)gmail(dot)com> wrote:
>> What I know no body is working on SQL/OLB ISO/IEC 9075-10 now.
>>
>> I proposed a 3 years ago a support of methods, but without success.
>> This propose was rejected. There isn't a real interest to implement it
>> from commiters. And I have to say - users doesn't request it too. And
>> there are a few issues with compatibility.
> It seems to me it's a bit unfair to say "there isn't real interest to
> implement it from committers". Plenty of features get implemented
> that no committer particularly cares about, because a number of
> committers - including me - spend a good deal of time reviewing and
> committing patches written by other people which they never would have
> written themselves. It's true that patches sometimes get swatted down
> because they are judged to be insufficiently useful or badly design or
> because they create compatibility breaks, but that's not the same as
> "not interested", which to me implies a sort of purely arbitrary
> rejection that I try hard to avoid.
>

Hello Robert,

a good moment to clear things up:

* Of course, compliance with an ISO-SQL standard is of minimal
importance -- I just grabbed it from the docs.

* The same holds (in a somewhat weaker way) for Java -- I would even
prefer the more general notion type instead of OO, but I am asking in
interest of a CS professor I worked for in the past, who is looking for
impressive demos of non standard DBMS technology. The students might
find resemblance to Java helpful, who knows?

* 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...

* The same question for doing rule bases on top of the PostgreSQL backend...

* 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.

== Academic prototypes, which preferably do not bring too much overhead
for students.

Cheers,

Nick

On 01/31/2011 03:22 PM, Robert Haas wrote:
> On Mon, Jan 31, 2011 at 3:32 AM, Jörg Roman Rudnick
> <joerg(dot)rudnick(at)t-online(dot)de> wrote:
>> * are there any people / projects known which are interested in ORDBMS /
>> OODBMS usage of PostgreSQL? Strict SQL standard conformance is less
>> important than the possibility to provide instructive and impressive
>> examples to students.
>>
>> * are there any people / projects known which are interested in extending
>> PostgreSQL at a higher level (plpgsql, creating operators, etc.) for the
>> sake of ORDBMS / OODBMS functionality?
>>
>> * are there any people / projects known which are interested in extending
>> PostgreSQL on the level of developing C code for the sake of ORDBMS / OODBMS
>> functionality?
>>
>> * in how far does the backend support such efforts -- would it do fine, or
>> is rather to be expected that doing ORDBMS / OODBMS driven queries would
>> lead to disastrous performance?
>>
>> * are there any people / projects known which are interested in using the
>> rule (?trigger?) system of PostgreSQL (maybe with extensions) to achieve
>> some kind of rule base / datalog type inference engines? In how far does the
>> backend constrain this in regard of performance?
> I don't really know much about ORDBMS / OODBMS functionality; a quick
> Google search suggests that SQL/OLB is mostly about Java language
> bindings, and there's a separate project (pgsql-jdbc) which works on
> PostgreSQL connectivity for Java. As far as changes to the core
> database are concerned, user-defined functions and operators are not
> hard to create, but I'm fuzzy on what specifically you want to do.
>

On Mon, Jan 31, 2011 at 5:09 PM, Nick Rudnick <joerg(dot)rudnick(at)t-online(dot)de> wrote:
> Interesting... I remember that some years ago, I fiddled around with
> functions, operators etc. to allow a method like syntax -- but I ever was
> worried this approach would have serious weaknesses -- are there any
> principal hindrances to having methods, if no, can this be implemented in a
> straightforward way?

It would help if you were a bit more specific. Do you mean you want
to write something like foo.bar(baz) and have that mean call the bar
method of foo and pass it baz as an argument?

If so, that'd certainly be possible to implement for purposes of a
college course, if you're so inclined - after all it's free software -
but we'd probably not make such a change to core PG, because right now
that would mean call the function bar in schema baz and pass it foo as
an argument. We try not to break people's code to when adding
nonstandard features.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jan 31, 2011 at 5:40 PM, Nick Rudnick <joerg(dot)rudnick(at)t-online(dot)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.

> * 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 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".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> It would help if you were a bit more specific. Do you mean you want
> to write something like foo.bar(baz) and have that mean call the bar
> method of foo and pass it baz as an argument?

> If so, that'd certainly be possible to implement for purposes of a
> college course, if you're so inclined - after all it's free software -
> but we'd probably not make such a change to core PG, because right now
> that would mean call the function bar in schema baz and pass it foo as
> an argument. We try not to break people's code to when adding
> nonstandard features.

You would probably have better luck shoehorning in such a feature if the
syntax looked like this:

(foo).bar(baz)

foo being a value of some type that has methods, and bar being a method
name. Another possibility is

foo->bar(baz)

I agree with Robert's opinion that it'd be unlikely the project would
accept such a patch into core, but if you're mainly interested in it
for research purposes that needn't deter you.

regards, tom lane

2011/2/1 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Mon, Jan 31, 2011 at 5:09 PM, Nick Rudnick <joerg(dot)rudnick(at)t-online(dot)de> wrote:
>> Interesting... I remember that some years ago, I fiddled around with
>> functions, operators etc. to allow a method like syntax -- but I ever was
>> worried this approach would have serious weaknesses -- are there any
>> principal hindrances to having methods, if no, can this be implemented in a
>> straightforward way?
>
> It would help if you were a bit more specific.  Do you mean you want
> to write something like foo.bar(baz) and have that mean call the bar
> method of foo and pass it baz as an argument?
>
> If so, that'd certainly be possible to implement for purposes of a
> college course, if you're so inclined - after all it's free software -
> but we'd probably not make such a change to core PG, because right now
> that would mean call the function bar in schema baz and pass it foo as
> an argument.  We try not to break people's code to when adding
> nonstandard features.
>

I has not a standard, so I am not sure what is in standard and what
not. It was a popular theme about year 2000 and OOP was planed to
SQL3. You can find a some presentation from this time. Oracle
implemented these features.

J. Melton: SQL:1999: Understanding Object-Relational and
Other Advanced Features, Morgan Kaufmann, 2003.

CREATE METHOD next_color (n INT)
RETURNS INT
FOR colored_part_t
RETURN SELF.color_id + n

SELECT partno, color_id, DEREF(oid).next_color(1) AS next
FROM colored_parts

some other databases implemented a dereferenced data (it's not only
Oracle's subject)

http://www.java2s.com/Code/Oracle/Object-Oriented-Database/DEREFDereferencetheRowAddresses.htm

Probably DB2 implements this functionality too. See doc for CREATE
TYPE statement, REF USING, NOT FINAL, method specification

CREATE TYPE type-name
...
METHOD attribute-name()
RETURNS attribute-type

these features are very nice - but is not well documented and probably not used.

Pavel

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Hello

it is part of ANSi SQL 2003

http://savage.net.au/SQL/sql-2003-2.bnf.html#method%20specification%20designator

2011/2/1 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2011/2/1 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> On Mon, Jan 31, 2011 at 5:09 PM, Nick Rudnick <joerg(dot)rudnick(at)t-online(dot)de> wrote:
>>> Interesting... I remember that some years ago, I fiddled around with
>>> functions, operators etc. to allow a method like syntax -- but I ever was
>>> worried this approach would have serious weaknesses -- are there any
>>> principal hindrances to having methods, if no, can this be implemented in a
>>> straightforward way?
>>
>> It would help if you were a bit more specific.  Do you mean you want
>> to write something like foo.bar(baz) and have that mean call the bar
>> method of foo and pass it baz as an argument?
>>
>> If so, that'd certainly be possible to implement for purposes of a
>> college course, if you're so inclined - after all it's free software -
>> but we'd probably not make such a change to core PG, because right now
>> that would mean call the function bar in schema baz and pass it foo as
>> an argument.  We try not to break people's code to when adding
>> nonstandard features.
>>
>
> I has not a standard, so I am not sure what is in standard and what
> not. It was a popular theme about year 2000 and OOP was planed to
> SQL3. You can find a some presentation from this time. Oracle
> implemented these features.
>
> J. Melton: SQL:1999: Understanding Object-Relational and
> Other Advanced Features, Morgan Kaufmann, 2003.
>
>
> CREATE METHOD next_color (n INT)
> RETURNS INT
> FOR colored_part_t
> RETURN SELF.color_id + n
>
> SELECT partno, color_id, DEREF(oid).next_color(1) AS next
> FROM colored_parts
>
> some other databases implemented a dereferenced data (it's not only
> Oracle's subject)
>
> http://www.java2s.com/Code/Oracle/Object-Oriented-Database/DEREFDereferencetheRowAddresses.htm
>
> Probably DB2 implements this functionality too. See doc for CREATE
> TYPE statement, REF USING, NOT FINAL, method specification
>
>  CREATE TYPE  type-name
>       ...
>     METHOD attribute-name()
>       RETURNS attribute-type
>
> these features are very nice - but is not well documented and probably not used.
>
> Pavel
>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>

On Mon, Jan 31, 2011 at 9:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> It would help if you were a bit more specific.  Do you mean you want
>> to write something like foo.bar(baz) and have that mean call the bar
>> method of foo and pass it baz as an argument?
>
>> If so, that'd certainly be possible to implement for purposes of a
>> college course, if you're so inclined - after all it's free software -
>> but we'd probably not make such a change to core PG, because right now
>> that would mean call the function bar in schema baz and pass it foo as
>> an argument.  We try not to break people's code to when adding
>> nonstandard features.
>
> You would probably have better luck shoehorning in such a feature if the
> syntax looked like this:
>
>        (foo).bar(baz)
>
> foo being a value of some type that has methods, and bar being a method
> name.  Another possibility is
>
>        foo->bar(baz)
>
> I agree with Robert's opinion that it'd be unlikely the project would
> accept such a patch into core, but if you're mainly interested in it
> for research purposes that needn't deter you.

Using an arrow definitely seems less problematic than using a dot.
Dot means too many things already.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2011/2/1 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Mon, Jan 31, 2011 at 9:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> It would help if you were a bit more specific.  Do you mean you want
>>> to write something like foo.bar(baz) and have that mean call the bar
>>> method of foo and pass it baz as an argument?
>>
>>> If so, that'd certainly be possible to implement for purposes of a
>>> college course, if you're so inclined - after all it's free software -
>>> but we'd probably not make such a change to core PG, because right now
>>> that would mean call the function bar in schema baz and pass it foo as
>>> an argument.  We try not to break people's code to when adding
>>> nonstandard features.
>>
>> You would probably have better luck shoehorning in such a feature if the
>> syntax looked like this:
>>
>>        (foo).bar(baz)
>>
>> foo being a value of some type that has methods, and bar being a method
>> name.  Another possibility is
>>
>>        foo->bar(baz)
>>
>> I agree with Robert's opinion that it'd be unlikely the project would
>> accept such a patch into core, but if you're mainly interested in it
>> for research purposes that needn't deter you.
>
> Using an arrow definitely seems less problematic than using a dot.
> Dot means too many things already.

sure, but it's out of standard :(

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

On Mon, Jan 31, 2011 at 11:41 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> CREATE METHOD next_color (n INT)
> RETURNS INT
> FOR colored_part_t
> RETURN SELF.color_id + n
>
> SELECT partno, color_id, DEREF(oid).next_color(1) AS next
> FROM colored_parts

DEREF(oid)? That's just bizarre.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2011/2/1 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Mon, Jan 31, 2011 at 11:41 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> CREATE METHOD next_color (n INT)
>> RETURNS INT
>> FOR colored_part_t
>> RETURN SELF.color_id + n
>>
>> SELECT partno, color_id, DEREF(oid).next_color(1) AS next
>> FROM colored_parts
>
> DEREF(oid)?  That's just bizarre.
>

have to look on this topic more complex :). There are some papers.

It's sadly so these features wasn't used more and world is controlled
by ORMs like Hibernate and company :(

We did a some OOP meta language -> PL/pgSQL translator and lot of
tasks was processed simply without deep SQL programming.

It was a strange tool :) - compiler to PL/pgSQL in PL/pgSQL :)

Regards

Pavel Stehule

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

On mån, 2011-01-31 at 21:53 -0500, Tom Lane wrote:
> You would probably have better luck shoehorning in such a feature if the
> syntax looked like this:
>
> (foo).bar(baz)
>
> foo being a value of some type that has methods, and bar being a method
> name.

The SQL standard has the <method invocation> clause that appears to
allow:

...something.column.method(args)

Good luck finding out how to interpret the dots, but it's specified
somewhere.

It'd be kind of nice as a syntax and namespacing alternative, actually,
but figuring out the compatibility problems would be a headache.

> Another possibility is
>
> foo->bar(baz)

This is in the SQL standard under <attribute or method reference>, but
it requires the left side to be of a reference type, which is something
that we don't have.

On Tue, Feb 1, 2011 at 10:11 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> The SQL standard has the <method invocation> clause that appears to
> allow:
>
>    ...something.column.method(args)
>
> Good luck finding out how to interpret the dots, but it's specified
> somewhere.

My head just exploded.

> It'd be kind of nice as a syntax and namespacing alternative, actually,
> but figuring out the compatibility problems would be a headache.

No joke.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/01/2011 03:36 AM, Robert Haas wrote:
> On Mon, Jan 31, 2011 at 5:40 PM, Nick Rudnick<joerg(dot)rudnick(at)t-online(dot)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

Nick Rudnick <joerg(dot)rudnick(at)t-online(dot)de> wrote:

> here an inelegant example

Based on that example, you should be sure to look at the INHERITS
clause of CREATE TABLE:

http://www.postgresql.org/docs/current/interactive/sql-createtable.html

PostgreSQL has the "is a" structure built in. That may not get you
all the way there, but between that and a few views, you might get
close without needing a lot of low level infrastructure work.

-Kevin

Hi Pavel,

I guess this represents most exactly what this professor is thinking
about -- being able to create methods and types with methods which can
be nested -- but syntactical details are of secondary importance.

All the best, Nick

On 02/01/2011 05:43 AM, Pavel Stehule wrote:
> Hello
>
> it is part of ANSi SQL 2003
>
> http://savage.net.au/SQL/sql-2003-2.bnf.html#method%20specification%20designator
>
>
> 2011/2/1 Pavel Stehule<pavel(dot)stehule(at)gmail(dot)com>:
>> 2011/2/1 Robert Haas<robertmhaas(at)gmail(dot)com>:
>>> On Mon, Jan 31, 2011 at 5:09 PM, Nick Rudnick<joerg(dot)rudnick(at)t-online(dot)de> wrote:
>>>> Interesting... I remember that some years ago, I fiddled around with
>>>> functions, operators etc. to allow a method like syntax -- but I ever was
>>>> worried this approach would have serious weaknesses -- are there any
>>>> principal hindrances to having methods, if no, can this be implemented in a
>>>> straightforward way?
>>> It would help if you were a bit more specific. Do you mean you want
>>> to write something like foo.bar(baz) and have that mean call the bar
>>> method of foo and pass it baz as an argument?
>>>
>>> If so, that'd certainly be possible to implement for purposes of a
>>> college course, if you're so inclined - after all it's free software -
>>> but we'd probably not make such a change to core PG, because right now
>>> that would mean call the function bar in schema baz and pass it foo as
>>> an argument. We try not to break people's code to when adding
>>> nonstandard features.
>>>
>> I has not a standard, so I am not sure what is in standard and what
>> not. It was a popular theme about year 2000 and OOP was planed to
>> SQL3. You can find a some presentation from this time. Oracle
>> implemented these features.
>>
>> J. Melton: SQL:1999: Understanding Object-Relational and
>> Other Advanced Features, Morgan Kaufmann, 2003.
>>
>>
>> CREATE METHOD next_color (n INT)
>> RETURNS INT
>> FOR colored_part_t
>> RETURN SELF.color_id + n
>>
>> SELECT partno, color_id, DEREF(oid).next_color(1) AS next
>> FROM colored_parts
>>
>> some other databases implemented a dereferenced data (it's not only
>> Oracle's subject)
>>
>> http://www.java2s.com/Code/Oracle/Object-Oriented-Database/DEREFDereferencetheRowAddresses.htm
>>
>> Probably DB2 implements this functionality too. See doc for CREATE
>> TYPE statement, REF USING, NOT FINAL, method specification
>>
>> CREATE TYPE type-name
>> ...
>> METHOD attribute-name()
>> RETURNS attribute-type
>>
>> these features are very nice - but is not well documented and probably not used.
>>
>> Pavel
>>
>>> --
>>> Robert Haas
>>> EnterpriseDB: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>

Hi Peter,

>> Another possibility is
>> foo->bar(baz)
>> This is in the SQL standard under<attribute or method reference>, but
>> it requires the left side to be of a reference type, which is something
>> that we don't have.
I think this is the point where I stopped my efforts in the past -- I
guessed that a reference, in PostgreSQL relational algebra, could be a
pair of a pg_class oid together with the object's oid (having to query
the pg_class oid each time seemed very expensive to me, then). I fiddled
around with a little C programming, then I lost confidence in whether I
was doing something reasonable -- I was afraid I did not know enough
about the internals to predict a convincing outcome.

All the best,

Nick

Hi Kevin,

this example was for teaching AI students (with limited PostgreSQL
knowledge) in a very basic lecture -- I did not want to tweak the SQL
semantics too much; just demonstrate why SQL is rightfully called a 4GL
language. ;-)

Cheers, Nick

On 02/01/2011 10:08 PM, Kevin Grittner wrote:
> Nick Rudnick<joerg(dot)rudnick(at)t-online(dot)de> wrote:
>
>> here an inelegant example
>
> Based on that example, you should be sure to look at the INHERITS
> clause of CREATE TABLE:
>
> http://www.postgresql.org/docs/current/interactive/sql-createtable.html
>
> PostgreSQL has the "is a" structure built in. That may not get you
> all the way there, but between that and a few views, you might get
> close without needing a lot of low level infrastructure work.
>
> -Kevin
>

May I sum up?

o in the recent there are no efforts known to experiment with
reference types, methods, or rule inference on top of PostgreSQL --
advice that can be given mostly points to the given documented functionality

o inside the PostgreSQL community, there is not many knowledge in
circulation in regard of performance effects of using deeply nested data
structures (with the possible exception of XML handling) or doing rule
inference on top oof PostgreSQL -- but at least, there also are no
substantial contraindications

o extensions of PostgreSQL to support such a kind of usage have to be
expected to be expected to be rejected from integration to the code base
core -- i.e., if they are done, students have to be told «you can't
expect this to become a part of PostgreSQL»

Is this understood correctly, especially the last point, or did
Robert/Tom just specifically address syntactical conflicts (between
schema and object semantics) with the point notation?

If not, it might be discouraging for lecture, as there might be interest
to present something which at least might be imagined once to become a
standard tool.

Otherwise, the striking lack of academical initiatives in the area of OO
and rule inference on top of PostgreSQL appears to me as a demand to

a. check out academic sources, whether principle efficience issues of
backend design discourage it so obviously that people do not even try it out

b. if this is not the case, to propose this professor to try to fill the
gap... ;-) In this case, regarding method semantics extensions, avoiding
conflicts with existent language constructs certainly will be
preferable, as these will be small projects.

Cheers, Nick

(my last two posts seemingly did not reach the HACKERS forum, so please
let me resend the last one ;-) )

May I sum up?

o in the recent there are no efforts known to experiment with
reference types, methods, or rule inference on top of PostgreSQL --
advice that can be given mostly points to the given documented
functionality

o inside the PostgreSQL community, there is not many knowledge in
circulation in regard of performance effects of using deeply nested data
structures (with the possible exception of XML handling) or doing rule
inference on top oof PostgreSQL -- but at least, there also are no
substantial contraindications

o extensions of PostgreSQL to support such a kind of usage have to be
expected to be expected to be rejected from integration to the code base
core -- i.e., if they are done, students have to be told «you can't
expect this to become a part of PostgreSQL»

Is this understood correctly, especially the last point, or did
Robert/Tom just specifically address syntactical conflicts (between
schema and object semantics) with the point notation?

If not, it might be discouraging for lecture, as there might be interest
to present something which at least might be imagined once to become a
standard tool.

Otherwise, the striking lack of academical initiatives in the area of OO
and rule inference on top of PostgreSQL appears to me as a demand to

a. check out academic sources, whether principle efficience issues of
backend design discourage it so obviously that people do not even try it
out

b. if this is not the case, to propose this professor to try to fill the
gap... ;-) In this case, regarding method semantics extensions, avoiding
conflicts with existent language constructs certainly will be
preferable, as these will be small projects.

Cheers, Nick

On Tue, Feb 8, 2011 at 2:23 AM, Nick Rudnick <joerg(dot)rudnick(at)t-online(dot)de> wrote:
> (my last two posts seemingly did not reach the HACKERS forum, so please let
> me resend the last one ;-) )

They got here - I think just no one had any further comment.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On lör, 2011-02-05 at 12:26 +0100, Nick Rudnick wrote:
> o extensions of PostgreSQL to support such a kind of usage have to
> be expected to be expected to be rejected from integration to the code
> base core -- i.e., if they are done, students have to be told «you
> can't expect this to become a part of PostgreSQL»

There are very varied interests in this community. But you have to keep
in mind that PostgreSQL is a production software package, not a research
platform. This doesn't mean that we discourage research with the
PostgreSQL code base. But that should be done in a fork. Code aimed
for inclusion in a proper PostgreSQL release should have demonstrable
real life use and should be developed according to certain standards and
in close corporation with the community. If a research project can deal
with that, great, but typically, research projects are there to
determine whether something could have real-life value in the first
place, and development follows different standards of quality.

> o in the recent there are no efforts known to experiment with
> reference types, methods, or rule inference on top of PostgreSQL --
> advice that can be given mostly points to the given documented
> functionality

Correct, AFAIK.

> o extensions of PostgreSQL to support such a kind of usage have to be
> expected to be expected to be rejected from integration to the code base
> core -- i.e., if they are done, students have to be told «you can't
> expect this to become a part of PostgreSQL»

Not necessarily. We "rule out" *very* few things from PostgreSQL; I
think the TODO list only has 3 ideas which are contraindicated.

However, the warning is that this particular *set* of ideas has some
very high hurdles to jump before it could be considered seriously for
core, and that none of the existing committers seem interested in
helping with it. So the level of difficulty for the implementer would
be considerably greater than for many other patches of less invasiveness
and clearer apparent utility.

Among the hurdles are:
a. performance: you'd have to work out how to make nested object
resolution not take forever and burn up the CPUs
b. resolution: you'd need to come up with an object naming practice
which compliments, intead of conflicts with, the SQL-standard syntax
c. utility: you'd have to demonstrate why all this was actually useful.

> Is this understood correctly, especially the last point, or did
> Robert/Tom just specifically address syntactical conflicts (between
> schema and object semantics) with the point notation?

Syntactic conflicts are also significant, as anyone who's used EDB's
"packages" mod can tell you. So these would need to be worked out as
well, and NOT in a way which breaks backwards compatibility.

> Otherwise, the striking lack of academical initiatives in the area of OO
> and rule inference on top of PostgreSQL appears to me as a demand to

Hmmm. I don't know about that; I've never seen that academics *cared*
whether or not their code god into -core.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

Hi Josh,

at first, thanks for all the interesting info given
> Correct, AFAIK.
>> o extensions of PostgreSQL to support such a kind of usage have to be
>> expected to be expected to be rejected from integration to the code base
>> core -- i.e., if they are done, students have to be told «you can't
>> expect this to become a part of PostgreSQL»
> Not necessarily. We "rule out" *very* few things from PostgreSQL; I
> think the TODO list only has 3 ideas which are contraindicated.
After reading these points, I would say it really is a very liberal
policy... and I can't resist the temptation to ask about garbage collection:

I remember well PostgreSQL has an own garbage collection (palloc()
etc.;I only know it is in utils of the backend, at mmgr), but I didn't
find it on the TODO list; so

o would you say "hands off the garbage collection" or could you
imagine extensions?

o would you consider the PostgreSQL garbage collection to be rather
specific (e.g., DBMS specific optimizations) or heavily interwoven into
the code -- or could it be conceivable that behaviour requirements from
PostgreSQL could be specified so far that alternative garbage
collections can be developed and inserted?
> However, the warning is that this particular *set* of ideas has some
> very high hurdles to jump before it could be considered seriously for
> core, and that none of the existing committers seem interested in
> helping with it. So the level of difficulty for the implementer would
> be considerably greater than for many other patches of less invasiveness
> and clearer apparent utility.
>
> Among the hurdles are:
> a. performance: you'd have to work out how to make nested object
> resolution not take forever and burn up the CPUs
Time for a 'coming out': In the last years, I have mostly worked with
pure typed declarative languages (Haskell, Mercury), so I personally
have a type system like Hindley-Milner in mind, which by concept does
not have infinite pointer chains and also in other regards seems to be
in much closer relationship with relational database systems (an
interesting, though early state effort: http://groups.inf.ed.ac.uk/links/).

As (partially due to emerging multicore technology) the trend seems to
go into this direction (Scala for Java, inclusion of FP functionality in
C++), and such type system may be regarded as competitive with OO, I
personally don't see much motivation for a such forced marriage like
ISO/IEC 9075-10 (SQL/OLB).

The difficulty in regard of pure typed declarative languages rather
seems to be garbage collection (therefore the question above), as it in
these language environments uses to be a very sophisticated piece of
programming, subject of intensive research efforts, so it would be a
problem if the PostgreSQL garbage collection would be equally complex.
> b. resolution: you'd need to come up with an object naming practice
> which compliments, intead of conflicts with, the SQL-standard syntax
of course...
> c. utility: you'd have to demonstrate why all this was actually useful.
>
in short gross words: a typed rulebase extension. ai. joint-venturing
with leading teams in this area.
>> Is this understood correctly, especially the last point, or did
>> Robert/Tom just specifically address syntactical conflicts (between
>> schema and object semantics) with the point notation?
> Syntactic conflicts are also significant, as anyone who's used EDB's
> "packages" mod can tell you. So these would need to be worked out as
> well, and NOT in a way which breaks backwards compatibility.
completely d'accord...
>> Otherwise, the striking lack of academical initiatives in the area of OO
>> and rule inference on top of PostgreSQL appears to me as a demand to
> Hmmm. I don't know about that; I've never seen that academics *cared*
> whether or not their code god into -core.
>
Unfortunately this is well said...

Thanks for your infos,

Nick

On Fri, Feb 11, 2011 at 6:40 PM, Nick Rudnick <joerg(dot)rudnick(at)t-online(dot)de> wrote:
> I remember well PostgreSQL has an own garbage collection (palloc() etc.;I
> only know it is in utils of the backend, at mmgr), but I didn't find it on
> the TODO list; so
>
> o   would you say "hands off the garbage collection" or could you imagine
> extensions?
>
> o   would you consider the PostgreSQL garbage collection to be rather
> specific (e.g., DBMS specific optimizations) or heavily interwoven into the
> code -- or could it be conceivable that behaviour requirements from
> PostgreSQL could be specified so far that alternative garbage collections
> can be developed and inserted?

Despite occasional posturing, I think that our community tends to take
a pretty pragmatic view of the world. If a patch can be shown to have
more upside than downside, we tend to accept it. If it has more
downside than upside, we reject it. Now, I think all of us have lists
of things that we're most interested in working on, based on the
problems that we encounter in real life. I worked on join removal
because it solved particular problems that I ran into when building
web applications. Greg Smith works on checkpointing and other issues
related to disk I/O because he helps people build big honking servers
that use PostgreSQL, and that's where he runs into problems. Kevin
Grittner implemented SSI for true serializability because it solves a
problem he has. If you're fired up about improving memory management,
by all means have a crack at it. As an armchair quarterback, I'm a
bit doubtful that there is a significant amount of efficiency or
maintainability that can be squeezed out of that subsystem, but I'd be
happy to be proven wrong. There are certainly workloads where palloc
overhead is significant, and if someone can find a way to make a
meaningful improvement there, we're not going to reject it because we
didn't think of it ourselves.

> Time for a 'coming out': In the last years, I have mostly worked with pure
> typed declarative languages (Haskell, Mercury), so I personally have a type
> system like Hindley-Milner in mind, which by concept does not have infinite
> pointer chains and also in other regards seems to be in much closer
> relationship with relational database systems (an interesting, though early
> state effort: http://groups.inf.ed.ac.uk/links/).

I've been interested in this in the past, but the implementation
challenges are daunting.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company