Chris Bitmead writes:

[ONLY]
> For UPDATE and DELETE it is absolutely correct, and useful, not to
> mention absolutely essential.

Could you explain how and why, preferably with a concrete example? I am
still at a loss.

> > the `SELECT **' syntax (bad idea, IMO), 
> 
> Why is it a bad idea (considering that every ODBMS on the planet does
> this)?

First of all, ODBMS and [O]RDBMS are not necessarily infinitely compatible
concepts. An ORDBMS is an RDBMS extended with OO'ish features such as
table inheritance and abstract data types to make data modeling easier for
those who like it. But below it all there's still relational algebra and
friends. An ODBMS is a paradigm shift to get rid of some restrictions in
relational databases, both technical and theoretical, the implication of
which is that it's no longer a relational database. Please correct me if
I'm wrong.

Specifically, a query on a relational database always returns a table, and
a table is a set of rows with the same number and types of columns. This
is a pretty fundamental assumption, and even accounting for the
possibility that it might be broken somehow is going to be a major effort
throughout the entire system.

Now a question in particular. I understand that this syntax might
give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
(a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
(b, c, e) and (b, c, h)?

Finally, it seems that the same effect can be obtained with a UNION query,
padding with NULLs where necessary and perhaps judicious use of
CORRESPONDING. What would be wrong with that?


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Sat, 20 May 2000 15:35:23 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: OO Patch
Peter Eisentraut wrote:
> 
> Chris Bitmead writes:
> 
> [ONLY]
> > For UPDATE and DELETE it is absolutely correct, and useful, not to
> > mention absolutely essential.
> 
> Could you explain how and why, preferably with a concrete example? I am
> still at a loss.

The simple answer is that UPDATE and DELETE should not act different to
SELECT. If SELECT returns a certain set of records with a particular
WHERE clause, then DELETE should delete the same set of records with
identical WHERE clause and UPDATE should UPDATE the same set of records.
Which part of this is tricky?

The complex answer is in your own SQL3 research. Now of course Postgres
is not implemented that way that the SQL3 model seems to imply (a good
thing IMHO). Columns that came from super tables are stored in the most
specific table. But the end result has to conform to the description in
your other posting. I'll comment a little more on your other posting.

> > > the `SELECT **' syntax (bad idea, IMO),
> >
> > Why is it a bad idea (considering that every ODBMS on the planet does
> > this)?
> 
> First of all, ODBMS and [O]RDBMS are not necessarily infinitely 
> compatible concepts. 

Why?

> An ORDBMS is an RDBMS extended with OO'ish features such as
> table inheritance and abstract data types to make data modeling easier 
> for those who like it. 

The custom data type aspect of ORDBMS is a good feature. The inheritance
feature of ORDBMS is IMHO half-baked. Take the class
shape/circle/square example...
CREATE TABLE SHAPE( ..);
CREATE TABLE SQUARE(x1, y1, x2, y2) INHERITS(shape);
CREATE TABLE CIRCLE(x, y, radius) INHERITS(shape);

I can't just go SELECT * FROM SHAPE and call some C++ method to display
the shape on the screen. If I maintain an attribute in SHAPE called
"classname" manually, then I can SELECT * FROM SHAPE, and then do a
separate query on the subclass when I know the type - very inefficient.
Or I can do 3 separate queries. But then I'm hosed when I add a TRIANGLE
type.

What I really want is..

Result r = Query<Shape>.select("SELECT ** FROM SHAPE");
foreach(r, item) {
	item->display();
}

Which still will work when I add a triangle. I.e. typical polymorphism
code maintenance advantage.

Which is what object databases do or an object relational mapper like
Persistance do. Without that ability I would argue there's very limited
point in having inheritance at all.

> But below it all there's still relational algebra and
> friends. An ODBMS is a paradigm shift to get rid of some restrictions 
> in relational databases, both technical and theoretical, the 
> implication of
> which is that it's no longer a relational database. Please correct me 
> if I'm wrong.

It's no longer a purely relational database true. I think it's always
been a crazy idea that everything should be squeezed into a pure
table/column model.

> Specifically, a query on a relational database always returns a table, 
> and a table is a set of rows with the same number and types of columns. 
> This is a pretty fundamental assumption, and even accounting for the
> possibility that it might be broken somehow is going to be a major 
> effort throughout the entire system.

It's a pretty fundamentally limiting assumption. If you're saying that
this might be a lot of work to fix I think I agree. If you're saying
that you can't see the relational and object models being merged into a
coherent and useful combination then I disagree. I can see no conflict
at all between them. Both models are like seeing half the world. Both
models without the other are limiting.
 
> Now a question in particular. I understand that this syntax might
> give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
> (a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
> (b, c, e) and (b, c, h)?

I don't think I understand this question.

> Finally, it seems that the same effect can be obtained with a UNION 
> query,
> padding with NULLs where necessary and perhaps judicious use of
> CORRESPONDING. What would be wrong with that?

Several things. Firstly, what happens when you introduce TRIANGLE? You
have to rewrite every query in your system. Secondly, what if you have
20 classes in your hierarchy each with 20 different fields. Now you have
a UNION with 400 fields, most of which are NULL.

Sun, 21 May 2000 10:55:34 +1000
Chris <chris@bitmead.com>
Re: OO Patch

> CREATE TABLE SHAPE( ..);
> CREATE TABLE SQUARE(x1, y1, x2, y2) INHERITS(shape);
> CREATE TABLE CIRCLE(x, y, radius) INHERITS(shape);
> 
> I can't just go SELECT * FROM SHAPE and call some C++ method to display
> the shape on the screen. If I maintain an attribute in SHAPE called
> "classname" manually, then I can SELECT * FROM SHAPE, and then do a
> separate query on the subclass when I know the type - very inefficient.
> Or I can do 3 separate queries. But then I'm hosed when I add a TRIANGLE
> type.
> 
> What I really want is..
> 
> Result r = Query<Shape>.select("SELECT ** FROM SHAPE");
> foreach(r, item) {
> 	item->display();
> }
> 
> Which still will work when I add a triangle. I.e. typical polymorphism
> code maintenance advantage.
> 
> Which is what object databases do or an object relational mapper like
> Persistance do. Without that ability I would argue there's very limited
> point in having inheritance at all.
> 

 I can agree with that. As I wrote a relational mapper for Smalltalk/X
based on the libpq API I noticed the same problems, when doing mapping on 
tables.

 But some questions/comments about that:

 a) How are the indices handled ? If I define an index on an attribute
    defined in TABLE SHAPE all subclasses are also handled by this index
    or do we have an index for the base table and each sub table on 
    this attribute ?

 b) Please do not make the libpq API too compilcated ! It's a charm how
    small the API is and how easy the initial connection to a psqgl
    database is -- compare it against the ODBC API ....

 b.1)
    Despite the ODBC API I rather would like to see to enhance the idea 
    of result
    sets supported by the libpq-API. I do not need to query each tuple
    what it delivers to me. I would like to open the result, query
    the structure and then handle the data. If the database returns
    multiple different sets (results from different tables): ok: do it the 
    same way for each result set.

 b.2)
    There were some postings about other delivering methods to retrieve
    the information from each tuple. Today we get an ASCII-representation
    of the result tuple and the client has to convert it.

    Some were not very happy about it, but I like it. Some were concerned
    about the fact, that they have to copy the result to the information
    structure within their software. When you use software systems, which 
    are based on garbage collection systems, then one ALMOST EVER has
    to do it.

 c)
    I would like to see more ideas about the extension of pgsql to become
    an active database. The notification systen is not enough, because
    it does not return the most interesting informations.

    I myself would like to see something like the VERSANT event system.

 d) 
    Please only add basic, language independent, support for 
    inheritance - special features can very often better simulated by
    software on the client side. The best example is the introduction
    of sequences.



 Marten

 



Sun, 21 May 2000 10:12:59 +0200 (CEST)
Marten Feldtmann <marten@feki.toppoint.de>
Re: OO Patch
Peter Eisentraut wrote:
> 
> > > the `SELECT **' syntax (bad idea, IMO),
> >
> > Why is it a bad idea (considering that every ODBMS on the planet does
> > this)?
> 
> First of all, ODBMS and [O]RDBMS are not necessarily infinitely compatible
> concepts. An ORDBMS is an RDBMS extended with OO'ish features such as
> table inheritance and abstract data types to make data modeling easier for
> those who like it.

And which may be ignored by those who don't, just like SELECT ** .

> But below it all there's still relational algebra and
> friends. An ODBMS is a paradigm shift to get rid of some restrictions in
> relational databases, both technical and theoretical, the implication of
> which is that it's no longer a relational database. Please correct me if
> I'm wrong.

Adding DATE and TIME datatypes or functions to SQL may have also seemed
a 
paradigm shift but seems quite essential once it is done.

> 
> Specifically, a query on a relational database always returns a table, and
> a table is a set of rows with the same number and types of columns.

Says who ? ;)

> This is a pretty fundamental assumption, and even accounting for the
> possibility that it might be broken somehow is going to be a major effort
> throughout the entire system.

In first round ** could we disallowed in subselects and other tricky
parts.

> Now a question in particular. I understand that this syntax might
> give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
> (a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
> (b, c, e) and (b, c, h)?

What would you need that for ?

If its really needed we could implement something like

SELECT B,C,E?,H? FROM BASECLASS.

but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
other 
it would perhaps be better to do

SELECT B,C,SUB1.E?,SUB3.H? FROM BASECLASS.

which means the attribute E defined in subclass SUB1 (an inherited by
its 
descendants)

or perhaps

SELECT B,C,E OF SUB1,H OF SUB3 FROM BASECLASS.

to be style-compatible vith general verbosity and english-likeness of
SQL ;)

> Finally, it seems that the same effect can be obtained with a UNION query,
> padding with NULLs where necessary and perhaps judicious use of
> CORRESPONDING. What would be wrong with that?

It would be overly complex and error-prone and need a rewrite each time
a new
sub-class is added.

------------
Hannu

Sun, 21 May 2000 22:19:53 +0300
Hannu Krosing <hannu@tm.ee>
Re: OO Patch
Marten Feldtmann wrote:

>  I can agree with that. As I wrote a relational mapper for Smalltalk/X
> based on the libpq API I noticed the same problems, when doing mapping on
> tables.
> 
>  But some questions/comments about that:
> 
>  a) How are the indices handled ? If I define an index on an attribute
>     defined in TABLE SHAPE all subclasses are also handled by this index
>     or do we have an index for the base table and each sub table on
>     this attribute ?

At the moment there is a separate index for each subclass, but this
should probably not be the default.

>  b) Please do not make the libpq API too compilcated ! It's a charm how
>     small the API is and how easy the initial connection to a psqgl
>     database is -- compare it against the ODBC API ....

There will be 2 levels of API. An important part of an object database
is the existance of the client side cache. The lowest level API would be
something like libpq - that is uncached. Next there would be ODMG
interfaces for various languages that incorporate the client side cache.
I would say the only people who would continue to use libpq level would
be people writing higher level interfaces. ODMG is just too convenient.

>  b.1)
>     Despite the ODBC API I rather would like to see to enhance the idea
>     of result
>     sets supported by the libpq-API. I do not need to query each tuple
>     what it delivers to me. I would like to open the result, query
>     the structure and then handle the data. If the database returns
>     multiple different sets (results from different tables): ok: do it the
>     same way for each result set.

I'm a bit vague on what you mean here. But if you go the full OO way,
the language polymorphism will do all the handling of different result
sets. The ODMG layer will do the hard work.

>  b.2)
>     There were some postings about other delivering methods to retrieve
>     the information from each tuple. Today we get an ASCII-representation
>     of the result tuple and the client has to convert it.
> 
>     Some were not very happy about it, but I like it. Some were concerned
>     about the fact, that they have to copy the result to the information
>     structure within their software. When you use software systems, which
>     are based on garbage collection systems, then one ALMOST EVER has
>     to do it.

Again, using ODMG takes the hard work out of it. You stop caring about
what format the information is delivered in.

>  c)
>     I would like to see more ideas about the extension of pgsql to become
>     an active database. The notification systen is not enough, because
>     it does not return the most interesting informations.
> 
>     I myself would like to see something like the VERSANT event system.

Oh, a Versant fan. Good, I like Versant. The only flaw in the Versant
event system is events can be lost when the receiver is dead, which may
or may not matter depending on the application.

>  d)
>     Please only add basic, language independent, support for
>     inheritance - special features can very often better simulated by
>     software on the client side. The best example is the introduction
>     of sequences.

Mon, 22 May 2000 09:27:32 +1000
Chris Bitmead <chris@bitmead.com>
Re: OO Patch
Hannu Krosing wrote:
  >Peter Eisentraut wrote:
  >> Now a question in particular. I understand that this syntax might
  >> give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
  >> (a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
  >> (b, c, e) and (b, c, h)?
  >
  >What would you need that for ?
  
In OO terms it should be illegal.  In terms of any one class, there is a
defined set of columns that can be seen.  What Peter is asking for is a union
of selects on different classes.  The ordinary union rules should apply.

  >If its really needed we could implement something like
  >
  >SELECT B,C,E?,H? FROM BASECLASS.
  >
  >but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
  >other 

I don't think that should be allowed.  It violates inheritance principles,
since the types are not compatible.


There is quite a lot right with inheritance as it is.  We support multiple
inheritance, and columns with the same name are merged in the child.  What we
immediately lack are features to make inheritance properly useful:

* shared index - an index that should point to the correct child class for
  quick recovery of rows from inheritance hierarchies. 

* inheritance of constraints, including Primary/Foreign keys (does this
  imply the necessity of turning inheritance off in certain cases?)  

* handling of some write operations on a hierarchy: DELETE and UPDATE
  (INSERT must require the exact class to be specified)

* automatic use of inheritance hierarchies (use ONLY to avoid it)

* ALTER ... ADD COLUMN inserting columns in the correct positions in
  child tables; alternatively, have column numbering independent of
  the physical representation, so that columns can be added at the end
  but shown in the correct place by SELECT.


There are further complexities in OO which might be desirable, but would
require a lot of design work.  One fundamental feature of pure OO is that
classes carry their own methods, whereas SELECT (for example) imposes a
global operation on the various classes of the inheritance tree.  This
makes the following problematic:

* renaming columns in multiple inheritance (to avoid column merging, or to
  allow a child's column to be of a different type) - what would SELECT do
  with them?

* deferred classes - tables that are used only for inheritance rather than
  for storing data rows - how could these be specified and implemented?

No doubt further research would bring up many more examples.

I'm not sure it is feasible to make PostgreSQL into a proper OO database,
but getting those first five features would really be useful.



-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "We are troubled on every side, yet not distressed; we 
      are perplexed, but not in despair; persecuted, but not
      forsaken; cast down, but not destroyed; Always bearing
      about in the body the dying of the Lord Jesus, that 
      the life also of Jesus might be made manifest in our 
      body."        II Corinthians 4:8-10 



Mon, 22 May 2000 10:55:40 +0100
"Oliver Elphick" <olly@lfix.co.uk>
Re: OO Patch 
> Hannu Krosing wrote:
>   >
>   >but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
>   >other 
> 
> I don't think that should be allowed.  It violates inheritance principles,
> since the types are not compatible.

 I see ... here's a person who has always programmed with typed
languages and now thinks, that this is the right definition .... it's
much more out there in the world. Open your mind and think about the
following:

 An attribute named "a" of "type" TIMESTAMP of an instance of a class
can be seen as a relation from this class to the class TIMESTAMP and
this relation is named "a".

 And if you're on the way to relations you're not far away to see,
that a relation is of course not limited to show to one specific class
... but perhaps to all subclasses also ... and this is not a
violation.

 I know, that for many people these are only theoretical questions and
they may even be true with that, but "violation of inheritance
principles" is simply wrong.

 But I also know, that we deal with a relational database and I do not
expect, that it will be as good as a pure object-oriented database -
but all those great wrapper software in the market work with
relational databases and they work pretty well - but I also see, that
they only use the basic technology to do their work.

 The reason seems to be, that all those nice oo-features within all
those databases do not scale very well ... and they're good for a
single implementation. There're other problems out there:

  - caching at the client side 

  - more powerful db desing evolution features. Change the type, the
    length of a typed attribute
  

  
 
Marten



Mon, 22 May 2000 19:23:31 +0200 (CEST)
Marten Feldtmann <marten@feki.toppoint.de>
Re: OO Patch
Marten Feldtmann wrote:
  >> Hannu Krosing wrote:
  >>   >
  >>   >but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
  >>   >other 
  >> 
  >> I don't think that should be allowed.  It violates inheritance principles,
  >> since the types are not compatible.
  >
  > I see ... here's a person who has always programmed with typed
  >languages and now thinks, that this is the right definition .... it's
  >much more out there in the world. Open your mind and think about the
  >following:
  >
  > An attribute named "a" of "type" TIMESTAMP of an instance of a class
  >can be seen as a relation from this class to the class TIMESTAMP and
  >this relation is named "a".
  >
  > And if you're on the way to relations you're not far away to see,
  >that a relation is of course not limited to show to one specific class
  >... but perhaps to all subclasses also ... and this is not a
  >violation.
 
However the example I was referring to talked of INT4, TIMESTAMP or VARBINARY.

These are not subclasses but totally unrelated.  Suppose you had 

   parent (id char(2))
   child1 (a int4)
   child2 (a timestamp)

and someone asks for

   select sum(a) from parent*

since the types are incompatible, the answer would be nonsense.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "We are troubled on every side, yet not distressed; we 
      are perplexed, but not in despair; persecuted, but not
      forsaken; cast down, but not destroyed; Always bearing
      about in the body the dying of the Lord Jesus, that 
      the life also of Jesus might be made manifest in our 
      body."        II Corinthians 4:8-10 



Mon, 22 May 2000 21:58:21 +0100
"Oliver Elphick" <olly@lfix.co.uk>
Re: OO Patch 
Oliver Elphick wrote:
> 
> Marten Feldtmann wrote:
>   >> Hannu Krosing wrote:
>   >>   >
>   >>   >but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
>   >>   >other
>   >>
>   >> I don't think that should be allowed.  It violates inheritance principles,
>   >> since the types are not compatible.
>   >
>   > I see ... here's a person who has always programmed with typed
>   >languages and now thinks, that this is the right definition .... it's
>   >much more out there in the world. Open your mind and think about the
>   >following:
>   >
>   > An attribute named "a" of "type" TIMESTAMP of an instance of a class
>   >can be seen as a relation from this class to the class TIMESTAMP and
>   >this relation is named "a".
>   >
>   > And if you're on the way to relations you're not far away to see,
>   >that a relation is of course not limited to show to one specific class
>   >... but perhaps to all subclasses also ... and this is not a
>   >violation.
> 
> However the example I was referring to talked of INT4, TIMESTAMP or VARBINARY.
> 
> These are not subclasses but totally unrelated.  Suppose you had
> 
>    parent (id char(2))
>    child1 (a int4)
>    child2 (a timestamp)
> 
> and someone asks for
> 
>    select sum(a) from parent*
> 
> since the types are incompatible, the answer would be nonsense.

MS Excel for example SUMs only things summable, by which logic in this
case 
the sum would/could/should be sum of int4 colums.

In real world not all things are summable.

OTOH for schema
    parent (id char(2))
    child1 (a orange)
    child2 (a apple)

select sum(a) from parent*

could yield "N apples and M oranges" or possibly "X fruits" if orange
and 
apple were subtypes of fruit. Yes, really ;)

Or depending on how the sum() function is defined it could even be "Y
kg" .

---------
Hannu

Tue, 23 May 2000 00:07:32 +0300
Hannu Krosing <hannu@tm.ee>
Re: OO Patch
Oliver Elphick wrote:

> These are not subclasses but totally unrelated.  Suppose you had
> 
>    parent (id char(2))
>    child1 (a int4)
>    child2 (a timestamp)
> 
> and someone asks for
> 
>    select sum(a) from parent*
> 
> since the types are incompatible, the answer would be nonsense.

That query would be disallowed, for the reason you note. Ambigous
coloumns would need to be specified by class.attribute.

Tue, 23 May 2000 10:57:12 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: OO Patch
Bruce,

On Tue, 23 Jan 2001 09:35:49 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us> wrote:

> We used someone elses.  Here is a copy. Please submit any patches
> against this version.

Sucks found:
- doesn't handle mediumint, converts it to mediuminteger.
The same for bigint, and probably shorting & tinyint as well.
I don't know whether 7.1 release has such type but even if yes 
more preferrable to keep compatibility with old releases (7.0.x doesn't have, right ?)
- it doesn't handle mysql UNIQUE (that is keyword for unique index) inside CREATE TABLE block
- better to create indices after data load (it does before)
- doesn't handle UNSIGNED keyword (should a least skip it, or, at user option, convert to CHECK(field>=0))
- doesn't convert AUTO_INCREMENT in right way, at least in my production database.

I don't see conversion of MySQL's SET and ENUM types.

Well, before do any improvements on mysql2pgsql, I want to inform you that my 
converter has all features described above. Maybe it's easier to modify it to fit your requirements ?
At least take a look at it.
I don't like to do the same work twice, and this one promises to be exactly so.

Sending you my MySQL db dump which I used to play with it.

Max Rudensky.
Tue, 23 Jan 2001 18:25:30 +0200
Max Rudensky <fonin@ziet.zhitomir.ua>
Re: [GENERAL] MySQL -> Postgres dump converter

Can some PostgreSQL people comment on this?  This person wrote a
MySQL->PostgreSQL converter too.  His version is at:

	http://ziet.zhitomir.ua/~fonin/code


> Bruce,
> 
> On Tue, 23 Jan 2001 09:35:49 -0500 (EST)
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> 
> > We used someone elses.  Here is a copy. Please submit any patches
> > against this version.
> 
> Sucks found:
> - doesn't handle mediumint, converts it to mediuminteger.
> The same for bigint, and probably shorting & tinyint as well.
> I don't know whether 7.1 release has such type but even if yes 
> more preferrable to keep compatibility with old releases (7.0.x doesn't have, right ?)
> - it doesn't handle mysql UNIQUE (that is keyword for unique index) inside CREATE TABLE block
> - better to create indices after data load (it does before)
> - doesn't handle UNSIGNED keyword (should a least skip it, or, at user option, convert to CHECK(field>=0))
> - doesn't convert AUTO_INCREMENT in right way, at least in my production database.
> 
> I don't see conversion of MySQL's SET and ENUM types.
> 
> Well, before do any improvements on mysql2pgsql, I want to inform you that my 
> converter has all features described above. Maybe it's easier to modify it to fit your requirements ?
> At least take a look at it.
> I don't like to do the same work twice, and this one promises to be exactly so.
> 
> Sending you my MySQL db dump which I used to play with it.
> 
> Max Rudensky.

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 23 Jan 2001 11:38:09 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] MySQL -> Postgres dump converter
> Can some PostgreSQL people comment on this?  This person wrote a
> MySQL->PostgreSQL converter too.  His version is at:
>         http://ziet.zhitomir.ua/~fonin/code

-- THIS VERSION IS EXTREMELY BUGSOME ! USE IT ON YOUR OWN RISK !!!

Hmm. My version does not have this feature, but it could be added ;)

Seriously, I haven't looked at the differences, but there is a licensing
difference (BSD vs GPL). Someone else with experience with MySQL should
evaluate both packages.

mysql2pgsql has been used to convert SourceForge, with ~90 tables and
moderately complicated schema, but that did not include enumerated types
(done with ints at SF) and "unique" keys (done with sequences at SF)
afaicr.

> Sucks found:...

Each is a one-liner to fix in mysql2pgsql. The (nonstandard) types
mentioned weren't used in the test cases I had available. I didn't
realize that we had *any* reports of troubles or lacking features in the
existing converter, but I'll leave it up to y'all to decide if the
licensing issues and feature issues are significant.

I'm willing to provide patches to address some of the concerns, but of
course will not be able to look at the GPL'd code for hints and can only
use the information posted here to help afaik.

Comments?

                   - Thomas

Tue, 23 Jan 2001 18:08:17 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [GENERAL] MySQL -> Postgres dump converter

Can someone look at both versions and merge the improvements into our
version?  Should be pretty easy.

> > Can some PostgreSQL people comment on this?  This person wrote a
> > MySQL->PostgreSQL converter too.  His version is at:
> >         http://ziet.zhitomir.ua/~fonin/code
> 
> -- THIS VERSION IS EXTREMELY BUGSOME ! USE IT ON YOUR OWN RISK !!!
> 
> Hmm. My version does not have this feature, but it could be added ;)
> 
> Seriously, I haven't looked at the differences, but there is a licensing
> difference (BSD vs GPL). Someone else with experience with MySQL should
> evaluate both packages.
> 
> mysql2pgsql has been used to convert SourceForge, with ~90 tables and
> moderately complicated schema, but that did not include enumerated types
> (done with ints at SF) and "unique" keys (done with sequences at SF)
> afaicr.
> 
> > Sucks found:...
> 
> Each is a one-liner to fix in mysql2pgsql. The (nonstandard) types
> mentioned weren't used in the test cases I had available. I didn't
> realize that we had *any* reports of troubles or lacking features in the
> existing converter, but I'll leave it up to y'all to decide if the
> licensing issues and feature issues are significant.
> 
> I'm willing to provide patches to address some of the concerns, but of
> course will not be able to look at the GPL'd code for hints and can only
> use the information posted here to help afaik.
> 
> Comments?
> 
>                    - Thomas
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 24 Jan 2001 11:06:12 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] MySQL -> Postgres dump converter
Guys,

Thomas said he won't look into GPL'ed code for ideas.
Well, I re-read GPL and found that it's up to author whether is to allow or not 
to use code for using in programs with another open-source license. So this isn't 
a problem - you may use my program and include code to your without limitations.
I even would prefer to change license to BSD or similar 
but I didn't find in GPL ideas about that.

Max Rudensky.

On Wed, 24 Jan 2001 11:06:12 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us> wrote:

> 
> Can someone look at both versions and merge the improvements into our
> version?  Should be pretty easy.
> 
> > > Can some PostgreSQL people comment on this?  This person wrote a
> > > MySQL->PostgreSQL converter too.  His version is at:
> > >         http://ziet.zhitomir.ua/~fonin/code
> > 
> > -- THIS VERSION IS EXTREMELY BUGSOME ! USE IT ON YOUR OWN RISK !!!
> > 
> > Hmm. My version does not have this feature, but it could be added ;)
> > 
> > Seriously, I haven't looked at the differences, but there is a licensing
> > difference (BSD vs GPL). Someone else with experience with MySQL should
> > evaluate both packages.
> > 
> > mysql2pgsql has been used to convert SourceForge, with ~90 tables and
> > moderately complicated schema, but that did not include enumerated types
> > (done with ints at SF) and "unique" keys (done with sequences at SF)
> > afaicr.
> > 
> > > Sucks found:...
> > 
> > Each is a one-liner to fix in mysql2pgsql. The (nonstandard) types
> > mentioned weren't used in the test cases I had available. I didn't
> > realize that we had *any* reports of troubles or lacking features in the
> > existing converter, but I'll leave it up to y'all to decide if the
> > licensing issues and feature issues are significant.
> > 
> > I'm willing to provide patches to address some of the concerns, but of
> > course will not be able to look at the GPL'd code for hints and can only
> > use the information posted here to help afaik.
> > 
> > Comments?
> > 
> >                    - Thomas
> > 
> 
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 25 Jan 2001 11:59:37 +0200
Max Rudensky <fonin@ziet.zhitomir.ua>
Re: [GENERAL] MySQL -> Postgres dump converter
On 27 Jan 2001 11:25:56 +0100
Adrian Phillips <adrianp@powertech.no> wrote:

> >>>>> "Max" == Max Rudensky <fonin@ziet.zhitomir.ua> writes:
> 
>     Max> Guys, Thomas said he won't look into GPL'ed code for ideas.
>     Max> Well, I re-read GPL and found that it's up to author whether
>     Max> is to allow or not to use code for using in programs with
>     Max> another open-source license. So this isn't a problem - you
>     Max> may use my program and include code to your without
>     Max> limitations.  I even would prefer to change license to BSD or
>     Max> similar but I didn't find in GPL ideas about that.
> 
> If you are the original author and have not had large contributions to
> it then its just a matter of rereleasing as BSD, if on the other hand
> others have contributed then you'll have to get permission from them.
Yes, I wrote it, and I'd like to re-release it to other from GPL license.

> 
> Sincerely,
> 
> Adrian Phillips
> 
> -- 
> Your mouse has moved.
> Windows NT must be restarted for the change to take effect.
> Reboot now?  [OK]

Sat, 27 Jan 2001 19:16:03 +0200
Max Rudensky <fonin@ziet.zhitomir.ua>
Re: Re: [GENERAL] MySQL -> Postgres dump converter
Max Rudensky wrote:
> 
> Guys,
> 
> Thomas said he won't look into GPL'ed code for ideas.
> Well, I re-read GPL and found that it's up to author whether is to allow or not
> to use code for using in programs with another open-source license. So this isn't
> a problem - you may use my program and include code to your without limitations.
> I even would prefer to change license to BSD or similar
> but I didn't find in GPL ideas about that.
> 

If it's all your code, then you are free to license it under any licence
you desire.

You are completely free to licence it under both GPL and BSD licenses at
the same time. 

You can even simultaneously license it under a commercial licence if you
want to ;)

What you  probably can't do is to revoke the GPL license.

-----------
Hannu

Sun, 28 Jan 2001 03:34:06 +0200
Hannu Krosing <hannu@tm.ee>
Re: Re: [GENERAL] MySQL -> Postgres dump converter
Hannu Krosing wrote:
  >If it's all your code, then you are free to license it under any licence
  >you desire.
...
  >What you  probably can't do is to revoke the GPL license.

You can't revoke it from existing copies "out there", but you can from any
copies you release from now on, even of the same code..

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Many are the afflictions of the righteous; but the 
      LORD delivereth him out of them all."             
                                        Psalm 34:19 



Sun, 28 Jan 2001 07:57:43 +0000
"Oliver Elphick" <olly@lfix.co.uk>
Re: Re: [GENERAL] MySQL -> Postgres dump converter 
OK, I have added this conversion scripts to CVS along with Thomas's.  If
someone wants to merge them into one, feel free to submit a patch.  I
have provided a URL to my2pg.pl to retrieve the most recent version.

The only license issue was that the bottom of the source code had a
mention referring to the GPL for more information.  I removed that
because it now clearly states it has a BSD-like license.

Thanks.

> > Can some PostgreSQL people comment on this?  This person wrote a
> > MySQL->PostgreSQL converter too.  His version is at:
> >         http://ziet.zhitomir.ua/~fonin/code
> 
> -- THIS VERSION IS EXTREMELY BUGSOME ! USE IT ON YOUR OWN RISK !!!
> 
> Hmm. My version does not have this feature, but it could be added ;)
> 
> Seriously, I haven't looked at the differences, but there is a licensing
> difference (BSD vs GPL). Someone else with experience with MySQL should
> evaluate both packages.
> 
> mysql2pgsql has been used to convert SourceForge, with ~90 tables and
> moderately complicated schema, but that did not include enumerated types
> (done with ints at SF) and "unique" keys (done with sequences at SF)
> afaicr.
> 
> > Sucks found:...
> 
> Each is a one-liner to fix in mysql2pgsql. The (nonstandard) types
> mentioned weren't used in the test cases I had available. I didn't
> realize that we had *any* reports of troubles or lacking features in the
> existing converter, but I'll leave it up to y'all to decide if the
> licensing issues and feature issues are significant.
> 
> I'm willing to provide patches to address some of the concerns, but of
> course will not be able to look at the GPL'd code for hints and can only
> use the information posted here to help afaik.
> 
> Comments?
> 
>                    - Thomas
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 10 Feb 2001 07:03:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] MySQL -> Postgres dump converter

Just a long standing curiosity?

For most web sites MySQL seems to work fine, but overall PostgreSQL offers 
more capabilites so why build upon a limited base such as MySQL?

Does anyone here have any idea as to why so many people select MySQL when 
both systems are open sourced?

Matthew


Sun, 28 Jul 2002 19:08:53 -0400
Matthew Tedder <matthew@tedder.com>
Why is MySQL more chosen over PostgreSQL?
> 
> Just a long standing curiosity?
> 
> For most web sites MySQL seems to work fine, but overall PostgreSQL offers 
> more capabilites so why build upon a limited base such as MySQL?
> 
> Does anyone here have any idea as to why so many people select MySQL when 
> both systems are open sourced?

Three likely effects:

a) ISP management toolsets include management tools for MySQL, and not 
PostgreSQL.

(CPanel is an example of such a toolset.)

b) Apparently the permissions model for PostgreSQL used to discourage its use 
in shared hosting environments.  (Ask Neil Conway more about this.)

c) There was corporate sponsorship of MySQL, and they probably spent money 
marketing it in the ISP web hosting market.

d) MySQL is GPL-licensed, and some people consider that very important.  (And 
are too stupid to grasp that they like XFree86, which _isn't_ licensed under 
the GPL...  Of course, this is d), and I said "three" likely effects...)

e) Inertia.  MySQL got more popular way back when; the reasons may no longer 
apply, but nobody is going to move to PostgreSQL without _compelling_ reason, 
and you'll have to show something _really compelling_.
--
(concatenate 'string "cbbrowne" "@acm.org")
http://cbbrowne.com/info/advocacy.html
FLORIDA: Where your vote counts and counts and counts.



Mon, 29 Jul 2002 08:53:20 -0400
cbbrowne@cbbrowne.com
Re: Why is MySQL more chosen over PostgreSQL? 
On Mon, 29 Jul 2002 cbbrowne@cbbrowne.com wrote:

[snip]

> e) Inertia.  MySQL got more popular way back when; the reasons may no longer 
> apply, but nobody is going to move to PostgreSQL without _compelling_ reason, 
> and you'll have to show something _really compelling_.

I would like to add one other thought.  There are many web site designers
that get thrust into being a web site programmer.  Without an
understanding of database design and a novice programmers (?) view of the
process the benefits of letting the database (RDBMS) do the database work
isn't recognized.  They code it all in the CGI.


Rod
-- 
  "Open Source Software - Sometimes you get more than you paid for..."


Mon, 29 Jul 2002 06:39:59 -0700 (PDT)
"Roderick A. Anderson" <raanders@acm.org>
Re: Why is MySQL more chosen over PostgreSQL? 
Matthew Tedder <matthew@tedder.com> wrote:
> For most web sites MySQL seems to work fine, but overall PostgreSQL offers 
> more capabilites so why build upon a limited base such as MySQL?
> Does anyone here have any idea as to why so many people select MySQL when 
> both systems are open sourced?
Some people working on win32 platforms, and mysql easy install on win32.
Just for starting on use databases in soft.

PgSQL easy-install on *unix-systems (mostly..:)), but on win32 ..it's hard..:(

IMHO.

--
Best regards, KVN.
 PHP4You   (<http://php4you.kiev.ua/>)
 PEAR [ru] (<http://pear.php.net/manual/ru/>)
  mailto:kvn@php.net

Mon, 29 Jul 2002 14:32:10 +0000 (UTC)
"Vitaliy N. Kravchenko" <kvn@phbme.ntu-kpi.kiev.ua>
Re: Why is MySQL more chosen over PostgreSQL?
well that and people tend to drift towards an easy answer,
like php... amazing how that combo is so popular... hrrmm...

Roderick A. Anderson writes:
 > On Mon, 29 Jul 2002 cbbrowne@cbbrowne.com wrote:
 > 
 > [snip]
 > 
 > > e) Inertia.  MySQL got more popular way back when; the reasons may no longer 
 > > apply, but nobody is going to move to PostgreSQL without _compelling_ reason, 
 > > and you'll have to show something _really compelling_.
 > 
 > I would like to add one other thought.  There are many web site designers
 > that get thrust into being a web site programmer.  Without an
 > understanding of database design and a novice programmers (?) view of the
 > process the benefits of letting the database (RDBMS) do the database work
 > isn't recognized.  They code it all in the CGI.
 > 
 > 
 > Rod
 > -- 
 >   "Open Source Software - Sometimes you get more than you paid for..."
 > 
 > 
 > ---------------------------(end of broadcast)---------------------------
 > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
Chris Humphries
Development InfoStructure
540.366.9809 

Mon, 29 Jul 2002 09:39:58 -0500
Chris Humphries <chumphries@devis.com>
Re: Why is MySQL more chosen over PostgreSQL? 
On Mon, 29 Jul 2002, Chris Humphries wrote:

> well that and people tend to drift towards an easy answer,
> like php... amazing how that combo is so popular... hrrmm...

Well people seem to get so ... about php that I didn't want to touch that 
topic.


Rod
-- 
  "Open Source Software - Sometimes you get more than you paid for..."


Mon, 29 Jul 2002 08:07:08 -0700 (PDT)
"Roderick A. Anderson" <raanders@acm.org>
Re: Why is MySQL more chosen over PostgreSQL? 
On Mon, 29 Jul 2002, Roderick A. Anderson wrote:

> I would like to add one other thought. There are many web site
> designers that get thrust into being a web site programmer. Without
> an understanding of database design and a novice programmers (?) view
> of the process the benefits of letting the database (RDBMS) do the
> database work isn't recognized. They code it all in the CGI.

Well, I'll add two points to this, then:

1. Often there's a lot more benefit to moving the work from the database
to the application structure. Database schemas are hard to change, and
hard to keep under revision control. When I was doing a large website,
it was much, much easier to say "everything goes through these Java
classes" than "everything goes through the database." I could change the
database schema at will and know that my data was safe, because I could
have old interfaces running simultaneously with new.

(Though I'll admit, good view support would have mitigated this problem
quite a lot. But there is *no* database in the world that has really
good view support; they all fail on various updates where one can
theoretically do the Right Thing, but in practice it's very difficult.
And I don't think that's going to change any time soon.)

2. I expect that even most PostgreSQL--or even database--experts don't
have a real understanding of relational theory, anyway. That we still
have table inheritance shows that. As far as I can tell, there is
nothing whatsoever that table inheritance does that the relational model
does not handle; the whole "OO" thing is just another, redundant way of
doing what we already ought to be able to do within the relational model.

I'm still waiting to find out just what advantage table inheritance
offers. I've asked a couple of times here, and nobody has even started
to come up with anything.

All that said, though, don't take this as any kind of a dismissal of
postgres. It's in most ways better than MySQL and also some commericial
systems, and many of its failures are being addressed. Postgres for some
reason seems to attract some really, really smart people to work on it.
If I could see something better, I'd be there. But I don't.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Tue, 30 Jul 2002 00:59:40 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL? 
Curt Sampson wrote:
> I'm still waiting to find out just what advantage table inheritance
> offers. I've asked a couple of times here, and nobody has even started
> to come up with anything.

We inherited inheritance from Berkeley.  I doubt we would have added it
ourselves.  It causes too much complexity in other parts of the system.

> All that said, though, don't take this as any kind of a dismissal of
> postgres. It's in most ways better than MySQL and also some commericial
> systems, and many of its failures are being addressed. Postgres for some
> reason seems to attract some really, really smart people to work on it.
> If I could see something better, I'd be there. But I don't.

Interbase/Firebird maybe?  They just came out with a 1.0 release in
March.

As for why PostgreSQL is less popular than MySQL, I think it is all
momentum from 1996 when MySQL worked and we sometimes crashed.  Looking
forward, I don't know many people who choose MySQL _if_ they consider
both PostgreSQL and MySQL, so the discussions people have over MySQL vs.
PostgreSQL are valuable because they get people to consider MySQL
alternatives, and once they do, they usually choose PostgreSQL.

As for momentum, we still have a smaller userbase than MySQL, but we are
increasing our userbase at a fast rate, perhaps faster than MySQL at
this point.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 29 Jul 2002 12:30:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why is MySQL more chosen over PostgreSQL?
Bruce Momjian wrote:
> 
> Curt Sampson wrote:
> > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything.
> 
> We inherited inheritance from Berkeley.  I doubt we would have added it
> ourselves.  It causes too much complexity in other parts of the system.

...

> As for why PostgreSQL is less popular than MySQL, I think it is all
> momentum from 1996 when MySQL worked and we sometimes crashed.  Looking
> forward, I don't know many people who choose MySQL _if_ they consider
> both PostgreSQL and MySQL, so the discussions people have over MySQL vs.
> PostgreSQL are valuable because they get people to consider MySQL
> alternatives, and once they do, they usually choose PostgreSQL.
> 
> As for momentum, we still have a smaller userbase than MySQL, but we are
> increasing our userbase at a fast rate, perhaps faster than MySQL at
> this point.

Its all due to sort-order. If Oracle was open source MySQL would still
be more popular. ;-)

Mike Mascari
mascarm@mascari.com

Mon, 29 Jul 2002 12:49:29 -0400
Mike Mascari <mascarm@mascari.com>
Re: Why is MySQL more chosen over PostgreSQL?
On Mon, 29 Jul 2002, Bruce Momjian wrote:

> Curt Sampson wrote:
> > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything.
>
> We inherited inheritance from Berkeley.  I doubt we would have added it
> ourselves.  It causes too much complexity in other parts of the system.

Ah, all the more reason to remove it, then! :-)

But really, please don't take that as a criticism of the current development
direction; I know it was inherited, and it's not new code. In fact, I think
it probably wasn't until _The Third Manifsto_ came out in 1998 that it
really became clear that table inheritance was not terribly useful--if it's
even generally known now. And even so, I'm open to other opinions on that,
since it's not been an intensive area of study by any means.

> > All that said, though, don't take this as any kind of a dismissal of
> > postgres. It's in most ways better than MySQL and also some commericial
> > systems, and many of its failures are being addressed. Postgres for some
> > reason seems to attract some really, really smart people to work on it.
> > If I could see something better, I'd be there. But I don't.
>
> Interbase/Firebird maybe?  They just came out with a 1.0 release in March.

Once in a while I go back to it, but I still can't build the darn thing
from scratch. Which makes it a bit difficult to evaluate....

> As for why PostgreSQL is less popular than MySQL, I think it is all
> momentum from 1996 when MySQL worked and we sometimes crashed.

Right. I have a lot of hope. After all, MySQL was for a couple of
years a second-runner to mSQL, remember?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Tue, 30 Jul 2002 02:01:19 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Mon, 2002-07-29 at 19:01, Curt Sampson wrote:
> On Mon, 29 Jul 2002, Bruce Momjian wrote:
> 
> > Curt Sampson wrote:
> > > I'm still waiting to find out just what advantage table inheritance
> > > offers. I've asked a couple of times here, and nobody has even started
> > > to come up with anything.

It is mostly a syntactic thing that makes it easier to humans to write
cleaner code.

Otherwise, it is proved that anything can be written for a Turing
Machine ;)

> > We inherited inheritance from Berkeley.  I doubt we would have added it
> > ourselves.  It causes too much complexity in other parts of the system.
> 
> Ah, all the more reason to remove it, then! :-)
>

It would make more sense to make it compatible with SQL99 and drop the
current behaviour only after that if possible.

As it stands now it is a strange mix of SQL99's

  CREATE TABLE thistable(...,LIKE anothertable,...);
and
  CREATE table mytable(...) UNDER anothertable;

with only a few additional goodies, like SELECT* (i.e not ONLY) which
selects from all tables that inherit from this.

other things that should be done are not (like inheriting constraints,
foreign and primary keys, triggers, ...)

Also we currently can't return more than one recordset from a query,
which also makes selecting from an inheritance hierarchy less versatile.

-----------
Hannu



29 Jul 2002 20:57:37 +0200
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
On Tue, 2002-07-30 at 14:51, Adrian 'Dagurashibanipal' von Bidder wrote:

> Bruce Momjian:
> > It causes too much complexity in other parts of the system.
> 
> That's one reason.

Seems like somewhat valid reason. But still not enough to do a lot of
work _and_ annoy a lot of existing users :)

> Curt Sampson wrote:
> > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything. and
> > there is nothing whatsoever that table inheritance does that the
> > relational model does not handle
> 
> That's the other one.

That's quite bogus imho. You could just as well argue that there is
nothing that relational model handles that can't be done in pure C.

----------------
Hannu


30 Jul 2002 13:13:24 +0500
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
On Mon, 2002-07-29 at 18:30, Bruce Momjian wrote:
> Curt Sampson wrote:
> > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything.
> 
> We inherited inheritance from Berkeley.  I doubt we would have added it
> ourselves.  It causes too much complexity in other parts of the system.

How about dropping it, then?

Just start to emit 

WARNING: inheritance will be dropped with postgres 8.0 
WARNING: please refer to http://.../ for an explanation why.

right now on every CREATE TABLE that uses it.

cheers
-- vbi

-- 
secure email with gpg                         http://fortytwo.ch/gpg
30 Jul 2002 10:19:39 +0200
Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch>
Re: Why is MySQL more chosen over PostgreSQL?
> > We inherited inheritance from Berkeley.  I doubt we would have added it
> > ourselves.  It causes too much complexity in other parts of the system.
>
> How about dropping it, then?
>
> Just start to emit
>
> WARNING: inheritance will be dropped with postgres 8.0
> WARNING: please refer to http://.../ for an explanation why.
>
> right now on every CREATE TABLE that uses it.

Why?  It doesn't hurt you personally!  Plus, it would annoy a _boatload_ of
existing inheritance users.

A more interesting question I think is how to allow our indexes to span
multiple relations, _without_ causing any performance degradation for non
inheritance users...

Chris


Tue, 30 Jul 2002 16:45:43 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Why is MySQL more chosen over PostgreSQL?
[don't cc: me, please.]
[please leave proper attribution in]

On Tue, 2002-07-30 at 10:45, Christopher Kings-Lynne wrote:
> > > We inherited inheritance from Berkeley.  I doubt we would have added it
> > > ourselves.  It causes too much complexity in other parts of the system.

[Inheritance]

> > How about dropping it, then?
[...]

> Why?  It doesn't hurt you personally!  

That's correct.

> Plus, it would annoy a _boatload_ of
> existing inheritance users.

Bruce Momjian:
> It causes too much complexity in other parts of the system.

That's one reason.

Curt Sampson wrote:
> I'm still waiting to find out just what advantage table inheritance
> offers. I've asked a couple of times here, and nobody has even started
> to come up with anything.
and
> there is nothing whatsoever that table inheritance does that the
> relational model does not handle

That's the other one.

cheers
-- vbi

-- 
secure email with gpg                         http://fortytwo.ch/gpg
30 Jul 2002 11:51:45 +0200
Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch>
Re: Why is MySQL more chosen over PostgreSQL?
On 30 Jul 2002, Hannu Krosing wrote:

> On Tue, 2002-07-30 at 14:51, Adrian 'Dagurashibanipal' von Bidder wrote:
>
> > Bruce Momjian:
> > > It causes too much complexity in other parts of the system.
> >
> > That's one reason.
>
> Seems like somewhat valid reason. But still not enough to do a lot of
> work _and_ annoy a lot of existing users :)

It's almost unquestionably more work to maintain than to drop. Dropping
support for it is a one-time operation. Maintaining it is an ongoing
expense.

> That's quite bogus imho. You could just as well argue that there is
> nothing that relational model handles that can't be done in pure C.

That's a straw man argument. What we (or I, anyway) are arguing is that
the relational model does everything that table inheritance does, and at
least as easily. Extending the model adds complexity without adding the
ability to do things you couldn't easily do before. (This, IMHO, makes
table inheritance quite inelegant.)

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Tue, 30 Jul 2002 20:00:13 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
On 29 Jul 2002, Hannu Krosing wrote:

> > > Curt Sampson wrote:
> > > > I'm still waiting to find out just what advantage table inheritance
> > > > offers. I've asked a couple of times here, and nobody has even started
> > > > to come up with anything.
>
> It is mostly a syntactic thing that makes it easier to humans to write
> cleaner code.

And how is using table inheritance "cleaner" than doing it the
relational way? It adds extra complexity to the system, which is
automatically a reduction in cleanliness, so it would have to have
some correspondingly cleanliness-increasing advantages in order
to be cleaner, overall.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Tue, 30 Jul 2002 20:02:44 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
* Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> [020730 04:20]:
> On Mon, 2002-07-29 at 18:30, Bruce Momjian wrote:
> > Curt Sampson wrote:
> > > I'm still waiting to find out just what advantage table inheritance
> > > offers. I've asked a couple of times here, and nobody has even started
> > > to come up with anything.

I think one of the values of it is that it is something that no one else
has.  It distinguishes us.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Tue, 30 Jul 2002 07:46:58 -0400
"D'Arcy J.M. Cain" <darcy@druid.net>
Re: Why is MySQL more chosen over PostgreSQL?
[No cc: please. Especially if you're not commenting on anything I said]

On Tue, 2002-07-30 at 13:46, D'Arcy J.M. Cain wrote:
> > > Curt Sampson wrote:
> > > > I'm still waiting to find out just what advantage table inheritance
> > > > offers. I've asked a couple of times here, and nobody has even started
> > > > to come up with anything.
> 
> I think one of the values of it is that it is something that no one else
> has.  It distinguishes us.

Coooool. Let's have the 'automatically phone KFC if developer works more
than 8 hours non-stop' feature, *that* is something nobody else has.
Yes. Cool.

In other words: this is an absolutely bogus argument.

As an implementor I'm always wary of using features nobody else has,
especially in databases. So, if I'd want postgres to have one thing
nobody else has, it would be the most complete standard SQL
implementation - so it would at least be the other products' fault if
I'd have to do any special porting work to/from postgres.

cheers
-- vbi

-- 
secure email with gpg                         http://fortytwo.ch/gpg
30 Jul 2002 14:01:35 +0200
Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch>
Re: Why is MySQL more chosen over PostgreSQL?
> As an implementor I'm always wary of using features nobody else has,
> especially in databases. So, if I'd want postgres to have one thing
> nobody else has, it would be the most complete standard SQL
> implementation - so it would at least be the other products' fault if
> I'd have to do any special porting work to/from postgres.

Why can't both be done?  If nobody extended the spec or came up with new
features there wouldn't exactly be any progress.

Yes, meeting the spec is a good goal, and one that is getting quite
close as far as the SQL part goes -- but it shouldn't be the only goal.


Inheritance currently saves me from issuing ~4 inserts, updates, deletes
as it handles it itself.  If indexes and a couple other things worked
across the entire tree it could be more useful.

I think what we need to do is expand on it, not blow it away.


There is a list of spec features we support. Stick to those (or the
subset) that is appropriate for portability.  If you plan on making an
embedded DB Based application the extra features may be useful.



30 Jul 2002 08:13:42 -0400
Rod Taylor <rbt@zort.ca>
Re: Why is MySQL more chosen over PostgreSQL?
* Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> [020730 08:01]:
> On Tue, 2002-07-30 at 13:46, D'Arcy J.M. Cain wrote:
> > I think one of the values of it is that it is something that no one else
> > has.  It distinguishes us.
> 
> Coooool. Let's have the 'automatically phone KFC if developer works more
> than 8 hours non-stop' feature, *that* is something nobody else has.
> Yes. Cool.

Excuse me all to hell but are you in the junior debating class or what?
No one said we need to include every possible feature just because it
is not in other products.  Your KFC suggestion has nothing whatsoever
to do with database management.  Inheritance does.  It is useful to
some and, as I said *one of the values* is the way it distinguishes us.

For the record, I do use the feature and I would miss it if it disappeared.
I think it can be improved upon, especially in the area of indexes and
prmary keys but overall it is a nice feature that has the added benefit
of differentiating us from other RDBMS systems.

> As an implementor I'm always wary of using features nobody else has,

How very conservative of you.  Personally I have spent my life trying
to do new things.  If I wanted Oracle or DB2 I know where to find it.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Tue, 30 Jul 2002 08:28:33 -0400
"D'Arcy J.M. Cain" <darcy@druid.net>
Re: Why is MySQL more chosen over PostgreSQL?
[Still no cc:s please]

On Tue, 2002-07-30 at 14:28, D'Arcy J.M. Cain wrote:
> * Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> [020730 08:01]:
> > On Tue, 2002-07-30 at 13:46, D'Arcy J.M. Cain wrote:
> > > I think one of the values of it is that it is something that no one else
> > > has.  It distinguishes us.
> > 
> > Coooool. Let's have the 'automatically phone KFC if developer works more
> > than 8 hours non-stop' feature, *that* is something nobody else has.
> > Yes. Cool.
> 
> Excuse me all to hell but are you in the junior debating class or what?

Sure, I was taking it to the extreme here (And I really am sorry if you
felt offended by my remark). But I strongly feel that having a feature
because 'it is something that no one else has.  It distinguishes us.' is
no justification at all.

Of course, if a feature provides some real use, then it is worth having
(yes, even if it's not in the standard). But exactly this seems not so
clear in the case of inheritance in postgres.

(And that's where I'm starting to say things I've said before. So I'll
just shut up now.)

cheers
-- vbi

-- 
secure email with gpg                         http://fortytwo.ch/gpg
30 Jul 2002 14:44:05 +0200
Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch>
Re: Why is MySQL more chosen over PostgreSQL?


Adrian 'Dagurashibanipal' von Bidder wrote:

> (And that's where I'm starting to say things I've said before. So I'll
> just shut up now.)

May be you can contribute some code :)


Tue, 30 Jul 2002 14:45:23 +0200
"Iavor Raytchev" <iavor.raytchev@verysmall.org>
Re: Why is MySQL more chosen over PostgreSQL?
> > I'm still waiting to find out just what advantage table inheritance
> > offers. I've asked a couple of times here, and nobody has even started
> > to come up with anything.
> and
> > there is nothing whatsoever that table inheritance does that the
> > relational model does not handle
> 
> That's the other one.

Irrelevant - thousands of people are using the feature!

Chris



Tue, 30 Jul 2002 21:48:37 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Inheritance (was: Re: Why is MySQL more chosen over PostgreSQL?)
On Tue, Jul 30, 2002 at 02:01:35PM +0200, Adrian 'Dagurashibanipal' von Bidder wrote:
> As an implementor I'm always wary of using features nobody else has,
> especially in databases. So, if I'd want postgres to have one thing
> nobody else has, it would be the most complete standard SQL
> implementation - so it would at least be the other products' fault if
> I'd have to do any special porting work to/from postgres.

SQL99 includes inheritance (albeit a somewhat different implementation
than the design in Postgres right now) -- so the "most complete standard
SQL implementation" would need to include inheritance.

I'd say removing inheritence would be a waste of time -- it would
probably be easier to just fix its deficiencies.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Tue, 30 Jul 2002 10:10:20 -0400
nconway@klamath.dyndns.org (Neil Conway)
Re: Why is MySQL more chosen over PostgreSQL?
> ... But I strongly feel that having a feature
> because 'it is something that no one else has.  It distinguishes us.' is
> no justification at all.

One reason why we have a database which *does* come very close to the
standards is precisely because it had (and has) things which no one else
had (or has). It demonstrated how to do things which are now part of
SQL99, but which were not implemented *anywhere else* back in the early
'90s.

Inheritance is not as well supported by us, but that is our fault for
focusing on other things recently. I think that some of the recent work
will end up benefiting inheritance features, so these might make some
progress soon too.

Search and destroy missions to eliminate all that is not "standard" will
diminish the product, because we will be constrained to work entirely
within the boundaries of a standard which is poorly thought out around
the edges. If our boundaries are always just a bit wider than that we'll
be OK ;)

All imho of course...

                     - Thomas

Tue, 30 Jul 2002 07:24:45 -0700
Thomas Lockhart <lockhart@fourpalms.org>
Re: Why is MySQL more chosen over PostgreSQL?
Adrian 'Dagurashibanipal' von Bidder wrote:

Checking application/pgp-signature: FAILURE
-- Start of PGP signed section.
> [Still no cc:s please]
> 
> On Tue, 2002-07-30 at 14:28, D'Arcy J.M. Cain wrote:
> > * Adrian 'Dagurashibanipal' von Bidder <avbidder@fortytwo.ch> [020730 08:01]:
> > > On Tue, 2002-07-30 at 13:46, D'Arcy J.M. Cain wrote:
> > > > I think one of the values of it is that it is something that no one else
> > > > has.  It distinguishes us.
> > > 
> > > Coooool. Let's have the 'automatically phone KFC if developer works more
> > > than 8 hours non-stop' feature, *that* is something nobody else has.
> > > Yes. Cool.
> > 
> > Excuse me all to hell but are you in the junior debating class or what?
> 
> Sure, I was taking it to the extreme here (And I really am sorry if you
> felt offended by my remark). But I strongly feel that having a feature
> because 'it is something that no one else has.  It distinguishes us.' is
> no justification at all.

I thought the KFC thing was very funny.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 30 Jul 2002 11:30:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why is MySQL more chosen over PostgreSQL?
On Tue, 2002-07-30 at 16:00, Curt Sampson wrote:
> On 30 Jul 2002, Hannu Krosing wrote:
> 
> > On Tue, 2002-07-30 at 14:51, Adrian 'Dagurashibanipal' von Bidder wrote:
> >
> > > Bruce Momjian:
> > > > It causes too much complexity in other parts of the system.
> > >
> > > That's one reason.
> >
> > Seems like somewhat valid reason. But still not enough to do a lot of
> > work _and_ annoy a lot of existing users :)
> 
> It's almost unquestionably more work to maintain than to drop. Dropping
> support for it is a one-time operation. Maintaining it is an ongoing
> expense.

I would not rush to drop advanced features, as they may be hard to put
back later. If they stay in, even in broken form, then there wont be
nearly as much patches which make fixing them harder.

I'm afraid that we have already dropped too much. 

For example we dropped time travel, but recent versions of Oracle now
have some form of it, usable mostly for recovering accidentally deleted
(and committed rows), although it is much harder to implement it using
logs than using MVCC.

Also, I suspect that dropping support for multiple return sets for one
query was done too fast.

> > That's quite bogus imho. You could just as well argue that there is
> > nothing that relational model handles that can't be done in pure C.
> 
> That's a straw man argument.

Actually it was meant to be 'one straw man against another straw man 
argument' ;)

> What we (or I, anyway) are arguing is that
> the relational model does everything that table inheritance does, and at
> least as easily.

The problem is that 'the relational model' does nothing by itself. It is
always the developers/DBAs who have to do things. 

And at least for some brain shapes it is much more convenient to inherit
tables than to (re)factor stuff into several tables to simulate
inheritance using the relational model. 

I still think that inheritance should be enchanced and made compatible
with standards not removed.

> Extending the model adds complexity without adding the
> ability to do things you couldn't easily do before. (This, IMHO, makes
> table inheritance quite inelegant.)

Then explain why SQL99 has included inheritance ?

---------------
Hannu


31 Jul 2002 00:54:14 +0500
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
> 2. I expect that even most PostgreSQL--or even database--experts don't
> have a real understanding of relational theory, anyway. That we still
> have table inheritance shows that. As far as I can tell, there is
> nothing whatsoever that table inheritance does that the relational model
> does not handle; the whole "OO" thing is just another, redundant way of
> doing what we already ought to be able to do within the relational model.
>
> I'm still waiting to find out just what advantage table inheritance
> offers. I've asked a couple of times here, and nobody has even started
> to come up with anything.

Can you point me (someone without a real understanding of relational theory) 
to some good resources that explain the concepts well?

Regards,
	Jeff

Tue, 30 Jul 2002 14:41:02 -0700
Jeff Davis <list-pgsql-hackers@empires.org>
Re: Why is MySQL more chosen over PostgreSQL?
On 31 Jul 2002, Hannu Krosing wrote:

> I would not rush to drop advanced features, as they may be hard to put
> back later.

If they are hard to put back, it's generally because the other code
in the system that relates to it has changed, so you can't just bring
back what is in the old versions in the CVS repository.

But if the code was left in, that meant that someone had to make all of
those integration changes you'd have to make to bring the code back;
it's just they had to make it as they were adding new features and
whatnot. If in the end you decide that the feature you didn't drop isn't
important, you just did a lot of work for nothing. You may also slow
down or stop the implementation of other, more useful features, because
people find that the work to add them isn't worthwhile, due to having to
change too much code.

> If they stay in, even in broken form, then there wont be
> nearly as much patches which make fixing them harder.

Summary: someone always has to do the patches. It's just a question of
whether you *might* do them *if* you decide to bring the feature back,
or whether you *will* do them because the feature is there.

> > What we (or I, anyway) are arguing is that
> > the relational model does everything that table inheritance does, and at
> > least as easily.
>
> The problem is that 'the relational model' does nothing by itself. It is
> always the developers/DBAs who have to do things.

Ok. So "the developer can do what table inheritance does just as easily
in the relational model."

> And at least for some brain shapes it is much more convenient to inherit
> tables than to (re)factor stuff into several tables to simulate
> inheritance using the relational model.

I highly doubt that. Relating two tables to each other via a key, and
joining them together, allows you to do everything that inheritance
allows you to do, but also more. If you have difficulty with keys and
joins, well, you really probably want to stop and fix that problem
before you do more work on a relational database....

> > Extending the model adds complexity without adding the
> > ability to do things you couldn't easily do before. (This, IMHO, makes
> > table inheritance quite inelegant.)
>
> Then explain why SQL99 has included inheritance ?

Becuase SQL has a long, long history of doing things badly. The language
has been non-relational in many ways from the very beginning. But Codd
and Date argue that much better than I do, so I'd prefer you read their
books and respond to those arguments. I can provide references if you
need them.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Wed, 31 Jul 2002 08:35:17 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Mon, 2002-07-29 at 08:53, cbbrowne@cbbrowne.com wrote:
> > Just a long standing curiosity?
> e) Inertia.  MySQL got more popular way back when; the reasons may no longer 

f) Win32 Support.  I can download a setup.exe for mysql and have it up
and running quickly on Windows.  I think that native Win32 support will
go a long way toward making Postgres more "popular"


30 Jul 2002 21:09:01 -0400
"Matthew T. O'Connor" <matthew@zeut.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Tue, 30 Jul 2002, Jeff Davis wrote:

> Can you point me (someone without a real understanding of relational theory)
> to some good resources that explain the concepts well?

C. J. Date's _An Introduction to Database Systems, Seventh Edition_ is
a fat tome that will give you an extremely good grasp of relational
theory if you take the time to study it. Even just browsing it is well
worthwhile. It has some discussion of "object-oriented" database systems
as well.

In particular (you'll see the relevance of this below) it has an
excellent analysis of the updatability of views.

Date and Darwen's _Foundation for Future Database Systems: the
Third Manifesto_ goes into much more detail about how they feel
object-oriented stuff should happen in relational databases. Appendix E
("Subtables and Supertables") discusses table inheritance. It ends with
this statement:

    To sum up: It looks as if the whole business of a subtable
    inheriting columns from a supertable is nothing but a syntatic
    shorthand--not that there is anything wrong with syntatic
    shorthands in general, of course, but this particular shorthand
    does not seem particularly useful, and in any case it is always
    more than adequately supported by the conventional view mechanism.

(This, BTW, addresses the note someone else made here about the
subtable/supertable thing letting him do one insert instead of two
or three; he just needs to create a view and appropriate rules,
and he'll get exactly the same effect. And maybe that will help
fix his index problems, too....)

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Wed, 31 Jul 2002 11:31:11 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
> I highly doubt that. Relating two tables to each other via a key, and
> joining them together, allows you to do everything that inheritance
> allows you to do, but also more. If you have difficulty with keys and
> joins, well, you really probably want to stop and fix that problem
> before you do more work on a relational database....

I'm still not convinced of this.  For example, my friend has a hardware
e-store and every different class of hardware has different properties.  ie
modems have baud and network cards have speed and video cards have ram.  He
simply just has a 'products' table from which he extends
'networkcard_products', etc. with the additional fields.  Easy.

Chris


Wed, 31 Jul 2002 10:43:30 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Why is MySQL more chosen over PostgreSQL?
> On Mon, 2002-07-29 at 08:53, cbbrowne@cbbrowne.com wrote:
> > > Just a long standing curiosity?
> > e) Inertia.  MySQL got more popular way back when; the reasons
> may no longer
>
> f) Win32 Support.  I can download a setup.exe for mysql and have it up
> and running quickly on Windows.  I think that native Win32 support will
> go a long way toward making Postgres more "popular"

Speaking of that - wasn't someone going to branch the CVS with a whole lot
of Win32 support stuff?  Jan?

Chris


Wed, 31 Jul 2002 10:44:26 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Why is MySQL more chosen over PostgreSQL?
On Wed, 31 Jul 2002, Christopher Kings-Lynne wrote:

> > I highly doubt that. Relating two tables to each other via a key, and
> > joining them together, allows you to do everything that inheritance
> > allows you to do, but also more. If you have difficulty with keys and
> > joins, well, you really probably want to stop and fix that problem
> > before you do more work on a relational database....
>
> I'm still not convinced of this.  For example, my friend has a hardware
> e-store and every different class of hardware has different properties.  ie
> modems have baud and network cards have speed and video cards have ram.  He
> simply just has a 'products' table from which he extends
> 'networkcard_products', etc. with the additional fields.  Easy.

And what's the problem with networkcard_products being a separate table
that shares a key with the products table?

    CREATE TABLE products (product_id int, ...)
    CREATE TABLE networkcard_products_data (product_id int, ...)
    CREATE VIEW networkcard_products AS
	SELECT products.product_id, ...
	FROM products
	JOINT networkcard_products_data USING (product_id)

What functionality does table inheritance offer that this traditional
relational method of doing things doesn't?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Wed, 31 Jul 2002 12:03:56 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
Curt Sampson wrote:
> On Wed, 31 Jul 2002, Christopher Kings-Lynne wrote:
> 
> > > I highly doubt that. Relating two tables to each other via a key, and
> > > joining them together, allows you to do everything that inheritance
> > > allows you to do, but also more. If you have difficulty with keys and
> > > joins, well, you really probably want to stop and fix that problem
> > > before you do more work on a relational database....
> >
> > I'm still not convinced of this.  For example, my friend has a hardware
> > e-store and every different class of hardware has different properties.  ie
> > modems have baud and network cards have speed and video cards have ram.  He
> > simply just has a 'products' table from which he extends
> > 'networkcard_products', etc. with the additional fields.  Easy.
> 
> And what's the problem with networkcard_products being a separate table
> that shares a key with the products table?
> 
>     CREATE TABLE products (product_id int, ...)
>     CREATE TABLE networkcard_products_data (product_id int, ...)
>     CREATE VIEW networkcard_products AS
> 	SELECT products.product_id, ...
> 	FROM products
> 	JOINT networkcard_products_data USING (product_id)
> 
> What functionality does table inheritance offer that this traditional
> relational method of doing things doesn't?

You can add children without modifying your code.  It is classic C++
inheritance;  parent table accesses work with the new child tables
automatically.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 30 Jul 2002 23:11:14 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why is MySQL more chosen over PostgreSQL?
On Tue, 30 Jul 2002, Bruce Momjian wrote:

> You can add children without modifying your code.  It is classic C++
> inheritance;  parent table accesses work with the new child tables
> automatically.

I don't see how my method doesn't do this as well. What code do you have
to modify in the relational way of doing things that you don't in this
inheritance way?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Wed, 31 Jul 2002 12:14:36 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
Curt Sampson wrote:
> On Tue, 30 Jul 2002, Bruce Momjian wrote:
> 
> > You can add children without modifying your code.  It is classic C++
> > inheritance;  parent table accesses work with the new child tables
> > automatically.
> 
> I don't see how my method doesn't do this as well. What code do you have
> to modify in the relational way of doing things that you don't in this
> inheritance way?

Seems like you have to modify your views to handle this, at least in the
example you just posted, right?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 30 Jul 2002 23:15:33 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why is MySQL more chosen over PostgreSQL?
On Wed, 2002-07-31 at 04:35, Curt Sampson wrote:
> On 31 Jul 2002, Hannu Krosing wrote:
> 
> > I would not rush to drop advanced features, as they may be hard to put
> > back later.
> 
> If they are hard to put back, it's generally because the other code
> in the system that relates to it has changed, so you can't just bring
> back what is in the old versions in the CVS repository.
> 
> But if the code was left in, that meant that someone had to make all of
> those integration changes you'd have to make to bring the code back;
> it's just they had to make it as they were adding new features and
> whatnot. If in the end you decide that the feature you didn't drop isn't
> important, you just did a lot of work for nothing. You may also slow
> down or stop the implementation of other, more useful features, because
> people find that the work to add them isn't worthwhile, due to having to
> change too much code.
> 
> > If they stay in, even in broken form, then there wont be
> > nearly as much patches which make fixing them harder.
> 
> Summary: someone always has to do the patches. It's just a question of
> whether you *might* do them *if* you decide to bring the feature back,
> or whether you *will* do them because the feature is there.

Often there are more than one way to do things. And the feature being
there may prompt the implementor to choose in favor of a way which does
not rule out the feature. It does not neccessarily make that harder for
new features, though it may.

> > > What we (or I, anyway) are arguing is that
> > > the relational model does everything that table inheritance does, and at
> > > least as easily.
> >
> > The problem is that 'the relational model' does nothing by itself. It is
> > always the developers/DBAs who have to do things.
> 
> Ok. So "the developer can do what table inheritance does just as easily
> in the relational model."
> 
> > And at least for some brain shapes it is much more convenient to inherit
> > tables than to (re)factor stuff into several tables to simulate
> > inheritance using the relational model.
> 
> I highly doubt that.

I said it is personal ;) Some other brain shapes are more fit to working
in relational model, even when writing front-ends in C++ or java.

> Relating two tables to each other via a key, and joining them together,

It gets more complicated fast when inheritance hierarchies get deeper,
and some info is often lost (or at least not explicitly visible from
schema). That's why advanced modeling tools allow you to model things as
inheritance hierarchies even when they have to map it to relational
model for databases which do not support inheritance.

An it is often easier to map OO languages to OOR database when you dont
have to change your mindset when going through the interface.

> allows you to do everything that inheritance allows you to do,
> but also more.

* you can do anything (and more ;) that DOMAINs do without domains.
* And you can do anything and more that can be done in C++ in C.
* And you can do anything sequences do and more without explicit syntax
  for sequences (except making them live outside of transactions,
  but this is mainly a performance hack and sequences are outside
  of relational theory anyway ;)
* And as I already mentioned, you can compute anything on
  a Turing Machine (I doubt you can compute more, but it is not
  entirely impossible as it has to work 'more' ;)

> If you have difficulty with keys and
> joins, well, you really probably want to stop and fix that problem
> before you do more work on a relational database....

It is of course beneficial to make joins faster, but it is often easier
to do for more specific cases, when the user has implicitly stated what
kind of a join he means.

One example of that is the existance of contrib/intagg which is meant to
make the relational method usable (performance-wise) for a class of
problems where _pure_ relational way falls down. 

> > > Extending the model adds complexity without adding the
> > > ability to do things you couldn't easily do before. (This, IMHO, makes
> > > table inheritance quite inelegant.)
> >
> > Then explain why SQL99 has included inheritance ?
> 
> Becuase SQL has a long, long history of doing things badly.

Or to rephrase it: SQL has a long, long history of doing things (though
badly)

> The language has been non-relational in many ways from the very beginning.

SQL has had pressure to be usable for a broad range of real-world
problems from the beginning, which theory has not.

> But Codd and Date argue that much better than I do, so I'd prefer you
> read their books and respond to those arguments. I can provide
> references if you need them.

In theory theory and practice are the same, in practice they are often
not nearly so.

 From your reference:

|Date and Darwen's _Foundation for Future Database Systems: the
|Third Manifesto_ goes into much more detail about how they feel
|object-oriented stuff should happen in relational databases. Appendix E
|("Subtables and Supertables") discusses table inheritance. It ends with
|this statement:
|
|    To sum up: It looks as if the whole business of a subtable
|    inheriting columns from a supertable is nothing but a syntatic
|    shorthand--not that there is anything wrong with syntatic
|    shorthands in general, of course, but this particular shorthand
|    does not seem particularly useful, and in any case it is always
|    more than adequately supported by the conventional view mechanism.

Which is clearly not true in PostgreSQL's case, as adequate support
would IMHO mean that the rules for insert/update/delete were generated
automatically for views as they are for select.

Of course we could go the other way and remove support for VIEW's as
they can be done using a table and a ON SELECT DO INSTEAD rule. 
Actually this is how they are done.

----------------------
Hannu


31 Jul 2002 09:06:23 +0500
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
On Tue, 30 Jul 2002, Bruce Momjian wrote:

> Curt Sampson wrote:
> > On Tue, 30 Jul 2002, Bruce Momjian wrote:
> >
> > > You can add children without modifying your code.  It is classic C++
> > > inheritance;  parent table accesses work with the new child tables
> > > automatically.
> >
> > I don't see how my method doesn't do this as well. What code do you have
> > to modify in the relational way of doing things that you don't in this
> > inheritance way?
>
> Seems like you have to modify your views to handle this, at least in the
> example you just posted, right?

You need to create a new view for the "child" table, yeah. But you had to
create a child table anyway. But all the previously existing code you had
continues to work unchanged.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Wed, 31 Jul 2002 13:31:15 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
Hannu Krosing <hannu@tm.ee> writes:
> Of course we could go the other way and remove support for VIEW's as
> they can be done using a table and a ON SELECT DO INSTEAD rule. 

Two points for Hannu ;-)

Seriously, this entire thread seems a waste of bandwidth to me.
Inheritance as a feature isn't costing us anything very noticeable
to maintain, and so I see no credible argument for expending the
effort to rip it out --- even if I placed zero value on the annoyance
factor for users who are depending on it.  (Which I surely don't.)

It's true that upgrading inheritance to handle features like cross-table
uniqueness constraints or cross-table foreign keys is not trivial.  But
I don't know of any way to handle those problems in bog-standard SQL92
either.  The fact that we don't have a solution to those issues at
present doesn't strike me as a reason to rip out the functionality we
do have.

In short: give it a rest.  There's lots of things we could be more
productively arguing about.  Think about which type conversions should
be implicit, if you need a topic ...

			regards, tom lane

Wed, 31 Jul 2002 02:17:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why is MySQL more chosen over PostgreSQL? 
On 31 Jul 2002, Hannu Krosing wrote:

> An it is often easier to map OO languages to OOR database when you dont
> have to change your mindset when going through the interface.

But you have to anyway! Adding this inheritance does not remove the
relational model; it's still there right in front of you, and you still
have to use it. You have simply added another model to keep track of as
well.

And I've done a fair amount of OO lanugage <-> relational database
interfacing, and the problems I've encountered are not helped by
table inheritance. In fact, table inheritance has been irrelevant.
But maybe I missed some problems.

> > allows you to do everything that inheritance allows you to do,
> > but also more.
>
> * And you can do anything and more that can be done in C++ in C.

Ok, this is really starting to annoy me. Can we stop with this argument,
since you *know* it is attacking a straw man?

> > If you have difficulty with keys and
> > joins, well, you really probably want to stop and fix that problem
> > before you do more work on a relational database....
>
> It is of course beneficial to make joins faster, but it is often easier
> to do for more specific cases, when the user has implicitly stated what
> kind of a join he means.

No, my point is, you simply cannot do good work at all on a relational
DB without understanding keys and joins. It does not matter whether
table inheritance is present or not. Therefore everybody effectivly
using a database is going to have enough knowledge to do this stuff
without table inheritance.

> One example of that is the existance of contrib/intagg which is meant to
> make the relational method usable (performance-wise) for a class of
> problems where _pure_ relational way falls down.

You seem to be confusing the relational model with a particular
implementation of a relational database. The relational model handles
this just fine, because the relational model doesn't have performance.

This particular contrib module does not change anything at all
about the relational model as implemented in postgres. It just
provides a particular performance work-around. Note also that the
performance problem can also be fixed in other ways; under MS-SQL server
I'd simply use a clustered index on the one-to-many table.

In fact, given that contrib/intagg works only with relatively static
data, I'm not sure why you'd use it instead of just using the
CLUSTER command once in a while.

> SQL has had pressure to be usable for a broad range of real-world
> problems from the beginning, which theory has not.

SQL is actually much less usable for many real-world problems than
a proper relational language is. But as I said, read Date, and then
argue; I'm not going to spend days rewriting his books here.

> |Date and Darwen's _Foundation for Future Database Systems: the
> |Third Manifesto_ goes into much more detail about how they feel
> |object-oriented stuff should happen in relational databases. Appendix E
> |("Subtables and Supertables") discusses table inheritance. It ends with
> |this statement:
> |
> |    To sum up: It looks as if the whole business of a subtable
> |    inheriting columns from a supertable is nothing but a syntatic
> |    shorthand--not that there is anything wrong with syntatic
> |    shorthands in general, of course, but this particular shorthand
> |    does not seem particularly useful, and in any case it is always
> |    more than adequately supported by the conventional view mechanism.
>
> Which is clearly not true in PostgreSQL's case, as adequate support
> would IMHO mean that the rules for insert/update/delete were generated
> automatically for views as they are for select.

It certainly would be nice if we did that.

> Of course we could go the other way and remove support for VIEW's as
> they can be done using a table and a ON SELECT DO INSTEAD rule.
> Actually this is how they are done.

*Sigh*. You seem to be unable to distinguish between changes to
the conceptual model of a system and changes to implementation
details.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Wed, 31 Jul 2002 15:23:43 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
Hi

> And what's the problem with networkcard_products being a separate table
> that shares a key with the products table?
>
>     CREATE TABLE products (product_id int, ...)
>     CREATE TABLE networkcard_products_data (product_id int, ...)
>     CREATE VIEW networkcard_products AS
> SELECT products.product_id, ...
> FROM products
> JOINT networkcard_products_data USING (product_id)
>
> What functionality does table inheritance offer that this traditional
> relational method of doing things doesn't?

Well, if you also have soundcard_products, in your example you could have a
product which is both a networkcard AND a soundcard. No way to restrict that
a product can be only one 'subclass' at a time... If you can make that
restriction using the relational model, you can do the same as with
subclasses. But afaict that is very hard to do...

Sander.




Wed, 31 Jul 2002 10:35:12 +0200
"Sander Steffann" <steffann@nederland.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Tue, 2002-07-30 at 14:54, Hannu Krosing wrote:
> On Tue, 2002-07-30 at 16:00, Curt Sampson wrote:
> > On 30 Jul 2002, Hannu Krosing wrote:
> > 
> > > On Tue, 2002-07-30 at 14:51, Adrian 'Dagurashibanipal' von Bidder wrote:
> > >
> > > > Bruce Momjian:
> > > > > It causes too much complexity in other parts of the system.
> > > >
> > > > That's one reason.
> > >
> > > Seems like somewhat valid reason. But still not enough to do a lot of
> > > work _and_ annoy a lot of existing users :)
> > 
> > It's almost unquestionably more work to maintain than to drop. Dropping
> > support for it is a one-time operation. Maintaining it is an ongoing
> > expense.
> 
> I would not rush to drop advanced features, as they may be hard to put
> back later. If they stay in, even in broken form, then there wont be
> nearly as much patches which make fixing them harder.

I seem to find this argument a lot on the list here.  For some reason,
many of the developers are under the impression that even if code is
never touched, it has a very high level of effort to keep it in the code
base.  That is, of course, completely untrue.  Now then, I'm not saying
that something as central as the topic at hand has a zero maintenance
cost associated with it, especially if it's constantly being run into by
the developers, but I do see it used WAY to often here for it to be
applicable in every case.

From what I can tell, in many cases, when one developer on the list
doesn't want to maintain or sees little value in a feature, it suddenly
seems to have a high price associated with it.  We need to be sure we're
making the distinction between, "I don't care to maintain this", and,
"maintaining this code is prohibitively high given it's feature
return...because...".   In other words, I find this argument used often
here will little to nothing used in context which would quantify it. 
Worse yet, it generally goes unchallenged and unquestioned.

> 
> I'm afraid that we have already dropped too much. 
> 
> For example we dropped time travel, but recent versions of Oracle now
> have some form of it, usable mostly for recovering accidentally deleted
> (and committed rows), although it is much harder to implement it using
> logs than using MVCC.

I must admit, I never understood this myself but I'm sure I'm ignorant
of the details.

> > That's a straw man argument.
> 
> Actually it was meant to be 'one straw man against another straw man 
> argument' ;)

Was clear to me!  I thought you made the point rather well.

> 
> > What we (or I, anyway) are arguing is that
> > the relational model does everything that table inheritance does, and at
> > least as easily.
> 
> The problem is that 'the relational model' does nothing by itself. It is
> always the developers/DBAs who have to do things. 
> 
> And at least for some brain shapes it is much more convenient to inherit
> tables than to (re)factor stuff into several tables to simulate
> inheritance using the relational model. 

Agreed.  It's important to remember, there are some cases where the
conceptual implications can allow for more freedom in implementation. 
This is the point that was being made with the "pure C" comment.  Sure,
I can do pretty much anything in asm, but that approach doesn't suddenly
invalidate every other way/language/concept/idiom to trying to
accomplish as given task.

Simply put, much of the power you get from any tool is often the
flexibility of a given tool to address a problem domain in many
different ways rather than just one.  Just because it doesn't fit your
paradigm doesn't mean it doesn't fit nicely into someone else's.

> 
> I still think that inheritance should be enchanced and made compatible
> with standards not removed.

I completely agree with that!

> 
> > Extending the model adds complexity without adding the
> > ability to do things you couldn't easily do before. (This, IMHO, makes
> > table inheritance quite inelegant.)
> 
> Then explain why SQL99 has included inheritance ?
> 

Yes please.  I'm very interested in hearing a rebuttal to this one.

Greg
01 Aug 2002 13:27:56 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Why is MySQL more chosen over PostgreSQL?
On 1 Aug 2002, Greg Copeland wrote:

> For some reason,
> many of the developers are under the impression that even if code is
> never touched, it has a very high level of effort to keep it in the code
> base.  That is, of course, completely untrue.

Where does this "of course" come from? I've been programming for quite a
while now, and in my experience every line of code costs you something
to maintain. As long as there's any interaction with other parts of
the system, you have to test it regularly, even if you don't need to
directly change it.

That said, if you've been doing regular work on postgres code base and you
say that it's cheap to maintain, I'll accept that.

> > Then explain why SQL99 has included inheritance ?
>
> Yes please.  I'm very interested in hearing a rebuttal to this one.

Because SQL99 is non-relational in many ways, so I guess they
figured making it non-relational in one more way can't hurt.

I mean come on, this is a language which started out not even
relationally complete!

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 2 Aug 2002 12:39:18 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
Greg Copeland <greg@CopelandConsulting.Net> writes:
> I seem to find this argument a lot on the list here.  For some reason,
> many of the developers are under the impression that even if code is
> never touched, it has a very high level of effort to keep it in the code
> base.  That is, of course, completely untrue.

FWIW, I did not notice any of the core developers making that case.

As far as I'm concerned, any patch to remove inheritance will be
rejected out of hand.  It's not costing us anything significant to
maintain as-is, and there are a goodly number of people using it.
Extending it (eg, making cross-table indexes to support inherited
uniqueness constraints) is a different kettle of fish --- but until
someone steps up to the plate with an implementation proposal, it's
rather futile to speculate what that might cost.  In the meantime,
the lack of any such plan is no argument for removing the functionality
we do have.

			regards, tom lane

Fri, 02 Aug 2002 00:30:38 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why is MySQL more chosen over PostgreSQL? 
On Fri, 2 Aug 2002, Tom Lane wrote:

> Greg Copeland <greg@CopelandConsulting.Net> writes:
> > I seem to find this argument a lot on the list here.  For some reason,
> > many of the developers are under the impression that even if code is
> > never touched, it has a very high level of effort to keep it in the code
> > base.  That is, of course, completely untrue.
>
> FWIW, I did not notice any of the core developers making that case.
>
> As far as I'm concerned, any patch to remove inheritance will be
> rejected out of hand.  It's not costing us anything significant to
> maintain as-is, and there are a goodly number of people using it.
> Extending it (eg, making cross-table indexes to support inherited
> uniqueness constraints) is a different kettle of fish --- but until
> someone steps up to the plate with an implementation proposal, it's
> rather futile to speculate what that might cost.  In the meantime,
> the lack of any such plan is no argument for removing the functionality
> we do have.

Definitely concur ... in fact, didn't someone recently do some work to
improve our inheritance code, as it wasn't 'object enough' for them?
Isn't inheritance kinda one of those things that is required in order to
be consider ourselves ORBDMS, which we do classify our selves as being?


Fri, 2 Aug 2002 03:52:21 -0300 (ADT)
"Marc G. Fournier" <scrappy@hub.org>
Re: Why is MySQL more chosen over PostgreSQL? 
On Fri, 2 Aug 2002, Marc G. Fournier wrote:

> Isn't inheritance kinda one of those things that is required in order to
> be consider ourselves ORBDMS, which we do classify our selves as being?

Well, it depends on what you call an ORDBMS. By the standards of
Date and Darwen in _The Third Manifesto_, table inheritance is not
required and is in fact discouraged as a feature trivially implemented
with views, foreign keys and constraints. (Though that does not
mean that posgresql currently has an implementation of these that
will make it trivial.)

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 2 Aug 2002 15:55:57 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL? 
On 2 Aug 2002, Hannu Krosing wrote:

> Is _The Third Manifesto_ available online ?

No. It's a book, and not a terribly small one, either.

    http://www.amazon.com/exec/obidos/ASIN/0201709287/

> Could you brief me why do they discourage a syntactical frontent to a
> feature that is trivially implemented ?

What's the point of adding it? It's just one more thing to learn.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 2 Aug 2002 19:15:40 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
On 2 Aug 2002, Hannu Krosing wrote:

> Could you point me to some pure relational languages ?
> Preferrably not pure academic at the same time ;)

The QUEL and PostQUEL languages used in Ingres and the old Postgres were
rather more "relational" than SQL.

> BTW, what other parts of SQL do you consider non-relational (and thus
> candidates for dropping) ?

I have nothing particular in mind right now. Also, note that merely
being non-relational does not make a language element a candidate
for dropping.  If lots of other databases implement a feature, it
would be silly to destroy compatability for the sake of theory.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 2 Aug 2002 19:23:04 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
* Hannu Krosing <hannu@tm.ee> [020802 06:32]:
> Your argument can as well be used against VIEWs - whats the point of
> having them, when they can trivially be implemented using ON XXX DO
> INSTEAD rules.

Well, at least on PostgreSQL it makes a difference.  We allow views to
have permissions granted to them independent of the underlying tables.
It a nice , distinguishing feature.  What other database allows you
to grant one person access to a subset of the colums of a table as
well as a subset of the rows?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Fri, 2 Aug 2002 06:46:32 -0400
"D'Arcy J.M. Cain" <darcy@druid.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Fri, 2002-08-02 at 08:55, Curt Sampson wrote:
> On Fri, 2 Aug 2002, Marc G. Fournier wrote:
> 
> > Isn't inheritance kinda one of those things that is required in order to
> > be consider ourselves ORBDMS, which we do classify our selves as being?
> 
> Well, it depends on what you call an ORDBMS. By the standards of
> Date and Darwen in _The Third Manifesto_,

Is _The Third Manifesto_ available online ?

> table inheritance is not
> required and is in fact discouraged as a feature trivially implemented
> with views, foreign keys and constraints. (Though that does not
> mean that posgresql currently has an implementation of these that
> will make it trivial.)

Could you brief me why do they discourage a syntactical frontent to a
feature that is trivially implemented ? 

If it is just views. foreign keys and constraints anyway, it should not
add compexity to implementation.

OTOH, stating explicitly what you mean, can give the system extra hints
for making good optimisation decisions.

-------------
Hannu


02 Aug 2002 13:07:56 +0200
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
On Fri, 2002-08-02 at 05:39, Curt Sampson wrote:
> Because SQL99 is non-relational in many ways, so I guess they
> figured making it non-relational in one more way can't hurt.
> 
> I mean come on, this is a language which started out not even
> relationally complete!

Could you point me to some pure relational languages ?

Preferrably not pure academic at the same time ;)

BTW, what other parts of SQL do you consider non-relational (and thus
candidates for dropping) ?

-------------
Hannu


02 Aug 2002 13:10:47 +0200
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
On Fri, 2002-08-02 at 12:15, Curt Sampson wrote:
> On 2 Aug 2002, Hannu Krosing wrote:
> 
> > Is _The Third Manifesto_ available online ?
> 
> No. It's a book, and not a terribly small one, either.
> 
>     http://www.amazon.com/exec/obidos/ASIN/0201709287/
> 
> > Could you brief me why do they discourage a syntactical frontent to a
> > feature that is trivially implemented ?
> 
> What's the point of adding it? It's just one more thing to learn.

You don't have to learn it if you don't want to. But once you do, you
have a higher level way of expressing a whole class of models.

Your argument can as well be used against VIEWs - whats the point of
having them, when they can trivially be implemented using ON XXX DO
INSTEAD rules.

--------------
Hannu


02 Aug 2002 13:34:21 +0200
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
> On Fri, 2002-08-02 at 08:55, Curt Sampson wrote:
> > On Fri, 2 Aug 2002, Marc G. Fournier wrote:
> > 
> > > Isn't inheritance kinda one of those things that is required in order to
> > > be consider ourselves ORBDMS, which we do classify our selves as being?
> > 
> > Well, it depends on what you call an ORDBMS. By the standards of
> > Date and Darwen in _The Third Manifesto_,
> 
> Is _The Third Manifesto_ available online ?

The full book is not.

An earlier version of the work is available as: http://www.acm.org/sigmod/recor
d/issues/9503/manifesto.ps

It's actually an easier read than the full book.
--
(concatenate 'string "cbbrowne" "@cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/finances.html
"very few people approach me in real life and insist on proving they
are drooling idiots."  -- Erik Naggum, comp.lang.lisp



Fri, 02 Aug 2002 09:55:07 -0400
cbbrowne@cbbrowne.com
Third Manifesto
On Thu, 2002-08-01 at 23:30, Tom Lane wrote:
> Greg Copeland <greg@CopelandConsulting.Net> writes:
> > I seem to find this argument a lot on the list here.  For some reason,
> > many of the developers are under the impression that even if code is
> > never touched, it has a very high level of effort to keep it in the code
> > base.  That is, of course, completely untrue.
> 
> FWIW, I did not notice any of the core developers making that case.
> 

I've seen it used a lot.  In many cases, it's asserted with nothing to
support it other than the fact that they are a core developer, however,
these assertions are often given against unspecified and undeveloped
code, so, it makes such an assertion invalid.  

Greg
02 Aug 2002 09:14:46 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Why is MySQL more chosen over PostgreSQL?
On Thu, 2002-08-01 at 22:39, Curt Sampson wrote:
> On 1 Aug 2002, Greg Copeland wrote:
> 
> > For some reason,
> > many of the developers are under the impression that even if code is
> > never touched, it has a very high level of effort to keep it in the code
> > base.  That is, of course, completely untrue.
> 
> Where does this "of course" come from? I've been programming for quite a
> while now, and in my experience every line of code costs you something
> to maintain.

Please re-read my statement.  Your assertion and my statement are by no
means exclusionary.  "Of course" was correctly used and does correctly
apply, however, it doesn't appear it was correctly comprehended by you
as it applied in context.  I agree with your statement of, "...every
line of code costs you something to maintain..." which in no way, shape,
or form contradicts my statement of, "...it has a very high level of
effort...of course not...".  Fact is, if code which is never touched and
requires a very level of effort to maintain, chances are you screwed up
somewhere.

Hopefully we can agree that "...costs you something..." does not have to
mean, "...very high level of effort..."

 As long as there's any interaction with other parts of
> the system, you have to test it regularly, even if you don't need to
> directly change it.

No one said otherwise.  Perhaps you were replying to someone else?!  :)

> 
> That said, if you've been doing regular work on postgres code base and you
> say that it's cheap to maintain, I'll accept that.

Please re-read my statement.  In my mind, this was implicately
understood from the statement I made.

Shesh...sure hope I remembered to dot all my "i's"...

Greg
02 Aug 2002 09:26:10 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Why is MySQL more chosen over PostgreSQL?
Greg Copeland <greg@CopelandConsulting.Net> writes:
> On Thu, 2002-08-01 at 23:30, Tom Lane wrote:
>> FWIW, I did not notice any of the core developers making that case.

> I've seen it used a lot.

Perhaps my meaning wasn't clear: I meant that no one who's familiar
with the code base has made that argument against inheritance.  It
doesn't impact enough of the code to be a maintenance problem.  There
is quite a bit of inheritance code in tablecmds.c, and one or two
other files, but overall it's a very small issue.

			regards, tom lane

Fri, 02 Aug 2002 10:35:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why is MySQL more chosen over PostgreSQL? 
> Well, if you also have soundcard_products, in your example you could have a
> product which is both a networkcard AND a soundcard. No way to restrict
> that a product can be only one 'subclass' at a time... If you can make that
> restriction using the relational model, you can do the same as with
> subclasses. But afaict that is very hard to do...
>

Perhaps I'm mistaken, but it looks to me as if the relational model still 
holds quite cleanly. 

CREATE TABLE products (
id int4 primary key,
name text );

CREATE TABLE soundcard (
prod_id int4 REFERENCES products(id),
some_feature BOOLEAN);

CREATE VIEW soundcard_v AS SELECT * FROM products, soundcard WHERE products.id 
= soundcard.prod_id;

CREATE TABLE networkcard (
prod_id int4 REFERENCES products(id),
hundred_base_t BOOLEAN);

CREATE VIEW networkcard_v AS SELECT * FROM products, networkcard WHERE 
products.id = networkcard.prod_id;

Now, to get the networkcard/soundcard combos, you just need to do:
SELECT * FROM soundcard_v, networkcard_v WHERE soundcard_v.id = 
networkcard_v.id;

For what it's worth, I didn't make any mistakes writing it up the first time. 
It most certainly "fits my brain" well and seems simple and clean.

I am not advocating that we remove inheritance, but I (so far) agree with Curt 
that it's pretty useless.

Regards,
	Jeff


Fri, 2 Aug 2002 10:53:30 -0700
Jeff Davis <list-pgsql-hackers@empires.org>
Re: Why is MySQL more chosen over PostgreSQL?
On Fri, 2002-08-02 at 13:53, Jeff Davis wrote:
> > Well, if you also have soundcard_products, in your example you could have a
> > product which is both a networkcard AND a soundcard. No way to restrict
> > that a product can be only one 'subclass' at a time... If you can make that
> > restriction using the relational model, you can do the same as with
> > subclasses. But afaict that is very hard to do...
> >
> 
> Perhaps I'm mistaken, but it looks to me as if the relational model still 
> holds quite cleanly. 
> 
> CREATE TABLE products (
> id int4 primary key,
> name text );
> 
> CREATE TABLE soundcard (
> prod_id int4 REFERENCES products(id),
> some_feature BOOLEAN);
> 
> CREATE VIEW soundcard_v AS SELECT * FROM products, soundcard WHERE products.id 
> = soundcard.prod_id;
> 
> CREATE TABLE networkcard (
> prod_id int4 REFERENCES products(id),
> hundred_base_t BOOLEAN);
> 
> CREATE VIEW networkcard_v AS SELECT * FROM products, networkcard WHERE 
> products.id = networkcard.prod_id;
> 
> Now, to get the networkcard/soundcard combos, you just need to do:
> SELECT * FROM soundcard_v, networkcard_v WHERE soundcard_v.id = 
> networkcard_v.id;
> 
> For what it's worth, I didn't make any mistakes writing it up the first time. 
> It most certainly "fits my brain" well and seems simple and clean.

Yup, you've basically done it -- but you still need the permissions
lines (soundcard people shouldn't be able to modify networkcard products
-- but rules on the views could accomplish that).

create table product(prod_id int4 primary key);
create table networkcard(hundred_base_t boolean) inherits(product);
create table soundcard(some_feature boolean) inherits(product);
create table something(some_feature integer) inherits(product);

My favorite (and regularly abused):

create table package_deal(package_price) inherits (product, networkcard,
soundcard, something);


Poor examples as noone would make a sellable package that way, but it
shows how it is simply shorter to do.   New 'product' consists of a
networkcard, soundcard, and something -- always.


Nobody is saying that:

ESC:%s/aba/wo/g

is a real easy way to know to replace all occurrences of 'aba' with
'wo', and there are lots of other ways of doing it -- but if you happen
to know it, then it certainly makes life easier but is not a very
portable command set :)


Views don't do much else but make life easier.  Putting the SQL into the
original queries is just as effective and slightly lower overhead.

Inheritance for me makes life a little bit easier in certain places. 
It's also easier for the programmers to follow than a wackload of views
and double inserts.


02 Aug 2002 14:50:08 -0400
Rod Taylor <rbt@zort.ca>
Re: Why is MySQL more chosen over PostgreSQL?
On 2 Aug 2002, Hannu Krosing wrote:

> On Fri, 2002-08-02 at 12:15, Curt Sampson wrote:
> > On 2 Aug 2002, Hannu Krosing wrote:
> >
> > > Could you brief me why do they discourage a syntactical frontent to a
> > > feature that is trivially implemented ?
> >
> > What's the point of adding it? It's just one more thing to learn.
>
> You don't have to learn it if you don't want to. But once you do, you
> have a higher level way of expressing a whole class of models.

Perhaps this is the problem. I disagree that it's  a "higher" level.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Sat, 3 Aug 2002 20:32:10 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Sat, 2002-08-03 at 16:32, Curt Sampson wrote:
> On 2 Aug 2002, Hannu Krosing wrote:
> 
> > On Fri, 2002-08-02 at 12:15, Curt Sampson wrote:
> > > On 2 Aug 2002, Hannu Krosing wrote:
> > >
> > > > Could you brief me why do they discourage a syntactical frontent to a
> > > > feature that is trivially implemented ?
> > >
> > > What's the point of adding it? It's just one more thing to learn.
> >
> > You don't have to learn it if you don't want to. But once you do, you
> > have a higher level way of expressing a whole class of models.
> 
> Perhaps this is the problem. I disagree that it's  a "higher" level.

I don't mean "morally higher" ;)

Just more concise and easier to grasp, same as VIEW vs. TABLE + ON xxx
DO INSTEAD rules.

With INSTEAD rules you can do more than a VIEW does, but when all you
want is a VIEW, then it is easier to define a VIEW, thus VIEW is a
higher level construct than TABLE + ON xxx DO INSTEAD

That is the same way that C is "higher" than ASM and ASM is higher than
writing code directly using hex editor.

--------------
Hannu


03 Aug 2002 18:19:30 +0500
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
Hi,

> > Well, if you also have soundcard_products, in your example you could
have a
> > product which is both a networkcard AND a soundcard. No way to restrict
> > that a product can be only one 'subclass' at a time... If you can make
that
> > restriction using the relational model, you can do the same as with
> > subclasses. But afaict that is very hard to do...
>
> CREATE VIEW networkcard_v AS SELECT * FROM products, networkcard WHERE
> products.id = networkcard.prod_id;

I think I was not clear enough... You just demonstrated that it is possible
to have a card that is a soundcard and a networkcard at the same time. The
point I tried to make was that it is difficult to _prevent_ this. Ofcourse I
agree with you that your example fits the relational model perfectly!

I have this problem in a few real-life cases, so if you have a sollution to
this, I would realy appreciate it!
Sander.




Sat, 3 Aug 2002 19:33:04 +0200
"Sander Steffann" <sander@steffann.nl>
Re: Why is MySQL more chosen over PostgreSQL?
On 3 Aug 2002, Hannu Krosing wrote:

> On Sat, 2002-08-03 at 16:32, Curt Sampson wrote:
> > On 2 Aug 2002, Hannu Krosing wrote:
> >
> > Perhaps this is the problem. I disagree that it's  a "higher" level.
>
> I don't mean "morally higher" ;)
> Just more concise and easier to grasp, same as VIEW vs. TABLE + ON xxx
> DO INSTEAD rules.

That's because we don't do a good job of implementing updatable views.
Views ought to be as fully updatable as possible given the definition,
without having to define rules for doing this. Simple views such as

    CREATE TABLE tab1 (
	id	int,
	foo	text
	)
    CREATE TABLE tab2 (
	id	int,
	bar	text
	)
    CREATE VIEW something AS
	SELECT tab1.id, tab1.foo, tab2.bar
	FROM tab1, tab2
	WHERE tab1.id = tab2.id

ought to be completely updatable without any special rules.

For further info see the detailed discussion of this in Date's
database textbook.

> That is the same way that C is "higher" than ASM and ASM is higher than
> writing code directly using hex editor.

No, this is the same way that Smalltalk is "higher" than Lisp.
(I.e., it isn't.)

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Sun, 4 Aug 2002 12:49:41 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Sat, 3 Aug 2002, Sander Steffann wrote:

> I have this problem in a few real-life cases, so if you have a sollution to
> this, I would realy appreciate it!

Add a card_type column to your main table, and insert something
indicating the value of the card type there.

That won't stop you from having entries for the card in both
network_card and sound_card, but one of those entries will be
meaningless extra data.

Of course, this also means you have to go back to the relational
model to select all your network cards. Doing

    SELECT * FROM network_card

may also return (incorrectly inserted) non-network cards, if your
data are not clean, but

    SELECT card.card_id, card.whatever, network_card.*
    FROM card, network_card
    WHERE card.card_id = network_card.card_id
	AND card.type = 'N'

is guaranteed to return correct results. And of course you can just
make that a view called network_card, and the same statement as
you used with the inerhited table will work.

Oops, did I just replace your "object-oriented" system with a
relational one that does everything just as easily, and even does
something the object-oriented one can't do? Sorry about that. :-)

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Wed, 7 Aug 2002 11:41:54 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
Curt Sampson wrote:
> On Sat, 3 Aug 2002, Sander Steffann wrote:
> 
> 
>>I have this problem in a few real-life cases, so if you have a sollution to
>>this, I would realy appreciate it!
> 
> 
> Add a card_type column to your main table, and insert something
> indicating the value of the card type there.
> 
> That won't stop you from having entries for the card in both
> network_card and sound_card, but one of those entries will be
> meaningless extra data.

So again relational theory can solve the problem but at a cost in 
efficiency.

So could a Turing machine.

> Of course, this also means you have to go back to the relational
> model to select all your network cards. Doing
> 
>     SELECT * FROM network_card
> 
> may also return (incorrectly inserted) non-network cards, if your
> data are not clean, but
> 
>     SELECT card.card_id, card.whatever, network_card.*
>     FROM card, network_card
>     WHERE card.card_id = network_card.card_id
> 	AND card.type = 'N'
> 
> is guaranteed to return correct results. And of course you can just
> make that a view called network_card, and the same statement as
> you used with the inerhited table will work.

The view would work, but of course you have to define the view.  Any 
time you have to do something manually, even something as simple as to 
define a view, the chance for casual error is introduced.

> Oops, did I just replace your "object-oriented" system with a
> relational one that does everything just as easily, and even does
> something the object-oriented one can't do?

You mean "waste space with meaningless extra data"?

Of *course* you can do that in an object-oriented one.  Your skills 
aren't unique, nor is your skill level though you act as though you 
think you're in a class of your own.

> Sorry about that. :-)

Me, too.  The relational model is extremely powerful but it's not the 
be-all and end-all of all things.

You still haven't answered my earlier observation that the PG model, 
with all its flaws, can reduce the number of joins required.

For instance in your example card and network card need to be joined if 
you want to return network card.  That's what I see in the view.

"FROM card, network_card"

Using PG's inheritance no join is necessary.

I assume you know that because you've demonstrated your brilliance to 
such an extent that I can only assume you've familiarized yourself with 
the actual details of PG's implementation?

I can't imagine you're the kind of mouth-flapper that would do so 
without such basic research, after all.

So ... assuming my assumption is true and that you've bothered to study 
the implementation, why should I prefer the join over the 
faster-executing single-table extraction if I use PG's type extension 
facility?


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


Tue, 06 Aug 2002 20:09:48 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Why is MySQL more chosen over PostgreSQL?
On Tue, 6 Aug 2002, Don Baccus wrote:

> So again relational theory can solve the problem but at a cost in
> efficiency.

If you're talking about theory, efficiency doesn't come into it.
The question is how and whether you can express the constratints
you need to express.

Note that I am not advocating removing anything that does not fit into
relational theory but does let us do things more efficiently. We live
in an imperfect world, after all.

In fact, why don't we split the dicussion into two separate parts:
relational theory vs. object-oriented theory, and practical use
with postgres, and never mix the two. Ok?

> So could a Turing machine.

Theory: Sure. But this is much harder to express in a turing machine
isn't it?

> The view would work, but of course you have to define the view.  Any
> time you have to do something manually, even something as simple as to
> define a view, the chance for casual error is introduced.

Theory: views should automatically make themselves as updatable as
possible, unless expressed otherwise. In fact, relationally, there
is no difference between a view and a base table; that's only part
of a storage model, which doesn't come into it in our perfect
theoretical world.

Practice: defining a non-updatable view is pretty trivial in
postgres.  Defining an updatable view is rather harder, and more
subject to error.  However, in this particular case it's a necessary
evil, since you can't use table inheritance to do what you want.

> > Oops, did I just replace your "object-oriented" system with a
> > relational one that does everything just as easily, and even does
> > something the object-oriented one can't do?
>
> You mean "waste space with meaningless extra data"?

No, I mean set up your database so that a card can be a network_card
or a sound_card, but not both.

You may also waste some space with meaningless data, if you have bugs
in your application, but a) that meaningless data is pretty easy to
clean up, and b) wasting a bit of space is a lot better than having
incorrect data.

> Me, too.  The relational model is extremely powerful but it's not the
> be-all and end-all of all things.

Theory: Never said it was. I said that table inheritance is an
unnecessary addition to a relational database; it offers no capabilities
you can't offer within the relational model, nor does it make things
easier to do than within the relational model. (Since we are talking
about theory, I hasten to add that it is possible to implement something
where the OO way is easier to use than the relational way, but you're
not forced to implement things this way.)

> You still haven't answered my earlier observation that the PG model,
> with all its flaws, can reduce the number of joins required.

Sorry. Let me deal with that now: that's an incorrect observation.

> For instance in your example card and network card need to be joined if
> you want to return network card.  That's what I see in the view.
>
> "FROM card, network_card"
>
> Using PG's inheritance no join is necessary.

But going the other way around:

    FROM card

Result  (cost=0.00..27.32 rows=6 width=36)
  ->  Append  (cost=0.00..27.32 rows=6 width=36)
        ->  Index Scan using ih_parent_pkey on ih_parent  (cost=0.00..4.82 rows=1 width=36)
        ->  Seq Scan on ih_child ih_parent  (cost=0.00..22.50 rows=5 width=36)

Sure looks like a join to me.

> So ... assuming my assumption is true and that you've bothered to study
> the implementation, why should I prefer the join over the
> faster-executing single-table extraction if I use PG's type extension
> facility?

Well, it depends on what your more frequent queries are.

But anyway, I realized that some of the joins I've shown are
unnecessary; I've incorrectly implemented, relationally, the inheritance
model you've shown. Here's the explanation:

Given a parent with an ID field as the primary key, and two children
that inherit that field, you can have the same ID in child1 and child2,
resulting in the ID appearing twice in the parent table. In other
words, the PRIMARY KEY constraint on the parent is a lie. If I were
to implement that relationally (though I'm not sure why I'd want to),
I'd just implement the parent as a view of the children, and add
another table to hold the parent-only data. Now the joins under all
circumstances would be exactly the same as in the version implemented
with inheritance, and you'd have the added advantage that there would be
no lies in the database schema. (And I'm sure I've even seen complaints
about this before, and requests for hacks such as cross-table indexes to
get around this.)

If you feel that I'm missing something here, please send me a schema and
queries that you believe that inheritance does more efficiently than any
relational method can in postgres, and I'll implement it relationally
and test it. If it is indeed impossible to implement as efficiently
relationally as it is with inheritance, I will agree with you that, for
the moment, inheritance has some practical uses in postgres. (I'll also
submit a change request to fix the relational stuff so that it can be
implemented as efficiently.)

It could even happen that you will show me something that the relational
model just doesn't handle, in which case you'll have won the argument.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC



Wed, 7 Aug 2002 13:48:51 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
Curt Sampson wrote:
> On Tue, 6 Aug 2002, Don Baccus wrote:
> 
> 
>>So again relational theory can solve the problem but at a cost in
>>efficiency.
> 
> 
> If you're talking about theory, efficiency doesn't come into it.

That's rather the point, isn't it?

In the real world, it does.

> The question is how and whether you can express the constratints
> you need to express.

Have I said anything other than this?

> Note that I am not advocating removing anything that does not fit into
> relational theory but does let us do things more efficiently. We live
> in an imperfect world, after all.
> 
> In fact, why don't we split the dicussion into two separate parts:
> relational theory vs. object-oriented theory, and practical use
> with postgres, and never mix the two. Ok?

Because in fact you have advocated removing the OO stuff.

You won't find me suggesting that this feature can't be modelled in 
relational theory.  AFter all I've got something like a quarter million 
lines of code over at OpenACS that proves you can.

However my co-developers and users would've glady accept the decreased 
effort in implementation and cleaner source code that the PG OO 
extensions offer if the implementation had been more complete.

>>The view would work, but of course you have to define the view.  Any
>>time you have to do something manually, even something as simple as to
>>define a view, the chance for casual error is introduced.
> 
> 
> Theory: views should automatically make themselves as updatable as
> possible, unless expressed otherwise. In fact, relationally, there
> is no difference between a view and a base table; that's only part
> of a storage model, which doesn't come into it in our perfect
> theoretical world.

Whether or not the view is written in such a way that it doesn't need to 
be rewritten, dropped and recreated when you change the tables that its 
composed of, you *still* need to write that view when you first extend 
your type using the table+view model.

That's what I was referring to above.  You have to write the view and 
get it right (i.e. write the join using the proper key for it and the 
base view you're extending).

Writing extra code, no matter how trivial, increases the odds that a 
mistake will be made.

You also need to write the proper foreign key and primary key 
constraints in the table being used to do the type extension.  Of course 
this is true of PG's current OO implementation  but if it were fixed it 
would be one less chore that the programmer needs to remember.

> But anyway, I realized that some of the joins I've shown are
> unnecessary; I've incorrectly implemented, relationally, the inheritance
> model you've shown.

You mean you accidently supported the argument that this approach is, 
perhaps, more error prone?

> It could even happen that you will show me something that the relational
> model just doesn't handle, in which case you'll have won the argument.

I haven't *made* that argument.   Please stop raising strawmen.

The argument I've made is that even though that you can model PG's OO 
features not just relationally but in real-live warts-and-all SQL92, 
that doesn't mean they're not useful.

We don't need the binary "integer" type, either.  We could just use 
"number".  Yes, operations on "number" are a bit slower and they often 
take more space, but ...

Shall we take a vote :)


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


Wed, 07 Aug 2002 06:06:18 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Why is MySQL more chosen over PostgreSQL?
On Wed, 2002-08-07 at 06:48, Curt Sampson wrote:
> On Tue, 6 Aug 2002, Don Baccus wrote:
> 
> > So again relational theory can solve the problem but at a cost in
> > efficiency.
> 
> If you're talking about theory, efficiency doesn't come into it.
> The question is how and whether you can express the constratints
> you need to express.
> 
> Note that I am not advocating removing anything that does not fit into
> relational theory but does let us do things more efficiently. We live
> in an imperfect world, after all.
> 
> In fact, why don't we split the dicussion into two separate parts:
> relational theory vs. object-oriented theory, and practical use
> with postgres, and never mix the two. Ok?
> 
> > So could a Turing machine.
> 
> Theory: Sure. But this is much harder to express in a turing machine
> isn't it?

You got it ;) The claim was that it is easiest to express it using
inheritance, a little harder using pure relational model and much harder
using a Turing machine.

> > The view would work, but of course you have to define the view.  Any
> > time you have to do something manually, even something as simple as to
> > define a view, the chance for casual error is introduced.
> 
> Theory: views should automatically make themselves as updatable as
> possible, unless expressed otherwise. In fact, relationally, there
> is no difference between a view and a base table; that's only part
> of a storage model, which doesn't come into it in our perfect
> theoretical world.
> 
> Practice: defining a non-updatable view is pretty trivial in
> postgres.  Defining an updatable view is rather harder, and more
> subject to error.

But defining an updatable inherited table is easy .

>  However, in this particular case it's a necessary
> evil, since you can't use table inheritance to do what you want.
> > > Oops, did I just replace your "object-oriented" system with a
> > > relational one that does everything just as easily, and even does
> > > something the object-oriented one can't do?
> >
> > You mean "waste space with meaningless extra data"?
> 
> No, I mean set up your database so that a card can be a network_card
> or a sound_card, but not both.

Why can't you do this using inheritance ?

create table card(...);
create table network_card(...) inherits(card);
create table sound_card(...) inherits(card);

should do exactly that.

> You may also waste some space with meaningless data, if you have bugs
> in your application, but a) that meaningless data is pretty easy to
> clean up, and b) wasting a bit of space is a lot better than having
> incorrect data.

in this case wasting a bit of space == having incorrect data.

The possiblity of getting out wrong data always exists if there is
incorrect data in the system. You can't reasonably expect that nobody
will query just the network_card table without doing the fancy join with
additional card.type='N'. The join version is also bound to be always
slower than the non-join version.

> > Me, too.  The relational model is extremely powerful but it's not the
> > be-all and end-all of all things.
> 
> Theory: Never said it was. I said that table inheritance is an
> unnecessary addition to a relational database; it offers no capabilities
> you can't offer within the relational model, nor does it make things
> easier to do than within the relational model. (Since we are talking
> about theory, I hasten to add that it is possible to implement something
> where the OO way is easier to use than the relational way, but you're
> not forced to implement things this way.)
> 
> > You still haven't answered my earlier observation that the PG model,
> > with all its flaws, can reduce the number of joins required.
> 
> Sorry. Let me deal with that now: that's an incorrect observation.
> 
> > For instance in your example card and network card need to be joined if
> > you want to return network card.  That's what I see in the view.
> >
> > "FROM card, network_card"
> >
> > Using PG's inheritance no join is necessary.
> 
> But going the other way around:
> 
>     FROM card
> 
> Result  (cost=0.00..27.32 rows=6 width=36)
>   ->  Append  (cost=0.00..27.32 rows=6 width=36)
>         ->  Index Scan using ih_parent_pkey on ih_parent 
(cost=0.00..4.82 rows=1 width=36)
>         ->  Seq Scan on ih_child ih_parent  (cost=0.00..22.50 rows=5
width=36)
> 
> Sure looks like a join to me.
> 

But you did not have to write it - it was written, debugged and
optimised by postgres.

> > So ... assuming my assumption is true and that you've bothered to study
> > the implementation, why should I prefer the join over the
> > faster-executing single-table extraction if I use PG's type extension
> > facility?
> 
> Well, it depends on what your more frequent queries are.
> 
> But anyway, I realized that some of the joins I've shown are
> unnecessary; I've incorrectly implemented, relationally, the inheritance
> model you've shown. Here's the explanation:

Which proves that using lower level idioms for describing inheritance is
more error prone. 

Btw, this is a general principle - the more lines of code you write to
solve the same problem, the more possibilities you have to make errors.
Given enough possibilities, everyone makes errors.

OTOH, sometimes you need to do low-level work to get the last bit of
performance out of the systems (sometimes down to assembly level).

...

> It could even happen that you will show me something that the relational
> model just doesn't handle, in which case you'll have won the argument.

As the inheritance model is built on top of relational one, it is
impossible to come up with something that relational model does not
handle. Just as it is impossible to show you a VIEW that can't be done
with ON SELECT DO INSTEAD rules.

What our current implementation does show, is that there is a subset of
generated views that are updatable. They are not explicitly statically
defined as views (because they change dynamically as new child tables
are inherited) but they are constructed each time you do a
SELECT/UPDATE/DELETE on parent table. 

I suspect that the fact that this is implemented and general updatable
views are not is due to bigger complexity of doing this for a general
case than for specific "inheritance" case.

---------------------
Hannu


07 Aug 2002 16:38:47 +0200
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
On 7 Aug 2002, Hannu Krosing wrote:

> > Theory: Sure. But this is much harder to express in a turing machine
> > isn't it?
>
> You got it ;) The claim was that it is easiest to express it using
> inheritance, a little harder using pure relational model and much harder
> using a Turing machine.

Ok. I agree that it's much harder with a turning machine. I do *not*
agree that it's harder with the relational model. In fact, since you
*must* use the relational model for some things, I argue that it's
harder to switch back and forth between the relational and OO models,
and understand the effects of each on the other, than it is just to do
it in OO form in the first place.

In fact, I'd argue at this point, as far as table inheritance goes,
we don't even have a real model here. Let's look at a few of the problems.

1. I create a base table with an column with a UNIQUE constraint on
it, and two child tables. I can insert the same value into that column
into the two child tables, thus violating the unique constraint in the
base table. Now how can it be acceptable, in postgres or any other
relational database, to have a column declared to contain unique values
have non-unique values in it? (Note that this was the source of my
error in re-implementing some table-inheritance-modeled stuff here in
relational form; I preserved that unique constraint when I should not
have.)

2. When you have child1 and child2 tables both inheriting directly
from a base table, you can have entries in both child1 and child2
whose component from the base table is the same. What does this
mean? Are we supposed to be able to have objects that can simultaneously
be both subtypes?

Well, I could go on, but just from this you can see that:

    1. We appear to have no proper theory even defined for how
    table inheritance should work.

    2. If we did, either postgres is not consistent with it, or
    the theory itself is in conflict with the relational portion
    of the database.

Whatever way you look at it, it's apparent to me that using table
inheritance is dangerous, confusing, and should be avoided if you
want to maintain data integrity and a self-consistent view of your
data.

> > No, I mean set up your database so that a card can be a network_card
> > or a sound_card, but not both.
>
> Why can't you do this using inheritance ?
>
> create table card(...);
> create table network_card(...) inherits(card);
> create table sound_card(...) inherits(card);
>
> should do exactly that.

But it doesn't. You can have an entry in network_card and another one in
sound_card which share the same primary key in the sound_card table.

> in this case wasting a bit of space == having incorrect data.

No, it doesn't. Your queries will never return incorrect data; the
"unused" records will be ignored.

> The possiblity of getting out wrong data always exists if there is
> incorrect data in the system.

No, you can't put incorrect data into the system. The data about what
type of card it is is not in the sound_card or network_card table, but
in the card table itself, and thus it can only ever have one value for
any card entry. It's impossible for that column to have more than one
value, thus impossible for that column to have incorrect data.

Now you may argue that, because there's an entry for that card in
both network_card and sound_card, that means that the card has two
types. But that's just deliberate misinterpretation, because you're
getting the type information from the wrong place.  You might as
well argue that a table holding temperatures is "incorrect data"
because someone put them in in degress centigrate, and you're
interpreting them as degrees Fahrenheit when you pull them out.

> > Sure looks like a join to me.
>
> But you did not have to write it - it was written, debugged and
> optimised by postgres.

So? The argument I was replying to stated that his method was more
efficient because it didn't use joins. Who wrote the join does not
matter; it turns out that inside it all joins happen, and so it's
not more efficient.

> > But anyway, I realized that some of the joins I've shown are
> > unnecessary; I've incorrectly implemented, relationally, the inheritance
> > model you've shown. Here's the explanation:
>
> Which proves that using lower level idioms for describing inheritance is
> more error prone.

No, it proves that the semantics of table inheritance are confusing, or
postgres incorrectly impelements them, or both. This kind of mistake is
*exactly* the reason I avoid table inheritance; I couldn't tell just
what you were doing! And I still am not convinced that what you were
doing was what you wanted to do, especially given that I've seen other
complaints in this forum that table inheritance specifically was *not*
doing what people wanted it to do (thus the plea for cross-table unique
indexes).

> I suspect that the fact that this is implemented and general updatable
> views are not is due to bigger complexity of doing this for a general
> case than for specific "inheritance" case.

I'll agree with that.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC



Thu, 8 Aug 2002 10:47:50 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Wed, 7 Aug 2002, Don Baccus wrote:

> >>So again relational theory can solve the problem but at a cost in
> >>efficiency.
> >
> > If you're talking about theory, efficiency doesn't come into it.
>
> That's rather the point, isn't it?
>
> In the real world, it does.

Well, I think I dealt with this elsewhere in my post by showing
that I can always implement what you did with inheritance just as
efficiently using relational methods, and sometimes more efficiently.

> Because in fact you have advocated removing the OO stuff.

Actually, I'd suggested thinking about removing the OO stuff. Starting
a discussion about the concept is far from "advocating" it. And in fact
I'd backed off the idea of removing it. However, now that it appears to
me that table inheritance actually breaks the relational portion of the
database, I'm considering advocating its removal. (This requires more
discussion, of course.)

> Writing extra code, no matter how trivial, increases the odds that a
> mistake will be made.

Yeah. But using a broken table inheritance model is far more likely to
cause bugs and errors. It certainly did when I tried to figure out what
you were doing using inheritance. Not only did I get it wrong, but I'm
not at all convinced that what you were doing was what you really wanted
to do.

> You mean you accidently supported the argument that this approach is,
> perhaps, more error prone?

No, supported the argument that table inheritance is either
ill-defined, broken, or both.

> The argument I've made is that even though that you can model PG's OO
> features not just relationally but in real-live warts-and-all SQL92,
> that doesn't mean they're not useful.

All right. I disagree with that, too. I think that they are not
only not useful, but harmful.

> We don't need the binary "integer" type, either.  We could just use
> "number".  Yes, operations on "number" are a bit slower and they often
> take more space, but ...
>
> Shall we take a vote :)

If you like. I vote we keep the integer type. Any other questions?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 8 Aug 2002 10:54:45 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Thu, 2002-08-08 at 06:47, Curt Sampson wrote:
> On 7 Aug 2002, Hannu Krosing wrote:
> 
> > > Theory: Sure. But this is much harder to express in a turing machine
> > > isn't it?
> >
> > You got it ;) The claim was that it is easiest to express it using
> > inheritance, a little harder using pure relational model and much harder
> > using a Turing machine.
> 
> Ok. I agree that it's much harder with a turning machine. I do *not*
> agree that it's harder with the relational model. In fact, since you
> *must* use the relational model for some things, I argue that it's
> harder to switch back and forth between the relational and OO models,

For me they are _not_ two different models but rather one
object-relational model. Same as C++ in _not_ a completely new language
but rather an extension of plain C.

As you seem to like fat books, check out :

"Object Relational Dbms: Tracking the Next Great Wave" by Michael
Stonebraker, Dorothy Moore (Contributor), Paul Brown
ISBN: 1558604529

I'm sure you find the requested arguments against Date there ;)

> and understand the effects of each on the other, than it is just to do
> it in OO form in the first place.
> 
> In fact, I'd argue at this point, as far as table inheritance goes,
> we don't even have a real model here.

The table inheritance _implementation_ in PG is in fact broken in
several ways, most notably in  not enforcing uniqueness over all
inherited tables and not inheriting other constraints.

But as you often like to emphasize, model and implementation _are_
different things.

--------------
Hannu


08 Aug 2002 09:01:15 +0500
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
Curt Sampson wrote:

>>Because in fact you have advocated removing the OO stuff.

> Actually, I'd suggested thinking about removing the OO stuff.

Man, aren't we into splitting hairs?

You actually stated your case quite strongly and indeed if you hadn't, 
the thread would've died long ago.

Whatever.   You're just dick-waving.

Enjoy your life :)

> Starting
> a discussion about the concept is far from "advocating" it. And in fact
> I'd backed off the idea of removing it. However, now that it appears to
> me that table inheritance actually breaks the relational portion of the
> database, I'm considering advocating its removal. (This requires more
> discussion, of course.)

Except apparently you have no life, oh well, not my problem.

>>Writing extra code, no matter how trivial, increases the odds that a
>>mistake will be made.
> 
> 
> Yeah. But using a broken table inheritance model is far more likely to
> cause bugs and errors. It certainly did when I tried to figure out what
> you were doing using inheritance. Not only did I get it wrong, but I'm
> not at all convinced that what you were doing was what you really wanted
> to do.

I wasn't using inheritance.  I didn't post an example.  And all agree 
that PG's model is broken and eventually needs to be fixed.

Three strawmen in one paragraph.

Again, you're dick-waving and further discussion is not useful.

>>You mean you accidently supported the argument that this approach is,
>>perhaps, more error prone?

> No, supported the argument that table inheritance is either
> ill-defined, broken, or both.

Then what you're saying is you've been arguing all this time against it 
without understanding how it works?

Because either

1. If you understood how it worked then you screwed up your more complex 
view-based analogue, therefore supporting the argument that you've shown 
that the mapping is more error prone.

2. Or you screwed up your code because you've been dick-waving without 
bothering to learn the semantics of the PG OO extensions, which doesn't 
really enhance your credibility.

Which is it?  The idiot behind door number one or the pendantic boor 
behind door number two?

>>We don't need the binary "integer" type, either.  We could just use
>>"number".  Yes, operations on "number" are a bit slower and they often
>>take more space, but ...
>>
>>Shall we take a vote :)
> 
> 
> If you like. I vote we keep the integer type. Any other questions?

Sure ... why the inconsistency without explanation?

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


Wed, 07 Aug 2002 23:19:13 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Why is MySQL more chosen over PostgreSQL?
On Wed, 7 Aug 2002, Don Baccus wrote:

> Whatever.   You're just dick-waving....
> Except apparently you have no life, oh well, not my problem....
> Again, you're dick-waving and further discussion is not useful....
> Which is it?  The idiot behind door number one or the pendantic boor
> behind door number two?

Uh, yeah. If ad hominem attacks win arguments, I guess you win.
I'll let others decide whether the above arguments are a good reason
to keep table inheritance in postgres.

>
> >>We don't need the binary "integer" type, either.  We could just use
> >>"number".  Yes, operations on "number" are a bit slower and they often
> >>take more space, but ...
> >>
> >>Shall we take a vote :)
> >
> > If you like. I vote we keep the integer type. Any other questions?
>
> Sure ... why the inconsistency without explanation?

Personally I don't find it inconsistent that I want to remove something
that's broken and of dubious utility but keep something that works and
is demonstrably useful. It must be something to do with my dick, I
suppose. But I'll admit, your arguments are beyond me. I surrender.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 8 Aug 2002 16:08:16 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
On Thu, 2002-08-08 at 17:57, Curt Sampson wrote:
> On 8 Aug 2002, Hannu Krosing wrote:
> 
> > For me they are _not_ two different models but rather one
> > object-relational model.
> 
> Well, given that we've already demonstrated two rather different ways
> of saying "the same thing," I think we have two models happening here.
> However, feel free to explain your "object-relational model" in more
> detail, including its advantages over the ordinary relational model.

The main difference (in the inheritance part) is that a relation does
not have one fixed set of fields, but can have any additional fields
added in inherited tables and still be part of to the base table as
well.

...

> > The table inheritance _implementation_ in PG is in fact broken in
> > several ways, most notably in  not enforcing uniqueness over all
> > inherited tables and not inheriting other constraints.
> 
> Right. I'm glad we agree on that.
> 
> > But as you often like to emphasize, model and implementation _are_
> > different things.
> 
> Ok. I won't object too much to the model, but let's get rid of this
> severely broken implementation, unless there are some prospects
> for fixing it. How's that?

Actually I am not against ripping out the current broken implementation,
but not before there has been a new, correct model available for at
least two releses, so that people have had time to switch over.

The inheritance model that SQL99 prescribes is more like java's - single
inheritance (so that you have no way of inheriting two primary keys ;) +
LIKE in table definition (in some ways similar to java interfaces)

I see that this could be implemented quite nicely by storing all the
inherited tables in the same page file, in which case primary key would
almost automatically span child relations and indexes on child relations
become partial indexes on the whole thing. There already is some support
for this present (namely tableoid system field stored in every tuple)

> BTW, can someone explain the model for inherited tables here? Is
> it really just as described _The Third Manifesto_, trivial syntactic
> sugar over the relational model?  

It is "just" syntactic sugar, just as VIEW is "just" syntactic sugar for
ON SELECT DO INSTEAD rules.

VIEWs are broken too, in the sense that you can't insert into them
without doing some hard work. 

But guess you would rather see VIEWs "fixed" to be insertable and
updatable, rather than ripped out "because the same thing and more" can
be done using RULEs ;)

> Or is it supposed to offer something
> that the relational model doesn't do very simply?

It is supposed to help programmers express structures that they would
describe as inheritance in an ERD diagramm in SQL without having to do
mental gymnastics each time they go from model to schema.

Having a shorter description is on one hand syntactic sugar, on the
other hand shorter.






08 Aug 2002 16:32:17 +0500
Hannu Krosing <hannu@tm.ee>
Re: Why is MySQL more chosen over PostgreSQL?
On 8 Aug 2002, Hannu Krosing wrote:

> For me they are _not_ two different models but rather one
> object-relational model.

Well, given that we've already demonstrated two rather different ways
of saying "the same thing," I think we have two models happening here.
However, feel free to explain your "object-relational model" in more
detail, including its advantages over the ordinary relational model.

> "Object Relational Dbms: Tracking the Next Great Wave" by Michael
> Stonebraker, Dorothy Moore (Contributor), Paul Brown
> ISBN: 1558604529
>
> I'm sure you find the requested arguments against Date there ;)

Unfortunately, this is a bit hard to order in Japan. So before I go
spend 8000 yen and wait a couple of weeks to get hold of a copy, I'd
be interested in just what is there that would dispute Date's points.
Looking through the index on Amazon.com, it appears that the book
devotes, at the very most, eight pages to table inheritance. What does
it say about it?

> The table inheritance _implementation_ in PG is in fact broken in
> several ways, most notably in  not enforcing uniqueness over all
> inherited tables and not inheriting other constraints.

Right. I'm glad we agree on that.

> But as you often like to emphasize, model and implementation _are_
> different things.

Ok. I won't object too much to the model, but let's get rid of this
severely broken implementation, unless there are some prospects
for fixing it. How's that?

BTW, can someone explain the model for inherited tables here? Is
it really just as described _The Third Manifesto_, trivial syntactic
sugar over the relational model? Or is it supposed to offer something
that the relational model doesn't do very simply? (Not to mention
correctly, in the case of postgres.)

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 8 Aug 2002 21:57:41 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
> > The table inheritance _implementation_ in PG is in fact broken in
> > several ways, most notably in  not enforcing uniqueness over all
> > inherited tables and not inheriting other constraints.
> 
> Right. I'm glad we agree on that.
> 
> > But as you often like to emphasize, model and implementation _are_
> > different things.
> 
> Ok. I won't object too much to the model, but let's get rid of this
> severely broken implementation, unless there are some prospects
> for fixing it. How's that?
> 

Wasn't that what was seemingly agreed on by pretty much everyone else on
this thread long ago?  The current implementation is problematic and
that it needs to be fixed.

As far as I can tell, the only difference of opinion here is, you seem
to hold zero value in table inheritance while others do see value.  At
this point in time, can't you guys agree to disagree and leave the
majority of this thread behind us?

> BTW, can someone explain the model for inherited tables here? Is
> it really just as described _The Third Manifesto_, trivial syntactic
> sugar over the relational model? Or is it supposed to offer something
> that the relational model doesn't do very simply? (Not to mention
> correctly, in the case of postgres.)

I would, however, enjoy seeing the theory portion continued as long as
it were kept at the theoretical level.  After all, I think everyone
agreed that Postgres' implementation is broken.  It doesn't seem like we
need to keep beating that horse.

Any takers?  ;)

Greg
08 Aug 2002 09:31:33 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Why is MySQL more chosen over PostgreSQL?
> BTW, can someone explain the model for inherited tables here? Is
> it really just as described _The Third Manifesto_, trivial syntactic
> sugar over the relational model? Or is it supposed to offer something
> that the relational model doesn't do very simply? (Not to mention
> correctly, in the case of postgres.)

No matter how much you grandstand, we're not getting rid of the
inheritance support.  It's not going to happen.  People are using it.

Chris



Thu, 8 Aug 2002 22:40:20 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Why is MySQL more chosen over PostgreSQL?
Greg,

Well put, I can't agree more.  I think even the horse has gotten up and
left.

I think what would be useful is to discuss the theory part.  When we go down
that path, we should all be referring to a consistent set of references.
There by we can have a common ground from which to talk.  In that spirit, I
would offer up the following references:

- Date has 3, however his most current work is dated 2000, The Third
Manifesto SECOND EDITION.
-There is the work done by Dr Kim, perhaps 'Modern Database Systems, The
Object Model, Interoperability, and Beyond'.
- Silberschatz, Korth, Sudarshan, A book I am sure we have all read,
Database System Concepts - Third Edition.

In any case, we should use the current editions of these books, not
something the author has reconsidered, re-written, and published again.

Jordan Henderson

----- Original Message -----
From: "Greg Copeland" <greg@CopelandConsulting.Net>
To: "Curt Sampson" <cjs@cynic.net>
Cc: "Hannu Krosing" <hannu@tm.ee>; "Don Baccus" <dhogaza@pacifier.com>;
"PostgresSQL Hackers Mailing List" <pgsql-hackers@postgresql.org>
Sent: Thursday, August 08, 2002 10:31 AM
Subject: Re: [HACKERS] Why is MySQL more chosen over PostgreSQL?




Thu, 8 Aug 2002 10:42:37 -0400
"Jordan Henderson" <jordan_henders@yahoo.com>
Re: Why is MySQL more chosen over PostgreSQL?
On 8 Aug 2002, Hannu Krosing wrote:

> The main difference (in the inheritance part) is that a relation does
> not have one fixed set of fields, but can have any additional fields
> added in inherited tables and still be part of to the base table as
> well.

This is trivial to do with a view.

> Actually I am not against ripping out the current broken implementation,
> but not before there has been a new, correct model available for at
> least two releses, so that people have had time to switch over.

So in other words, you want to let people use broken stuff, rather
than switch to another method, currently available, that has all
of the functionality but is not broken. I guess that's an opinion, all right.

> VIEWs are broken too, in the sense that you can't insert into them
> without doing some hard work.

Views are missing functionality. That is rather different from
making other tables lie about what they contain, essentially
destroying the requested data integrity.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 9 Aug 2002 10:38:38 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
Curt Sampson wrote:
> On 8 Aug 2002, Hannu Krosing wrote:
> 
> 
>>The main difference (in the inheritance part) is that a relation does
>>not have one fixed set of fields, but can have any additional fields
>>added in inherited tables and still be part of to the base table as
>>well.
> 
> 
> This is trivial to do with a view.

And views of this sort are trivial to do using PG's OO extensions.

I think I see a trend in this thread.  Why not give it up, dude?


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


Thu, 08 Aug 2002 18:46:05 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Why is MySQL more chosen over PostgreSQL?
On Thu, 8 Aug 2002, Don Baccus wrote:

> And views of this sort are trivial to do using PG's OO extensions.

So long as you don't mind them being broken, yeah. But hell, when someone
asks for a unique constraint, they probably don't really mean it, do they?
And what's wrong with multiple records with the same primary key? It's clear
to me now I've been working from the wrong direction; we should leave the OO
stuff and delete the relational stuff from the database instead.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 9 Aug 2002 10:56:02 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Why is MySQL more chosen over PostgreSQL?
Curt Sampson wrote:
> On Thu, 8 Aug 2002, Don Baccus wrote:
> 
> 
>>And views of this sort are trivial to do using PG's OO extensions.
> 
> 
> So long as you don't mind them being broken, yeah. But hell, when someone
> asks for a unique constraint, they probably don't really mean it, do they?

Good grief, we all agree that they're currently broken and need to be 
fixed someday.

Give it up.  You're being a boor.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


Thu, 08 Aug 2002 19:02:48 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Why is MySQL more chosen over PostgreSQL?
On Thu, 8 Aug 2002, Jordan Henderson wrote:

> I think what would be useful is to discuss the theory part.

As do I.

> - Date has 3, however his most current work is dated 2000, The Third
> Manifesto SECOND EDITION.

This is actually Date and Darwen.

I think we should also add Date's _An Introduction to Database Systems,
7th Edition_, as it covers some relational stuff in more detail than
than _The Third Manifesto_. For example, it investigates the details of
automatic view updatability, which came up during this discussion, and
which most books just completely cop out on. (For example, _Database
System Concepts_ just points out a couple of problems with view
updatability and says, "Because of problems such as these, modifications
are generally not permitted on view relations, except in limited
cases.")

> - Silberschatz, Korth, Sudarshan, A book I am sure we have all read,
> Database System Concepts - Third Edition.
> ...
> In any case, we should use the current editions of these books, not
> something the author has reconsidered, re-written, and published again.

In that case we ought to use the fourth edition of this book.

Here are some questions I'd like to see people answer or propose
answers to:

    1. What models of table inheritance have been proposed, and how
    do they differ?

    2. What models of table inheritance are actually implemented in
    currently available database systems?

    3. What are the advantages of describing something using table
    inheritance rather than an equivalant relational description?

    4. If you think table inheritance is "object oriented," why do
    you think so.

    5. How ought we to fix the table inheritance in postgres?

The last question comes up because, during the conversation up to this
point, we seem to have implicitly accepted that table inheritance is
an "object-oriented" way of doing things. Thinking further on this,
however, I've decided that it's not in fact object-oriented at all.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Mon, 12 Aug 2002 08:41:38 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Table Inheritance Discussion
Curt Sampson wrote:

> The last question comes up because, during the conversation up to this
> point, we seem to have implicitly accepted that table inheritance is
> an "object-oriented" way of doing things. Thinking further on this,
> however, I've decided that it's not in fact object-oriented at all.

It's just type extensibility, really.

As to why, again there's an efficiency argument, as I said earlier some 
joins can be avoided given PG's implementation of this feature:

dotlrn=# create table foo(i integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'foo_pkey' for table 'foo'
CREATE
dotlrn=# create table bar(j integer) inherits (foo);
CREATE
dotlrn=# explain select * from bar;
NOTICE:  QUERY PLAN:

Seq Scan on bar  (cost=0.00..20.00 rows=1000 width=8)

EXPLAIN
...

dotlrn=# create table foo(i integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'foo_pkey' for table 'foo'
CREATE
dotlrn=# create table bar(i integer references foo primary key, j integer);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'bar_pkey' for table 'bar'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY 
check(s)
CREATE
dotlrn=# create view foobar as select foo.*, bar.j from foo, bar;
CREATE

dotlrn=# explain select * from foobar;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..30020.00 rows=1000000 width=8)
   ->  Seq Scan on foo  (cost=0.00..20.00 rows=1000 width=4)
   ->  Seq Scan on bar  (cost=0.00..20.00 rows=1000 width=4)

EXPLAIN

There's also some error checking (using my inherited example):

dotlrn=# drop table foo;
ERROR:  Relation "bar" inherits from "foo"
dotlrn=#

Which doesn't exist in the view approach in PG at least (I'm unclear on 
standard SQL92 and of course this says nothing about the relational 
model in theory, just PG and perhaps SQL92 in practice).

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org


Sun, 11 Aug 2002 16:59:48 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Table Inheritance Discussion
On Sun, 11 Aug 2002, Don Baccus wrote:

> It's just type extensibility, really.

Yeah.

> As to why, again there's an efficiency argument, as I said earlier some
> joins can be avoided given PG's implementation of this feature:
> [TI and relational examples deleted]

What you gave is not the relational equivalant of the TI case as
implemented in postgres. Modeled correctly, you should be creating
a table for the child, and a view for the parent. Then you will
find that the relational definition uses or avoids joins exactly
where the TI definition does.

> There's also some error checking (using my inherited example):

The relational definition doesn't force the dependency, but as you
can delete and recreate the view at will without data loss, the
amount of safety is the same.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Mon, 12 Aug 2002 09:18:16 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Table Inheritance Discussion

> > > Yes, that's the whole point. If I have a constraint on a table, I think
> > > it should *never* be possible for that constraint to be violated. If a
> > > subtable should not have constraint the supertable has, it shouldn't
> > > inherit from the supertable.
> >
> > If you want that, you simply need to only create constraints that apply to
> > all tables in the hierarchy. Note that you *can* do this. It should imho be
> > the default behavior.
> 
> So what you're saying is that constraints shouldn't be inherited?

No. I even said that inheriting should be the default.
 
> > > To do otherwise breaks the relational model.
> >
> > That is probably a point of argument. Imho the inheritance feature
> > is something orthogonal to the relational model. It is something else, and
> > thus cannot break the relational model.
> 
> So then constraints must be inherited. The relational model, if I
> am not incorrect here, says that, given a table definition such as
> this:
> 
>     CREATE TABLE my_table (
> 	my_key int PRIMARY KEY,
> 	my_value text UNIQUE,
> 	my_other_value int CHECK (my_other_value > 0)
>     )

A local constraint should be made obvious from looking at the schema, 
a possible syntax (probably both ugly :-):
CHECK my_table ONLY (my_other_value > 0)
or
CHECK LOCAL (my_other_value > 0)

> 
> You will never, ever, when selecting from this table, have 
> returned to you
> 
>     1. two rows with the same value of my_key but different values
>     for the other columns,
> 
>     2. two rows with the same value of my_value but different values
>     for the other columns, or
> 
>     3. a row in which the value of my_other_value is not 
> greater than zero.
> 

Well, that is where I do not think this is flexible enough, and keep in mind 
that all triggers and rules would then also need such restrictions. 

> I would strongly object to that.

Regardless whether your objection is *strong* or not :-)
If you don't like the feature (to add a local constraint), don't use it.
(Remember you are talking about removing an implemented feature)

Andreas

Mon, 19 Aug 2002 16:30:46 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Inheritance 
On Mon, 19 Aug 2002, Zeugswetter Andreas SB SD wrote:

> > So what you're saying is that constraints shouldn't be inherited?
>
> No. I even said that inheriting should be the default.

Ah. So you think it should be possible not to inherit constraints.

> A local constraint should be made obvious from looking at the schema,

Ok, this now I could live with. Though I'm not sure that its
theoretically very defensible, or worth the effort. Other languages
that offer constraints, such as Eiffel (and soon Java), do not allow
constraints that are not inherited, as far as I know. Do you have some
counterexamples.

> Well, that is where I do not think this is flexible enough, and keep in mind
> that all triggers and rules would then also need such restrictions.

Yes, all triggers, rules, and everything else would have to be inherited.

> Regardless whether your objection is *strong* or not :-)
> If you don't like the feature (to add a local constraint), don't use it.
> (Remember you are talking about removing an implemented feature)

1. It's not exactly an implemented feature, it's an accident of an
incomplete implementation of inheritance done in a certain way.

2. Should we change the way we decide to implement inheritance,
perhaps to make fixing the current problems much easier, it might
be a lot of work to add this.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Mon, 19 Aug 2002 23:42:51 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance 
On Mon, 2002-08-19 at 15:42, Curt Sampson wrote:
> > A local constraint should be made obvious from looking at the schema,
> 
> Ok, this now I could live with. Though I'm not sure that its
> theoretically very defensible, or worth the effort. Other languages
> that offer constraints, such as Eiffel (and soon Java), do not allow
> constraints that are not inherited, as far as I know. Do you have some
> counterexamples.

In Eiffel, at least, I can say "invariant feature_x" and redefine
feature_x in a descendant class, thus effectively redefining the
constraint.  If we decide to inherit constraints unconditionally, the
application writer can achieve similar flexibility by moving the logic
of the constraint into a function whose behaviour depends on which table
it is used on.  This would put the burden on the application rather than
requiring additional syntax in PostgreSQL.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For every one that asketh receiveth; and he that 
      seeketh findeth; and to him that knocketh it shall be 
      opened."                         Luke 11:10 


19 Aug 2002 16:06:27 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: Inheritance
On Mon, 2002-08-19 at 09:42, Curt Sampson wrote:
> On Mon, 19 Aug 2002, Zeugswetter Andreas SB SD wrote:
> 
> > > So what you're saying is that constraints shouldn't be inherited?
> >
> > No. I even said that inheriting should be the default.
> 
> Ah. So you think it should be possible not to inherit constraints.

I've been silent for a bit because I wanted to kick the concept around
in my head.  After some thought, I say that I support children
inheriting constraints.  In a more abstract sense, we are really setting
conditions for all entities of a given type (class) which must be met to
classify as a defined type.  Again, in an abstract sense, if I say all
"candies" (type/class, candy) must have sugar (constraint), and I go on
to create a subclass of candy which I desire not to have sugar, then
there is a fundamental problem.  Either I incorrectly identified my
problem domain and didn't properly create my entities which address my
domain needs or what I'm trying to express really isn't a candy at all. 
In other words, it sounds like candy should of been a subclass of a more
abstract base entity.  Likewise, the newly desired class which doesn't
have sugar should also inherit from the newly created base class and not
be derived from candy at all.


> 
> > A local constraint should be made obvious from looking at the schema,
> 
> Ok, this now I could live with. Though I'm not sure that its
> theoretically very defensible, or worth the effort. Other languages
> that offer constraints, such as Eiffel (and soon Java), do not allow
> constraints that are not inherited, as far as I know. Do you have some
> counterexamples.

I tend to agree.  Constraints should be inherited.  See above.

> 
> > Well, that is where I do not think this is flexible enough, and keep in mind
> > that all triggers and rules would then also need such restrictions.
> 
> Yes, all triggers, rules, and everything else would have to be inherited.

Agreed.

> 
> > Regardless whether your objection is *strong* or not :-)
> > If you don't like the feature (to add a local constraint), don't use it.
> > (Remember you are talking about removing an implemented feature)
> 
> 1. It's not exactly an implemented feature, it's an accident of an
> incomplete implementation of inheritance done in a certain way.
> 
> 2. Should we change the way we decide to implement inheritance,
> perhaps to make fixing the current problems much easier, it might
> be a lot of work to add this.
> 

I'm still trying to figure out if subclasses should be allowed to have
localized constraints.  I tend to think yes even though it's certainly
possible to create seemingly illogical/incompatible/conflicting
constraints with parent classes.  Then again, my gut feeling is, that's
more and an architectural/design issue rather than a fundamental issue
with the concept.


--Greg Copeland
19 Aug 2002 10:10:57 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Inheritance
The August draft of the SQL:200n standard (9075-2 Foundation) says in
Section 4.17.2: "Every table constraint specified for base table T is
implicitly a constraint on every subtable of T, by virtue of the fact
that every row in a subtable is considered to have a corresponding
superrow in every one of its supertables."

Peter Gulutzan
Co-Author, SQL-99 Complete, Really
Co-Author, SQL Performance Tuning

28 Aug 2002 06:23:46 -0700
71022.733@compuserve.com (Peter Gulutzan)
Re: Inheritance
Peter Gulutzan wrote:
> The August draft of the SQL:200n standard (9075-2 Foundation) says in
> Section 4.17.2: "Every table constraint specified for base table T is
> implicitly a constraint on every subtable of T, by virtue of the fact
> that every row in a subtable is considered to have a corresponding
> superrow in every one of its supertables."

Yep, this is where we are stuck;  having an index span multiple tables
in some way.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 3 Sep 2002 12:36:37 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Inheritance
On Tue, 3 Sep 2002, Bruce Momjian wrote:

> Yep, this is where we are stuck;  having an index span multiple tables
> in some way.

Or implementing it by keeping all data in the table in which it
was declared. (I.e., supertable holds all rows; subtable holds
only the primary key and those columns of the row that are not
in the supertable.)

 From looking at the various discussions of this in books, and what
it appears to me that the SQL standard says, it seems that their
overall vision of table inheritance is to be consistent with the
implementation that I described above.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 5 Sep 2002 10:57:05 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance
On 5 Sep 2002, Hannu Krosing wrote:

> On Thu, 2002-09-05 at 03:57, Curt Sampson wrote:
>
> > Or implementing it by keeping all data in the table in which it
> > was declared. (I.e., supertable holds all rows; subtable holds
> > only the primary key and those columns of the row that are not
> > in the supertable.)
>
> How would you do it for _multiple_ inheritance ?

Exactly the same way. Each column resides in only one physical table,
so you need only find the table it resides in, and do the insert there.
I'll be happy to provide an example if this is not clear.

> 1) the way you describe (parent holding common columns + child tables
> for added child columns), which makes it easy to define constraints but
> hard to do inserts/updates/deletes on inherited tables

I wouldn't say it makes it "hard" to do inserts, updates and deletes.
Postgres already has pretty near all of the code it needs to support
these updates, because these are the semantic equivalant of the separate
actions applied to the separate tables within one transaction.

> 2) the postgresql way (a new table for each child), which makes it hard
> to define constraints but easy to do inserts/updates/deletes.

I agree that making constraints work in this model is very difficult and
a lot of work.

> This way it could probably be done even more effectively than you
> describe by:
>
> 1) keeping _all_ (not only the inherited columns)  the data for
> inheritance hierarchy in the same physical file.

You appear to have delved into a different database layer than one
I'm looking at, here. I was examining storage on the table level,
which is unrelated to files. (E.g., postgres sometimes stores a
table in one file, sometimes in more than one. MS SQL Server stores
many tables in one file. It doesn't matter which approach is used when
discussing the two inheritance implementation options above.)

> 4) update/delete of all child tables are trivial as they are actually
> done in the same table and not using joins

Or are you talking about storing all of the columns in a single
table? That's a possibility, but wouldn't it be costly to update
the entire table every time you add a new child table? And table
scans on child tables would certainly be more costly if you had
many of them, becuase the effective row width would be much wider.
But it might be worth thinking about.

> It seems that single inheritance avoids other conceptual problems, like
> what to do with primary keys when inheriting from two tables that have
> them.

I don't see where there's a conceptual problem here, either. With
multiple inheritance you can simply demote both keys to candidate
keys, and continue on as normal. (The only difference between a
primary key and a candidate key is that you can leave out the column
names when declaring foreign keys in another table.)

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 5 Sep 2002 16:28:16 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance
On Thu, 2002-09-05 at 03:57, Curt Sampson wrote:
> On Tue, 3 Sep 2002, Bruce Momjian wrote:
> 
> > Yep, this is where we are stuck;  having an index span multiple tables
> > in some way.
> 
> Or implementing it by keeping all data in the table in which it
> was declared. (I.e., supertable holds all rows; subtable holds
> only the primary key and those columns of the row that are not
> in the supertable.)

How would you do it for _multiple_ inheritance ?

When implementing it on top of standard relational model you have more
or less two ways to slice the problem 

1) the way you describe (parent holding common columns + child tables
for added child columns), which makes it easy to define constraints but
hard to do inserts/updates/deletes on inherited tables

2) the postgresql way (a new table for each child), which makes it hard
to define constraints but easy to do inserts/updates/deletes.

> From looking at the various discussions of this in books, and what
> it appears to me that the SQL standard says, it seems that their
> overall vision of table inheritance is to be consistent with the
> implementation that I described above.

Yes. The SQL99 standard specifies only _single_ inheritance for tables +
LIKE in column definition part, making the model somewhat similar to
Java's (single inheritance + interfaces).

This way it could probably be done even more effectively than you
describe by:

1) keeping _all_ (not only the inherited columns)  the data for
inheritance hierarchy in the same physical file.

2) having partial indexes (involving tableoid=thiskindoftable) for
possible speeding up of SELECT .. ONLY queries.

3) no changes to (unique) indexes - they still reference simple TID's
without additional table part.

4) update/delete of all child tables are trivial as they are actually
done in the same table and not using joins


It seems that single inheritance avoids other conceptual problems, like
what to do with primary keys when inheriting from two tables that have
them.

--------------------
Hannu



05 Sep 2002 10:05:09 +0200
Hannu Krosing <hannu@tm.ee>
Re: Inheritance
On 5 Sep 2002, Hannu Krosing wrote:

> What I meant was that it is relatively more costly to update several
> "physical" tables than updating one .

Oh, I see. Not that this is that big a deal, I think. Given that
it doesn't work correctly at the moment, making it work fast is a
definite second priority, I would think.

Once it's working right, one can always replace the internals with
something else that does the same job but is more efficient.

> > I agree that making constraints work in this model is very difficult and
> > a lot of work.
>
> But again this is not _conceptually_ hard, just hard to implement
> efficiently.

No, it's conceptually hard. Not all constraints are implemented with
just a unique index you know. And changing a constraint means you have
to check all the child tables, etc. etc. It's difficult just to track
down down all the things you have to try to preserve. Not to mention,
there's always the question of what happens to triggers and suchlike
when handed a tuple with extra columns from what it expects, and having
it modify the insert into a different table.

The beauty of storing all supertable columns in the supertable itself is
that the behaviour is automatically correct.

> What I was actually trying to describe was that the tuple format would
> be what it is currently, just stored in the same table with parent.

So what you're saying is that each tuple in the table would have a
format appropriate for its "subtype," and the table would be full of
tuples of varying types? At first blush, that seems like a reasonable
approach, if it can be done.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 5 Sep 2002 17:52:08 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance
I have a question about inheritance:

You have 2 tables: Programmer and employee. Programmer inherits employee. You 
put in a generic employee record for someone, but then she becomes a 
programmer. What do you do? (I borrowed this example from a book by C.J. 
Date, who posed this question). Do you DELETE then INSERT? Something seems 
wrong with that somehow. Are the postgres developers agreed upon how that 
situation should be handled? What about the database users, and their 
expectations of the behavior? 

I am not advocating that we remove inheritence (I say this because this topic 
has generated some significant discussion about that). However, I will stick 
to the well-defined relational model until I see something useful from the 
inheritance system that is as well-defined. I agree it saves a few keystrokes 
(and can help organize things for you, as do objects in a programming 
language), but mind is more at peace when I am actually sure of what's 
happening. I can always throw more rules/views/triggers at the situation 
until I have a nice set of things to work with in the application.

Or, I suppose, if someone shows me something that I can't do in the relational 
model, but can with inheritance, I might be convinced otherwise.

Regards,
	Jeff Davis



On Thursday 05 September 2002 01:05 am, Hannu Krosing wrote:
> On Thu, 2002-09-05 at 03:57, Curt Sampson wrote:
> > On Tue, 3 Sep 2002, Bruce Momjian wrote:
> > > Yep, this is where we are stuck;  having an index span multiple tables
> > > in some way.
> >
> > Or implementing it by keeping all data in the table in which it
> > was declared. (I.e., supertable holds all rows; subtable holds
> > only the primary key and those columns of the row that are not
> > in the supertable.)
>
> How would you do it for _multiple_ inheritance ?
>
> When implementing it on top of standard relational model you have more
> or less two ways to slice the problem
>
> 1) the way you describe (parent holding common columns + child tables
> for added child columns), which makes it easy to define constraints but
> hard to do inserts/updates/deletes on inherited tables
>
> 2) the postgresql way (a new table for each child), which makes it hard
> to define constraints but easy to do inserts/updates/deletes.
>
> > From looking at the various discussions of this in books, and what
> > it appears to me that the SQL standard says, it seems that their
> > overall vision of table inheritance is to be consistent with the
> > implementation that I described above.
>
> Yes. The SQL99 standard specifies only _single_ inheritance for tables +
> LIKE in column definition part, making the model somewhat similar to
> Java's (single inheritance + interfaces).
>
> This way it could probably be done even more effectively than you
> describe by:
>
> 1) keeping _all_ (not only the inherited columns)  the data for
> inheritance hierarchy in the same physical file.
>
> 2) having partial indexes (involving tableoid=thiskindoftable) for
> possible speeding up of SELECT .. ONLY queries.
>
> 3) no changes to (unique) indexes - they still reference simple TID's
> without additional table part.
>
> 4) update/delete of all child tables are trivial as they are actually
> done in the same table and not using joins
>
>
> It seems that single inheritance avoids other conceptual problems, like
> what to do with primary keys when inheriting from two tables that have
> them.
>
> --------------------
> Hannu
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


Thu, 5 Sep 2002 02:04:23 -0700
Jeff Davis <list-pgsql-hackers@empires.org>
Re: Inheritance
On Thu, 2002-09-05 at 09:28, Curt Sampson wrote:
> On 5 Sep 2002, Hannu Krosing wrote:
> 
> > On Thu, 2002-09-05 at 03:57, Curt Sampson wrote:
> >
> > > Or implementing it by keeping all data in the table in which it
> > > was declared. (I.e., supertable holds all rows; subtable holds
> > > only the primary key and those columns of the row that are not
> > > in the supertable.)
> >
> > How would you do it for _multiple_ inheritance ?
> 
> Exactly the same way. Each column resides in only one physical table,
> so you need only find the table it resides in, and do the insert there.
> I'll be happy to provide an example if this is not clear.
> 
> > 1) the way you describe (parent holding common columns + child tables
> > for added child columns), which makes it easy to define constraints but
> > hard to do inserts/updates/deletes on inherited tables
> 
> I wouldn't say it makes it "hard" to do inserts, updates and deletes.
> Postgres already has pretty near all of the code it needs to support
> these updates, because these are the semantic equivalant of the separate
> actions applied to the separate tables within one transaction.

What I meant was that it is relatively more costly to update several
"physical" tables than updating one .

> > 2) the postgresql way (a new table for each child), which makes it hard
> > to define constraints but easy to do inserts/updates/deletes.
> 
> I agree that making constraints work in this model is very difficult and
> a lot of work.

But again this is not _conceptually_ hard, just hard to implement
efficiently.

> > This way it could probably be done even more effectively than you
> > describe by:
> >
> > 1) keeping _all_ (not only the inherited columns)  the data for
> > inheritance hierarchy in the same physical file.
> 
> You appear to have delved into a different database layer than one
> I'm looking at, here.

probably. I was describing to a way to efficiently implement single
inheritance. 

The layer was somewhere between physical files and logical tables, i.e.
above splitting stuff into main/toast and also above splitting big files
to 1Gb chunks, but below logical tables, which are (or are not when
omitting ONLY ;) still separate logically.

Perhaps it could be named "logical file".

> I was examining storage on the table level, which is unrelated to files.

> (E.g., postgres sometimes stores a table in one file, sometimes in more
> than one. MS SQL Server stores many tables in one file.
> It doesn't matter which approach is used when
> discussing the two inheritance implementation options above.)

It does not matter in case you are assuming that the storage model can't
be changed. The trick with inherited tables is that in some sense they
are the same table and in another sense they are separate tables.

> > 4) update/delete of all child tables are trivial as they are actually
> > done in the same table and not using joins
> 
> Or are you talking about storing all of the columns in a single
> table? That's a possibility, but wouldn't it be costly to update
> the entire table every time you add a new child table?

You should not need it, as the storage for existing tuples does not
change - even now you can do ADD COLUMN without touching existing
tuples.

> And table
> scans on child tables would certainly be more costly if you had
> many of them, becuase the effective row width would be much wider.

It would not be noticably wider (only 1 bit/column) even if I did
propose storing all columns.

What I was actually trying to describe was that the tuple format would
be what it is currently, just stored in the same table with parent.

> But it might be worth thinking about.
> 
> > It seems that single inheritance avoids other conceptual problems, like
> > what to do with primary keys when inheriting from two tables that have
> > them.
> 
> I don't see where there's a conceptual problem here, either. With
> multiple inheritance you can simply demote both keys to candidate
> keys, and continue on as normal. (The only difference between a
> primary key and a candidate key is that you can leave out the column
> names when declaring foreign keys in another table.)

That's one possibility. The other would be to keep the one from the
first table as primary and demote onlly the other primary keys.

With single inheritance you don't even have to think about it.

-----------------
Hannu




05 Sep 2002 11:15:27 +0200
Hannu Krosing <hannu@tm.ee>
Re: Inheritance
On Thu, 5 Sep 2002, Jeff Davis wrote:

> You have 2 tables: Programmer and employee. Programmer inherits employee. You
> put in a generic employee record for someone, but then she becomes a
> programmer. What do you do? (I borrowed this example from a book by C.J.
> Date, who posed this question). Do you DELETE then INSERT? Something seems
> wrong with that somehow.

This is not so wrong. If you think about it, you have the same
problem in most object-oriented programming languages: a person
object can't generally easily become a subclass of itself after
being created.

This is a case, I would say, where you simply don't want to use
inheritance. A person has-a job, not is-a job.

> What about the database users, and their expectations of the behavior?

Nobody really knows; table inheritance in databases is not well-defined.
(Though perhaps the latest SQL spec. changes that.)

> However, I will stick to the well-defined relational model until I see
> something useful from the inheritance system that is as well-defined.

Amen! :-)

> Or, I suppose, if someone shows me something that I can't do in the
> relational model, but can with inheritance, I might be convinced
> otherwise.

I think that most people are at this point agreed that table
inheritance, at least as currently implemented in any known system,
doesn't offer anything that can't easily be done relationally.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 5 Sep 2002 18:16:48 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance
On 5 Sep 2002, Hannu Krosing wrote:

> > Oh, I see. Not that this is that big a deal, I think. Given that
> > it doesn't work correctly at the moment, making it work fast is a
> > definite second priority, I would think.
>
> But choosing an implementation that _can_be_ made to work fast is not.

I would say it definitely is. A correctly working implementation
can be replaced. An incorrectly working implementation destroys
data integrety.

Which is more important for PostgreSQL? Speed or maintaining data
integrity?

> > Not to mention,
> > there's always the question of what happens to triggers and suchlike
> > when handed a tuple with extra columns from what it expects, and having
> > it modify the insert into a different table.
>
> IMHO that the trigger should not be aware of underlying implementation -
> so it needs not worry about modifying the insert into a different table.

I agree.

> > The beauty of storing all supertable columns in the supertable itself is
> > that the behaviour is automatically correct.
>
> But "automatically correct" may not be what you want ;)
>
> What about trigger that generates a cached printname using function
> printname(row) that is different for each table - here you definitely do
> not want to run the function defined for base table for anything
> inherited.

Right. But that will be "automatically correct" when you store all
base data in the base table. It's when you start storing those data
in other tables that the trigger can get confused.

Or are you saying that when I insert a row into "just" a child
table, the trigger shouldn't be invoked on the "parent table"
portion of that insert? If so, I'd strongly disagree. If that
trigger is acting as an integrety constraint on the base table,
you might destroy the table's integrity.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 5 Sep 2002 18:34:42 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance
On Thu, 2002-09-05 at 10:52, Curt Sampson wrote:
> On 5 Sep 2002, Hannu Krosing wrote:
> 
> > What I meant was that it is relatively more costly to update several
> > "physical" tables than updating one .
> 
> Oh, I see. Not that this is that big a deal, I think. Given that
> it doesn't work correctly at the moment, making it work fast is a
> definite second priority, I would think.

But choosing an implementation that _can_be_ made to work fast is not.

> Once it's working right, one can always replace the internals with
> something else that does the same job but is more efficient.

I still think that choosing the right implementation can also help in
making it work right.

> > > I agree that making constraints work in this model is very difficult and
> > > a lot of work.
> >
> > But again this is not _conceptually_ hard, just hard to implement
> > efficiently.
> 
> No, it's conceptually hard. Not all constraints are implemented with
> just a unique index you know. And changing a constraint means you have
> to check all the child tables, etc. etc. It's difficult just to track
> down down all the things you have to try to preserve.

It may be a lot of work, but not _conceptually_ hard. Conceptually you
have to do the same thing as for a single table, but just for all
inherited tables.

> Not to mention,
> there's always the question of what happens to triggers and suchlike
> when handed a tuple with extra columns from what it expects, and having
> it modify the insert into a different table.

IMHO that the trigger should not be aware of underlying implementation -
so it needs not worry about modifying the insert into a different table.

> The beauty of storing all supertable columns in the supertable itself is
> that the behaviour is automatically correct.

But "automatically correct" may not be what you want ;)

What about trigger that generates a cached printname using function
printname(row) that is different for each table - here you definitely do
not want to run the function defined for base table for anything
inherited.

> > What I was actually trying to describe was that the tuple format would
> > be what it is currently, just stored in the same table with parent.
> 
> So what you're saying is that each tuple in the table would have a
> format appropriate for its "subtype," and the table would be full of
> tuples of varying types? At first blush, that seems like a reasonable
> approach, if it can be done.

At least it makes some parts easier ;)

----------------
Hannu









05 Sep 2002 12:23:54 +0200
Hannu Krosing <hannu@tm.ee>
Re: Inheritance
>
> This is not so wrong. If you think about it, you have the same
> problem in most object-oriented programming languages: a person
> object can't generally easily become a subclass of itself after
> being created.
>
> This is a case, I would say, where you simply don't want to use
> inheritance. A person has-a job, not is-a job.
>

But a person is-a employee (allow me to momentarily step aside from the rules 
of english grammer, if you would), and a person is-a programmer. That's why I 
didn't call my table "job" :)  [1]

I don't like the way some OO programming languages handle objects, if they 
mean to say you can't change an object's type without performing a logical 
data copy to a new object. If you don't use some kind of extra layer of 
abstraction in C, you will end up with that problem: you'd need to copy all 
that RAM over to change from one struct to another. Most people would rather 
take that RAM copying hit than all the hits for allowing "room to expand" (at 
least in some applications). However, postgres needs to provide that "room to 
expand" for each tuple anyway, so to go through the same copying seems bad 
(especially since we're no longer just talking RAM). 

Take as an example python... it's easy to emulate other objects: just assign 
to the attribute, even if it's not there yet, it'll add the attribute. Same 
with python, it's providing room to expand for it's objects already, so why 
do all the copying? Now compare with Java, and see why you'd be annoyed. It 
has the facilities to change the objects all around, but you can't do it.

Even if you disregard all implementation details, and assume that the database 
is intelligent enough to not redundantly write data (and if you could name 
one such database, I would like to know), you're still doing something that 
doesn't logically make sense: you're deleting and inserting atomically, when 
the more obvious logical path is to expand on the data you already carry 
about an entity.

I like entities to be mutable, at least as far as makes sense to an 
application. Try telling an employee that as part of a promotion, they're 
going to be fired, lose their workstation, then be re-hired, and get a new 
workstation; I bet he'd have an interesting expression on his face (hey, at 
least postgres guarantees the "A" in ACID, or else bad things could happen to 
that poor guy :)

Thanks for responding, and I agreed with everything else you said. As you 
might have guessed, I don't much like "most object-oriented languages" if 
that's what they're going to try to tell me I have to do. Python works 
nicely, however :)

Regards,
	Jeff Davis

[1] Come to think of it, the JOIN operator seems to, at least on a first 
thought, represent the "has-a" relationship you describe. You could have the 
tuples "manager" and "programmer" in the table "job" and join with a "people" 
table. Don't ask about inheritance yet for this model, I'm still thinking 
about that one (does "has-a" even have an analogue to inheriteance?). Send me 
your thoughts about this, if you should have any.

Thu, 5 Sep 2002 03:29:43 -0700
Jeff Davis <list-pgsql-hackers@empires.org>
Re: Inheritance
On Thu, 2002-09-05 at 11:34, Curt Sampson wrote:
> On 5 Sep 2002, Hannu Krosing wrote:
> 
> > > Oh, I see. Not that this is that big a deal, I think. Given that
> > > it doesn't work correctly at the moment, making it work fast is a
> > > definite second priority, I would think.
> >
> > But choosing an implementation that _can_be_ made to work fast is not.
> 
> I would say it definitely is. A correctly working implementation
> can be replaced. An incorrectly working implementation destroys
> data integrety.
> 
> Which is more important for PostgreSQL? Speed or maintaining data
> integrity?

Both of course. The lack of one often makes the other unusable.

But as MySQL experience suggest, often people select speed over data
integrity. OTOH sometimes you happily accept a 10sec delay in updates to
have data integrity (like when doing a money transfer over internet;)

> > > Not to mention,
> > > there's always the question of what happens to triggers and suchlike
> > > when handed a tuple with extra columns from what it expects, and having
> > > it modify the insert into a different table.
> >
> > IMHO that the trigger should not be aware of underlying implementation -
> > so it needs not worry about modifying the insert into a different table.
> 
> I agree.
> 
> > > The beauty of storing all supertable columns in the supertable itself is
> > > that the behaviour is automatically correct.
> >
> > But "automatically correct" may not be what you want ;)
> >
> > What about trigger that generates a cached printname using function
> > printname(row) that is different for each table - here you definitely do
> > not want to run the function defined for base table for anything
> > inherited.
> 
> Right. But that will be "automatically correct" when you store all
> base data in the base table. It's when you start storing those data
> in other tables that the trigger can get confused.
> 
> Or are you saying that when I insert a row into "just" a child
> table, the trigger shouldn't be invoked on the "parent table"
> portion of that insert? If so, I'd strongly disagree.

Conceptually there are no "portions" of table - the trigger is invoked
on one _tuple_ exactly (pg has only row-level triggers), and each tuple
belongs to only one table regardless how it is implemented internally.

> If that
> trigger is acting as an integrety constraint on the base table,
> you might destroy the table's integrity.

What I try to say is that you should have the same freedom with triggers
that you have with select/insert/update/delete - you must be able to
choose if the trigger is on the parent table ONLY or on parent and all
children. 

And you should be able to override a trigger for child table even if it
is defined on parent as applying to all children - I guess that
overriding by trigger _name_ would be what most people expect.

Suppose you have a table CITIZEN with table-level constraint IS_GOOD
which is defined as kills_not_others(CITIZEN). and there is table
CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
(...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD so
you must either be able to override the trigger for that table (and its
children) or make sure that the functions used are dynamically mached to
the actual tuple type (header in Relational Model parlance) so that
kills_not_others(MILITARY) will be used, which presents the system
MILITARYs view of the being good ;)

What I'm after here is dynamic (and automatic) row level dispach of the
right function based on row type - so that for rows in CITIZEN or
CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but
for rows in MILITAY the kills_not_others(MILITARY) is used.

---------
  Hannu



05 Sep 2002 15:15:06 +0200
Hannu Krosing <hannu@tm.ee>
Re: Inheritance
On Thu, 2002-09-05 at 12:29, Jeff Davis wrote:
> >
> > This is not so wrong. If you think about it, you have the same
> > problem in most object-oriented programming languages: a person
> > object can't generally easily become a subclass of itself after
> > being created.
> >
> > This is a case, I would say, where you simply don't want to use
> > inheritance. A person has-a job, not is-a job.
> >
> 
> But a person is-a employee (allow me to momentarily step aside from the rules 
> of english grammer, if you would), and a person is-a programmer. That's why I 
> didn't call my table "job" :)  [1]
> 
> I don't like the way some OO programming languages handle objects, if they 
> mean to say you can't change an object's type without performing a logical 
> data copy to a new object. If you don't use some kind of extra layer of 
> abstraction in C, you will end up with that problem: you'd need to copy all 
> that RAM over to change from one struct to another. Most people would rather 
> take that RAM copying hit than all the hits for allowing "room to expand" (at 
> least in some applications). However, postgres needs to provide that "room to 
> expand" for each tuple anyway, so to go through the same copying seems bad 
> (especially since we're no longer just talking RAM). 

I would like to have UPDATEs both up and down the inheritance hierarchy,
so that when I have hierarchy

OBJECT(id serial primary key)
 + HUMAN(name text,age int)
   + EMPLOYEE(salary numeric)
     + ENGINEER(workstation computer)
     + PHB(laptop computer)

and ENGINEER named Bob

I could do

UPDATE ENGINEER 
    TO PHB
   SET salary = salary * 2 + age * 1000,
       laptop.disksize = max(laptop.disksize ,
                             workstation.disksize + 1000000)
 WHERE name='Bob'
;

to promote Bob from an engineer to phb, give him a salary rise and a
laptop with default configuration ensuring big enough disk to keep all
his old files, but still keep all FK related records.

> Take as an example python... it's easy to emulate other objects: just assign 
> to the attribute, even if it's not there yet, it'll add the attribute. Same 
> with python, it's providing room to expand for it's objects already, so why 
> do all the copying?

that's unless you use the new-style objects and __slots__

>>> class myobj(object):
...     __slots__ = ['a','b']
...    
>>> M = myobj()
>>> M.a =1
>>> M.c =1
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
AttributeError: 'myobj' object has no attribute 'c'
>>> 

> Same with python, it's providing room to expand for it's objects already,
> so why do all the copying?


> [1] Come to think of it, the JOIN operator seems to, at least on a first 
> thought, represent the "has-a" relationship you describe. You could have the 
> tuples "manager" and "programmer" in the table "job" and join with a "people" 
> table. Don't ask about inheritance yet for this model, I'm still thinking 
> about that one (does "has-a" even have an analogue to inheriteance?).

Not in inheritance, but in OO world attributes are used to express has-a
relations. So

 bob = people(name='Bob')
 bob.job = job('Manager')

makes an has-a relation between Bob and his job in python

BTW, good programming guidelines in python tell you not to test if bob
is-a something but rather test if the interface for something exists -
to see if you can iterate over bob you do not test if bob is a sequence
but just try it:

try:
    for idea in bob:
        examine(idea)
except TypeError:
    print 'Failed to iterate over %s %s !' % (bob,job.name, bob.name)

---------------
Hannu






05 Sep 2002 16:00:23 +0200
Hannu Krosing <hannu@tm.ee>
Re: Inheritance
I really like Hannu's idea of storing an entire (single-inheritance)
hierarchy in a single file.

I guess the question we need to ask ourselves is if we're prepared to
abandon support of multiple inheritance.  Personally I am, but...

			regards, tom lane

Thu, 05 Sep 2002 10:23:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Inheritance 
On Thu, 2002-09-05 at 19:23, Tom Lane wrote:
> I really like Hannu's idea of storing an entire (single-inheritance)
> hierarchy in a single file.
> 
> I guess the question we need to ask ourselves is if we're prepared to
> abandon support of multiple inheritance.  Personally I am, but...

So am I, but I think we should move in stages -

1) first implement the SQL99 standard 
   CREATE TABLE mytable() UNDER parenttable ;
   using the above idea and make it work right vs constraints,
   triggers, functions, etc.

   This should include the ability to include other table structures
   using LIKE :

   CREATE TABLE engine(...);
   CREATE TABLE vehicule(...);
   CREATE TABLE car (
      model text,
      wheels  wheel[],
      LIKE engine,
   ) UNDER vehicule;

   which could then hopefully be used for migrating most code of form

   CREATE TABLE car (
      model text primary key,
      wheels  wheel[]
   ) INHERITS (vehicule, engine);

   it would be nice (maybe even neccessary) to keep the current
   functionality that columns introduced by LIKE are automatically
   added/renamed/deleted when LIKE's base table changes.

2) when it is working announce non-SQL99-standard-and-broken INHERITS
   to be deprecated and removed in future.

3) give people time for some releases to move over to UNDER + LIKE .
   Or if someone comes up with bright ideas/impementations for fixing
   multiple inheritance, then un-deprecate and keep it.

4) else try to remove INHERITS.

5) if too many people object, goto 3)  ;)

-------------------
Hannu



05 Sep 2002 20:10:59 +0500
Hannu Krosing <hannu@tm.ee>
Re: Inheritance
On Thu, Sep 05, 2002 at 10:23:02AM -0400, Tom Lane wrote:
> I really like Hannu's idea of storing an entire (single-inheritance)
> hierarchy in a single file.

Wouldn't this require solving the ALTER TABLE ADD COLUMN (to parent)
column ordering problem? 

> I guess the question we need to ask ourselves is if we're prepared to
> abandon support of multiple inheritance.  Personally I am, but...

No opinion - I've not used the inheritance much, since I'm not willing to
give up referential integrity.

Ross

Thu, 5 Sep 2002 12:02:42 -0500
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Inheritance
On Fri, 2002-09-06 at 03:19, Greg Copeland wrote:
> On Thu, 2002-09-05 at 08:15, Hannu Krosing wrote:
> > On Thu, 2002-09-05 at 11:34, Curt Sampson wrote:
> > > On 5 Sep 2002, Hannu Krosing wrote:
> 
> > > If that
> > > trigger is acting as an integrety constraint on the base table,
> > > you might destroy the table's integrity.
> > 
> > What I try to say is that you should have the same freedom with triggers
> > that you have with select/insert/update/delete - you must be able to
> > choose if the trigger is on the parent table ONLY or on parent and all
> > children. 
> 
> Sounds like a mechanism to make the distinction between virtual (child
> can override parent) and non-virtual (child is constrained by the
> parent) constraints are needed.
> 
> After all, there are two basic needs for constraints.  One is for
> relational integrity and the other is business rule integrity.  That is,
> one seeks to ensure that the database makes sense in respect to the data
> model (a shoe is a product) while the other is to enforce business rules
> (products are never free).  Seems like the DBA should be able to dictate
> which domain his constraint falls into in some manner.
>
> > And you should be able to override a trigger for child table even if
it
> > is defined on parent as applying to all children - I guess that
> > overriding by trigger _name_ would be what most people expect.
> > 
> 
> That's the reason I used virtual and non-virtual above.  If we think
> using C++ idioms, the child is stuck with it if it's deemed
> non-virtual.  Generally speaking, if someone designed something with
> that expectation in mind, there's probably a good reason for it.  In
> this case, we could assume that such non-virtual constraints would be to
> help ensure proper RI.  Something that otherwise, IMO, would be tossed
> out with the bath water.

I agree to this.

What I described (making overriding decision solely in child) is
probably a bad idea.

> > What I'm after here is dynamic (and automatic) row level dispach of the
> > right function based on row type - so that for rows in CITIZEN or
> > CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but
> > for rows in MILITAY the kills_not_others(MILITARY) is used.
> 
> I think we're touching on some form of RTTI information here.  That is,
> triggers and even functions may need to be able to dynamically determine
> the row type that is actively being worked on.

Should be easy if the row comes directly from a table : just use
tableoid column.

> If we're on the same page, I think that seemingly makes a lot of sense.
> 
> What about the concept of columns being public or private?  That is,
> certain columns may not be inherited by a child?  Any thought to such a
> concept?  Perhaps different types of table inheritance can be considered
> in our model...has-a, is-a, etc...

I can't fit this in my mental model of table inheritance for two reasons

1) all parent table columns must be present in child

2) granting some right to parent should automatically allow selecting
from children

both are required for select/insert/update/delete to work on table and
its children (i.e. without ONLY)


But maybe i just need to think more about it ;)

------------------
Hannu












06 Sep 2002 01:51:51 +0500
Hannu Krosing <hannu@tm.ee>
Re: Inheritance
On Thu, 2002-09-05 at 08:15, Hannu Krosing wrote:
> On Thu, 2002-09-05 at 11:34, Curt Sampson wrote:
> > On 5 Sep 2002, Hannu Krosing wrote:

> > If that
> > trigger is acting as an integrety constraint on the base table,
> > you might destroy the table's integrity.
> 
> What I try to say is that you should have the same freedom with triggers
> that you have with select/insert/update/delete - you must be able to
> choose if the trigger is on the parent table ONLY or on parent and all
> children. 

Sounds like a mechanism to make the distinction between virtual (child
can override parent) and non-virtual (child is constrained by the
parent) constraints are needed.

After all, there are two basic needs for constraints.  One is for
relational integrity and the other is business rule integrity.  That is,
one seeks to ensure that the database makes sense in respect to the data
model (a shoe is a product) while the other is to enforce business rules
(products are never free).  Seems like the DBA should be able to dictate
which domain his constraint falls into in some manner.

> 
> And you should be able to override a trigger for child table even if it
> is defined on parent as applying to all children - I guess that
> overriding by trigger _name_ would be what most people expect.
> 

That's the reason I used virtual and non-virtual above.  If we think
using C++ idioms, the child is stuck with it if it's deemed
non-virtual.  Generally speaking, if someone designed something with
that expectation in mind, there's probably a good reason for it.  In
this case, we could assume that such non-virtual constraints would be to
help ensure proper RI.  Something that otherwise, IMO, would be tossed
out with the bath water.

> What I'm after here is dynamic (and automatic) row level dispach of the
> right function based on row type - so that for rows in CITIZEN or
> CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but
> for rows in MILITAY the kills_not_others(MILITARY) is used.

I think we're touching on some form of RTTI information here.  That is,
triggers and even functions may need to be able to dynamically determine
the row type that is actively being worked on.

If we're on the same page, I think that seemingly makes a lot of sense.

What about the concept of columns being public or private?  That is,
certain columns may not be inherited by a child?  Any thought to such a
concept?  Perhaps different types of table inheritance can be considered
in our model...has-a, is-a, etc...


Regards,

	Greg Copeland
05 Sep 2002 17:19:32 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Inheritance
On 5 Sep 2002, Hannu Krosing wrote:

> Suppose you have a table CITIZEN with table-level constraint IS_GOOD
> which is defined as kills_not_others(CITIZEN). and there is table
> CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
> (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD....

This I very much disagree with.

In most object-oriented languages (Eiffel being a notable exception, IIRC),
you can't specify constraints on objects. But in a relational database,
you can specify constraints on tables, and it should *never* *ever* be
possible to violate those constraints, or the constraints are pointless.

So if I have a constraint that says, "no rows appearing in this
table will ever violate constraint X," and then you go and create
a way of inserting rows into that table that violate that constraint,
I think you've just made the database into a non-relational database.
I really don't want to break postgres' relational side for some
inheritance features of dubious utility. Constraints should be explicitly
removed from tables if they are no longer needed, not implicitly removed
through the creation of another table.

I think we should settle this point before going any further.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 6 Sep 2002 14:37:59 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance
On 5 Sep 2002, Greg Copeland wrote:

> Sounds like a mechanism to make the distinction between virtual (child
> can override parent) and non-virtual (child is constrained by the
> parent) constraints are needed.

Oh, I should mention that I have no problem with being able to declare a
constraint "overridable" by subtables, so long as it's not the default,
and it's clear from the table definition that it might be overridden.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 6 Sep 2002 14:40:57 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance
On Thu, 5 Sep 2002, Jeff Davis wrote:

> But a person is-a employee (allow me to momentarily step aside from
> the rules of english grammer, if you would), and a person is-a
> programmer. That's why I didn't call my table "job" :) [1]

Certainly it's not the case that a person is-a job, by virtue of the
fact that a person can have no job. Nor is it the case that a person
is-a programmer; not all people are programmers.

Perhaps you're reversing the sense of "is-a"? One says "subtype is-a
supertype," not "supertype is-a subtype."

But even reversing these, it's not the case that job is-a person, by
virtue of the fact that you cannot use a job anywhere you can use a
person. (A person can file his tax return, a job can't.) That might be a
matter of bad mappings of object names to real-world concepts, though.

As for "programmer is-a person," yes, you could model things that way if
you really wanted to. But it's a bad way to do it because, as you point
out, a person can change his job, or not have a job. Now what do you do
with that programmer-subtype-of-person object you created? I think in
this case English misled you: we do say that "he is a programmer," but
what we really mean is that "one of the characteristics of that person
is that he programs." So create a separate characteristic type and have
the person object "have-a" as many or as few of those characteristics as
you need.

> I don't like the way some OO programming languages handle objects, if they
> mean to say you can't change an object's type without performing a logical
> data copy to a new object.

That's not a problem with the programming language; that's you
modelling things badly.

> Take as an example python... it's easy to emulate other objects: just assign
> to the attribute, even if it's not there yet, it'll add the attribute. Same
> with python, it's providing room to expand for it's objects already, so why
> do all the copying? Now compare with Java, and see why you'd be annoyed. It
> has the facilities to change the objects all around, but you can't do it.

Yes, you can't do it in Java because you Can't Do It in a language where
you can specify static typing. If I have field that holds a String, I'm
given a guarantee that, if I can put a reference in that field, it is
and always will be a String.

In non-statically-typed languages that give you the option of changing
types, you might give a referenc to a string, change the objects type on
me, and then I might blow up when I try to use it later. These bugs tend
to be quite difficult to track down because the source and manifestation
of the problem can be widely separated in code and in time. That's why
most languages don't allow this.

> ...when the more obvious logical path is to expand on the data you
> already carry about an entity.

Yes, that's the perfectly obvious path. And that's just what the
relational model lets us do, and do very well.

Why do you want to use an ill-fitting, error-prone model when you've
already got something that works better?

> [1] Come to think of it, the JOIN operator seems to, at least on a first
> thought, represent the "has-a" relationship you describe.

You bet! Hey, this relational stuff doesn't suck so badly after
all, does it? Especially for a 30-year old theory. :-)

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 6 Sep 2002 14:54:42 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance
On 6 Sep 2002, Hannu Krosing wrote:

> > In most object-oriented languages (Eiffel being a notable exception, IIRC),
> > you can't specify constraints on objects. But in a relational database,
> > you can specify constraints on tables, and it should *never* *ever* be
> > possible to violate those constraints, or the constraints are pointless.
>
> That's not how real world (which data is supposed to model) operates ;)

Sure it is. Please don't blame the language for being wrong when you
incorrectly model things for your purposes. To chose a much simpler
and more obvious example: if you stored birthdate as a date only, and
someone complained that you're not born all day, but at a particular
time on that day, you don't blame the language for having the date type
not store the time of day. You fix your problem to use both a date and a
time to store that value.

If the language specifies that contstraints on tables are not to be
violated, then don't use those constraints when you don't want them.

> To elaborate on Gregs example if you have table GOODS and under it a
> table CAMPAIGN_GOODS then you may place a general overridable constraint
> valid_prices on GOODS which checks that you dont sell cheaper than you
> bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
> you override the constraint for CAMPAIGN_GOODS.

This looks like a classic case of incorrect modelling to me. Does the
good itself change when it becomes a campaign_good? No. The price
changes, but that's obviously not an integral part of the good itself.
So separate your price information from your good information, and then
you can do things like have campaign prices, multiple prices per good
(since you probably want to keep the original price information as
well), and so on.

I'm really getting the feeling a lot of these applications that
want table inheritance want it just to be different, not because
it provides anything useful.

I am completely committed to object-oriented programming, and use
inheritance heavily, so it's not that I don't understand or like the
concepts. But just because a concept works well in one type of use does
not mean it will do any good, or even not do harm, when brought into a
completely different world.

> SQL standard constraints should be non-overridable. I still think that
> Constraint triggers should be overridable/dynamic.

I still don't like it. Eiffel had good reasons for making the
constraints non-overridable. Other OO languages don't have constraints,
or they would probably do the same.

That said, I could live with dynamic dispatch, if the default were
to make it non-dynamic, and you had to add a special flag to make it
dynamic. That way it would be obvious to the casual user or a DBA
familiar with other databases but not postgres that something unusual is
going on.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Fri, 6 Sep 2002 16:53:12 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Inheritance
On Fri, 2002-09-06 at 07:37, Curt Sampson wrote:
> On 5 Sep 2002, Hannu Krosing wrote:
> 
> > Suppose you have a table CITIZEN with table-level constraint IS_GOOD
> > which is defined as kills_not_others(CITIZEN). and there is table
> > CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
> > (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD....
> 
> This I very much disagree with.
> 
> In most object-oriented languages (Eiffel being a notable exception, IIRC),
> you can't specify constraints on objects. But in a relational database,
> you can specify constraints on tables, and it should *never* *ever* be
> possible to violate those constraints, or the constraints are pointless.

That's not how real world (which data is supposed to model) operates ;)

As Greg already pointed out, there are two kinds of constraints -
database integrity constraints (foreign key, unique, not null, check),
which should never be overridden and business-rule constraints which
should be overridable in child tables.

one can argue that the latter are not constraints at all, but they sure
look like constraints to me ;)

To elaborate on Gregs example if you have table GOODS and under it a
table CAMPAIGN_GOODS then you may place a general overridable constraint
valid_prices on GOODS which checks that you dont sell cheaper than you
bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
you override the constraint for CAMPAIGN_GOODS.

> So if I have a constraint that says, "no rows appearing in this
> table will ever violate constraint X," and then you go and create
> a way of inserting rows into that table that violate that constraint,
> I think you've just made the database into a non-relational database.

SQL standard constraints should be non-overridable. I still think that
Constraint triggers should be overridable/dynamic. 

Or maybe it is better to just make the check function should be
dynamically dispatched, so the constraint will always hold, it just can
mean different things for different types.

> I really don't want to break postgres' relational side for some
> inheritance features of dubious utility. Constraints should be explicitly
> removed from tables if they are no longer needed, not implicitly removed
> through the creation of another table.
> 
> I think we should settle this point before going any further.

It seems that the dynamic dispatch of trigger function should be enough
for business-rule constraints. 

And it is also simpler and cleaner (both conceptually and to implement)
if constraints themselves are not overridable.

So in my CAMPAIGN_GOODS example you just have different
valid_prices(GOODS) and valid_prices(CAMPAIGN_GOODS), but one constraint
on GOODS which states that price must be valid . 

Doing it this way ensures that you are not able to have a record in
GOODS for which valid_price(ROW) does not hold.

If you don't want inherited tables to be able to override valid_price()
use it in CHECK constraint in GOODS, which should use the
valid_prices(cast(ROW as GOODS)) for any inherited type.

-----------------
Hannu











06 Sep 2002 10:21:52 +0200
Hannu Krosing <hannu@tm.ee>
Re: Inheritance
On Fri, 2002-09-06 at 09:53, Curt Sampson wrote:
> 
> If the language specifies that contstraints on tables are not to be
> violated, then don't use those constraints when you don't want them.

But what _should_ i use then if i want the same business rule on most
top-level types, but a changed one on some down the hierarchy ?
 
> > To elaborate on Gregs example if you have table GOODS and under it a
> > table CAMPAIGN_GOODS then you may place a general overridable constraint
> > valid_prices on GOODS which checks that you dont sell cheaper than you
> > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
> > you override the constraint for CAMPAIGN_GOODS.
> 
> This looks like a classic case of incorrect modelling to me. Does the
> good itself change when it becomes a campaign_good? No. The price
> changes, but that's obviously not an integral part of the good itself.

Perhaps we mean different things by good. I meant a GOOD to be a THING 
bought with the purpose of reselling. Price (actually prices: 
selling_price and buying_price) is what makes it a GOOD and thus it is
an integral part of it.

> So separate your price information from your good information, and then
> you can do things like have campaign prices, multiple prices per good
> (since you probably want to keep the original price information as
> well), and so on.

It does not solve the problem described above - the price at which the
good is soled is still constrained differently for orninary and campaign
goods.

in standard relational model you would make the distinction inside the
constraint (CHECK (selling_price > buying_price) OR is_campaign_good)
but this localises the check in wrong place - in OO model I'd expect it
to be possible to define the constraint near the child type, not change
the parent constraint each time I derive new child types.

> I'm really getting the feeling a lot of these applications that
> want table inheritance want it just to be different, not because
> it provides anything useful.

As with any other inheritance, it is just a way to organize stuff.

In case of being able to override constraints for child tables it can
also be a significant performance boost - if you have 10 000 000 goods
in a table you don't want to change a constraint on GOODS to allow
campaign goods to be sold cheaper than bought as it would have to check
all goods for validity according to new constraint - putting the
constraint on just CAMPAIGN_GOODS will enable the DB engine to check
just tuples in CAMPAIGN_GOODS.

> I am completely committed to object-oriented programming, and use
> inheritance heavily, so it's not that I don't understand or like the
> concepts. But just because a concept works well in one type of use does
> not mean it will do any good, or even not do harm, when brought into a
> completely different world.

  Surely great caution is needed when defining the desired behaviour.

> > SQL standard constraints should be non-overridable. I still think that
> > Constraint triggers should be overridable/dynamic.
> 
> I still don't like it. Eiffel had good reasons for making the
> constraints non-overridable. Other OO languages don't have constraints,
> or they would probably do the same.
> 
> That said, I could live with dynamic dispatch, if the default were
> to make it non-dynamic, and you had to add a special flag to make it
> dynamic. That way it would be obvious to the casual user or a DBA
> familiar with other databases but not postgres that something unusual is
> going on.

That seems about the right compromise between constraining and developer
freedom.

-------------
Hannu








06 Sep 2002 14:53:37 +0200
Hannu Krosing <hannu@tm.ee>
Re: Inheritance
On Thu, 2002-09-05 at 15:51, Hannu Krosing wrote:
> On Fri, 2002-09-06 at 03:19, Greg Copeland wrote:
> > 
> > What about the concept of columns being public or private?  That is,
> > certain columns may not be inherited by a child?  Any thought to such a
> > concept?  Perhaps different types of table inheritance can be considered
> > in our model...has-a, is-a, etc...
> 
> I can't fit this in my mental model of table inheritance for two reasons
> 
> 1) all parent table columns must be present in child

Okay, I must admit, I'm not really sure why.  If we look at it in a
physical versus logical manner, even if it's physically there, why must
it be logically exposed?  Can you help me understand why it would even
need to physically be there.  After all, if a child can't update it,
they don't need to see it.

> 
> 2) granting some right to parent should automatically allow selecting
> from children

Unless the parent deemed it inappropriate access (private)?

If a column were deemed private, that would have a couple of
stipulations on it.  That is, it would have to ensure that "NOT NULL"
where not one of the constraints, or, if it did, ensure that a default
value were also provided.

> 
> both are required for select/insert/update/delete to work on table and
> its children (i.e. without ONLY)
> 
> 
> But maybe i just need to think more about it ;)
> 

Well, I guess I'm lagging behind you on this manner.  Perhaps "holding
my hand" and explaining it a bit will allow you to work through it some
more and help bring me in line with what you're thinking.

Greg
06 Sep 2002 08:01:27 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Inheritance
On Fri, 2002-09-06 at 07:53, Hannu Krosing wrote:
> On Fri, 2002-09-06 at 09:53, Curt Sampson wrote:
> > This looks like a classic case of incorrect modelling to me. Does the
> > good itself change when it becomes a campaign_good? No. The price
> > changes, but that's obviously not an integral part of the good itself.
> 
> Perhaps we mean different things by good. I meant a GOOD to be a THING 
> bought with the purpose of reselling. Price (actually prices: 
> selling_price and buying_price) is what makes it a GOOD and thus it is
> an integral part of it.

No matter now you look at the example, invalidating it does not address
the issue raised as it still exists.  Either way, Hannu and I seem to
agree that some class of constraints need to be able to be overridden.

> In case of being able to override constraints for child tables it can
> also be a significant performance boost - if you have 10 000 000 goods
> in a table you don't want to change a constraint on GOODS to allow
> campaign goods to be sold cheaper than bought as it would have to check
> all goods for validity according to new constraint - putting the
> constraint on just CAMPAIGN_GOODS will enable the DB engine to check
> just tuples in CAMPAIGN_GOODS.

I had not considered this before.  Does that still hold true if we go
with a parent contains all columns implementation?  Of are you simply
saying that it doesn't matter as when the constraint were applied it
would only scan the rows the below to the child?  Perhaps this doesn't
matter for this portion of the conversation.  But hey, I was curious. 
:)

> 
> > > SQL standard constraints should be non-overridable. I still think that
> > > Constraint triggers should be overridable/dynamic.
> > 
> > I still don't like it. Eiffel had good reasons for making the
> > constraints non-overridable. Other OO languages don't have constraints,
> > or they would probably do the same.

Well Curt, as you outlined above (clipped out) about it being a
different world...I think also applies here.  IMO, we are treading
lightly on new and perhaps thin ground so we need to be careful that we
apply common parallels and idioms only we are certain that they need
apply.  What I'm trying to say is, just because it's not allowed in
Eiffel does have to mean the same applies here.

> > 
> > That said, I could live with dynamic dispatch, if the default were
> > to make it non-dynamic, and you had to add a special flag to make it
> > dynamic. That way it would be obvious to the casual user or a DBA
> > familiar with other databases but not postgres that something unusual is
> > going on.
> 
> That seems about the right compromise between constraining and developer
> freedom.
> 

I agree.  That does appear to be pointing us in a conservatively sane
and safe direction.


Greg
06 Sep 2002 08:19:51 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Inheritance
> On Fri, 2002-09-06 at 07:37, Curt Sampson wrote:
> > On 5 Sep 2002, Hannu Krosing wrote:
> > 
> > > Suppose you have a table CITIZEN with table-level constraint IS_GOOD
> > > which is defined as kills_not_others(CITIZEN). and there is table
> > > CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
> > > (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD....
> > 
> > This I very much disagree with.
> > 
> > In most object-oriented languages (Eiffel being a notable exception, IIRC),
> > you can't specify constraints on objects. But in a relational database,
> > you can specify constraints on tables, and it should *never* *ever* be
> > possible to violate those constraints, or the constraints are pointless.
> 
> That's not how real world (which data is supposed to model) operates ;)
> 
> As Greg already pointed out, there are two kinds of constraints -
> database integrity constraints (foreign key, unique, not null, check),
> which should never be overridden and business-rule constraints which
> should be overridable in child tables.
> 
> one can argue that the latter are not constraints at all, but they sure
> look like constraints to me ;)
> 
> To elaborate on Gregs example if you have table GOODS and under it a
> table CAMPAIGN_GOODS then you may place a general overridable constraint
> valid_prices on GOODS which checks that you dont sell cheaper than you
> bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
> you override the constraint for CAMPAIGN_GOODS.

What that tells me is that the constraint, valid_prices, shouldn't have been 
on GOODS in the first place.  If it is not a legitimate constraint for the 
children, then it is not a legitimate constraint for the parent.

In human inheritance, if you marry someone with "funny coloured skin," you 
don't get to choose that your children won't have "funny coloured skin."  
That's a pretty forcible "constraint."  :-).

For the GOODS situation, the constraint ought not to be on GOODS in the first 
place.  There ought to be a table ORDINARY_GOODS, or some such thing, to which 
the constraint applies, and from which CAMPAIGN_GOODS will _not_ be inheriting.

> > So if I have a constraint that says, "no rows appearing in this
> > table will ever violate constraint X," and then you go and create
> > a way of inserting rows into that table that violate that constraint,
> > I think you've just made the database into a non-relational database.
> 
> SQL standard constraints should be non-overridable. I still think that
> Constraint triggers should be overridable/dynamic. 
> 
> Or maybe it is better to just make the check function should be
> dynamically dispatched, so the constraint will always hold, it just can
> mean different things for different types.

Or maybe if someone is doing an Object Oriented design, and making extensive 
use of inheritance, they'll need to apply constraints in a manner that allow 
them to be properly inherited.
--
(concatenate 'string "aa454" "@freenet.carleton.ca")
http://cbbrowne.com/info/
If a cow laughed, would milk come out its nose? 



Fri, 06 Sep 2002 09:57:03 -0400
cbbrowne@cbbrowne.com
Re: Inheritance 
On Fri, 2002-09-06 at 08:57, cbbrowne@cbbrowne.com wrote:
> > On Fri, 2002-09-06 at 07:37, Curt Sampson wrote:
> > > On 5 Sep 2002, Hannu Krosing wrote:
> > 
> > To elaborate on Gregs example if you have table GOODS and under it a
> > table CAMPAIGN_GOODS then you may place a general overridable constraint
> > valid_prices on GOODS which checks that you dont sell cheaper than you
> > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
> > you override the constraint for CAMPAIGN_GOODS.
> 
> What that tells me is that the constraint, valid_prices, shouldn't have been 
> on GOODS in the first place.  If it is not a legitimate constraint for the 
> children, then it is not a legitimate constraint for the parent.
> 

I don't agree with you on that point.  This concept is common to many
OO-implementations.  Unless you can come up with a powerful argument as
to why our "to-be" picture should never do this, I'm less than
convinced.

> In human inheritance, if you marry someone with "funny coloured skin," you 
> don't get to choose that your children won't have "funny coloured skin."  
> That's a pretty forcible "constraint."  :-).
> 

Fine, but that only works for YOUR specific example.  In that example,
the color constraint should be non-virtual, meaning, the child should
not be able to change it.  On the other hand, if I replace "human" with
"metal product", hopefully I won't be stuck with gun metal gray for
every derived product.  Hopefully, somewhere along the lines, I'll be
able to override the parent's color constraint.

> > Or maybe it is better to just make the check function should be
> > dynamically dispatched, so the constraint will always hold, it just can
> > mean different things for different types.
> 
> Or maybe if someone is doing an Object Oriented design, and making extensive 
> use of inheritance, they'll need to apply constraints in a manner that allow 
> them to be properly inherited.

The problem with that assumption is that there is normally nothing wrong
with having seemingly mutually exclusive sets of *business rules* for a
parent and child.

Greg
06 Sep 2002 10:27:26 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Inheritance
Oops! greg@CopelandConsulting.Net (Greg Copeland) was seen spray-painting on a wall:
> --=-eu74lKXry3SVx8eZ/qBD
> Content-Type: text/plain
> Content-Transfer-Encoding: quoted-printable
> On Fri, 2002-09-06 at 08:57, cbbrowne@cbbrowne.com wrote:
>> > On Fri, 2002-09-06 at 07:37, Curt Sampson wrote:
>> > > On 5 Sep 2002, Hannu Krosing wrote:
>> > To elaborate on Gregs example if you have table GOODS and under it a
>> > table CAMPAIGN_GOODS then you may place a general overridable constraint
>> > valid_prices on GOODS which checks that you dont sell cheaper than you
>> > bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so
>> > you override the constraint for CAMPAIGN_GOODS.

>> What that tells me is that the constraint, valid_prices, shouldn't
>> have been on GOODS in the first place.  If it is not a legitimate
>> constraint for the children, then it is not a legitimate constraint
>> for the parent.

> I don't agree with you on that point.  This concept is common to
> many OO-implementations.  Unless you can come up with a powerful
> argument as to why our "to-be" picture should never do this, I'm
> less than convinced.

If the plan is for table CAMPAIGN_GOODS to virtually be a view on GOODS,
then I'd say it _is_ necessary.

>> In human inheritance, if you marry someone with "funny coloured skin," yo=
> u=20
>> don't get to choose that your children won't have "funny coloured skin."=
> =20=20
>> That's a pretty forcible "constraint."  :-).
>>=20

Is there something broken with your mailer?  It's reformatting quotes
rather horribly...

> Fine, but that only works for YOUR specific example.  In that
> example, the color constraint should be non-virtual, meaning, the
> child should not be able to change it.  On the other hand, if I
> replace "human" with "metal product", hopefully I won't be stuck
> with gun metal gray for every derived product.  Hopefully, somewhere
> along the lines, I'll be able to override the parent's color
> constraint.

That happens by _adding_ an additional characteristic, presumably that
of "what kind of paint the metal is covered with."  That doesn't
override the fundamental constraint that if it's a metal product,
there _will_ be metallic properties.

If you decide to add in some "non-metallic" products, then it would be
_silly_ to have them inherit all their characteristics from
"METAL_PRODUCTS;" they should head back up the class hierarchy and
inherit their basic characteristics from the _appropriate_ parent.

Reality, with the "GOODS/CAMPAIGN_GOODS" example, is that GOODS isn't
the appropriate parent class for CAMPAIGN_GOODS.  Both should be
inheriting the common characteristics from some common ancestor.  If
that is done, then there's nothing to "override."

>> > Or maybe it is better to just make the check function should be
>> > dynamically dispatched, so the constraint will always hold, it just can
>> > mean different things for different types.
>>=20
>> Or maybe if someone is doing an Object Oriented design, and making extens=
> ive=20
>> use of inheritance, they'll need to apply constraints in a manner that al=
> low=20
>> them to be properly inherited.

> The problem with that assumption is that there is normally nothing
> wrong with having seemingly mutually exclusive sets of *business
> rules* for a parent and child.

If the rules are totally different, it begs the question of why they
_should_ be considered to be related in a "parent/child" relationship.

It may well be that they _aren't_ related as "parent/child."  They may
merely be "cousins," sharing some common ancestors.
-- 
(concatenate 'string "chris" "@cbbrowne.com")
http://cbbrowne.com/info/spreadsheets.html
"Note that if I can get you  to `su and say' something just by asking,
you have a very serious security problem on your system and you should
look into it."  -- Paul Vixie, vixie-cron 3.0.1 installation notes

Fri, 06 Sep 2002 12:05:03 -0400
cbbrowne@cbbrowne.com
Re: Inheritance


There was a comment earlier that was not really addressed.
What can you do with table inheritance that you can not do
with a relational implementation?  Or what would work *better*
as inheritance?  (you define better)

This is a genuine question, not a snarky comment.  I really
want to know.  This is the reason I can think of to use
inheritance: Several tables have a common set of attributes and
there is some reason for these tables to be separate AND there
is some reason for the common columns to be queried en masse.
What kinds of "some reasons" are there, though?  And if my
condition for using table inheritance is lacking or misguided, what should
be the criteria for using table inheritance?

Creating indexes across tables is a project.  Is it the most important
project?  Will it benefit the most users?  Will it benefit any users?
Theory is great and important, but if no one uses the functionality,
who cares?  If these changes will enable people to use the functionality
that until now had been too much of a PITA then it might be worth
it.  However,  I suspect the majority of people who would use these
changes are participating in these discussions.

These features were never widely used in Illustra nor Informix although
their implementations were a little smoother imho.

To weigh in on the constraints issues, it seems problematic
that currently some constraints (check) are inherited and
others are not (foreign keys).  The chcheers,oice of which ones are
or aren't is clear to people familiar with the implementation
but what about the rest of the world who just want some
consistent rule.

I also agree with the people who say, if we inherit constrainsts,
then we must be able to override them in the subtables.
I like the suggested "LOCAL" keyword, myself.

cheers,

elein


:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
     elein@norcov.com   (510)543-6079
     "Taking a Trip. Not taking a Trip." --anonymous
:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:


Fri, 06 Sep 2002 11:00:01 -0700
elein <elein@norcov.com>
Re: Inheritance
On Fri, 2002-09-06 at 11:05, cbbrowne@cbbrowne.com wrote:
> Oops! greg@CopelandConsulting.Net (Greg Copeland) was seen spray-painting on a wall:
> >> That's a pretty forcible "constraint."  :-).
> >>=20
> 
> Is there something broken with your mailer?  It's reformatting quotes
> rather horribly...

Hmm...not that I know off.  Never had complaints before anyways.  Looks
like an issue with MIME contents...perhaps your mailer doesn't properly
parse some MIME and/or mine is hosing it some how.  Not really sure.

> Reality, with the "GOODS/CAMPAIGN_GOODS" example, is that GOODS isn't
> the appropriate parent class for CAMPAIGN_GOODS.  Both should be
> inheriting the common characteristics from some common ancestor.  If
> that is done, then there's nothing to "override."
> 

You can complain about and redefine the model to suit your needs all day
long and get no where.  It doesn't change the need for it.  Fact is, it
would be nice to allow.  Fact is, OO-implementations tend to allow
this.  I'm quite happy to let you go to every OO computer language camp
and inform them that they've done it all wrong.  ;)

Citing that a specific example is all wrong hardly invalidates the
concept.  Since we are pretty much at the conceptual stage, I welcome a
conceptual argument on why this is bad and should never be done. 
Please, be high level and generic.  After all, I too can give you a
hundred specific reasons why a cat is not dog (i.e. bad model)...but it
does nothing to facilitate the topic at hand.

> > The problem with that assumption is that there is normally nothing
> > wrong with having seemingly mutually exclusive sets of *business
> > rules* for a parent and child.
> 
> If the rules are totally different, it begs the question of why they
> _should_ be considered to be related in a "parent/child" relationship.

Because this is how the real world works.  Often there are exceptions to
the rules.  When these rules differ, I've not seen a valid high level
conceptual reason that should prevent it.

Example:

animal
	quadruped (has 4-trunk limbs)
		dog
		injuredDog (has 0 or more trunk limbs)

Hopefully we can agree that a dog is still a dog even if it only has
three legs?  Hopefully you'll realize this was given to illustrate an
example and to prove a point.  Sometimes a model needs to allow for
exceptions to the rule.  You can argue that a three-legged dog is no
longer a quadruped but I prefer to believe that it is a quadruped which
just happens to be an exception to the rule.
	
> 
> It may well be that they _aren't_ related as "parent/child."  They may
> merely be "cousins," sharing some common ancestors.

Yes, it's true.  Sometimes the wrong model is applied but that hardly
invalidates the concept or alleviates the need.

Regards,

	Greg Copeland
06 Sep 2002 15:23:40 -0500
Greg Copeland <greg@CopelandConsulting.Net>
Re: Inheritance
On Fri, 2002-09-06 at 19:00, elein wrote:
> 
> 
> There was a comment earlier that was not really addressed.
> What can you do with table inheritance that you can not do
> with a relational implementation?  Or what would work *better*
> as inheritance?  (you define better)

There is nothing that you cannot do in some way; that way may not be
very convenient compared to the use of inheritance.  I consider
simplicity to be preferable to conceptual purity.

> This is a genuine question, not a snarky comment.  I really
> want to know.  This is the reason I can think of to use
> inheritance: Several tables have a common set of attributes and
> there is some reason for these tables to be separate AND there
> is some reason for the common columns to be queried en masse.
> What kinds of "some reasons" are there, though?  And if my
> condition for using table inheritance is lacking or misguided, what should
> be the criteria for using table inheritance?

I use it when a group of tables are closely related; they are all
members of some higher class.  For example:

                               person <.......................> address
                                  |
                   +--------------+--------------+
                   |                             |
             organisation                   individual <......> pay_tax
                   |                             |
          +--------+--------+          +---------+---------+
          |        |        |          |         |         |
     customer  supplier ...etc...   staff  homeworker ...etc...
          |
     +----+-------------+
     |                  |
home_customer    export_customer

It is convenient to use a higher class when you are interested in all
its members and only in the attributes of the higher class.  So I can
say

   SELECT * FROM person,address
    WHERE address.person = person.id AND
          address.town = 'London';

to get all rows for people in London.  I will only get those attributes
that are in person itself; if I want to know about credit limits, that
is only relevant in the customer hierarchy and I have to SELECT from
customer instead..

Similarly, I can use the whole customer hierarchy when changing or
reporting on outstanding customer balances.

If foreign key relations were valid against an inheritance tree, I could
implement it for a table of addresses referencing the highest level
(every person has an address) and of pay and tax records at the
individual level.  These don't change as you go down the hierarchy, but
a purely relational implementation has to be redone at each level.  A
reciprocal relation requires an extra table to hold all the hierarchy's
keys and that in turn needs triggers to keep that table maintained.
(I.e., person should have a FK reference to address and address to
person; instead, address needs a reference to person_keys, which I have
to create because FK against a hierarchy isn't valid.)  The lack of
inherited RI makes the design more complex and more difficult to
understand.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For whosoever shall call upon the name of the Lord 
      shall be saved."         Romans 10:13 


07 Sep 2002 16:33:18 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: Inheritance


At 08:33 AM 9/7/02, Oliver Elphick wrote:
>On Fri, 2002-09-06 at 19:00, elein wrote:
> >
> >
> > There was a comment earlier that was not really addressed.
> > What can you do with table inheritance that you can not do
> > with a relational implementation?  Or what would work *better*
> > as inheritance?  (you define better)
>
>There is nothing that you cannot do in some way; that way may not be
>very convenient compared to the use of inheritance.  I consider
>simplicity to be preferable to conceptual purity.

yes, simplicity is a very reasonable criteria for better.

> > This is a genuine question, not a snarky comment.  I really
> > want to know.  This is the reason I can think of to use
> > inheritance: Several tables have a common set of attributes and
> > there is some reason for these tables to be separate AND there
> > is some reason for the common columns to be queried en masse.
> > What kinds of "some reasons" are there, though?  And if my
> > condition for using table inheritance is lacking or misguided, what should
> > be the criteria for using table inheritance?

In non-OO terms, you have both reasons for tables to
be separate and reasons to query an entire hierarchy.
Your exact reasons are clear and reasonable.
This is helpful.

>I use it when a group of tables are closely related; they are all
>members of some higher class.  For example:
>
>                                person <.......................> address
>                                   |
>                    +--------------+--------------+
>                    |                             |
>              organisation                   individual <......> pay_tax
>                    |                             |
>           +--------+--------+          +---------+---------+
>           |        |        |          |         |         |
>      customer  supplier ...etc...   staff  homeworker ...etc...
>           |
>      +----+-------------+
>      |                  |
>home_customer    export_customer
>
>It is convenient to use a higher class when you are interested in all
>its members and only in the attributes of the higher class.  So I can
>say
>
>    SELECT * FROM person,address
>     WHERE address.person = person.id AND
>           address.town = 'London';
>
>to get all rows for people in London.  I will only get those attributes
>that are in person itself; if I want to know about credit limits, that
>is only relevant in the customer hierarchy and I have to SELECT from
>customer instead..
>
>Similarly, I can use the whole customer hierarchy when changing or
>reporting on outstanding customer balances.

I don't think table inheritance will "go away" and so being
consistent about the indexes and constraints is necessary
in order to keep its usage simpler.  This might lessen the PITA
factor for a few more people, but we should prioritize the
project.  I think few people have put the thought into it that
you have.


>If foreign key relations were valid against an inheritance tree, I could
>implement it for a table of addresses referencing the highest level
>(every person has an address) and of pay and tax records at the
>individual level.  These don't change as you go down the hierarchy, but
>a purely relational implementation has to be redone at each level.  A
>reciprocal relation requires an extra table to hold all the hierarchy's
>keys and that in turn needs triggers to keep that table maintained.
>(I.e., person should have a FK reference to address and address to
>person; instead, address needs a reference to person_keys, which I have
>to create because FK against a hierarchy isn't valid.)  The lack of
>inherited RI makes the design more complex and more difficult to
>understand.
>
>--
>Oliver Elphick                                Oliver.Elphick@lfix.co.uk
>Isle of Wight, UK
>http://www.lfix.co.uk/oliver
>GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "For whosoever shall call upon the name of the Lord
>       shall be saved."         Romans 10:13
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html

:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
     elein@norcov.com   (510)543-6079
     "Taking a Trip. Not taking a Trip." --anonymous
:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:


Sat, 07 Sep 2002 09:54:33 -0700
elein <elein@norcov.com>
Re: Inheritance
I am working on getting a shrink-wrapped version of PostgreSQL for Windows

Currently it installs a customized version of Cygwin, PostgreSQL 7.2.3, 
cygipc, psqlodbc, and pgadminII

I currently have the setup done.

The target audience is not the enterprise, it is aimed at people using 
Access wanting to upgrade.

I've looked long and hard and can't find any license issues. Does anyone 
know of any that I may have missed? As far as I can see, as long as I 
maintain GPL restrictions, I should be fine.




Tue, 03 Dec 2002 01:17:23 -0500
mlw <pgsql@mohawksoft.com>
Shrinkwrap Windows Product, any issues? Anyone?
> I've looked long and hard and can't find any license issues. Does anyone 
> know of any that I may have missed? As far as I can see, as long as I 
> maintain GPL restrictions, I should be fine.

PostgreSQL isn't licensed under the GPL, so it sounds to me as though you're 
confused about the licensing issues.
--
(concatenate 'string "cbbrowne" "@cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/lsf.html
"My mom said she learned how to swim. Someone took her out in the lake
and threw  her off  the boat. That's  how she  learned how to  swim. I
said, 'Mom, they  weren't trying to teach you how  to swim.' " 
-- Paula Poundstone



Tue, 03 Dec 2002 07:16:29 -0500
cbbrowne@cbbrowne.com
Re: Shrinkwrap Windows Product, any issues? Anyone? 


cbbrowne@cbbrowne.com wrote:

>>I've looked long and hard and can't find any license issues. Does anyone 
>>know of any that I may have missed? As far as I can see, as long as I 
>>maintain GPL restrictions, I should be fine.
>>    
>>
>
>PostgreSQL isn't licensed under the GPL, so it sounds to me as though you're 
>confused about the licensing issues.
>
I'm not confused about the licensing issues. PostgreSQL is less 
restrictive than is GPL. Maybe I should have phrased it as the most 
restrictive license is GPL, so as long as I maintain GPL restrictions I 
should be fine.

>--
>(concatenate 'string "cbbrowne" "@cbbrowne.com")
>http://www3.sympatico.ca/cbbrowne/lsf.html
>"My mom said she learned how to swim. Someone took her out in the lake
>and threw  her off  the boat. That's  how she  learned how to  swim. I
>said, 'Mom, they  weren't trying to teach you how  to swim.' " 
>-- Paula Poundstone
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>  
>



Tue, 03 Dec 2002 08:01:24 -0500
mlw <pgsql@mohawksoft.com>
Re: Shrinkwrap Windows Product, any issues? Anyone?
Wow, there's been a lot of discussion on this issue!


While it won't address some of the issues that have been brought up,
there is one very simple thing we can do that will help sysadmins
quite a lot: eliminate the postmaster's use of $PGDATA, and force the
data directory to be specified on the command line.  It's fine if the
shell scripts still use $PGDATA, but the postmaster should not.

The reason is that at least it'll always be possible for
administrators to figure out where the data is by looking at the
output of 'ps'.


While I'd prefer to also see a GUC variable added to the config file
that tells the postmaster where to look for the data, the above will
at least simplify the postmaster's code (since the logic for dealing
with $PGDATA can be eliminated) while eliminating some of the trouble
administrators currently have with it.



-- 
Kevin Brown					      kevin@sysexperts.com

Thu, 13 Feb 2003 17:22:18 -0800
Kevin Brown <kevin@sysexperts.com>
Location of the configuration files, round 2
One of the things that I HATE about this discussion is that everyone 
wants to put limits on configurability.

I am an old fashion UNIX guy, capability without enforcing policy! 
Adding an ability is different than enforcing a policy. All I any to do 
is add the capability of configuration in a way that most admins would 
be used to.

If people want an FHS compatible install, I don't care. I want to enable 
it, but it should not be enforced.


Kevin Brown wrote:

>Wow, there's been a lot of discussion on this issue!
>
>
>While it won't address some of the issues that have been brought up,
>there is one very simple thing we can do that will help sysadmins
>quite a lot: eliminate the postmaster's use of $PGDATA, and force the
>data directory to be specified on the command line.  It's fine if the
>shell scripts still use $PGDATA, but the postmaster should not.
>
>The reason is that at least it'll always be possible for
>administrators to figure out where the data is by looking at the
>output of 'ps'.
>
>
>While I'd prefer to also see a GUC variable added to the config file
>that tells the postmaster where to look for the data, the above will
>at least simplify the postmaster's code (since the logic for dealing
>with $PGDATA can be eliminated) while eliminating some of the trouble
>administrators currently have with it.
>
>
>
>  
>



Sat, 15 Feb 2003 01:13:04 -0500
mlw <pgsql@mohawksoft.com>
Re: Location of the configuration files, round 2

mlw <pgsql@mohawksoft.com> wrote: 

> I am an old fashion UNIX guy, capability without enforcing policy! 
> Adding an ability is different than enforcing a policy. 

You can push this too far, though.  Every capability that
you add still complicates testing and documentation, even if
very difficult to code.  And ultimately you're going to want
it to default or auto-configure in a reasonable way, so most
people aren't even going to look at the additional
capabilities.

> All I any to do is add the capability of configuration in
> a way that most admins would be used to.

Which is an important issue of course.  So you need some 
discussion to establish what the common practices are. 


Sat, 15 Feb 2003 08:48:02 -0800
"J. M. Brenner" <doom@kzsu.stanford.edu>
Re: Location of the configuration files, round 2 
I have been working on moving some of my software to a more SOAP 
compatible interface. As I was doing it, it occured to me that a generic 
function could be written, in PostgreSQL's new function manager that 
allows multiple columns to be returned, that is a generic SOAP interface.

All one would need do is define what is expected from the SOAP call in 
the "CREATE FUNCTION" statement. Then the generic SOAP function could 
then read what is expected and return the XML/SOAP data as a set of 
results as if it were a subquery.

What is needed is an efficient way to find the data types and names from 
the functions definition. Does anyone know how to do that?

A small program could also parse a WSDL file and write a "CREATE 
FUNCTION" script for the XML as well.

On the flip side, I am also working on a PostgreSQL SOAP interface, 
where one does this:

http://somehost/postgresql?query="select * from table"

And a SOAP compatible resultset is returned.

On a more advanced horizon, one should be able to do this:

select * from localtable, 
mysoap('http://remotehost/postgresql?query=select * from foo') as soap 
where soap.field = localtable.field;

If we can do that, PostgreSQL could fit into almost ANY service 
environment. What do you guys think? Anyone want to help out?


Fri, 28 Mar 2003 09:01:08 -0500
mlw <pgsql@mohawksoft.com>
PostgreSQL and SOAP, version 7.4/8.0
First, a SOAP query should be posted in SOAP message format, not using the
query string as you do.  Second, I like the idea of calling external SOAP
services, but consider creating a language 'soap' you could do with a CREATE
FUNCTION type thing. e.g.

CREATE FUNCTION "foo" (TEXT) RETURNS INTEGER AS
	'http://somewhere.com/path/to/.wsdl', 'foo'
	LANGUAGE 'soap';

(hmm, it is unclear if this is what you are suggesting or not...)

Second, I hate SOAP because it is too bloated (have you read the spec(s)?).
If you can support xmlrpc instead, you'll save yourself a lot of headaches.
If you got SOAP working, though, I'd use it.  It's more an implementation
thing.


On Fri, Mar 28, 2003 at 09:01:08AM -0500, mlw wrote:
> I have been working on moving some of my software to a more SOAP 
> compatible interface. As I was doing it, it occured to me that a generic 
> function could be written, in PostgreSQL's new function manager that 
> allows multiple columns to be returned, that is a generic SOAP interface.
> 
> All one would need do is define what is expected from the SOAP call in 
> the "CREATE FUNCTION" statement. Then the generic SOAP function could 
> then read what is expected and return the XML/SOAP data as a set of 
> results as if it were a subquery.
> 
> What is needed is an efficient way to find the data types and names from 
> the functions definition. Does anyone know how to do that?
> 
> A small program could also parse a WSDL file and write a "CREATE 
> FUNCTION" script for the XML as well.
> 
> On the flip side, I am also working on a PostgreSQL SOAP interface, 
> where one does this:
> 
> http://somehost/postgresql?query="select * from table"
> 
> And a SOAP compatible resultset is returned.
> 
> On a more advanced horizon, one should be able to do this:
> 
> select * from localtable, 
> mysoap('http://remotehost/postgresql?query=select * from foo') as soap 
> where soap.field = localtable.field;
> 
> If we can do that, PostgreSQL could fit into almost ANY service 
> environment. What do you guys think? Anyone want to help out?
> 

I have no time to volunteer for projects, but what the hell...!  It's too
cool.  I can't spend much time on it but bounce things off me and I'll
do whatever hacking I can squeeze in.  What soap implementation would you
use for the PostgreSQL plugin?  libsoap, last I checked, is a wee bit 
out of date.  And not documented.

-Jason


Fri, 28 Mar 2003 12:17:57 -0500
"Jason M. Felice" <jfelice@cronosys.com>
Re: PostgreSQL and SOAP, version 7.4/8.0
Jason wrote:
> If you can support xmlrpc instead, you'll save yourself a lot of headaches.

XML-RPC has three merits over SOAP:

 1.  It's a simple specification, and thus readily implemented.

 2.  Microsoft and IBM aren't fighting over control over it, so it's
     not suffering from the "we keep adding pseudo-standards to it"
     problem.  (Which further complicates the specifications.)
     You can have a /complete/ implementation of XML-RPC, whereas,
     for SOAP, you can hold ghastly long arguments as to what SOAP
     means, anyways.

 3.  There's a (perhaps not "standard", but definitely widely
     implemented) scheme for bundling multiple XML-RPC requests into
     one message, which improves latency a LOT for small messages.

Of course, CORBA has actually been quite formally standardized, suffers
from many fairly interoperable implementations, and is rather a lot less
bloated than any of the XML-based schemes.  It might be worth trying,
too...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://cbbrowne.com/info/soap.html
I just got skylights put in my place. The people who live above me are
furious.


Fri, 28 Mar 2003 13:36:43 -0500
cbbrowne@cbbrowne.com
Re: PostgreSQL and SOAP, version 7.4/8.0 
On Fri, Mar 28, 2003 at 01:36:43PM -0500, cbbrowne@cbbrowne.com wrote:
> Of course, CORBA has actually been quite formally standardized, suffers
> from many fairly interoperable implementations, and is rather a lot less
> bloated than any of the XML-based schemes.  It might be worth trying,
> too...

The ability to use the HTTP transport has it's advantages with web services--
You can throw something together with a few lines of PHP, you don't have to
worry about how to activate objects, I've never been able to figure out how
to handle transport-layer security and authentication with CORBA (of course,
this was all fairly new stuff when I was working with it), all this stuff
comes for free with the HTTP transport.

I like CORBA, though, and I'd probably find a CORBA module useful, but it
doesn't solve all the same problems.

Hrm, I wonder if the overhead of XML-RPC wouldn't be too bad for the new
PostgreSQL protocol... it probably would, but it would be entirely useful.
You can make XML-RPC calls from mozilla javascript, so you could do some
pretty sweet tweaking to keep your addresses in a pgsql database.

As an "additional" protocol which postmaster can listen to it would rule.
I'm making a habit of putting all the business logic into stored procedures,
and this would basically publish the business logic in a very useful way.

-Jason


Fri, 28 Mar 2003 13:52:20 -0500
"Jason M. Felice" <jfelice@cronosys.com>
Re: PostgreSQL and SOAP, version 7.4/8.0
Jason M. Felice wrote:

>First, a SOAP query should be posted in SOAP message format, not using the
>query string as you do.  Second, I like the idea of calling external SOAP
>services, but consider creating a language 'soap' you could do with a CREATE
>FUNCTION type thing. e.g.
>
>CREATE FUNCTION "foo" (TEXT) RETURNS INTEGER AS
>	'http://somewhere.com/path/to/.wsdl', 'foo'
>	LANGUAGE 'soap';
>
>(hmm, it is unclear if this is what you are suggesting or not...)
>
>Second, I hate SOAP because it is too bloated (have you read the spec(s)?).
>If you can support xmlrpc instead, you'll save yourself a lot of headaches.
>If you got SOAP working, though, I'd use it.  It's more an implementation
>thing.
>

Here's the thing, yes I know there are a "lot" of alternatives to SOAP, 
all with varying levels of "being better than SOAP." It still stands 
that a SOAP interface would be useful for people.

>
>
>On Fri, Mar 28, 2003 at 09:01:08AM -0500, mlw wrote:
>  
>
>>I have been working on moving some of my software to a more SOAP 
>>compatible interface. As I was doing it, it occured to me that a generic 
>>function could be written, in PostgreSQL's new function manager that 
>>allows multiple columns to be returned, that is a generic SOAP interface.
>>
>>All one would need do is define what is expected from the SOAP call in 
>>the "CREATE FUNCTION" statement. Then the generic SOAP function could 
>>then read what is expected and return the XML/SOAP data as a set of 
>>results as if it were a subquery.
>>
>>What is needed is an efficient way to find the data types and names from 
>>the functions definition. Does anyone know how to do that?
>>
>>A small program could also parse a WSDL file and write a "CREATE 
>>FUNCTION" script for the XML as well.
>>
>>On the flip side, I am also working on a PostgreSQL SOAP interface, 
>>where one does this:
>>
>>http://somehost/postgresql?query="select * from table"
>>
>>And a SOAP compatible resultset is returned.
>>
>>On a more advanced horizon, one should be able to do this:
>>
>>select * from localtable, 
>>mysoap('http://remotehost/postgresql?query=select * from foo') as soap 
>>where soap.field = localtable.field;
>>
>>If we can do that, PostgreSQL could fit into almost ANY service 
>>environment. What do you guys think? Anyone want to help out?
>>
>>    
>>
>
>I have no time to volunteer for projects, but what the hell...!  It's too
>cool.  I can't spend much time on it but bounce things off me and I'll
>do whatever hacking I can squeeze in.  What soap implementation would you
>use for the PostgreSQL plugin?  libsoap, last I checked, is a wee bit 
>out of date.  And not documented.
>

I was thinking of using SOAP over HTTP as the protocol, and a minimalist 
version at best. If the people want "more" let them add it.

I have an HTTP service class in my open source library. It would br 
trivial to accept a SQL query formatted as a GET request, and then 
execute the query and, using libpq, format the result as XML. It should 
be simple enough to do.

>
>-Jason
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>  
>









Jason M. Felice wrote:

First, a SOAP query should be posted in SOAP message format, not using the
query string as you do.  Second, I like the idea of calling external SOAP
services, but consider creating a language 'soap' you could do with a CREATE
FUNCTION type thing. e.g.

CREATE FUNCTION "foo" (TEXT) RETURNS INTEGER AS
	'http://somewhere.com/path/to/.wsdl', 'foo'
	LANGUAGE 'soap';

(hmm, it is unclear if this is what you are suggesting or not...)

Second, I hate SOAP because it is too bloated (have you read the spec(s)?).
If you can support xmlrpc instead, you'll save yourself a lot of headaches.
If you got SOAP working, though, I'd use it.  It's more an implementation
thing.


Here's the thing, yes I know there are a "lot" of alternatives to SOAP, all
with varying levels of "being better than SOAP." It still stands that a SOAP
interface would be useful for people.





On Fri, Mar 28, 2003 at 09:01:08AM -0500, mlw wrote:
  

I have been working on moving some of my software to a more SOAP 
compatible interface. As I was doing it, it occured to me that a generic 
function could be written, in PostgreSQL's new function manager that 
allows multiple columns to be returned, that is a generic SOAP interface.

All one would need do is define what is expected from the SOAP call in 
the "CREATE FUNCTION" statement. Then the generic SOAP function could 
then read what is expected and return the XML/SOAP data as a set of 
results as if it were a subquery.

What is needed is an efficient way to find the data types and names from 
the functions definition. Does anyone know how to do that?

A small program could also parse a WSDL file and write a "CREATE 
FUNCTION" script for the XML as well.

On the flip side, I am also working on a PostgreSQL SOAP interface, 
where one does this:

http://somehost/postgresql?query="select * from table"

And a SOAP compatible resultset is returned.

On a more advanced horizon, one should be able to do this:

select * from localtable, 
mysoap('http://remotehost/postgresql?query=select * from foo') as soap 
where soap.field = localtable.field;

If we can do that, PostgreSQL could fit into almost ANY service 
environment. What do you guys think? Anyone want to help out?

    


I have no time to volunteer for projects, but what the hell...!  It's too
cool.  I can't spend much time on it but bounce things off me and I'll
do whatever hacking I can squeeze in.  What soap implementation would you
use for the PostgreSQL plugin?  libsoap, last I checked, is a wee bit 
out of date.  And not documented.


I was thinking of using SOAP over HTTP as the protocol, and a minimalist
version at best. If the people want "more" let them add it.

I have an HTTP service class in my open source library. It would br trivial
to accept a SQL query formatted as a GET request, and then execute the query
and, using libpq, format the result as XML. It should be simple enough to
do. 



-Jason


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Fri, 28 Mar 2003 16:39:34 -0500
mlw <pgsql@mohawksoft.com>
Re: PostgreSQL and SOAP, version 7.4/8.0
On Fri, 2003-03-28 at 14:39, mlw wrote:

> I was thinking of using SOAP over HTTP as the protocol, and a
> minimalist version at best. If the people want "more" let them add it.
> 
> I have an HTTP service class in my open source library. It would br
> trivial to accept a SQL query formatted as a GET request, and then
> execute the query and, using libpq, format the result as XML. It
> should be simple enough to do. 

It would be easy.  I've done something similar (using ODBC to
get to PostgreSQL) - but using a language none of the rest of
you are likely to be interested in (Unicon).  Works just fine,
though the implementation (deliberately, by personal preference)
avoids accepting arbitrary SQL statements from SOAP clients,
instead forcing the clients to use an RPC interface so I can
do sanity checking in the Unicon [which I know better than I know
PostgreSQL...] SOAP servers.

I, too, opted for a 'minimal-SOAP' implementation.  A 'real'
implementation boggles the mind.

-- 
Steve Wampler <swampler@noao.edu>
National Solar Observatory


28 Mar 2003 14:47:09 -0700
Steve Wampler <swampler@noao.edu>
Re: PostgreSQL and SOAP, version 7.4/8.0
mlw writes:

> On the flip side, I am also working on a PostgreSQL SOAP interface,
> where one does this:
>
> http://somehost/postgresql?query="select * from table"
>
> And a SOAP compatible resultset is returned.

That looks quite similar to the planned XML functionality.  While that
plan doesn't contain the word "SOAP", one could of course create a small
layer to convert the output format to any other XML format.  If you're
interested, please look up the XML discussion of the last few weeks.

-- 
Peter Eisentraut   peter_e@gmx.net


Sat, 29 Mar 2003 12:26:00 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: PostgreSQL and SOAP, version 7.4/8.0
Given a HTTP formatted query:
GET "http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2"

The output is entered below.

Questions:
Is there a way, without spcifying a binary cursor, to get the data types 
associated with columns? Right now I am just using undefined, as the 
ODBC version works.

Anyone see any basic improvements needed?

<?xml version = "1.0"?>
<soap:Envelope xmlns:MWSSQL="http://www.mohawksoft.com/MWSSQL/envelope">
 <soap:Header>
  <!-- Fields in set -->
  <Columns count="9">
   <muzenbr>undefined</muzenbr>
   <disc>undefined</disc>
   <trk>undefined</trk>
   <song>undefined</song>
   <artistid>undefined</artistid>
   <acd>undefined</acd>
   <trackid>undefined</trackid>
   <datasrc>undefined</datasrc>
   <extid>undefined</extid>
  </Columns>
 </soap:Header>
 <soap:Body>
  <ROWSET columns="9" rows="2">
   <ROW ROWID="0">
    <muzenbr>424965</muzenbr>
    <disc>1</disc>
    <trk>5</trk>
    <song>Write My Name In The Groove</song>
    <artistid>100021391</artistid>
    <acd>A</acd>
    <trackid>203429573</trackid>
    <datasrc>1</datasrc>
    <extid>203429573</extid>
   </ROW>
   <ROW ROWID="1">
    <muzenbr>177516</muzenbr>
    <disc>1</disc>
    <trk>1</trk>
    <song>Papa Was A Rolling Stone</song>
    <artistid>100000411</artistid>
    <acd>P</acd>
    <trackid>200000000</trackid>
    <datasrc>1</datasrc>
    <extid>200000000</extid>
   </ROW>
  </ROWSET>
 </soap:Body>
</soap:Envelope>


Steve Wampler wrote:

>On Fri, 2003-03-28 at 14:39, mlw wrote:
>
>  
>
>>I was thinking of using SOAP over HTTP as the protocol, and a
>>minimalist version at best. If the people want "more" let them add it.
>>
>>I have an HTTP service class in my open source library. It would br
>>trivial to accept a SQL query formatted as a GET request, and then
>>execute the query and, using libpq, format the result as XML. It
>>should be simple enough to do. 
>>    
>>
>
>It would be easy.  I've done something similar (using ODBC to
>get to PostgreSQL) - but using a language none of the rest of
>you are likely to be interested in (Unicon).  Works just fine,
>though the implementation (deliberately, by personal preference)
>avoids accepting arbitrary SQL statements from SOAP clients,
>instead forcing the clients to use an RPC interface so I can
>do sanity checking in the Unicon [which I know better than I know
>PostgreSQL...] SOAP servers.
>
>I, too, opted for a 'minimal-SOAP' implementation.  A 'real'
>implementation boggles the mind.
>
>  
>


Sun, 30 Mar 2003 19:43:12 -0500
mlw <pgsql@mohawksoft.com>
Re: PostgreSQL and SOAP,  suggestions?
mlw kirjutas E, 31.03.2003 kell 03:43:
> Given a HTTP formatted query:
> GET "http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2"
> 
> The output is entered below.
> 
> Questions:
> Is there a way, without spcifying a binary cursor, to get the data types 
> associated with columns? Right now I am just using undefined, as the 
> ODBC version works.
> 
> Anyone see any basic improvements needed?
> 
> <?xml version = "1.0"?>
> <soap:Envelope xmlns:MWSSQL="http://www.mohawksoft.com/MWSSQL/envelope">
>  <soap:Header>
>   <!-- Fields in set -->
>   <Columns count="9">

The SOAP 1.1 spec specifies (p4.2) the following about SOAP Header:

The encoding rules for header entries are as follows: 

     1. A header entry is identified by its fully qualified element
        name, which consists of the namespace URI and the local name.
        All immediate child elements of the SOAP Header element MUST be
        namespace-qualified. 

I'm not sure that SOAP Header is the right place for Query header info,
as the header is meant for:


  SOAP provides a flexible mechanism for extending a message in a 
  decentralized and modular way without prior knowledge between the 
  communicating parties. Typical examples of extensions that can be 
  implemented as header entries are authentication, transaction 
  management, payment etc.

So the definition of structure should probably be inside SOAP:Body .

---------------
Hannu


31 Mar 2003 15:43:18 +0300
Hannu Krosing <hannu@tm.ee>
Re: PostgreSQL and SOAP,  suggestions?
Actually, as far as I am aware, the header is for metadata, i.e. it is the
place to describe the data being returned. The description of the fields
isn't the actual data retrieved, so it doesn't belong in the body, so it
should go into the header.



> mlw kirjutas E, 31.03.2003 kell 03:43:
>> Given a HTTP formatted query:
>> GET "http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2"
>> 
>> The output is entered below.
>> 
>> Questions:
>> Is there a way, without spcifying a binary cursor, to get the data
>> types  associated with columns? Right now I am just using undefined,
>> as the  ODBC version works.
>> 
>> Anyone see any basic improvements needed?
>> 
>> <?xml version = "1.0"?>
>> <soap:Envelope
>> xmlns:MWSSQL="http://www.mohawksoft.com/MWSSQL/envelope">
>>  <soap:Header>
>>   <!-- Fields in set -->
>>   <Columns count="9">
> 
> The SOAP 1.1 spec specifies (p4.2) the following about SOAP Header:
> 
> The encoding rules for header entries are as follows: 
> 
>      1. A header entry is identified by its fully qualified element
>         name, which consists of the namespace URI and the local name.
>         All immediate child elements of the SOAP Header element MUST be
>         namespace-qualified. 
> 
> I'm not sure that SOAP Header is the right place for Query header info,
> as the header is meant for:
> 
> 
>   SOAP provides a flexible mechanism for extending a message in a 
>   decentralized and modular way without prior knowledge between the 
>   communicating parties. Typical examples of extensions that can be 
>   implemented as header entries are authentication, transaction 
>   management, payment etc.
> 
> So the definition of structure should probably be inside SOAP:Body .
> 
> ---------------
> Hannu
> 
> 
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html


Mon, 31 Mar 2003 11:52:03 -0500 (EST)
pgsql@mohawksoft.com
Re: PostgreSQL and SOAP,  suggestions?
pgsql@mohawksoft.com kirjutas E, 31.03.2003 kell 19:52:
> Actually, as far as I am aware, the header is for metadata, i.e. it is the
> place to describe the data being returned.

Did you read the SOAP spec ?

> The description of the fields
> isn't the actual data retrieved, so it doesn't belong in the body, so it
> should go into the header.

That is logical, but this is not what the spec tells.

Also the spec requires immediate child elements of SOAP:Header to have
full namespace URI's.

And another question - why do you have the namespace MWSSQL defined but
never used ?

-------------
Hannu


31 Mar 2003 19:56:54 +0300
Hannu Krosing <hannu@tm.ee>
Re: PostgreSQL and SOAP,  suggestions?


Hannu Krosing wrote:

>pgsql@mohawksoft.com kirjutas E, 31.03.2003 kell 19:52:
>  
>
>>Actually, as far as I am aware, the header is for metadata, i.e. it is the
>>place to describe the data being returned.
>>    
>>
>
>Did you read the SOAP spec ?
>
yes

>
>  
>
>>The description of the fields
>>isn't the actual data retrieved, so it doesn't belong in the body, so it
>>should go into the header.
>>    
>>
>
>That is logical, but this is not what the spec tells.
>
This is exactly what the spec calles for. The spec, at least 1.1, says 
very little about what should not be in the header. For an XML request, 
it should carry. It is very particular about soap header attributes, but 
header contents is very flexable.

>
>Also the spec requires immediate child elements of SOAP:Header to have
>full namespace URI's.
>
Yup, that was a bug.

>
>And another question - why do you have the namespace MWSSQL defined but
>never used ?
>
That was part of the same bug as above, it now outputs this:

<?xml version = "1.0"?>
<mwssql:Envelope xmlns:mwssql="http://www.mohawksoft.com/mwssql/envelope">
 <mwssql:Header>
  <exec:sql>update cgrpairs set ratio=0 where srcitem=100098670</exec:sql>
  <exec:affected>2657</exec:affected>
  <qry:sql>select * from ztitles limit 2</qry:sql>
  <qry:ROWSET>
   <qry:ROW columns="28">
    <t:acd>undefined</t:acd>
    <t:muzenbr>undefined</t:muzenbr>
    <t:cat2>undefined</t:cat2>
    <t:cat3>undefined</t:cat3>
    <t:cat4>undefined</t:cat4>
    <t:performer>undefined</t:performer>
    <t:performer2>undefined</t:performer2>
    <t:title>undefined</t:title>
    <t:artist1>undefined</t:artist1>
    <t:engineer>undefined</t:engineer>
    <t:producer>undefined</t:producer>
    <t:labelname>undefined</t:labelname>
    <t:catalog>undefined</t:catalog>
    <t:distribut>undefined</t:distribut>
    <t:released>undefined</t:released>
    <t:origrel>undefined</t:origrel>
    <t:nbrdiscs>undefined</t:nbrdiscs>
    <t:spar>undefined</t:spar>
    <t:minutes>undefined</t:minutes>
    <t:seconds>undefined</t:seconds>
    <t:monostereo>undefined</t:monostereo>
    <t:studiolive>undefined</t:studiolive>
    <t:available>undefined</t:available>
    <t:previews>undefined</t:previews>
    <t:pnotes>undefined</t:pnotes>
    <t:artistid>undefined</t:artistid>
    <t:datasrc>undefined</t:datasrc>
    <t:extid>undefined</t:extid>
   </qry:ROW>
  </qry:ROWSET>
 </mwssql:Header>
 <mwssql:Body>
  <ROWSET columns="28" rows="2">
   <ROW ROWID="0">
    <acd>P</acd>
    <muzenbr>68291</muzenbr>
    <cat2>Performer</cat2>
    <cat3>Jazz Instrument</cat3>
    <cat4>Guitar</cat4>
    <performer>Steve Khan</performer>
    <performer2>Khan, Steve</performer2>
    <title>Evidence</title>
    <artist1></artist1>
    <engineer></engineer>
    <producer></producer>
    <labelname>Novus</labelname>
    <catalog>3074</catalog>
    <distribut>BMG</distribut>
    <released>02/13/1990</released>
    <origrel>n/a</origrel>
    <nbrdiscs>1</nbrdiscs>
    <spar>n/a</spar>
    <minutes></minutes>
    <seconds></seconds>
    <monostereo>Stereo</monostereo>
    <studiolive>Studio</studiolive>
    <available>N</available>
    <previews></previews>
    <pnotes></pnotes>
    <artistid>100025343</artistid>
    <datasrc>1</datasrc>
    <extid>68291</extid>
   </ROW>
   <ROW ROWID="1">
    <acd>P</acd>
    <muzenbr>67655</muzenbr>
    <cat2>Collection</cat2>
    <cat3>Jazz Instrument</cat3>
    <cat4></cat4>
    <performer>Various Artists</performer>
    <performer2>Various Artists</performer2>
    <title>Metropolitan Opera House Jam Session</title>
    <artist1></artist1>
    <engineer></engineer>
    <producer></producer>
    <labelname>Jazz Anthology</labelname>
    <catalog>550212</catalog>
    <distribut>n/a</distribut>
    <released>1992</released>
    <origrel>n/a</origrel>
    <nbrdiscs>1</nbrdiscs>
    <spar>n/a</spar>
    <minutes></minutes>
    <seconds></seconds>
    <monostereo>Mono</monostereo>
    <studiolive>Live</studiolive>
    <available>N</available>
    <previews></previews>
    <pnotes></pnotes>
    <artistid>100050450</artistid>
    <datasrc>1</datasrc>
    <extid>67655</extid>
   </ROW>
  </ROWSET>
 </mwssql:Body>
</mwssql:Envelope>


Tue, 01 Apr 2003 07:29:51 -0500
mlw <pgsql@mohawksoft.com>
Re: PostgreSQL and SOAP,  suggestions?
Out of curiousity, what is the purpose of putting the qry:ROWSET
description into the message at all (header or not)?  Isn't it a
perfectly valid SOAP message (and just as parseable) with that removed?

I freely admit to not being a soap expert, but similar SOAP
messages I generate from queries seem to work fine without this
metadata.  Is having it required by some part of the SOAP spec
I don't understand?

Thanks!

On Tue, 2003-04-01 at 05:29, mlw wrote:

> That was part of the same bug as above, it now outputs this:
> 
> <?xml version = "1.0"?>
> <mwssql:Envelope xmlns:mwssql="http://www.mohawksoft.com/mwssql/envelope">
>  <mwssql:Header>
>   <exec:sql>update cgrpairs set ratio=0 where srcitem=100098670</exec:sql>
>   <exec:affected>2657</exec:affected>
>   <qry:sql>select * from ztitles limit 2</qry:sql>
>   <qry:ROWSET>
>    <qry:ROW columns="28">
>     <t:acd>undefined</t:acd>
>     <t:muzenbr>undefined</t:muzenbr>
>     <t:cat2>undefined</t:cat2>
>     <t:cat3>undefined</t:cat3>
>     <t:cat4>undefined</t:cat4>
>     <t:performer>undefined</t:performer>
>     <t:performer2>undefined</t:performer2>
>     <t:title>undefined</t:title>
>     <t:artist1>undefined</t:artist1>
>     <t:engineer>undefined</t:engineer>
>     <t:producer>undefined</t:producer>
>     <t:labelname>undefined</t:labelname>
>     <t:catalog>undefined</t:catalog>
>     <t:distribut>undefined</t:distribut>
>     <t:released>undefined</t:released>
>     <t:origrel>undefined</t:origrel>
>     <t:nbrdiscs>undefined</t:nbrdiscs>
>     <t:spar>undefined</t:spar>
>     <t:minutes>undefined</t:minutes>
>     <t:seconds>undefined</t:seconds>
>     <t:monostereo>undefined</t:monostereo>
>     <t:studiolive>undefined</t:studiolive>
>     <t:available>undefined</t:available>
>     <t:previews>undefined</t:previews>
>     <t:pnotes>undefined</t:pnotes>
>     <t:artistid>undefined</t:artistid>
>     <t:datasrc>undefined</t:datasrc>
>     <t:extid>undefined</t:extid>
>    </qry:ROW>
>   </qry:ROWSET>
>  </mwssql:Header>
>  <mwssql:Body>
>   <ROWSET columns="28" rows="2">
>    <ROW ROWID="0">
>     <acd>P</acd>
>     <muzenbr>68291</muzenbr>
>     <cat2>Performer</cat2>
>     <cat3>Jazz Instrument</cat3>
>     <cat4>Guitar</cat4>
>     <performer>Steve Khan</performer>
>     <performer2>Khan, Steve</performer2>
>     <title>Evidence</title>
>     <artist1></artist1>
>     <engineer></engineer>
>     <producer></producer>
>     <labelname>Novus</labelname>
>     <catalog>3074</catalog>
>     <distribut>BMG</distribut>
>     <released>02/13/1990</released>
>     <origrel>n/a</origrel>
>     <nbrdiscs>1</nbrdiscs>
>     <spar>n/a</spar>
>     <minutes></minutes>
>     <seconds></seconds>
>     <monostereo>Stereo</monostereo>
>     <studiolive>Studio</studiolive>
>     <available>N</available>
>     <previews></previews>
>     <pnotes></pnotes>
>     <artistid>100025343</artistid>
>     <datasrc>1</datasrc>
>     <extid>68291</extid>
>    </ROW>
>    <ROW ROWID="1">
>     <acd>P</acd>
>     <muzenbr>67655</muzenbr>
>     <cat2>Collection</cat2>
>     <cat3>Jazz Instrument</cat3>
>     <cat4></cat4>
>     <performer>Various Artists</performer>
>     <performer2>Various Artists</performer2>
>     <title>Metropolitan Opera House Jam Session</title>
>     <artist1></artist1>
>     <engineer></engineer>
>     <producer></producer>
>     <labelname>Jazz Anthology</labelname>
>     <catalog>550212</catalog>
>     <distribut>n/a</distribut>
>     <released>1992</released>
>     <origrel>n/a</origrel>
>     <nbrdiscs>1</nbrdiscs>
>     <spar>n/a</spar>
>     <minutes></minutes>
>     <seconds></seconds>
>     <monostereo>Mono</monostereo>
>     <studiolive>Live</studiolive>
>     <available>N</available>
>     <previews></previews>
>     <pnotes></pnotes>
>     <artistid>100050450</artistid>
>     <datasrc>1</datasrc>
>     <extid>67655</extid>
>    </ROW>
>   </ROWSET>
>  </mwssql:Body>
> </mwssql:Envelope>
-- 
Steve Wampler <swampler@noao.edu>
National Solar Observatory


01 Apr 2003 10:40:24 -0700
Steve Wampler <swampler@noao.edu>
Re: PostgreSQL and SOAP,  suggestions?

I can certainly imagine cases for processing where having the field names
and other metadata up front (maybe add type info, nullable, etc instead of
just "undefined") would be useful.

here's another question:

If the intention is to use field names as (local) tag names, how will you
handle the case where the field name isn't a valid XML name? Of course, one
could do some sort of mapping (replace illegal chars with "_", for example)
but then you can't be 100% certain that you haven't generated a collision,
I should think.

andrew

----- Original Message -----
From: "Steve Wampler" <swampler@noao.edu>
To: "mlw" <pgsql@mohawksoft.com>
Cc: "Hannu Krosing" <hannu@tm.ee>; <jfelice@cronosys.com>;
"Postgres-hackers" <pgsql-hackers@postgresql.org>
Sent: Tuesday, April 01, 2003 12:40 PM
Subject: Re: [HACKERS] PostgreSQL and SOAP, suggestions?


> Out of curiousity, what is the purpose of putting the qry:ROWSET
> description into the message at all (header or not)?  Isn't it a
> perfectly valid SOAP message (and just as parseable) with that removed?
>
> I freely admit to not being a soap expert, but similar SOAP
> messages I generate from queries seem to work fine without this
> metadata.  Is having it required by some part of the SOAP spec
> I don't understand?
>
> Thanks!
>
> On Tue, 2003-04-01 at 05:29, mlw wrote:
>
> > That was part of the same bug as above, it now outputs this:
> >
> > <?xml version = "1.0"?>
> > <mwssql:Envelope
xmlns:mwssql="http://www.mohawksoft.com/mwssql/envelope">
> >  <mwssql:Header>
> >   <exec:sql>update cgrpairs set ratio=0 where
srcitem=100098670</exec:sql>
> >   <exec:affected>2657</exec:affected>
> >   <qry:sql>select * from ztitles limit 2</qry:sql>
> >   <qry:ROWSET>
> >    <qry:ROW columns="28">
> >     <t:acd>undefined</t:acd>
> >     <t:muzenbr>undefined</t:muzenbr>
> >     <t:cat2>undefined</t:cat2>
> >     <t:cat3>undefined</t:cat3>
> >     <t:cat4>undefined</t:cat4>
> >     <t:performer>undefined</t:performer>
> >     <t:performer2>undefined</t:performer2>
> >     <t:title>undefined</t:title>
> >     <t:artist1>undefined</t:artist1>
> >     <t:engineer>undefined</t:engineer>
> >     <t:producer>undefined</t:producer>
> >     <t:labelname>undefined</t:labelname>
> >     <t:catalog>undefined</t:catalog>
> >     <t:distribut>undefined</t:distribut>
> >     <t:released>undefined</t:released>
> >     <t:origrel>undefined</t:origrel>
> >     <t:nbrdiscs>undefined</t:nbrdiscs>
> >     <t:spar>undefined</t:spar>
> >     <t:minutes>undefined</t:minutes>
> >     <t:seconds>undefined</t:seconds>
> >     <t:monostereo>undefined</t:monostereo>
> >     <t:studiolive>undefined</t:studiolive>
> >     <t:available>undefined</t:available>
> >     <t:previews>undefined</t:previews>
> >     <t:pnotes>undefined</t:pnotes>
> >     <t:artistid>undefined</t:artistid>
> >     <t:datasrc>undefined</t:datasrc>
> >     <t:extid>undefined</t:extid>
> >    </qry:ROW>
> >   </qry:ROWSET>
> >  </mwssql:Header>
> >  <mwssql:Body>
> >   <ROWSET columns="28" rows="2">
> >    <ROW ROWID="0">
> >     <acd>P</acd>
> >     <muzenbr>68291</muzenbr>
> >     <cat2>Performer</cat2>
> >     <cat3>Jazz Instrument</cat3>
> >     <cat4>Guitar</cat4>
> >     <performer>Steve Khan</performer>
> >     <performer2>Khan, Steve</performer2>
> >     <title>Evidence</title>
> >     <artist1></artist1>
> >     <engineer></engineer>
> >     <producer></producer>
> >     <labelname>Novus</labelname>
> >     <catalog>3074</catalog>
> >     <distribut>BMG</distribut>
> >     <released>02/13/1990</released>
> >     <origrel>n/a</origrel>
> >     <nbrdiscs>1</nbrdiscs>
> >     <spar>n/a</spar>
> >     <minutes></minutes>
> >     <seconds></seconds>
> >     <monostereo>Stereo</monostereo>
> >     <studiolive>Studio</studiolive>
> >     <available>N</available>
> >     <previews></previews>
> >     <pnotes></pnotes>
> >     <artistid>100025343</artistid>
> >     <datasrc>1</datasrc>
> >     <extid>68291</extid>
> >    </ROW>
> >    <ROW ROWID="1">
> >     <acd>P</acd>
> >     <muzenbr>67655</muzenbr>
> >     <cat2>Collection</cat2>
> >     <cat3>Jazz Instrument</cat3>
> >     <cat4></cat4>
> >     <performer>Various Artists</performer>
> >     <performer2>Various Artists</performer2>
> >     <title>Metropolitan Opera House Jam Session</title>
> >     <artist1></artist1>
> >     <engineer></engineer>
> >     <producer></producer>
> >     <labelname>Jazz Anthology</labelname>
> >     <catalog>550212</catalog>
> >     <distribut>n/a</distribut>
> >     <released>1992</released>
> >     <origrel>n/a</origrel>
> >     <nbrdiscs>1</nbrdiscs>
> >     <spar>n/a</spar>
> >     <minutes></minutes>
> >     <seconds></seconds>
> >     <monostereo>Mono</monostereo>
> >     <studiolive>Live</studiolive>
> >     <available>N</available>
> >     <previews></previews>
> >     <pnotes></pnotes>
> >     <artistid>100050450</artistid>
> >     <datasrc>1</datasrc>
> >     <extid>67655</extid>
> >    </ROW>
> >   </ROWSET>
> >  </mwssql:Body>
> > </mwssql:Envelope>
> --


Tue, 1 Apr 2003 13:08:45 -0500
"Andrew Dunstan" <andrew@dunslane.net>
Re: PostgreSQL and SOAP,  suggestions?
> 
> I can certainly imagine cases for processing where having the field
> names and other metadata up front (maybe add type info, nullable, etc
> instead of just "undefined") would be useful.
> 
> here's another question:
> 
> If the intention is to use field names as (local) tag names, how will
> you handle the case where the field name isn't a valid XML name? Of
> course, one could do some sort of mapping (replace illegal chars with
> "_", for example) but then you can't be 100% certain that you haven't
> generated a collision, I should think.
> 

I'm not sure, I have to really research how to handle that case. I have been
simply doing a %hex translation on characters that do not conform to XML,
that may actually be "good enough(tm)." 

As for the field names being undefined, if you can find a way to get the
field types without having to specify a binary cursor I'd like that.
Admitedly, I have not looked very hard. This is a small part of a bigger
project. The SQL/XML provider currently supports PG and ODBC.

The web services project, which contains the SQL/XML provider, has a bunch
of other services.


Tue, 1 Apr 2003 13:55:26 -0500 (EST)
pgsql@mohawksoft.com
Re: PostgreSQL and SOAP,  suggestions?
mlw writes:

> Given a HTTP formatted query:
> GET "http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2"
>
> The output is entered below.

That looks a lot like the SQL/XML-style output plus a SOAP header.  Below
is the output that I get from the SQL/XML function that I wrote.  A simple
XSLT stylesheet should do the trick for you.

Btw., I also have an XSLT stylesheet that can make an HTML table out of
this output and I have a table function that can generate a virtual table
from this output.


=> select table2xml('select * from products');

<?xml version='1.0'?>
<table
    xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
    xsi:noNamespaceSchemaLocation='#'> <!-- XXX this needs to be fixed -->

<xsd:schema
    xmlns:xsd='http://www.w3.org/2001/XMLSchema'
    xmlns:sqlxml='http://www.iso-standards.org/mra/9075/2001/12/sqlxml'>

  <xsd:import
      namespace='http://www.iso-standards.org/mra/9075/2001/12/sqlxml'
      schemaLocation='http://www.iso-standards.org/mra/9075/2001/12/sqlxml.xsd' />

<xsd:simpleType name='peter.pg_catalog.text'>
  <xsd:restriction base='xsd:string'>
    <xsd:maxLength value='MLIT' /> <!-- XXX needs actual value -->
  </xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name='INTEGER'>
  <xsd:restriction base='xsd:integer'>
    <xsd:maxInclusive value='2147483647'/>
    <xsd:minInclusive value='-2147483648'/>
  </xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name='NUMERIC'>
  <xsd:restriction base='xsd:decimal'>
    <xsd:totalDigits value='PLIT'/> <!-- XXX needs actual values -->
    <xsd:fractionDigits value='SLIT'/>
  </xsd:restriction>
</xsd:simpleType>

<xsd:complexType name='RowType'>
  <xsd:sequence>
    <xsd:element name='name' type='peter.pg_catalog.text' nillable='true'></xsd:element>
    <xsd:element name='category' type='INTEGER' nillable='true'></xsd:element>
    <xsd:element name='price' type='NUMERIC' nillable='true'></xsd:element>
  </xsd:sequence>
</xsd:complexType>

<xsd:complexType name='TableType'>
  <xsd:sequence>
    <xsd:element name='row' type='RowType' minOccurs='0' maxOccurs='unbounded' />
  </xsd:sequence>
</xsd:complexType>

<xsd:element name='table' type='TableType' />

</xsd:schema>

  <row>
    <name>screwdriver</name>
    <category>3</category>
    <price>7.99</price>
  </row>

  <row>
    <name>drill</name>
    <category>9</category>
    <price>12.49</price>
  </row>

</table>

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 1 Apr 2003 23:41:36 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: PostgreSQL and SOAP,  suggestions?
That function looks great, but what happens if you need to return 1 
million records? Wouldn't you exhaust all the memory in the server? Or 
can you stream it somehow?

I have an actual libpq program which performs a query against a server, 
and will stream out the XML, so the number of records has very little 
affect on efficiency. I think the table2xml function is great for 99% of 
all the queries, but for those huge resultsets, I think it may be 
problematic.

What do you think?

BTW, I routinely have queries that return millions of rows.


Peter Eisentraut wrote:

>mlw writes:
>
>  
>
>>Given a HTTP formatted query:
>>GET "http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2"
>>
>>The output is entered below.
>>    
>>
>
>That looks a lot like the SQL/XML-style output plus a SOAP header.  Below
>is the output that I get from the SQL/XML function that I wrote.  A simple
>XSLT stylesheet should do the trick for you.
>
>Btw., I also have an XSLT stylesheet that can make an HTML table out of
>this output and I have a table function that can generate a virtual table
>from this output.
>
>
>=> select table2xml('select * from products');
>
><?xml version='1.0'?>
><table
>    xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
>    xsi:noNamespaceSchemaLocation='#'> <!-- XXX this needs to be fixed -->
>
><xsd:schema
>    xmlns:xsd='http://www.w3.org/2001/XMLSchema'
>    xmlns:sqlxml='http://www.iso-standards.org/mra/9075/2001/12/sqlxml'>
>
>  <xsd:import
>      namespace='http://www.iso-standards.org/mra/9075/2001/12/sqlxml'
>      schemaLocation='http://www.iso-standards.org/mra/9075/2001/12/sqlxml.xsd' />
>
><xsd:simpleType name='peter.pg_catalog.text'>
>  <xsd:restriction base='xsd:string'>
>    <xsd:maxLength value='MLIT' /> <!-- XXX needs actual value -->
>  </xsd:restriction>
></xsd:simpleType>
>
><xsd:simpleType name='INTEGER'>
>  <xsd:restriction base='xsd:integer'>
>    <xsd:maxInclusive value='2147483647'/>
>    <xsd:minInclusive value='-2147483648'/>
>  </xsd:restriction>
></xsd:simpleType>
>
><xsd:simpleType name='NUMERIC'>
>  <xsd:restriction base='xsd:decimal'>
>    <xsd:totalDigits value='PLIT'/> <!-- XXX needs actual values -->
>    <xsd:fractionDigits value='SLIT'/>
>  </xsd:restriction>
></xsd:simpleType>
>
><xsd:complexType name='RowType'>
>  <xsd:sequence>
>    <xsd:element name='name' type='peter.pg_catalog.text' nillable='true'></xsd:element>
>    <xsd:element name='category' type='INTEGER' nillable='true'></xsd:element>
>    <xsd:element name='price' type='NUMERIC' nillable='true'></xsd:element>
>  </xsd:sequence>
></xsd:complexType>
>
><xsd:complexType name='TableType'>
>  <xsd:sequence>
>    <xsd:element name='row' type='RowType' minOccurs='0' maxOccurs='unbounded' />
>  </xsd:sequence>
></xsd:complexType>
>
><xsd:element name='table' type='TableType' />
>
></xsd:schema>
>
>  <row>
>    <name>screwdriver</name>
>    <category>3</category>
>    <price>7.99</price>
>  </row>
>
>  <row>
>    <name>drill</name>
>    <category>9</category>
>    <price>12.49</price>
>  </row>
>
></table>
>
>  
>


Tue, 01 Apr 2003 19:47:55 -0500
mlw <pgsql@mohawksoft.com>
Re: PostgreSQL and SOAP,  suggestions?
mlw kirjutas T, 01.04.2003 kell 15:29:
> Hannu Krosing wrote:
> 
> >pgsql@mohawksoft.com kirjutas E, 31.03.2003 kell 19:52:
> >  
> >
> >>Actually, as far as I am aware, the header is for metadata, i.e. it is the
> >>place to describe the data being returned.
> >>    
> >>
> >
> >Did you read the SOAP spec ?
> >
> yes

???


What you have come up with _is_not_ a SOAP v1.1 message at all. It does
use some elements with similar names but from different namespace.

the SOAP Envelope, Header and Body elemants must be from namespace
http://schemas.xmlsoap.org/soap/envelope/

Per section 3 paragraph 2 of SOAP spec a conforming SOAP processor MUST
discard a message that has incorrect namespace.

> <?xml version = "1.0"?>
> <mwssql:Envelope xmlns:mwssql="http://www.mohawksoft.com/mwssql/envelope">
>  <mwssql:Header>

The <SOAP-ENV:Header> "is a generic mechanism for adding features to a
SOAP message in a decentralized manner without prior agreement between
the communicating parties. SOAP defines a few attributes that can be
used to indicate who should deal with a feature and whether it is
optional or mandatory (see section 4.2)".

The <SOAP-ENV:Body> "is a container for mandatory information intended
for the ultimate recipient of the message (see section 4.3). SOAP
defines one element for the body, which is the Fault element used for
reporting errors."


The Header element is encoded as the first immediate child element of
the SOAP Envelope XML element. All immediate child elements of the
Header element are called header entries.

The encoding rules for header entries are as follows: 

     1. A header entry is identified by its fully qualified element
        name, which consists of the namespace URI and the local name.
        All immediate child elements of the SOAP Header element MUST be
        namespace-qualified.

...

An example is a header with an element identifier of "Transaction", a
"mustUnderstand" value of "1", and a value of 5. This would be encoded
as follows:

<SOAP-ENV:Header>
   <t:Transaction
      xmlns:t="some-URI" SOAP-ENV:mustUnderstand="1">
          5
   </t:Transaction>
</SOAP-ENV:Header>

>   <exec:sql>update cgrpairs set ratio=0 where srcitem=100098670</exec:sql>
>   <exec:affected>2657</exec:affected>
>   <qry:sql>select * from ztitles limit 2</qry:sql>
>   <qry:ROWSET>
>    <qry:ROW columns="28">

where are namespaces exec:, qry: abd t: defined ?

----------------
Hannu


02 Apr 2003 13:29:09 +0300
Hannu Krosing <hannu@tm.ee>
Re: PostgreSQL and SOAP,  suggestions?


Hannu Krosing wrote:

>mlw kirjutas T, 01.04.2003 kell 15:29:
>  
>
>>Hannu Krosing wrote:
>>
>>    
>>
>>>pgsql@mohawksoft.com kirjutas E, 31.03.2003 kell 19:52:
>>> 
>>>
>>>      
>>>
>>>>Actually, as far as I am aware, the header is for metadata, i.e. it is the
>>>>place to describe the data being returned.
>>>>   
>>>>
>>>>        
>>>>
>>>Did you read the SOAP spec ?
>>>
>>>      
>>>
>>yes
>>    
>>
>
>???
>
>
>What you have come up with _is_not_ a SOAP v1.1 message at all. It does
>use some elements with similar names but from different namespace.
>
>the SOAP Envelope, Header and Body elemants must be from namespace
>http://schemas.xmlsoap.org/soap/envelope/
>
[snip]
Hmm, I read "SHOULD" and "MAY" in the spec, assuming that it was not 
"MUST" are you saying it is invalid if I do not use the SOAP URIs for 
the name spaces? If so, no big deal, I'll change them.

As for defining the namespaces, yea that's easy enough, just tack on an 
attribute.

I still don't see where putting the field definitions in the soap header 
is an invalid use of that space.


Wed, 02 Apr 2003 07:56:49 -0500
mlw <pgsql@mohawksoft.com>
Re: PostgreSQL and SOAP,  suggestions?
Andrew Dunstan writes:

> If the intention is to use field names as (local) tag names, how will you
> handle the case where the field name isn't a valid XML name? Of course, one
> could do some sort of mapping (replace illegal chars with "_", for example)
> but then you can't be 100% certain that you haven't generated a collision,
> I should think.

The SQL/XML draft specifies an reversible escape mechanism.  Basically,
when mapping an SQL identifier to an XML name you replace problematic
characters with an escape sequence based on the Unicode code point, like
_x2A3B_.

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 2 Apr 2003 23:40:57 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: PostgreSQL and SOAP,  suggestions?
mlw writes:

> That function looks great, but what happens if you need to return 1
> million records?

The same thing that happens with any set-returning function: memory
exhaustion.

> I have an actual libpq program which performs a query against a server,
> and will stream out the XML, so the number of records has very little
> affect on efficiency. I think the table2xml function is great for 99% of
> all the queries, but for those huge resultsets, I think it may be
> problematic.
>
> What do you think?

Clearly, my approach is not sufficient if you need to handle big result
sets.  But perhaps a compromise based on cursors could be designed so that
large parts of the format can be managed centrally.  Such as:

DECLARE foo CURSOR FOR SELECT ... ;

-- gives you the XML Schema for the result set
SELECT xmlschema_from_cursor(foo);

-- gives you ones row (<row>...</row>)
SELECT xmldata_from_cursor(foo);

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 2 Apr 2003 23:42:55 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: PostgreSQL and SOAP,  suggestions?
mlw kirjutas K, 02.04.2003 kell 15:56:
> Hannu Krosing wrote:
> 
> >What you have come up with _is_not_ a SOAP v1.1 message at all. It does
> >use some elements with similar names but from different namespace.
> >
> >the SOAP Envelope, Header and Body elemants must be from namespace
> >http://schemas.xmlsoap.org/soap/envelope/
> >
> [snip]
> Hmm, I read "SHOULD" and "MAY" in the spec, assuming that it was not 
> "MUST" are you saying it is invalid if I do not use the SOAP URIs for 
> the name spaces? If so, no big deal, I'll change them.

AFAICS you can _leave_out_ the namespace, but not put in another,
nonconforming namespace.

> As for defining the namespaces, yea that's easy enough, just tack on an 
> attribute.
> 
> I still don't see where putting the field definitions in the soap header 
> is an invalid use of that space.

It is not strictly nonconforming, just not the intended use of
"transparently adding" new info:

  4.2 SOAP Header

  SOAP provides a flexible mechanism for extending a message in a
  decentralized and modular way without prior knowledge between the
  communicating parties. Typical examples of extensions that can be
  implemented as header entries are authentication, transaction
  management, payment etc.

I.e. the intended use of *SOAP* Header is *not* defining the structure
of the message but is rather something similar to e-mail (rfc822)
Headers.

The XML way of defining a message is using a DTD, XML-schema, Relax NG
schema or somesuch, either embedded (forbidden for DTD's in SOAP) or
referenced.

Also for me the following:

  The Header element is encoded as the first immediate child element of
  the SOAP Envelope XML element. All immediate child elements of the
  Header element are called header entries.

  The encoding rules for header entries are as follows: 

     1. A header entry is identified by its fully qualified element
        name, which consists of the namespace URI and the local name.
        All immediate child elements of the SOAP Header element MUST be
        namespace-qualified.

describes an element with a full embedded URI, not just
namespace-qualified tagname, but I may be reading it wrong and the
namespace could be defined at outer level. But defining namespace at the
outer level is counterintuitive for cases where the header element is to
be processed and removed by some "SOAP intermediary".

Also this seems to support *not* using Header for essensial structure
definitions:

  4.3.1 Relationship between SOAP Header and Body

  While the Header and Body are defined as independent elements, they
  are in fact related. The relationship between a body entry and a
  header entry is as follows: A body entry is semantically equivalent to
  a header entry intended for the default actor and with a SOAP
  mustUnderstand attribute with a value of "1". The default actor is
  indicated by not using the actor attribute (see section 4.2.2).

This suggests that putting the structure definition as 1-st Body element
and data as second would be equivalent to putting structure in Header

-----------------
Hannu


03 Apr 2003 00:51:56 +0300
Hannu Krosing <hannu@tm.ee>
Re: PostgreSQL and SOAP,  suggestions?


Hannu Krosing wrote:

>mlw kirjutas K, 02.04.2003 kell 15:56:
>  
>
>>Hannu Krosing wrote:
>>
>>    
>>
>>>What you have come up with _is_not_ a SOAP v1.1 message at all. It does
>>>use some elements with similar names but from different namespace.
>>>
>>>the SOAP Envelope, Header and Body elemants must be from namespace
>>>http://schemas.xmlsoap.org/soap/envelope/
>>>
>>>      
>>>
>>[snip]
>>Hmm, I read "SHOULD" and "MAY" in the spec, assuming that it was not 
>>"MUST" are you saying it is invalid if I do not use the SOAP URIs for 
>>the name spaces? If so, no big deal, I'll change them.
>>    
>>
>
>AFAICS you can _leave_out_ the namespace, but not put in another,
>nonconforming namespace.
>
[snip]

I think you are interpreting the spec a bit too restrictively. The 
syntax is fairly rigid, but the spec has a great degree of flexibility. 
I agree that, syntactically, it must work through a parser, but there is 
lots of room to be flexible.


Wed, 02 Apr 2003 17:32:10 -0500
mlw <pgsql@mohawksoft.com>
Re: PostgreSQL and SOAP,  suggestions?
mlw wrote:
> I think you are interpreting the spec a bit too restrictively. The 
> syntax is fairly rigid, but the spec has a great degree of flexibility. 
> I agree that, syntactically, it must work through a parser, but there is 
> lots of room to be flexible.

This is /exactly/ the standard problem with SOAP.

There is enough "flexibility" that there are differing approaches
associated, generally speaking, with "IBM versus Microsoft" whereby it's
easy to generate SOAP requests that work fine with one that break with
the other.

For a pretty simple example of a longstanding bug that has never been
fixed, see:
<http://sourceforge.net/tracker/index.php?func=detail&aid=559324&group_id=26590&atid=387667>

The precis:

The SOAP implementation used by the XMethods folks to publish stock
prices is buggy, rejecting perfectly legitimate messages submitted using
ZSI (a Python SOAP implementation).

The bug isn't with ZSI; it is quite clearly with the server, apparently
implemented in Java using one of the EJB frameworks.  

In practice, what happens is that since that service is fairly popular,
particularly for sample applications, the implementors of SOAP libraries
wind up coding around the bugs.

The problem is that it gets difficult to tell the difference between
bugs and variations in interpretations of the standards.

If the specs were more strictly defined, it would be a lot easier to use
SOAP, because you wouldn't be left puzzling over whether the
interoperability problems you're having are:

 a) Problems with the client;
 b) Problems with the server;
 c) Problems with interpretation of specs;
 d) ...

The vast degree to which messages can get rewritten behind your back
adds to the fun.

Of course, it's only fun if you *enjoy* having interoperability
problems...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www.ntlug.org/~cbbrowne/soap.html
He who laughs last thinks slowest. 


Wed, 02 Apr 2003 18:01:05 -0500
cbbrowne@cbbrowne.com
Re: PostgreSQL and SOAP, suggestions? 
cbbrowne@cbbrowne.com kirjutas N, 03.04.2003 kell 02:01:
> mlw wrote:
> > I think you are interpreting the spec a bit too restrictively. The 
> > syntax is fairly rigid, but the spec has a great degree of flexibility. 
> > I agree that, syntactically, it must work through a parser, but there is 
> > lots of room to be flexible.
> 
> This is /exactly/ the standard problem with SOAP.
> 
> There is enough "flexibility" that there are differing approaches
> associated, generally speaking, with "IBM versus Microsoft" whereby it's
> easy to generate SOAP requests that work fine with one that break with
> the other.

Do you know of some:

a) standard conformance tests

b) recommended best practices for being compatible with all mainstream
implementations (I'd guess a good approach would be to generate very
strictly conformant code but accept all that you can, even if against
pedantic reading of the spec)

-----------------
Hannu


03 Apr 2003 11:20:32 +0300
Hannu Krosing <hannu@tm.ee>
Re: PostgreSQL and SOAP, suggestions?


Hannu Krosing wrote:

>cbbrowne@cbbrowne.com kirjutas N, 03.04.2003 kell 02:01:
>  
>
>>mlw wrote:
>>    
>>
>>>I think you are interpreting the spec a bit too restrictively. The 
>>>syntax is fairly rigid, but the spec has a great degree of flexibility. 
>>>I agree that, syntactically, it must work through a parser, but there is 
>>>lots of room to be flexible.
>>>      
>>>
>>This is /exactly/ the standard problem with SOAP.
>>
>>There is enough "flexibility" that there are differing approaches
>>associated, generally speaking, with "IBM versus Microsoft" whereby it's
>>easy to generate SOAP requests that work fine with one that break with
>>the other.
>>    
>>
>
>Do you know of some:
>
>a) standard conformance tests
>
Off the top of my head, no, but I bet it is a goole away. If you know 
any good links, I'd love to know. I have been working off the W3C spec.

>
>b) recommended best practices for being compatible with all mainstream
>implementations (I'd guess a good approach would be to generate very
>strictly conformant code but accept all that you can, even if against
>pedantic reading of the spec)
>
I have been planning to "test" the whole thing with a few .NET 
applications. I am currently using expat to parse the output to ensure 
that it all works correcty.

>
>  
>

>  
>


Thu, 03 Apr 2003 07:38:53 -0500
mlw <pgsql@mohawksoft.com>
Re: PostgreSQL and SOAP, suggestions?
> cbbrowne@cbbrowne.com kirjutas N, 03.04.2003 kell 02:01:
> > mlw wrote:
> > > I think you are interpreting the spec a bit too restrictively. The 
> > > syntax is fairly rigid, but the spec has a great degree of flexibility. 
> > > I agree that, syntactically, it must work through a parser, but there is 
> > > lots of room to be flexible.
> > 
> > This is /exactly/ the standard problem with SOAP.
> > 
> > There is enough "flexibility" that there are differing approaches
> > associated, generally speaking, with "IBM versus Microsoft" whereby it's
> > easy to generate SOAP requests that work fine with one that break with
> > the other.
> 
> Do you know of some:
> 
> a) standard conformance tests
> 
> b) recommended best practices for being compatible with all mainstream
> implementations (I'd guess a good approach would be to generate very
> strictly conformant code but accept all that you can, even if against
> pedantic reading of the spec)

The problem with a) is that SOAP, unlike CORBA, doesn't have the notion of 
standardized language bindings.  That makes it tough to be sure that your 
implementation is "standard" in any meaningful way in the first place.

The "best practices" have involved scripting up interoperability tests where 
they construct sets of functions with varying data types and verify that "my 
client implementation can talk to your server implementation," and vice-versa.

And when you run into problems, you chip off bits of code until the block of 
stone starts looking like an elephant.

In order to have confidence of interoperability, you have to test your client 
library against all the servers you care about, or vice-versa.  That's 
definitely not the same thing as being a "conformance" test.

Trying to be "really strict" doesn't seem to be a viable strategy, as far as I 
can see...
--
(concatenate 'string "cbbrowne" "@ntlug.org")
http://www3.sympatico.ca/cbbrowne/wp.html
"The cost of living has just gone up another dollar a quart."  
-- W.C. Fields


Thu, 03 Apr 2003 07:44:50 -0500
cbbrowne@cbbrowne.com
Re: PostgreSQL and SOAP, suggestions? 
> I have been planning to "test" the whole thing with a few .NET 
> applications. I am currently using expat to parse the output to ensure 
> that it all works correcty.

That, unfortunately, probably implies that your implementation is almost 
totally non-interoperable.

You should put out of your mind the notion of being "correct."  Being 
"correct" is pretty irrelevant if 80% of the requests that come from a VB.NET 
client fail because Microsoft implemented part of their request differently 
than what you interpreted as "correct."

The point is that "correctness" isn't the thing you need to aim for; what you 
should aim for is interoperability with the important client implementations.

SOAP::Lite, .NET, probably some Java ones, C++ ones, and such.

Nobody does "correctness" testing; they do interoperability tests where they 
try to submit requests to Apache AXIS, .NET, WebSphere, and the lot of other 
important implementations.  If you're testing a server (as is the case here), 
then the point is to run tests with a bunch of clients.

Head to the SOAP::Lite and Axis projects; you'll see matrices describing this 
sort of thing...
--
(reverse (concatenate 'string "ac.notelrac.teneerf@" "454aa"))
http://www.ntlug.org/~cbbrowne/advocacy.html
"Fear leads to anger. Anger leads to hate. Hate leads to using Windows
NT for mission-critical applications."  --- What Yoda *meant* to say


Thu, 03 Apr 2003 07:54:13 -0500
cbbrowne@cbbrowne.com
Re: PostgreSQL and SOAP, suggestions? 
On Thu, Apr 03, 2003 at 07:54:13AM -0500, cbbrowne@cbbrowne.com wrote:
> > I have been planning to "test" the whole thing with a few .NET 
> > applications. I am currently using expat to parse the output to ensure 
> > that it all works correcty.
> 
> That, unfortunately, probably implies that your implementation is almost 
> totally non-interoperable.
> 
> You should put out of your mind the notion of being "correct."  Being 
> "correct" is pretty irrelevant if 80% of the requests that come from a VB.NET 
> client fail because Microsoft implemented part of their request differently 
> than what you interpreted as "correct."
> 
> The point is that "correctness" isn't the thing you need to aim for; what you 
> should aim for is interoperability with the important client implementations.
> 
> SOAP::Lite, .NET, probably some Java ones, C++ ones, and such.
> 
> Nobody does "correctness" testing; they do interoperability tests where they 
> try to submit requests to Apache AXIS, .NET, WebSphere, and the lot of other 
> important implementations.  If you're testing a server (as is the case here), 
> then the point is to run tests with a bunch of clients.
> 
> Head to the SOAP::Lite and Axis projects; you'll see matrices describing this 
> sort of thing...

Hmmm.  Can I reiterate my support of XML-RPC here?  <g>

-Jay 'Eraserhead' Felice

> --
> (reverse (concatenate 'string "ac.notelrac.teneerf@" "454aa"))
> http://www.ntlug.org/~cbbrowne/advocacy.html
> "Fear leads to anger. Anger leads to hate. Hate leads to using Windows
> NT for mission-critical applications."  --- What Yoda *meant* to say
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


Thu, 3 Apr 2003 10:19:41 -0500
"Jason M. Felice" <jfelice@cronosys.com>
Re: PostgreSQL and SOAP, suggestions?