Requests for Development

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.

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

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 #

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 #

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

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

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

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

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 #

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

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: