Thread

Requests for Development

Josh Berkus <josh@agliodbs.com> — 2000-11-09T16:40:41Z

Tom, Bruce, Jan, etc.:

	As a PGSQL developer and business customer, I wanted to make some
public requests as to the development path of PGSQL.  While, obviously,
you will develop the functionality *you* are interested in, I thought it
might be valuable to you to know what things would be most appreciated
(and please, list folks, speak up).

1. Please finish 7.1, stabilize it, and release it.  I am probably not
the only developer with an application that is waiting for the many
wonderful improvements Tom has added to 7.1, but I can't build a
commercial app off the CVS source tree.

The rest of these requests apply to 7.2:

2. Stored Procedure functionality, i.e. outputting a full recordset from
a function (or new structure, if functions are hard to adapt) based on
the last SELECT statement passed to the function.  An alternative would
be to develop parameterized views, which might be the easiest path.

3. Slightly more informative syntax error messages - frankly, just
grabbing a little more text around the word or punctuation that
triggered the error would be enormously helpful (I can't tell you the
number of times I've gotten "Error at or near ')'" in a huge DDL
statement.

4. Use of named in addition to ordinal variables in PL/PGSQL functions
(e.g. $account_type, $period instead of $1, $2).

	Thanks so much for your ongoing hard work!

						-Josh Berkus
-- 
______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology      josh@agliodbs.com
    and data management solutions       (415) 436-9166
   for law firms, small businesses       fax  436-0137
    and non-profit organizations.       pager 338-4078
                        		San Francisco

Re: Requests for Development

Roberto Mello <rmello@cc.usu.edu> — 2000-11-09T20:40:00Z

Josh Berkus wrote:
> 
> Tom, Bruce, Jan, etc.:
> 
>         As a PGSQL developer and business customer, I wanted to make some
> public requests as to the development path of PGSQL.  While, obviously,
> you will develop the functionality *you* are interested in, I thought it
> might be valuable to you to know what things would be most appreciated
> (and please, list folks, speak up).

	I second all Josh's requests and I could add:

	- Procedures instead of just functions on PL/PgSQL (and maybe PL/Tcl).
	- Default values for PL/PgSQL functions/procedures.

	Thanks for the great work PG team.

	-Roberto
-- 
Computer Science			Utah State University
Space Dynamics Laboratory		Web Developer
USU Free Software & GNU/Linux Club 	http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto

Re: Requests for Development

Andreas Tille <tillea@rki.de> — 2000-11-10T07:43:14Z

On Thu, 9 Nov 2000, Josh Berkus wrote:

> 2. Stored Procedure functionality, i.e. outputting a full recordset from
> a function (or new structure, if functions are hard to adapt) based on
> the last SELECT statement passed to the function.  An alternative would
> be to develop parameterized views, which might be the easiest path.
I'm not really sure if parameterized views are a real alternative.
They would help in some cases, but *real* stored procedures would be
much more powerful.  In my opinion it is also in the sense of easier
porting from databases to PostgreSQL to the benefit od PostgreSQL.

I wonder if there couldn't borrowed some code from Interbase which has
full featured stored procedures - at least it was told to me that it has ...
 
> 3. Slightly more informative syntax error messages - frankly, just
> grabbing a little more text around the word or punctuation that
> triggered the error would be enormously helpful (I can't tell you the
> number of times I've gotten "Error at or near ')'" in a huge DDL
> statement.
Waht about i18n.  Could PostgreSQL sources gettext-ized?
 
> 	Thanks so much for your ongoing hard work!
Couldn'trepeated often enough alos for the past!

Kind regards

        Andreas.

Re: Re: Requests for Development

KuroiNeko <evpopkov@carrier.kiev.ua> — 2000-11-10T10:00:50Z

> I wonder if there couldn't borrowed some code from Interbase which has
> full featured stored procedures - at least  it was told to me that it has
> ...

 Well, I have some hands-on experience  with IB, don't know whether this is
perfectly relevant, but here goes....
 Indeed, stored  procedures in  IB can do  what's called  `returning record
sets' in  this thread. This  is helpfull  when tuples restriction  is based
upon  condition  that is  not  easy/possible  to  formulate in  SQL  (where
clause).  On the  other hand,  IB has  two different  ways to  call an  SP:
execute  procedure  for `singleton'  SPs  and  select for  those  returning
multiple tuples.
 However, IB supports  only its own SP language. It's  pretty much complete
and well thought and implemented, but if you want an SP in PERL, you're out
of luck.
 What I'd really  like to see is  `pre-compiled' SPs in PGSQL.  IB has this
feature (SPs are converted to BLR when DDL statement is executed), not sure
about PGSQL.  I've noticed  that language-specific errors  in SPs  are only
reported by  PGSQL when SP is  executed, so I suggest  that interpreter (eg
for PL/PGSQL) is called each time.


--

 Sniper's rifle is an extension of his eye. He kills with his injurious vision.

 JM

Re: Requests for Development

Jan Wieck <janwieck@yahoo.com> — 2000-11-15T09:53:02Z

Josh Berkus wrote:
> Tom, Bruce, Jan, etc.:
>
> [...]
>
> The rest of these requests apply to 7.2:
>
> 2. Stored Procedure functionality, i.e. outputting a full recordset from
> a function (or new structure, if functions are hard to adapt) based on
> the last SELECT statement passed to the function.  An alternative would
> be to develop parameterized views, which might be the easiest path.

    That's one of my favorite requests, and I'd be glad to have a
    chance to start on it. Unfortunately the basic support in the
    parser  and  other  parts of the core engine isn't completely
    planned yet, otherwise PL/pgSQL and PL/Tcl would've had  this
    from the very beginning.

> 3. Slightly more informative syntax error messages - frankly, just
> grabbing a little more text around the word or punctuation that
> triggered the error would be enormously helpful (I can't tell you the
> number of times I've gotten "Error at or near ')'" in a huge DDL
> statement.

    That's  a  general  problem  of a lex/yacc parser and I'm not
    sure how to force it to be a little more  explanative.  Maybe
    we have a chance to grab something from the lex input buffer,
    but IIRC that's unsafe because nobody knows how much of  that
    is already eaten into yacc tokens.

> 4. Use of named in addition to ordinal variables in PL/PGSQL functions
> (e.g. $account_type, $period instead of $1, $2).

    Another  general  problem  in the core engine. Dunno if we'll
    have named arguments in the near  future.  In  the  meantime,
    PL/pgSQL  functions  can  use  ALIAS  to define the names for
    arguments at the very top (it's a precompile time only thing,
    so  there  is  little  to no performance impact).  And PL/Tcl
    functions could easily do a "set account_type $1" as well, so
    I  don't  see  a  real  problem  for  the  readability of the
    functions body.

    To put the ball back into your  yard,  I'd  like  to  make  a
    request  too.   There  seem  to be alot people using PL/pgSQL
    and/or PL/Tcl extensively.  OTOH there are newbies again  and
    again asking for a good tutorial, programming examples and so
    on. Writing a good tutorial doesn't require  a  good  backend
    developer,  IMHO  an  experienced SQL-programmer would be the
    better guy anyway. During the past 4 years  I've  heard  over
    and  over that people would like to contribute their $0.05 if
    they only could code in C. That's an area where nobody  needs
    any C experience.


Jan

--

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

Re: Re: Requests for Development

Jan Wieck <janwieck@yahoo.com> — 2000-11-15T10:20:39Z

KuroiNeko wrote:
> > I wonder if there couldn't borrowed some code from Interbase which has
> > full featured stored procedures - at least  it was told to me that it has
> > ...
>
>  Well, I have some hands-on experience  with IB, don't know whether this is
> perfectly relevant, but here goes....
>  Indeed, stored  procedures in  IB can do  what's called  `returning record
> sets' in  this thread. This  is helpfull  when tuples restriction  is based
> upon  condition  that is  not  easy/possible  to  formulate in  SQL  (where
> clause).  On the  other hand,  IB has  two different  ways to  call an  SP:
> execute  procedure  for `singleton'  SPs  and  select for  those  returning
> multiple tuples.
>  However, IB supports  only its own SP language. It's  pretty much complete
> and well thought and implemented, but if you want an SP in PERL, you're out
> of luck.
>  What I'd really  like to see is  `pre-compiled' SPs in PGSQL.  IB has this
> feature (SPs are converted to BLR when DDL statement is executed), not sure
> about PGSQL.  I've noticed  that language-specific errors  in SPs  are only
> reported by  PGSQL when SP is  executed, so I suggest  that interpreter (eg
> for PL/PGSQL) is called each time.

    Not entirely true.

    PL/Tcl  has  "spi_exec" as well as "spi_prepare/spi_execp". A
    function is  only  sourced  into  the  interpreter  once  per
    session  (backend  lifetime) and has a global upvar called GB
    where it could store prepared plans at it's first call. Since
    version  8.0  Tcl  uses  a  bytecode  compiler  and  will not
    interpret the real source text again and again.

    PL/pgSQL parses the entire function body at first  call  (per
    backend).   But  the  SPI querystrings for all the statements
    aren't parsed at that time.  It uses SPI_prepare()  only  for
    expressions and queries that actually get executed, so that a
    huge function that is called only once in a backend, erroring
    out  at  the  first  IF, will not parse most of it's queries.
    This is surely  a  win  for  performance,  but  it  makes  it
    difficult  to  develop.  This  will  change  a  little in the
    future, but I do delay those  changes  because  I  think  the
    changes when tuple sets get supported will be huge anyway and
    complicating the code now wouldn't help.


Jan

--

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

Re: Requests for Development

Josh Berkus <josh@agliodbs.com> — 2000-11-17T15:40:22Z

Jan,

>     To put the ball back into your  yard,  I'd  like  to
> make  a
>     request  too.   There  seem  to be alot people using
> PL/pgSQL
>     and/or PL/Tcl extensively.  OTOH there are newbies
> again  and
>     again asking for a good tutorial, programming
> examples and so
>     on. Writing a good tutorial doesn't require  a  good
> backend
>     developer,  IMHO  an  experienced SQL-programmer
> would be the
>     better guy anyway. During the past 4 years  I've
> heard  over
>     and  over that people would like to contribute their
> $0.05 if
>     they only could code in C. That's an area where
> nobody  needs
>     any C experience.

Point taken.  Hmmm... when we finish the current project, I
ought to have more than a few dozen PL/PGSQL functions as
examples.  I can definitely talk to my help writer about
dressing those up into an educational "chapter".  It'll cost
me a little more than $0.05, but is only my fair
contribution.  Look for something in february-march.

-Josh Berkus

Re: Requests for Development

Roberto Mello <rmello@cc.usu.edu> — 2000-11-17T16:06:17Z

On Wed, 15 Nov 2000, Jan Wieck wrote:
> 
>     To put the ball back into your  yard,  I'd  like  to  make  a
>     request  too.   There  seem  to be alot people using PL/pgSQL
>     and/or PL/Tcl extensively.  OTOH there are newbies again  and
>     again asking for a good tutorial, programming examples and so
>     on. Writing a good tutorial doesn't require  a  good  backend
>     developer,  IMHO  an  experienced SQL-programmer would be the
>     better guy anyway. During the past 4 years  I've  heard  over
>     and  over that people would like to contribute their $0.05 if
>     they only could code in C. That's an area where nobody  needs
>     any C experience.

	I have this on the way. I started creating such document a
couple months ago when I was porting stuff from Oracle to PostgreSQL and
stumbled on the few examples on the documentation. I'd be glad to finish
it up, add more things to it and then put it somewhere for review,
comments, suggestions, additions, etc.
	Part of this document will be on how to port Oracle PL/SQL to
Postgres' PL/SQL and PL/Tcl.	

  - Roberto Mello
--------------------
Utah State University - Computer Science
USU Free Software and GNU/Linux Club - http://linux.usu.edu
Linux para quem fala Portugues- http://linux.brasileiro.net
Linux Registered User #96240

Re: Re: Requests for Development

KuroiNeko <evpopkov@carrier.kiev.ua> — 2000-11-17T16:17:20Z

>     PL/pgSQL parses the entire function body at first call (per
>     backend). But the SPI querystrings for all the statements
>     aren't parsed at that time. It uses SPI_prepare() only for
>     expressions and queries that actually get executed, so that a
>     huge function that is called only once in a backend, erroring
>     out at the first IF, will not parse most of it's queries.
>     This is surely a win for performance, but it makes it
>     difficult to develop.

 Thanks  for the  explanation.  Although,  I can't  see  how this  improves
performance, I'll keep this in my mind when designing PL/PGSQL SPs.


--

 Sniper's rifle is an extension of his eye. He kills with his injurious vision.

 JM

Re: Requests for Development

Larry Rosenman <ler@lerctr.org> — 2000-11-17T16:58:59Z

* Jan Wieck <janwieck@Yahoo.com> [001117 08:26]:
> > triggered the error would be enormously helpful (I can't tell you the
> > number of times I've gotten "Error at or near ')'" in a huge DDL
> > statement.
> 
>     That's  a  general  problem  of a lex/yacc parser and I'm not
>     sure how to force it to be a little more  explanative.  Maybe
>     we have a chance to grab something from the lex input buffer,
>     but IIRC that's unsafe because nobody knows how much of  that
>     is already eaten into yacc tokens.
I was reading the O'Reilly  Lex & YACC book over the weekend, and they
have some tricks that should make this easier.  If someone wants to
look into it....

