Implementing SQL/PSM for PG 8.2

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
36 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Implementing SQL/PSM for PG 8.2

Denis Lussier-2
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 big fan of adding Oracle compatibility to PL/pgSQL, but, I'm biased in this regard because EnterpriseDB's SPL (Superset Procedural Language) 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.  Although I do think it should heavily leverage the solid foundation afforded by the PL/pgSQL code base.  I think it should start as a separate project on PgFoundry.   Once it is working and fully tested and rock solid and proven...  I think it 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 ANSI stored proc signatures and flexible out/inout parameter passing.   EnterpriseDB will publish those suggested server changes for 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
 
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Tom Lane-2
"Denis Lussier" <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Pavel Stehule-5
On Sun, 26 Jun 2005, Tom Lane wrote:

> "Denis Lussier" <[hidden email]> 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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Andrew Dunstan
In reply to this post by Denis Lussier-2

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 big fan of adding Oracle compatibility to PL/pgSQL, but, I'm biased in this regard because EnterpriseDB's SPL (Superset Procedural Language) 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.  Although I do think it should heavily leverage the solid foundation afforded by the PL/pgSQL code base.  I think it should start as a separate project on PgFoundry.   Once it is working and fully tested and rock solid and proven...  I think it 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 ANSI stored proc signatures and flexible out/inout parameter passing.   EnterpriseDB will publish those suggested server changes for 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
>
>
>  
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Alvaro Herrera
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)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Andrew Dunstan


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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Denis Lussier-2
In reply to this post by Denis Lussier-2
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
Hi Affan,
 
Please read this SQL/PSM thread over and then address how EDB did it (and of course how you would recommend generalizing it for PG 8.2).
 
Perhaps our SQL/PSM could be designed from the ground up with "debugability" :-) in mind.
 
--Luss


From: Alvaro Herrera [mailto:[hidden email]]
Sent: Sun 6/26/2005 6:06 PM
To: Andrew Dunstan
Cc: Denis Lussier; [hidden email]
Subject: Re: [HACKERS] Implementing SQL/PSM for PG 8.2

On Sun, Jun 26, 2005 at 06:06 -05, Alvaro Herrera wrote:

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

 

Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Jan Wieck
In reply to this post by Pavel Stehule-5
On 6/26/2005 4:10 PM, Pavel Stehule wrote:

> On Sun, 26 Jun 2005, Tom Lane wrote:
>
>> "Denis Lussier" <[hidden email]> 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.                                  #
#================================================== [hidden email] #

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Jonah H. Harris
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" <[hidden email]> 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
>


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Neil Conway-2
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
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Neil Conway-2
In reply to this post by Jan Wieck
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Rod Taylor
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.
--


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Chris Browne
In reply to this post by Jonah H. Harris
A long time ago, in a galaxy far, far away, [hidden email] ("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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Peter Eisentraut-2
In reply to this post by Neil Conway-2
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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2

Dave Cramer-8
In reply to this post by Neil Conway-2
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
>
>


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2 - debugger

Pavel Stehule-5
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
> >
> >
>


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2 - debugger

Dave Cramer-8
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
>>>
>>>
>>>
>>
>>
>
>
>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2 - debugger

Pavel Stehule-5
>
> 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


---------------------------(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
Reply | Threaded
Open this post in threaded view
|

Re: Implementing SQL/PSM for PG 8.2 - debugger

Tom Lane-2
Pavel Stehule <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
12