Thread: SQLJ

SQLJ

From
Dan Gowin
Date:
All,I've just read in DBMS magazine that Oracle, Informix, etc...
are replacing there proprietary Procedural Language's (PL/SQL) with
JVMs (JAVA virtual machine). Apparently according to the database
vendors, this will obviate the need for middle ware products and to
make CORBA much more intuitive.

My personal opinion is this is yet another mechanism to slow down
the database engine. I don't mind using JAVA in the database engine
as a procedural language, but I think there is still great merit in
breaking
this out as a separate process (Application Server).

D.


Re: [HACKERS] SQLJ

From
Constantin Teodorescu
Date:
Dan Gowin wrote:
> 
>       I've just read in DBMS magazine that Oracle, Informix, etc...
> are replacing there proprietary Procedural Language's (PL/SQL) with
> JVMs (JAVA virtual machine). Apparently according to the database
> vendors, this will obviate the need for middle ware products and to
> make CORBA much more intuitive.

This was a problem that I also wanted to talk about.
> My personal opinion is this is yet another mechanism to slow down
> the database engine. I don't mind using JAVA in the database engine
> as a procedural language, but I think there is still great merit in
> breaking
> this out as a separate process (Application Server).

I don't think that it would be slower than PlTcl for example.

Talking about speed:

I made some tests with some procedures written in PlPgsql and PlTcl!
I understood also that PlPgsql is compiling the procedure body into a
sort of bytecodes and the execution is somewhat faster.
In PlTcl, I feel (I didn't poke the sources) that a PlTcl procedure body
is interpreted each time at execution time. For this reason, it doesn't
beneffit from Tcl 8.x compiler, making it slowly than PlPgsql. Am I
right ?

The first question is : Is Tcl 8.x able to precompile a script and
return the bytecodes that should be kept into the database and execute
them later?

>From this point of view, I think that compiling a Java language
procedure and storing the resulting .class might be at execution time as
faster as PlPgsql. And , I think, perfectly possible.

Right now, I am working at a big project using :
1. Tcl/Tk as thin client
2. Messaging server written in Java as a middle-tier with JDBC
3. PostgreSQL RDBMS

It would be perfect from me if PostgreSQL will allow procedures written
in Java language.

-- 
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA


Re: [HACKERS] SQLJ

From
jwieck@debis.com (Jan Wieck)
Date:
Constantin Teodorescu wrote:

>
> Dan Gowin wrote:
> [...]
>
> > My personal opinion is this is yet another mechanism to slow down
> > the database engine. I don't mind using JAVA in the database engine
> > as a procedural language, but I think there is still great merit in
> > breaking
> > this out as a separate process (Application Server).
>
> I don't think that it would be slower than PlTcl for example.
>
> Talking about speed:
>
> I made some tests with some procedures written in PlPgsql and PlTcl!
> I understood also that PlPgsql is compiling the procedure body into a
> sort of bytecodes and the execution is somewhat faster.
> In PlTcl, I feel (I didn't poke the sources) that a PlTcl procedure body
> is interpreted each time at execution time. For this reason, it doesn't
> beneffit from Tcl 8.x compiler, making it slowly than PlPgsql. Am I
> right ?
>
> The first question is : Is Tcl 8.x able to precompile a script and
> return the bytecodes that should be kept into the database and execute
> them later?

    Speaking  about  the  versions of PL/pgSQL and PL/Tcl shipped
    with Postgres v6.4 (and bugfix releases).

    PL/pgSQL does precompile the source of the function body  the
    first  time,  a  function is called in a backend. For most of
    the statements, even if they are  a  simple  assignment  like
    "var  :=  0;",  a  prepared SPI statement get's created. This
    makes PL/pgSQL damned slow and I plan  to  optimize  that  by
    handling  simple  expressions  that  have only one targetlist
    entry directly instead of using the SPI manager.

    PL/Tcl uses the precompiler of Tcl8.0. Inside the PL  handler
    module, a safe Tcl interpreter is created at the first of all
    calls to one function written in PL/Tcl. All functions called
    are  somewhat  "sourced"  into the safe interpreter. Thus, on
    multiple invocations  of  one  and  the  same  function,  the
    Interpreter does reuse the precompiled bytecode it is holding
    inside for the procedures. But the  programmer  must  arrange
    for  usage of prepared and saved SPI execution plans for data
    access. The Tcl  command  "spi_exec",  available  in  PL/Tcl,
    really   calls  SPI_exec  -  resulting  in  a  parse-rewrite-
    optimize-execute  sequence  for  the  statement  every  time.
    Using spi_prepare (only on first call of the PL/Tcl function)
    and spi_execp instead will gain much performance increase!

    Another area that should be optimized sometimes in PL/Tcl  is
    using the object interface which came with Tcl8.0. Up to now,
    the PL/Tcl handler uses the string level C-API only.

    I don't think it would be  good  to  remember  the  generated
    bytecode  of  Tcl itself. This would mean to muck around with
    internals of Tcl  objects.  A  clear  violation  of  the  OOP
    paradigm.

    Both  languages have performance problems in scenarios like a
    WEB environment. By default, any WEB  access  creates  a  new
    Postgres backend, that has to compile the functions used.

>
> >From this point of view, I think that compiling a Java language
> procedure and storing the resulting .class might be at execution time as
> faster as PlPgsql. And , I think, perfectly possible.

    Should  (and could) use the same interfacing mechanism as is.
    It is just another loadable procedural language handler.  And
    the  design  changes  we  did in the fmgr for v6.3.2 where to
    enable these language handlers.

>
> Right now, I am working at a big project using :
> 1. Tcl/Tk as thin client
> 2. Messaging server written in Java as a middle-tier with JDBC
> 3. PostgreSQL RDBMS
>
> It would be perfect from me if PostgreSQL will allow procedures written
> in Java language.

    PL/Tcl and PL/pgSQL are the  only  two  languages  available,
    because  noone else than me did anything in that corner up to
    now. You're welcome :-).


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Re: [HACKERS] SQLJ

From
Constantin Teodorescu
Date:
Jan Wieck wrote:
> 
> PL/Tcl and PL/pgSQL are the  only  two  languages  available,
> because  noone else than me did anything in that corner up to
> now. You're welcome :-).

:-)

Yeap! Easy to say! Hard to do!

I'm not a "core" programmer. I am rather new to Java (only 2 weeks), and
I don't know anything about inside PostgreSQL structures.
We would need someone with much more expertise in "C", "C++", Java and
PostgreSQL than me.

-- 
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA


Re: [HACKERS] SQLJ

From
Tatsuo Ishii
Date:
>Jan Wieck wrote:
>> 
>> PL/Tcl and PL/pgSQL are the  only  two  languages  available,
>> because  noone else than me did anything in that corner up to
>> now. You're welcome :-).
>
>:-)
>
>Yeap! Easy to say! Hard to do!
>
>I'm not a "core" programmer. I am rather new to Java (only 2 weeks), and
>I don't know anything about inside PostgreSQL structures.
>We would need someone with much more expertise in "C", "C++", Java and
>PostgreSQL than me.

I've been interested in this topic too. My idea is having JVM as a
separate process from the backend. Maybe JServ(Apache's servlet
extension package) is a good starting point. Also it would be nice if
the backend could use some functionalities of Java, such as encoding
conversions.

I am by no means a Java guru, thus this would be a great challenge for
me:-)
--
Tatsuo Ishii


Re: [HACKERS] SQLJ

From
Peter T Mount
Date:
On Wed, 6 Jan 1999, Tatsuo Ishii wrote:

> >Jan Wieck wrote:
> >> 
> >> PL/Tcl and PL/pgSQL are the  only  two  languages  available,
> >> because  noone else than me did anything in that corner up to
> >> now. You're welcome :-).
> >
> >:-)
> >
> >Yeap! Easy to say! Hard to do!
> >
> >I'm not a "core" programmer. I am rather new to Java (only 2 weeks), and
> >I don't know anything about inside PostgreSQL structures.
> >We would need someone with much more expertise in "C", "C++", Java and
> >PostgreSQL than me.
> 
> I've been interested in this topic too. My idea is having JVM as a
> separate process from the backend. Maybe JServ(Apache's servlet
> extension package) is a good starting point. Also it would be nice if
> the backend could use some functionalities of Java, such as encoding
> conversions.
> 
> I am by no means a Java guru, thus this would be a great challenge for
> me:-)

I havent touched the native side of Java (have stuck with just pure java
so far, and will probably stay that way), but from what I understand on
how JNI works:

1. If say PL/Java was implemented _AND_ enabled, then when the postmaster
is first started, you can start a JVM as a separate process at that time.
While waiting for connections, this JVM would be sitting dormant.

2. When a connection is made to the backend, a Thread (Java not native) is
started in the JVM, which creates any objects needed by the PL/Java
code. (For performance sake, I'd think this would be done by using a
parameter set in the database - that way db's without the need of PL/Java
support wouldn't be affected.

3. When a call to a PL/Java procedure is made, then a fresh Thread is made
in the JVM, and calls the method to handle the procedure.

Obviously this scheme would need the PL/Java functions pre-compiled using
the JDK, and the class files (or better still as a single jar file) being
introduced to the database.

I'd thought that running EcmaScript (what is commonly known as JavaScript)
would be too slow.

>From my tests using the latest JVM, at least under Windows, it's
performance has improved tremendously. It's still slow in starting up, but
once running it's pretty good. Also, when running without a GUI, it's
better still.

Another plus would be CORBA. If, using JNI we had a way of getting at SPI
functions, then you could create objects in Java that fed directly into
the backend. It would be limited (as only a small part of CORBA 2.0 is in
JDK 1.2), but it would be a start.

--       Peter T Mount peter@retep.org.uk     Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgresJava PDF Generator: http://www.retep.org.uk/pdf