Thread: Requests for Development
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
informationtechnology 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
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
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.
>>> Josh Berkus <josh@agliodbs.com> 09.11.2000 17.40 Uhr >>> > 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'm still dreaming of 'database links', or how ever you want to call the possibility to access tables/views in different dbs, maybe on different hosts, in one sql statement. > Thanks so much for your ongoing hard work! ditto ----------------- Gerhard
> 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
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 firstcall. Since version 8.0 Tcl uses a bytecode compiler and will not interpret the real source text again andagain. 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 parsemost 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 tuplesets 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 #
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 supportin the parser and other parts of the core engine isn't completely planned yet, otherwise PL/pgSQL and PL/Tclwould'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 nobodyknows 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 timeonly 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 examplesand so on. Writing a good tutorial doesn't require a good backend developer, IMHO an experienced SQL-programmerwould be the better guy anyway. During the past 4 years I've heard over and over that people wouldlike 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 #
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
> 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
* 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
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
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.
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
informationtechnology 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
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 doesnot work. It returns an exception: setBinaryStream does not support an input as an InputStream. So how to make this exemplework ? Regards -- Yves Martin yma, Lausanne
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