Thread: Implementing SQL/PSM for PG 8.2

"Denis Lussier" <denis@enterprisedb.com> writes:
> For various technical and backward compatibility reasons, I don't think
> SQL/PSM should be a replacement for PL/pgSQL.  Although I do think it
> should heavily leverage the solid foundation afforded by the PL/pgSQL
> code base.

"Solid"?  I've wanted for quite some time to throw away plpgsql and
start over --- there are too many things that need rewritten in it,
starting with the parser.  This project would be a great place to do
that.
        regards, tom lane

On Sun, 26 Jun 2005, Tom Lane wrote:

> "Denis Lussier" <denis@enterprisedb.com> writes:
> > For various technical and backward compatibility reasons, I don't think
> > SQL/PSM should be a replacement for PL/pgSQL.  Although I do think it
> > should heavily leverage the solid foundation afforded by the PL/pgSQL
> > code base.
> 
> "Solid"?  I've wanted for quite some time to throw away plpgsql and
> start over --- there are too many things that need rewritten in it,
> starting with the parser.  This project would be a great place to do
> that.

What is wrong on plpgsql code? I see some problems with processing SQL 
statements, with efectivity evaluation of expr, but parser is clean (in my 
opinion). 

what have to be rewriten?

Regards
Pavel Stehule

Is the intention here to make PSM a first class language (i.e. handled 
by the main dbengine scanner/parser) of just another PL? If the latter 
it seems far less worth doing. Doing this as a first class language, 
however, would be great, just great.

As for pgfoundry, I think it's fair to say (from my various perspectives 
as a pgfoundry admin, owner of a PL project there, and general hacker) 
that experience is mixed on things that have close backend integration 
requirements. In particular, I would advise you to conduct pretty much 
all discussions abou the project on the -hackers list for a project like 
this. That way you avoid giving anyone surprises, and you will get the 
best and most wide-ranging advice and feedback.

cheers

andrew

Denis Lussier wrote:

>Hi All,
> 
>My company (EnterpriseDB) is very interested in helping to make ANSI-ISO SQL Stored Procedures part of standard BSD
Postgres. The SQL/PSM standard is currently used in DB2 and is being implemented in MySQL 5.0.   Note that I'm NOT a
bigfan of adding Oracle compatibility to PL/pgSQL, but, I'm biased in this regard because EnterpriseDB's SPL (Superset
ProceduralLanguage) supports Redwood (pl/sql) and Redmond (transact-sql) style programming.
 
> 
>For various technical and backward compatibility reasons, I don't think SQL/PSM should be a replacement for PL/pgSQL.
AlthoughI do think it should heavily leverage the solid foundation afforded by the PL/pgSQL code base.  I think it
shouldstart as a separate project on PgFoundry.   Once it is working and fully tested and rock solid and proven...  I
thinkit should then be considered to become part of the core & installed by default alongside plpgsql.   
 
> 
>Please note that this is all appropriate for 8.2, because changes to the server side code are necessary to support
ANSIstored proc signatures and flexible out/inout parameter passing.   EnterpriseDB will publish those suggested server
changesfor review so that work can begin on plsqlpsm sooner rather than later.
 
> 
>What do y'all think??   I believe the first step is for us to create "plsqlpsm" as a BSD project in PgFoundry.
> 
>--Luss
> 
>
>  
>

On Sun, Jun 26, 2005 at 04:44:13PM -0400, Andrew Dunstan wrote:

> Is the intention here to make PSM a first class language (i.e. handled 
> by the main dbengine scanner/parser) of just another PL? If the latter 
> it seems far less worth doing. Doing this as a first class language, 
> however, would be great, just great.

I've seen some example code on the EnterpriseDB website using their SPL
language, and it doesn't seem to be handled like "just another PL".  The
function body does not look at all like quoted strings, as in our
regular PLs.  I don't know how they did it, but I don't think they added
support for the whole language to the main parser.


-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu.  Five minutes later I realize that it's also talking
about food" (Donald Knuth)

Alvaro Herrera wrote:

>On Sun, Jun 26, 2005 at 04:44:13PM -0400, Andrew Dunstan wrote:
>
>  
>
>>Is the intention here to make PSM a first class language (i.e. handled 
>>by the main dbengine scanner/parser) of just another PL? If the latter 
>>it seems far less worth doing. Doing this as a first class language, 
>>however, would be great, just great.
>>    
>>
>
>I've seen some example code on the EnterpriseDB website using their SPL
>language, and it doesn't seem to be handled like "just another PL".  The
>function body does not look at all like quoted strings, as in our
>regular PLs.  I don't know how they did it, but I don't think they added
>support for the whole language to the main parser.
>
>  
>

It could be done by putting the SPL parser in front of the SQL parser. 
Maybe Luss will tell us how it was done ;-)

cheers

andrew

On 6/26/2005 4:10 PM, Pavel Stehule wrote:

> On Sun, 26 Jun 2005, Tom Lane wrote:
> 
>> "Denis Lussier" <denis@enterprisedb.com> writes:
>> > For various technical and backward compatibility reasons, I don't think
>> > SQL/PSM should be a replacement for PL/pgSQL.  Although I do think it
>> > should heavily leverage the solid foundation afforded by the PL/pgSQL
>> > code base.
>> 
>> "Solid"?  I've wanted for quite some time to throw away plpgsql and
>> start over --- there are too many things that need rewritten in it,
>> starting with the parser.  This project would be a great place to do
>> that.
>
> What is wrong on plpgsql code? I see some problems with processing SQL 
> statements, with efectivity evaluation of expr, but parser is clean (in my 
> opinion). 

The whole parser is a hack that attempts to parse the procedural parts 
of the function but preserving the SQL parts as query strings while 
substituting variables with numbered parameters. That is anything but 
clean. It was the only way I saw at the time of implementation to build 
a parser that automatically supports future changes of the main Postgres 
query language. But that doesn't mean that I like the implementation.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

I agree with Jan,

As part of my own projects I had to deal with the PL/pgSQL parser.  
While it was a workable design at the beginning, it now makes some 
things harder with the quoting etc.

Don't get me wrong, I've never really had any beef with PL/pgSQL, it has 
worked great for a long time but I think it could definitely use a rewrite.

I don't recommend discussion for this in this thread, but it could also 
tie in with the packages support we've discussed and (although some may 
argue this), compiling the PL to bytecode and using that.

-Jonah

Jan Wieck wrote:

> On 6/26/2005 4:10 PM, Pavel Stehule wrote:
>
>> On Sun, 26 Jun 2005, Tom Lane wrote:
>>
>>> "Denis Lussier" <denis@enterprisedb.com> writes:
>>> > For various technical and backward compatibility reasons, I don't 
>>> think
>>> > SQL/PSM should be a replacement for PL/pgSQL.  Although I do think it
>>> > should heavily leverage the solid foundation afforded by the PL/pgSQL
>>> > code base.
>>>
>>> "Solid"?  I've wanted for quite some time to throw away plpgsql and
>>> start over --- there are too many things that need rewritten in it,
>>> starting with the parser.  This project would be a great place to do
>>> that.
>>
>>
>> What is wrong on plpgsql code? I see some problems with processing 
>> SQL statements, with efectivity evaluation of expr, but parser is 
>> clean (in my opinion). 
>
>
> The whole parser is a hack that attempts to parse the procedural parts 
> of the function but preserving the SQL parts as query strings while 
> substituting variables with numbered parameters. That is anything but 
> clean. It was the only way I saw at the time of implementation to 
> build a parser that automatically supports future changes of the main 
> Postgres query language. But that doesn't mean that I like the 
> implementation.
>
>
> Jan
>

Jonah H. Harris wrote:
> I don't recommend discussion for this in this thread, but it could also 
> tie in with the packages support we've discussed and (although some may 
> argue this), compiling the PL to bytecode and using that.

How would compilation to bytecode help?

-Neil

Jan Wieck wrote:
> The whole parser is a hack that attempts to parse the procedural parts 
> of the function but preserving the SQL parts as query strings while 
> substituting variables with numbered parameters. That is anything but 
> clean. It was the only way I saw at the time of implementation to build 
> a parser that automatically supports future changes of the main Postgres 
> query language.

I agree the current parser is a hack, but it's difficult to see how else 
it could be implemented. One possibility I've mentioned in the past is 
to rewrite the main SQL parser by hand (e.g. as a recursive descent 
parser), so that we could directly call into the main SQL parser from 
the PL/PgSQL parser. I believe that would let us embed SQL in PL/PgSQL 
without needing to teach the PL/PgSQL anything about the main SQL 
grammar. But of course this has the downside of needing to write and 
maintain a recursive descent parser.

Any better ideas?

-Neil

On Tue, 2005-06-28 at 10:40 +1000, Neil Conway wrote:
> Jan Wieck wrote:
> > The whole parser is a hack that attempts to parse the procedural parts 
> > of the function but preserving the SQL parts as query strings while 
> > substituting variables with numbered parameters. That is anything but 
> > clean. It was the only way I saw at the time of implementation to build 
> > a parser that automatically supports future changes of the main Postgres 
> > query language.
> 
> I agree the current parser is a hack, but it's difficult to see how else 
> it could be implemented. One possibility I've mentioned in the past is 

Could the reverse be done? Combine the PL/PgSQL and SQL grammar for the
main parser (thus allowing procedural logic in standard SQL locations)
and perhaps for the other PLs they can hook into a specific statement
grammar which is a subset of the PL/PgSQL grammar by prefixing a keyword
-- say EXECUTE to their strings.

I would like to have some logic in psql, much as you can build simple
loops and logic with shell on the command line on the fly.
-- 

A long time ago, in a galaxy far, far away, jharris@tvi.edu ("Jonah H. Harris") wrote:
> I don't recommend discussion for this in this thread, but it could
> also tie in with the packages support we've discussed and (although
> some may argue this), compiling the PL to bytecode and using that.

This makes me think of the old jwz quote...
 "Some people, when confronted with a problem, think 'I know, I'll use regular expressions.'  Now they have two
problems."-- Jamie Zawinski, on comp.lang.emacs
 

There are essentially four choices:

1.  Embed a JVM in PostgreSQL, and use that; the fact that there are
already multiple "pljava" implementations suggests that it may be
difficult to pick a strategy...

2.  Embed some clone of CLR in PostgreSQL, let's say, MONO.

I don't think there's a suitable BSDL'ed option...

3.  Embed Parrot (the Perl/Python thing) in PostgreSQL.  (Not that
Parrot can be considered "done".)

4.  Make up a PostgreSQL-specific bytecode interpreter.

I'm quite sure that this leads to adding to the problems...
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/nonrdbms.html
Love the   scientific  sampling  language,  when any  sample   that is
selected from  Usenet readers and  additionally self-selected is about
as representative as a wombat is of European wildlife.
-- Madeleine Page

Neil Conway wrote:
> I agree the current parser is a hack, but it's difficult to see how
> else it could be implemented.

Since the lexical structure of SQL/PSM seems to be about the same as the 
main SQL, maybe you could get away with having the main parser just 
accepting any tokens at the point where the function body belongs and 
make it count BEGIN's and END's or whatever nesting elements there 
might be.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 6/28/2005 5:55 AM, Peter Eisentraut wrote:
> Neil Conway wrote:
>> I agree the current parser is a hack, but it's difficult to see how
>> else it could be implemented.
> 
> Since the lexical structure of SQL/PSM seems to be about the same as the 
> main SQL, maybe you could get away with having the main parser just 
> accepting any tokens at the point where the function body belongs and 
> make it count BEGIN's and END's or whatever nesting elements there 
> might be.
> 

Which then would require that SPI gets another interface added that 
allows to feed in a token sequence instead of a query string.

After thinking more about what I wrote yesterday I noticed that we would 
lose the potential for query plan recompilation after system cache 
invalidation if we do not keep the queries inside of a PL function in 
some sort of source code (lexer tokens still are).


Jan

One thing bytecode would allow us to do is to write a debugger with  
break points etc.

Using a java jvm however is considerable overkill.

Dave
On 27-Jun-05, at 8:28 PM, Neil Conway wrote:

> Jonah H. Harris wrote:
>
>> I don't recommend discussion for this in this thread, but it could  
>> also tie in with the packages support we've discussed and  
>> (although some may argue this), compiling the PL to bytecode and  
>> using that.
>>
>
> How would compilation to bytecode help?
>
> -Neil
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>

On Tue, 28 Jun 2005, Dave Cramer wrote:

> One thing bytecode would allow us to do is to write a debugger with  
> break points etc.
> 

We can write debugger with breakpoints without bytecode. Every stmt rec 
can have flag if has breakpoints. No problem. I don't see any advance of
bytecode. Maybe, goto stmt is possible. 

What is problem? We need synchronous comunication (message) between 
backend frontend.

I have idea (in exec_stmt()
 CHECK_FOR_INTERRUPTS(); if (stmt->breakpoints)estate->debug_mode = true; if (estate->debug_mode) {for (;;){    rc =
request_command();   switch (rc)    {        case 'c': -- continue            estate->debug_mode = false;
break       case 'q':            elog(EXCEPTION, "stop debug");            break;        case 'n':            break;
   case 'l':              sendstring(line(estate->src,                stmt->lineno));
 
Please, can somebody help me with protocol enhancing? It is mayor work on 
PL/pgSQL debugger (and plperl and plpython too).


> Using a java jvm however is considerable overkill.
> 
> Dave
> On 27-Jun-05, at 8:28 PM, Neil Conway wrote:
> 
> > Jonah H. Harris wrote:
> >
> >> I don't recommend discussion for this in this thread, but it could  
> >> also tie in with the packages support we've discussed and  
> >> (although some may argue this), compiling the PL to bytecode and  
> >> using that.
> >>
> >
> > How would compilation to bytecode help?
> >
> > -Neil
> >
> > ---------------------------(end of  
> > broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faq
> >
> >
> 

Pavel,

What do you think you need for enhanced protocol ?

Dave
On 28-Jun-05, at 8:51 AM, Pavel Stehule wrote:

> On Tue, 28 Jun 2005, Dave Cramer wrote:
>
>
>> One thing bytecode would allow us to do is to write a debugger with
>> break points etc.
>>
>>
>
> We can write debugger with breakpoints without bytecode. Every stmt  
> rec
> can have flag if has breakpoints. No problem. I don't see any  
> advance of
> bytecode. Maybe, goto stmt is possible.
>
> What is problem? We need synchronous comunication (message) between
> backend frontend.
>
> I have idea (in exec_stmt()
>
>   CHECK_FOR_INTERRUPTS();
>   if (stmt->breakpoints)
>     estate->debug_mode = true;
>   if (estate->debug_mode)
>   {
>     for (;;)
>     {
>         rc = request_command();
>         switch (rc)
>         {
>             case 'c': -- continue
>                 estate->debug_mode = false;
>                 break
>             case 'q':
>                 elog(EXCEPTION, "stop debug");
>                 break;
>             case 'n':
>                 break;
>             case 'l':
>                   sendstring(line(estate->src,
>                     stmt->lineno));
>
> Please, can somebody help me with protocol enhancing? It is mayor  
> work on
> PL/pgSQL debugger (and plperl and plpython too).
>
>
>
>> Using a java jvm however is considerable overkill.
>>
>> Dave
>> On 27-Jun-05, at 8:28 PM, Neil Conway wrote:
>>
>>
>>> Jonah H. Harris wrote:
>>>
>>>
>>>> I don't recommend discussion for this in this thread, but it could
>>>> also tie in with the packages support we've discussed and
>>>> (although some may argue this), compiling the PL to bytecode and
>>>> using that.
>>>>
>>>>
>>>
>>> How would compilation to bytecode help?
>>>
>>> -Neil
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 5: Have you checked our extensive FAQ?
>>>
>>>               http://www.postgresql.org/docs/faq
>>>
>>>
>>>
>>
>>
>
>
>

> 
> What do you think you need for enhanced protocol ?
> 

What I need? Some like synchronous elog(NOTICE,''), which can return some 
user's interaction, if it's possible. I didn't find how I do it with 
current set of messages. But my knowleadges of protocol are minimal.

Pavel

Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
>> What do you think you need for enhanced protocol ?

> What I need? Some like synchronous elog(NOTICE,''), which can return some 
> user's interaction, if it's possible. I didn't find how I do it with 
> current set of messages. But my knowleadges of protocol are minimal.

It'd probably be smarter to manage the debugging across a separate
connection, so that you could carry out debugging without requiring
sophisticated support for it inside the client program.  If it's
single-connection then it will be essentially impractical to debug
except from a few specialized clients such as pgadmin; which will
make it hard to investigate behaviors that are only seen under load
from a client app.

I don't know exactly how to cause such a connection to get set up,
especially remotely.  But we should try to think of a way.
        regards, tom lane

On Tue, 28 Jun 2005, Tom Lane wrote:

> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> >> What do you think you need for enhanced protocol ?
> 
> > What I need? Some like synchronous elog(NOTICE,''), which can return some 
> > user's interaction, if it's possible. I didn't find how I do it with 
> > current set of messages. But my knowleadges of protocol are minimal.
> 
> It'd probably be smarter to manage the debugging across a separate
> connection, so that you could carry out debugging without requiring
> sophisticated support for it inside the client program.  If it's
> single-connection then it will be essentially impractical to debug
> except from a few specialized clients such as pgadmin; which will
> make it hard to investigate behaviors that are only seen under load
> from a client app.

I don't think it. Debug process halt query process in bouth variants - 
remote | protocol. Remote debugging has one advance. I can monitor any 
living plpgsql process, but I have to connect to some special port, and it 
can be problem. Protocol debugging can be supported libpq, and all clients 
libpq can debug. But is problem if PostgreSQL support bouth variants?

btw: debuging have to be only for some users,GRANT DEBUG ON LANGUAGE plpgsql TO ..

For me, is better variant if I can debug plpgsql code in psql console. 
Without spec application. I don't speak so spec application don't have to 
exists (from my view, ofcourse). 

Maybe:set debug_mode to true; -- if 't' then func stmt has srcreset function myfce(integer, integer); -- need
recompilationcreatebreakpoint on myfce(integer, integer) line 1;select myfce(10,10);dbg> \l .. list current line     \c
..continue     \n .. next stmt            \L .. show src     \s .. show stack     \b .. switch breakpoint            \q
..quit function            select myvar+10 .. any sql expression     variable .. print variable    \cmyfce       -----
     10
 
that's all. Maybe I have big fantasy :). 

Regards
Pavel

+ small argument: if psql support debug mode, I don't need leave my emacs 
postgresql mode.



> 
> I don't know exactly how to cause such a connection to get set up,
> especially remotely.  But we should try to think of a way.
> 
>             regards, tom lane
> 

Christopher Browne <cbbrowne@acm.org> writes:
> There are essentially four choices:

Aside:

I suppose there are as many possible choices as there are bytecode
compiled systems out there.  One could consider Icon, CLISP, Python,
PHP, OCAML, CMU/CL, all of which have bytecode compilers.

But none of those VMs are particularly intended to be reused/abused
for other purposes; they were designed for the convenience of the
respective language implementors.

Mind you, the Icon VM is probably pretty stable by now :-).
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>

Pavel,

I am in agreement with Tom here, we should use a separate port, and  
protocol specifically designed for this.

My understanding is that this protocol would be synchronous, and be  
used for transferring state information, variables, etc back and forth
whereas the existing protocol would still be used to transfer data  
back and forth

Dave
On 28-Jun-05, at 10:36 AM, Pavel Stehule wrote:

> On Tue, 28 Jun 2005, Tom Lane wrote:
>
>
>> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
>>
>>>> What do you think you need for enhanced protocol ?
>>>>
>>
>>
>>> What I need? Some like synchronous elog(NOTICE,''), which can  
>>> return some
>>> user's interaction, if it's possible. I didn't find how I do it with
>>> current set of messages. But my knowleadges of protocol are minimal.
>>>
>>
>> It'd probably be smarter to manage the debugging across a separate
>> connection, so that you could carry out debugging without requiring
>> sophisticated support for it inside the client program.  If it's
>> single-connection then it will be essentially impractical to debug
>> except from a few specialized clients such as pgadmin; which will
>> make it hard to investigate behaviors that are only seen under load
>> from a client app.
>>
>
> I don't think it. Debug process halt query process in bouth variants -
> remote | protocol. Remote debugging has one advance. I can monitor any
> living plpgsql process, but I have to connect to some special port,  
> and it
> can be problem. Protocol debugging can be supported libpq, and all  
> clients
> libpq can debug. But is problem if PostgreSQL support bouth variants?
>
> btw: debuging have to be only for some users,
>     GRANT DEBUG ON LANGUAGE plpgsql TO ..
>
> For me, is better variant if I can debug plpgsql code in psql console.
> Without spec application. I don't speak so spec application don't  
> have to
> exists (from my view, ofcourse).
>
> Maybe:
>     set debug_mode to true; -- if 't' then func stmt has src
>     reset function myfce(integer, integer); -- need recompilation
>     create breakpoint on myfce(integer, integer) line 1;
>     select myfce(10,10);
>     dbg> \l .. list current line
>          \c .. continue
>          \n .. next stmt
>              \L .. show src
>          \s .. show stack
>          \b .. switch breakpoint
>              \q .. quit function
>              select myvar+10 .. any sql expression
>          variable .. print variable
>         \c
>     myfce
>         -----
>          10
>
> that's all. Maybe I have big fantasy :).
>
> Regards
> Pavel
>
> + small argument: if psql support debug mode, I don't need leave my  
> emacs
> postgresql mode.
>
>
>
>
>>
>> I don't know exactly how to cause such a connection to get set up,
>> especially remotely.  But we should try to think of a way.
>>
>>             regards, tom lane
>>
>>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to  
> majordomo@postgresql.org)
>
>

On Tue, 28 Jun 2005, Dave Cramer wrote:

> Pavel,
> 
> I am in agreement with Tom here, we should use a separate port, and  
> protocol specifically designed for this.
> 
> My understanding is that this protocol would be synchronous, and be  
> used for transferring state information, variables, etc back and forth
> whereas the existing protocol would still be used to transfer data  
> back and forth
> 

We can it. It can be good start point. I can do it alone. It simpler.
But I don't think so this is optimal solution. You need two protocols. 
Maybe I don't understand, but I think so changes in protocol3 files will 
be minimal. I wont to do prototype. 

Pavel

> Dave
> On 28-Jun-05, at 10:36 AM, Pavel Stehule wrote:
> 
> > On Tue, 28 Jun 2005, Tom Lane wrote:
> >
> >
> >> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> >>
> >>>> What do you think you need for enhanced protocol ?
> >>>>
> >>
> >>
> >>> What I need? Some like synchronous elog(NOTICE,''), which can  
> >>> return some
> >>> user's interaction, if it's possible. I didn't find how I do it with
> >>> current set of messages. But my knowleadges of protocol are minimal.
> >>>
> >>
> >> It'd probably be smarter to manage the debugging across a separate
> >> connection, so that you could carry out debugging without requiring
> >> sophisticated support for it inside the client program.  If it's
> >> single-connection then it will be essentially impractical to debug
> >> except from a few specialized clients such as pgadmin; which will
> >> make it hard to investigate behaviors that are only seen under load
> >> from a client app.
> >>
> >
> > I don't think it. Debug process halt query process in bouth variants -
> > remote | protocol. Remote debugging has one advance. I can monitor any
> > living plpgsql process, but I have to connect to some special port,  
> > and it
> > can be problem. Protocol debugging can be supported libpq, and all  
> > clients
> > libpq can debug. But is problem if PostgreSQL support bouth variants?
> >
> > btw: debuging have to be only for some users,
> >     GRANT DEBUG ON LANGUAGE plpgsql TO ..
> >
> > For me, is better variant if I can debug plpgsql code in psql console.
> > Without spec application. I don't speak so spec application don't  
> > have to
> > exists (from my view, ofcourse).
> >
> > Maybe:
> >     set debug_mode to true; -- if 't' then func stmt has src
> >     reset function myfce(integer, integer); -- need recompilation
> >     create breakpoint on myfce(integer, integer) line 1;
> >     select myfce(10,10);
> >     dbg> \l .. list current line
> >          \c .. continue
> >          \n .. next stmt
> >              \L .. show src
> >          \s .. show stack
> >          \b .. switch breakpoint
> >              \q .. quit function
> >              select myvar+10 .. any sql expression
> >          variable .. print variable
> >         \c
> >     myfce
> >         -----
> >          10
> >
> > that's all. Maybe I have big fantasy :).
> >
> > Regards
> > Pavel
> >
> > + small argument: if psql support debug mode, I don't need leave my  
> > emacs
> > postgresql mode.
> >
> >
> >
> >
> >>
> >> I don't know exactly how to cause such a connection to get set up,
> >> especially remotely.  But we should try to think of a way.
> >>
> >>             regards, tom lane
> >>
> >>
> >
> >
> > ---------------------------(end of  
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to  
> > majordomo@postgresql.org)
> >
> >
> 

Dave,

I lean with you and Tom.  While running it over the same libpq protocol 
would be helpful in some ways, it would have a lot of drawbacks and 
would really change the function of libpq.  I think a separate debugging 
protocol is in order.

Also, as far as bytecode comments go, let's separate them from this 
thread.  I have a pretty sweet hand-written stack-based VM that 
understands PL/SQL, but it's kinda old and written using PCCTS 1.33 (a 
recursive descent parser).  It has compilation, decompilation, and full 
debugging capabilities.  Unfortunately, PCCTS is no longer maintained as 
Terrence Parr (the originator) has since moved to ANTLR.  ANTLR 
currently does not generate C code although I have done some starting 
work on it (ANTLR currently generates Python, Java, or C++).  I don't 
suggest we really reuse one of the current VMs as it would require a lot 
more support and coordination.  Let's take the bytecode discussion off 
this thread and move it to another.  There is certainly a good and bad 
side to using bytecode and I would be glad to discuss it in another thread.

Dave Cramer wrote:

> Pavel,
>
> I am in agreement with Tom here, we should use a separate port, and  
> protocol specifically designed for this.
>
> My understanding is that this protocol would be synchronous, and be  
> used for transferring state information, variables, etc back and forth
> whereas the existing protocol would still be used to transfer data  
> back and forth
>
> Dave
> On 28-Jun-05, at 10:36 AM, Pavel Stehule wrote:
>
>> On Tue, 28 Jun 2005, Tom Lane wrote:
>>
>>
>>> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
>>>
>>>>> What do you think you need for enhanced protocol ?
>>>>>
>>>
>>>
>>>> What I need? Some like synchronous elog(NOTICE,''), which can  
>>>> return some
>>>> user's interaction, if it's possible. I didn't find how I do it with
>>>> current set of messages. But my knowleadges of protocol are minimal.
>>>>
>>>
>>> It'd probably be smarter to manage the debugging across a separate
>>> connection, so that you could carry out debugging without requiring
>>> sophisticated support for it inside the client program.  If it's
>>> single-connection then it will be essentially impractical to debug
>>> except from a few specialized clients such as pgadmin; which will
>>> make it hard to investigate behaviors that are only seen under load
>>> from a client app.
>>>
>>
>> I don't think it. Debug process halt query process in bouth variants -
>> remote | protocol. Remote debugging has one advance. I can monitor any
>> living plpgsql process, but I have to connect to some special port,  
>> and it
>> can be problem. Protocol debugging can be supported libpq, and all  
>> clients
>> libpq can debug. But is problem if PostgreSQL support bouth variants?
>>
>> btw: debuging have to be only for some users,
>>     GRANT DEBUG ON LANGUAGE plpgsql TO ..
>>
>> For me, is better variant if I can debug plpgsql code in psql console.
>> Without spec application. I don't speak so spec application don't  
>> have to
>> exists (from my view, ofcourse).
>>
>> Maybe:
>>     set debug_mode to true; -- if 't' then func stmt has src
>>     reset function myfce(integer, integer); -- need recompilation
>>     create breakpoint on myfce(integer, integer) line 1;
>>     select myfce(10,10);
>>     dbg> \l .. list current line
>>          \c .. continue
>>          \n .. next stmt
>>              \L .. show src
>>          \s .. show stack
>>          \b .. switch breakpoint
>>              \q .. quit function
>>              select myvar+10 .. any sql expression
>>          variable .. print variable
>>         \c
>>     myfce
>>         -----
>>          10
>>
>> that's all. Maybe I have big fantasy :).
>>
>> Regards
>> Pavel
>>
>> + small argument: if psql support debug mode, I don't need leave my  
>> emacs
>> postgresql mode.
>>
>>
>>
>>
>>>
>>> I don't know exactly how to cause such a connection to get set up,
>>> especially remotely.  But we should try to think of a way.
>>>
>>>             regards, tom lane
>>>
>>>
>>
>>
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>     (send "unregister YourEmailAddressHere" to  
>> majordomo@postgresql.org)
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match

> I lean with you and Tom.  While running it over the same libpq protocol 
> would be helpful in some ways, it would have a lot of drawbacks and 
> would really change the function of libpq.  I think a separate debugging 
> protocol is in order.
> 
One message? I can't belive :). 

> work on it (ANTLR currently generates Python, Java, or C++).  I don't 
> suggest we really reuse one of the current VMs as it would require a lot 
> more support and coordination.  Let's take the bytecode discussion off 
> this thread and move it to another.  There is certainly a good and bad 
> side to using bytecode and I would be glad to discuss it in another thread.
> 

I see only one advantage of WM - sharing between languages. But SQL/PSM or 
PL/pgSQL are not clasic languages. Big advantage is big disadvantage too 
-> relation on SQL engine. I can use all SQL types, but I can't to do
efective concation of strings. Sorry, I don't see any benefit of bytecode 
for these languages. 

PL/pgSQL works fine (for specific task). What can be better?
 o evaluation of expressions. -- needs integration with sql parser o debugging  o persistent compiled code o syntax

Please, write me, private, your opinions. And don't scowl at me, so I am 
in oportunity :).

Regards
Pavek

Jonah,

What do you see as the advantages of using a VM and bytecode?


Regarding Antlr etal, are there any that generate C code. I am more  
familiar with the java parsers. If we can't generate C this is  
probably a non-starter.

Dave
On 28-Jun-05, at 5:58 PM, Jonah H. Harris wrote:

> Dave,
>
> I lean with you and Tom.  While running it over the same libpq  
> protocol would be helpful in some ways, it would have a lot of  
> drawbacks and would really change the function of libpq.  I think a  
> separate debugging protocol is in order.
>
> Also, as far as bytecode comments go, let's separate them from this  
> thread.  I have a pretty sweet hand-written stack-based VM that  
> understands PL/SQL, but it's kinda old and written using PCCTS 1.33  
> (a recursive descent parser).  It has compilation, decompilation,  
> and full debugging capabilities.  Unfortunately, PCCTS is no longer  
> maintained as Terrence Parr (the originator) has since moved to  
> ANTLR.  ANTLR currently does not generate C code although I have  
> done some starting work on it (ANTLR currently generates Python,  
> Java, or C++).  I don't suggest we really reuse one of the current  
> VMs as it would require a lot more support and coordination.  Let's  
> take the bytecode discussion off this thread and move it to  
> another.  There is certainly a good and bad side to using bytecode  
> and I would be glad to discuss it in another thread.
>
> Dave Cramer wrote:
>
>
>> Pavel,
>>
>> I am in agreement with Tom here, we should use a separate port,  
>> and  protocol specifically designed for this.
>>
>> My understanding is that this protocol would be synchronous, and  
>> be  used for transferring state information, variables, etc back  
>> and forth
>> whereas the existing protocol would still be used to transfer  
>> data  back and forth
>>
>> Dave
>> On 28-Jun-05, at 10:36 AM, Pavel Stehule wrote:
>>
>>
>>> On Tue, 28 Jun 2005, Tom Lane wrote:
>>>
>>>
>>>
>>>> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
>>>>
>>>>
>>>>>> What do you think you need for enhanced protocol ?
>>>>>>
>>>>>>
>>>>
>>>>
>>>>
>>>>> What I need? Some like synchronous elog(NOTICE,''), which can   
>>>>> return some
>>>>> user's interaction, if it's possible. I didn't find how I do it  
>>>>> with
>>>>> current set of messages. But my knowleadges of protocol are  
>>>>> minimal.
>>>>>
>>>>>
>>>>
>>>> It'd probably be smarter to manage the debugging across a separate
>>>> connection, so that you could carry out debugging without requiring
>>>> sophisticated support for it inside the client program.  If it's
>>>> single-connection then it will be essentially impractical to debug
>>>> except from a few specialized clients such as pgadmin; which will
>>>> make it hard to investigate behaviors that are only seen under load
>>>> from a client app.
>>>>
>>>>
>>>
>>> I don't think it. Debug process halt query process in bouth  
>>> variants -
>>> remote | protocol. Remote debugging has one advance. I can  
>>> monitor any
>>> living plpgsql process, but I have to connect to some special  
>>> port,  and it
>>> can be problem. Protocol debugging can be supported libpq, and  
>>> all  clients
>>> libpq can debug. But is problem if PostgreSQL support bouth  
>>> variants?
>>>
>>> btw: debuging have to be only for some users,
>>>     GRANT DEBUG ON LANGUAGE plpgsql TO ..
>>>
>>> For me, is better variant if I can debug plpgsql code in psql  
>>> console.
>>> Without spec application. I don't speak so spec application  
>>> don't  have to
>>> exists (from my view, ofcourse).
>>>
>>> Maybe:
>>>     set debug_mode to true; -- if 't' then func stmt has src
>>>     reset function myfce(integer, integer); -- need recompilation
>>>     create breakpoint on myfce(integer, integer) line 1;
>>>     select myfce(10,10);
>>>     dbg> \l .. list current line
>>>          \c .. continue
>>>          \n .. next stmt
>>>              \L .. show src
>>>          \s .. show stack
>>>          \b .. switch breakpoint
>>>              \q .. quit function
>>>              select myvar+10 .. any sql expression
>>>          variable .. print variable
>>>         \c
>>>     myfce
>>>         -----
>>>          10
>>>
>>> that's all. Maybe I have big fantasy :).
>>>
>>> Regards
>>> Pavel
>>>
>>> + small argument: if psql support debug mode, I don't need leave  
>>> my  emacs
>>> postgresql mode.
>>>
>>>
>>>
>>>
>>>
>>>>
>>>> I don't know exactly how to cause such a connection to get set up,
>>>> especially remotely.  But we should try to think of a way.
>>>>
>>>>             regards, tom lane
>>>>
>>>>
>>>>
>>>
>>>
>>> ---------------------------(end of   
>>> broadcast)---------------------------
>>> TIP 2: you can get off all lists at once with the unregister command
>>>     (send "unregister YourEmailAddressHere" to   
>>> majordomo@postgresql.org)
>>>
>>>
>>>
>>
>>
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>       choose an index scan if your joining column's datatypes do not
>>       match
>>
>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>

Andrew Dunstan wrote:

> It could be done by putting the SPL parser in front of the SQL parser.
> Maybe Luss will tell us how it was done ;-)

We added SPL 'CREATE [OR REPLACE] PROCEDURE' and 'CREATE [OR REPLACE]
FUNCTION' Syntax support to the main scanner, parser for the backend.
By entering exclusive state for scanning the body, similar to quoted
string handling for the PostgreSQL language-agnostic function creation
syntax, we achieve the desired result. We return the scanner to
INITIAL state by encountering the last END token; based upon the block
depth level, that we are keeping track of in the exclusive state.

The rest of the handling, behind the parser, conforms to standard
PostgreSQL Language-agnostic Function creation with all the required
attributes set for
PG_PROC via the CreateFunctionStmt node.

Please note that I am using 'PG_PROC' and 'CreateFunctionStmt' just to
maintain the standard PostgreSQL Reference Point, our implementation
actually differs
in terms of catalog(s), structure(s) naming as we went for
semi-bifurcation between procedures and functions to meet our future
needs. During the process, however, we have ensured full backward
compatibility.

This list of required attributes includes the language to be set as
EDB-SPL (configured as the default PL for EnterpriseDB), in addition
to other attributes
such as parameter(s) information etc.

Subsequently, like for any other PL in PostgreSQL, SPL Language
Handler maintains the responsibility of performing the compilation and
execution of the SPL
proc/function body source text.

-Affan
Lead Database Architect,
EnterpriseDB Corporation.

Hey Dave,

I see a few of the advantages and disadvantages as follows:

ADVANTAGES
- Faster execution (a single parse/compile)
- The ability for companies/people to write PL code and not directly 
share the source (though disassembly is always possible)
- Built-in debugging support (could be added to something like PL/pgSQL, 
but would work better if built into the system from the ground-up)
- Support for PL profiling (great for some of the newbie PL writers and 
would be useful for finding performance problems when packages come around)
- Better/faster exception handling

DISADVANTAGES
- Generated bytecode would have to be platform independent
- Maintenance of the VM itself (how many people would be able to pick up 
development/support)
- Platform support for the VM (not really that big of an issue if it's 
done right)

I have experience writing both stack and register based VMs but I 
believe that a stack-based VM would be a great way to implement PLs.  
Sure, a register-based VM sometimes runs faster than a stack-based 
machine, but it is also a great deal more complex.

Just a couple thoughts :)

-Jonah

Dave Cramer wrote:

> Jonah,
>
> What do you see as the advantages of using a VM and bytecode?
>
>
> Regarding Antlr etal, are there any that generate C code. I am more  
> familiar with the java parsers. If we can't generate C this is  
> probably a non-starter.
>
> Dave
> On 28-Jun-05, at 5:58 PM, Jonah H. Harris wrote:
>
>> Dave,
>>
>> I lean with you and Tom.  While running it over the same libpq  
>> protocol would be helpful in some ways, it would have a lot of  
>> drawbacks and would really change the function of libpq.  I think a  
>> separate debugging protocol is in order.
>>
>> Also, as far as bytecode comments go, let's separate them from this  
>> thread.  I have a pretty sweet hand-written stack-based VM that  
>> understands PL/SQL, but it's kinda old and written using PCCTS 1.33  
>> (a recursive descent parser).  It has compilation, decompilation,  
>> and full debugging capabilities.  Unfortunately, PCCTS is no longer  
>> maintained as Terrence Parr (the originator) has since moved to  
>> ANTLR.  ANTLR currently does not generate C code although I have  
>> done some starting work on it (ANTLR currently generates Python,  
>> Java, or C++).  I don't suggest we really reuse one of the current  
>> VMs as it would require a lot more support and coordination.  Let's  
>> take the bytecode discussion off this thread and move it to  
>> another.  There is certainly a good and bad side to using bytecode  
>> and I would be glad to discuss it in another thread.
>>
>> Dave Cramer wrote:
>>
>>
>>> Pavel,
>>>
>>> I am in agreement with Tom here, we should use a separate port,  
>>> and  protocol specifically designed for this.
>>>
>>> My understanding is that this protocol would be synchronous, and  
>>> be  used for transferring state information, variables, etc back  
>>> and forth
>>> whereas the existing protocol would still be used to transfer  data  
>>> back and forth
>>>
>>> Dave
>>> On 28-Jun-05, at 10:36 AM, Pavel Stehule wrote:
>>>
>>>
>>>> On Tue, 28 Jun 2005, Tom Lane wrote:
>>>>
>>>>
>>>>
>>>>> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
>>>>>
>>>>>
>>>>>>> What do you think you need for enhanced protocol ?
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>> What I need? Some like synchronous elog(NOTICE,''), which can   
>>>>>> return some
>>>>>> user's interaction, if it's possible. I didn't find how I do it  
>>>>>> with
>>>>>> current set of messages. But my knowleadges of protocol are  
>>>>>> minimal.
>>>>>>
>>>>>>
>>>>>
>>>>> It'd probably be smarter to manage the debugging across a separate
>>>>> connection, so that you could carry out debugging without requiring
>>>>> sophisticated support for it inside the client program.  If it's
>>>>> single-connection then it will be essentially impractical to debug
>>>>> except from a few specialized clients such as pgadmin; which will
>>>>> make it hard to investigate behaviors that are only seen under load
>>>>> from a client app.
>>>>>
>>>>>
>>>>
>>>> I don't think it. Debug process halt query process in bouth  
>>>> variants -
>>>> remote | protocol. Remote debugging has one advance. I can  monitor 
>>>> any
>>>> living plpgsql process, but I have to connect to some special  
>>>> port,  and it
>>>> can be problem. Protocol debugging can be supported libpq, and  
>>>> all  clients
>>>> libpq can debug. But is problem if PostgreSQL support bouth  variants?
>>>>
>>>> btw: debuging have to be only for some users,
>>>>     GRANT DEBUG ON LANGUAGE plpgsql TO ..
>>>>
>>>> For me, is better variant if I can debug plpgsql code in psql  
>>>> console.
>>>> Without spec application. I don't speak so spec application  don't  
>>>> have to
>>>> exists (from my view, ofcourse).
>>>>
>>>> Maybe:
>>>>     set debug_mode to true; -- if 't' then func stmt has src
>>>>     reset function myfce(integer, integer); -- need recompilation
>>>>     create breakpoint on myfce(integer, integer) line 1;
>>>>     select myfce(10,10);
>>>>     dbg> \l .. list current line
>>>>          \c .. continue
>>>>          \n .. next stmt
>>>>              \L .. show src
>>>>          \s .. show stack
>>>>          \b .. switch breakpoint
>>>>              \q .. quit function
>>>>              select myvar+10 .. any sql expression
>>>>          variable .. print variable
>>>>         \c
>>>>     myfce
>>>>         -----
>>>>          10
>>>>
>>>> that's all. Maybe I have big fantasy :).
>>>>
>>>> Regards
>>>> Pavel
>>>>
>>>> + small argument: if psql support debug mode, I don't need leave  
>>>> my  emacs
>>>> postgresql mode.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>
>>>>> I don't know exactly how to cause such a connection to get set up,
>>>>> especially remotely.  But we should try to think of a way.
>>>>>
>>>>>             regards, tom lane
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> ---------------------------(end of   
>>>> broadcast)---------------------------
>>>> TIP 2: you can get off all lists at once with the unregister command
>>>>     (send "unregister YourEmailAddressHere" to   
>>>> majordomo@postgresql.org)
>>>>
>>>>
>>>>
>>>
>>>
>>> ---------------------------(end of  
>>> broadcast)---------------------------
>>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>>       choose an index scan if your joining column's datatypes do not
>>>       match
>>>
>>
>>
>>
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match