Hi,

I'm wondering about alternative accesses to a PostgreSQL data base
by means other than SQL. I know one can map many things to SQL, but
let me think outside the box for just a moment:

- Sending a parse tree in XML for processing by the optimizer.
   This circumvents the SQL language and avoids the kinds of
   syntactic ideosyncrasies of SQL (e.g., where you put commas.)
   This is fairly trivial, but of course the question is, would
   it be worth it?

- Sending an execution plan in XML directly to the executor.
   This now circumvents the SQL parser and optimizer. I know this
   in in a way against the relational doxology and I don't take that
   light-heartedly. However, isn't it true that most optimizers
   cannot deal very well with more than 6 joins? I may be wrong,
   but I find myself spending quite a bit of time fighting with the
   Oracle or PostgreSQL optimizer to convince it to choose the plan
   that I want. There is so much magic to it with hints and the
   way you write SQL (where in relational theory the expressions are
   equivalent, they make huge difference in what plan is being
   generated.) So, it appears to me almost easier to just send a
   plan directly and have the system execute that plan.

- These direct interfaces could be a nice way to experiment with
   new strategies without having to implement it on all three
   layers (SQL language, optimizer, and executor.)

You noticed I sneaked in XML as the interface, and that would be
neat because with XSLT it's so easy to manipulate. But I'm also
thinking about a Prolog binding or constraint logic programming
binding, that might be better optimizeable if it goes through a
more direct path than SQL.

Am I crazy?
-Gunther

-- 
Gunther Schadow, M.D., Ph.D.                    gschadow@regenstrief.org
Medical Information Scientist      Regenstrief Institute for Health Care
Adjunct Assistant Professor        Indiana University School of Medicine
tel:1(317)630-7960                         http://aurora.regenstrief.org



Fri, 24 May 2002 12:43:36 -0500
Gunther Schadow <gunther@aurora.regenstrief.org>
Alternatives to SQL ...

The SQL interface is bullet-proof because it validates tables, computes
offsets, and stuff like that.  Passing something else into the database
and bypassing the SQL stage would require rewriting all the C logic for
SQL to match your new language --- a lot of work for little gain.

---------------------------------------------------------------------------

Gunther Schadow wrote:
> Hi,
> 
> I'm wondering about alternative accesses to a PostgreSQL data base
> by means other than SQL. I know one can map many things to SQL, but
> let me think outside the box for just a moment:
> 
> - Sending a parse tree in XML for processing by the optimizer.
>    This circumvents the SQL language and avoids the kinds of
>    syntactic ideosyncrasies of SQL (e.g., where you put commas.)
>    This is fairly trivial, but of course the question is, would
>    it be worth it?
> 
> - Sending an execution plan in XML directly to the executor.
>    This now circumvents the SQL parser and optimizer. I know this
>    in in a way against the relational doxology and I don't take that
>    light-heartedly. However, isn't it true that most optimizers
>    cannot deal very well with more than 6 joins? I may be wrong,
>    but I find myself spending quite a bit of time fighting with the
>    Oracle or PostgreSQL optimizer to convince it to choose the plan
>    that I want. There is so much magic to it with hints and the
>    way you write SQL (where in relational theory the expressions are
>    equivalent, they make huge difference in what plan is being
>    generated.) So, it appears to me almost easier to just send a
>    plan directly and have the system execute that plan.
> 
> - These direct interfaces could be a nice way to experiment with
>    new strategies without having to implement it on all three
>    layers (SQL language, optimizer, and executor.)
> 
> You noticed I sneaked in XML as the interface, and that would be
> neat because with XSLT it's so easy to manipulate. But I'm also
> thinking about a Prolog binding or constraint logic programming
> binding, that might be better optimizeable if it goes through a
> more direct path than SQL.
> 
> Am I crazy?
> -Gunther
> 
> -- 
> Gunther Schadow, M.D., Ph.D.                    gschadow@regenstrief.org
> Medical Information Scientist      Regenstrief Institute for Health Care
> Adjunct Assistant Professor        Indiana University School of Medicine
> tel:1(317)630-7960                         http://aurora.regenstrief.org
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

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

Fri, 7 Jun 2002 13:51:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Alternatives to SQL ...
On Fri, May 24, 2002 at 12:43:36PM -0500, Gunther Schadow wrote:
> - Sending a parse tree in XML for processing by the optimizer.
>    This circumvents the SQL language and avoids the kinds of
>    syntactic ideosyncrasies of SQL (e.g., where you put commas.)
>    This is fairly trivial, but of course the question is, would
>    it be worth it?

I don't know if you can design something in XML that is expressive and
simple enough to compete with SQL. SQL is a simple language, why replace it
with something unless it is demonstrably better.

> - Sending an execution plan in XML directly to the executor.
>    This now circumvents the SQL parser and optimizer. I know this
>    in in a way against the relational doxology and I don't take that
>    light-heartedly. However, isn't it true that most optimizers
>    cannot deal very well with more than 6 joins? I may be wrong,
>    but I find myself spending quite a bit of time fighting with the
>    Oracle or PostgreSQL optimizer to convince it to choose the plan
>    that I want. There is so much magic to it with hints and the
>    way you write SQL (where in relational theory the expressions are
>    equivalent, they make huge difference in what plan is being
>    generated.) So, it appears to me almost easier to just send a
>    plan directly and have the system execute that plan.

The detail contained in plans is quite substantial (as you can see using
EXPLAIN VERBOSE). I doubt you can rely on programmers getting all the
details right. As for the join problem, some people get good results tweaking
the genetic query optimiser using documented interfaces. And if you don't
like the way the tables are joined, the INNER/OUTER/LEFT/RIGHT JOIN syntax
in SQL allows you to force the order.
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Sat, 8 Jun 2002 13:31:00 +1000
Martijn van Oosterhout <kleptog@svana.org>
Re: Alternatives to SQL ...
The world rejoiced as kleptog@svana.org (Martijn van Oosterhout) wrote:
> On Fri, May 24, 2002 at 12:43:36PM -0500, Gunther Schadow wrote:
>> - Sending a parse tree in XML for processing by the optimizer.
>>    This circumvents the SQL language and avoids the kinds of
>>    syntactic ideosyncrasies of SQL (e.g., where you put commas.)
>>    This is fairly trivial, but of course the question is, would it
>>    be worth it?
X-Mailer: mh-e 6.1; nmh 1.0.4+dev; Emacs 21.4

> I don't know if you can design something in XML that is expressive
> and simple enough to compete with SQL. SQL is a simple language, why
> replace it with something unless it is demonstrably better.

SQL is good at providing "linear" queries; queries that indicate some
"linear" relationship between elements.

It is not so good at representing hierarchical relationships, which is
what XML is about.

The SQL: 
   SELECT FIELDS FROM TABLE
provides you with a linear list.

SQL isn't _nearly_ as nice at representing things that are naturally
expressed as trees.  It's pretty easy to have a DB schema where you
essentially have to submit an SQL query for every level of the tree.

And I am not ignoring JOIN here; that adds _some_ ability to join
together levels of trees, but not an unlimited ability.

The XML model fundamentally involves a hierarchy, and the 'query
method' involves passing in a function that reshapes that hierarchy.
I think there would be considerable value to that.

It certainly needs to be thought about before it is implemented, but
it's worth thinking about, to be sure.
-- 
(reverse (concatenate 'string "gro.gultn@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/multiplexor.html
"It  is easier to move a  problem around  (for  example, by moving the
problem to a different part  of the overall network architecture) than
it is to solve it."  -- RFC 1925

Sat, 08 Jun 2002 12:57:45 -0400
cbbrowne@cbbrowne.com
Re: Alternatives to SQL ...