LER


-- 
Larry Rosenman                      http://www.lerctr.org/~ler
Phone: +1 972-414-9812 (voice) Internet: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: Requests for Development

Ross Reedstrom <reedstrm@rice.edu> — 2000-11-17T17:30:29Z

On Fri, Nov 17, 2000 at 10:06:17AM -0600, Roberto Mello wrote:
> 
> 	I have this on the way. I started creating such document a
> couple months ago when I was porting stuff from Oracle to PostgreSQL and
> stumbled on the few examples on the documentation. I'd be glad to finish
> it up, add more things to it and then put it somewhere for review,
> comments, suggestions, additions, etc.

Don't worry too much about final polish: "release early, release often!"

> 	Part of this document will be on how to port Oracle PL/SQL to
> Postgres' PL/SQL and PL/Tcl.	

Excellent. Now we need someone to do the MySQL version...

Ross
-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.

Re: Requests for Development

Josh Berkus <josh@agliodbs.com> — 2000-11-17T19:07:52Z

Roberto -

> >       I have this on the way. I started creating such document a
> > couple months ago when I was porting stuff from Oracle to PostgreSQL and
> > stumbled on the few examples on the documentation. I'd be glad to finish
> > it up, add more things to it and then put it somewhere for review,
> > comments, suggestions, additions, etc.
> 
> Don't worry too much about final polish: "release early, release often!"

To further that ... let me put my ex-professional copy-editor skills at
your disposal.  Post the text, I'll help clean it up!

					-Josh
-- 
______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology      josh@agliodbs.com
    and data management solutions       (415) 436-9166
   for law firms, small businesses       fax  436-0137
    and non-profit organizations.       pager 338-4078
                        		San Francisco

Re: [SQL] Requests for Development

Jonathan Ellis <jellis@advocast.com> — 2000-11-18T00:39:29Z

> > Part of this document will be on how to port Oracle PL/SQL to
> > Postgres' PL/SQL and PL/Tcl. 
> 
> Excellent. Now we need someone to do the MySQL version...

?  MySQL doesn't have stored procedures AFAIK.

-Jonathan

Re: Re: [SQL] Requests for Development

Jan Wieck <janwieck@yahoo.com> — 2000-11-21T17:43:40Z

Jonathan Ellis wrote:
> > > Part of this document will be on how to port Oracle PL/SQL to
> > > Postgres' PL/SQL and PL/Tcl.
> >
> > Excellent. Now we need someone to do the MySQL version...
>
> ?  MySQL doesn't have stored procedures AFAIK.

    So writing that doc should be fairly easy. :-)

Jan

--

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

Statement too long

Yves Martin <yma@elca.ch> — 2000-11-23T15:34:33Z

	Hello,

   I have some problem with too long statement.
  In 'psql', the error returned for my insert statement is
ERROR: Tuple is too big: size 10436

  In fact, I use JDBC driver to insert a long long string into a 'text'
  field. The exception is

The SQL Statement is too long - INSERT INTO TRIGGERQUEUE       (DUETIME,
TYPE, EVENT, ACTION) VALUES( 974992122555, 'CONTAINER', 
.....(long really long).......

  So is it possible to re-build postmaster to avoid this limitation for
  too long statement or is there another way in JDBC ?

  Another problem is that with jdbc 6.5 and 7.0 the example of using largeobject
  in postgreSQL development corner site does not work. It returns an exception:
  setBinaryStream does not support an input as an InputStream.
  So how to make this exemple work ?

 Regards
  
-- 
Yves Martin
yma, Lausanne

Re: Statement too long

Serge Canizares <serge@ephilosopher.com> — 2000-11-24T14:46:07Z

You probably have PostgreSQL compiled with the default blocksize, which is 8k.
OpenACS.org has a nice set of instructions explaining how to increase the size to
16k or 32k.

http://openacs.org/doc/openacs/html/simple-pg-install-2.html#ss2.2

Hope that helps.

Yves Martin wrote:

>         Hello,
>
>    I have some problem with too long statement.
>   In 'psql', the error returned for my insert statement is
> ERROR: Tuple is too big: size 10436
>
>   In fact, I use JDBC driver to insert a long long string into a 'text'
>   field. The exception is