FAQ
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

Search Discussions

  • Tom Lane at Jun 26, 2005 at 7:20 pm

    "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
  • Pavel Stehule at Jun 26, 2005 at 8:10 pm

    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
  • Jan Wieck at Jun 27, 2005 at 7:08 pm

    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 #
  • Jonah H. Harris at Jun 27, 2005 at 7:50 pm
    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
  • Neil Conway at Jun 28, 2005 at 12:28 am

    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
  • Dave Cramer at Jun 28, 2005 at 12:03 pm
    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
  • Chris Browne at Jun 28, 2005 at 2:21 am

    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
  • Chris Browne at Jun 28, 2005 at 4:04 pm

    Christopher Browne 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/>
  • Neil Conway at Jun 28, 2005 at 12:40 am

    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
  • Rod Taylor at Jun 28, 2005 at 1:05 am

    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.
    --
  • Peter Eisentraut at Jun 28, 2005 at 9:56 am

    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.
  • Jan Wieck at Jun 28, 2005 at 11:15 am

    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
  • Andrew Dunstan at Jun 26, 2005 at 8:44 pm
    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


  • Alvaro Herrera at Jun 26, 2005 at 10:06 pm

    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)
  • Andrew Dunstan at Jun 26, 2005 at 10:16 pm

    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
  • Denis Lussier at Jun 27, 2005 at 8:04 am
    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
    Sent: Sun 6/26/2005 6:06 PM
    To: Andrew Dunstan
    Cc: Denis Lussier; pgsql-hackers@postgresql.org
    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.
  • Affan Bin Salman at Jun 29, 2005 at 4:21 pm

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 26, '05 at 6:53p
activeJun 29, '05 at 4:21p
posts18
users13
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase