Re: [HACKERS] SQLJ - Mailing list pgsql-hackers
From | jwieck@debis.com (Jan Wieck) |
---|---|
Subject | Re: [HACKERS] SQLJ |
Date | |
Msg-id | m0zxCsJ-000EBQC@orion.SAPserv.Hamburg.dsh.de Whole thread Raw |
In response to | Re: [HACKERS] SQLJ (Constantin Teodorescu <teo@flex.ro>) |
List | pgsql-hackers |
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) #
pgsql-hackers by date: