Re: 16 parameter limit

There was a message posted in March regarding this. Bruce replied that this
issue did not come up often. However, I think there is more to it than
that. I think one reason that it does not come up is because most Oracle
DBAs are not going to dig through mailing lists and take the time to post
questions. Once they discover that PL/pgSQL != PL/SQL they just move on.

I think that the limitations of PL/pgSQL is a huge factor in people not being
able to use Postgres instead of Oracle. My company is quite small, but we
have several very large insurance companies for clients that we develop web
based applications for. Currently I have 5 schemas totaling about 1500
tables and about as many stored procedures and functions. The applications
do not even have any permissions on a single table. All selects are done on
views and all inserts/updates/deletes are done through stored procedures.
Our procs have many parameters, one per column or more. Most of the app
developers do not even know that much about the schema. They just know the
exposed procedural interface.

Other issues similar to this with regards to PL/SQL are the need for packages
and the ability to declare cursors ahead of time, like in a package so that
they can be shared and opened when needed. This also makes much cleaner
code since the select statement for many cursors clouds the code where it is
used if it is inline like PL/pgSQL.

Named parameters would also be nice and at least allowing the use of giving
names to parameters in the declarations instead of $1, $2, etc.

Also, the inablity to trap database "exceptions" is too limiting. In
Oracle, we trap every single exception, start an autonomous transacation, log
the exception to an exception table along with the procedure name, the
operation being performed and some marker to mke it easy to locate the
offending statement. This also allows us to recover, which is very important
for imports and data loads.

I work with many other Oracle DBAs and I think many have interest in
Postgres, but also know that without a procedural language on par with PL/SQL
that it is not possible to switch. All of the Oracle shops that I know of
are very big on PL/SQL and write almost all business logic and table
interfaces in it. It also seems that Microsoft SQLServer shops are moving
in the same direction now that the procedural support for it is getting much
better.

I am not complaining about Postgres at all. I think it is fantastic and I
enjoy using it for personal projects. However, I think it might be a bit
misleading to assume that lack of posts regarding the limits of PL/pgSQL
equate to it being adequate for most large applications. It is the number
one reason that I could not use Postgres in 4 large insurance companies.

John Proctor

John,

You bring up some interesting points. I agree with you in some parts,
but some of your difficulties with PL/pgSQL are based on
misinformation, which would be good to correct.

First, some prefaces: PL/pgSQL does not currently have a real devoted
project head. It was mostly the brainchild of Jan Wieck, who I
believe developed it as a "side effect" of creating PL/pgTCL. So one
of the reasons that the capabilites of PL/pgSQL have been limited is
that nobody with the required skills has stepped forward from the
community to take PL/pgSQL to the next stage of development. The 6
core developers are a little busy.

Second, with the robustness of Java, J2EE, C++, and Perl::DBI, I
believe that it has long been assumed by the core developers and a
majority of the community that any large application would be
programmed using a seperate middleware langauge and full-blown n-tier
development. Thus, for a lot of people, if PL/pgSQL is adequate for
complex triggers and rules, it is sufficient; if you need incapsulated
business logic, use Perl or Java.

I'm not putting this forward as what I necessarily believe in, but the
logic that drives the current "lightweight" nature of PL/pgSQL as
compared with PL/SQL. It's an open-source project, though ... hire a
C programmer and you can change that.

> I think one reason that it does not come up is because most
> Oracle
> DBAs are not going to dig through mailing lists and take the time to
> post
> questions. Once they discover that PL/pgSQL != PL/SQL they just
> move on.

Yes, but we're not going to interest those people anyway. If they
can't handle using mailing lists as your knowledge base, IMNSHO they
have no place in the Open Source world. Stick to expensive,
well-documented proprietary products.

> I think that the limitations of PL/pgSQL is a huge factor in people
> not being
> able to use Postgres instead of Oracle.

See above. IMHO, Great Bridge was mistaken to target Oracle instead of
targeting MS SQL Server as their main competitor, something they paid
the price for. I still reccommend Oracle to some (but very few) of my
customers who need some of the add-ons that come with Oracle and have
more money than time.

> The
> applications
> do not even have any permissions on a single table. All selects are
> done on
> views and all inserts/updates/deletes are done through stored
> procedures.
> Our procs have many parameters, one per column or more. Most of the
> app
> developers do not even know that much about the schema. They just
> know the
> exposed procedural interface.

I've done this on a smaller scale with Postgres + PHP. It's a good
rapid development approach for intranet apps, and relatively secure.
I just don't try to get PL/pgSQL to do anything it can't, and do my
error handling in interface code.

> Other issues similar to this with regards to PL/SQL are the need for
> packages
> and the ability to declare cursors ahead of time, like in a package
> so that
> they can be shared and opened when needed. This also makes much
> cleaner
> code since the select statement for many cursors clouds the code
> where it is
> used if it is inline like PL/pgSQL.

If you feel strongly enough about this, I am sure that Jan would
happily give you all of his PL/pgSQL development notes so that you can
expand the language.

> Named parameters would also be nice and at least allowing the use of
> giving
> names to parameters in the declarations instead of $1, $2, etc.

PL/pgSQL has had parameter aliases since Postgres 7.0.0.

> Also, the inablity to trap database "exceptions" is too limiting.
> In
> Oracle, we trap every single exception, start an autonomous
> transacation, log
> the exception to an exception table along with the procedure name,
> the
> operation being performed and some marker to mke it easy to locate
> the
> offending statement. This also allows us to recover, which is very
> important
> for imports and data loads.

This is a singnificant failing. Once again, I can only point out the
Postgres team's shortage of skilled manpower. Wanna donate a
programmer? I'd love to see cursor and error handling in PL/pgSQL
improved, and I can't think that anybody would object.

> It also seems that Microsoft SQLServer shops are
> moving
> in the same direction now that the procedural support for it is
> getting much
> better.

Here, I disagree. I am a certified MS SQL Server admin, and PL/pgSQL
is already miles ahead of Transact-SQL. Further, Microsoft is not
improving the procedural elements of T-SQL in new versions because MS
wants you to use .NET objects and not stored procedures that might be
portable to another platform. Perhaps more importantly, MS did not
write T-SQL (Sybase did), and as a result has trouble modifying it.

> I am not complaining about Postgres at all. I think it is fantastic
> and I
> enjoy using it for personal projects. However, I think it might be
> a bit
> misleading to assume that lack of posts regarding the limits of
> PL/pgSQL
> equate to it being adequate for most large applications.

Yes, but without the posts, we don't know what's wrong, now, do we?

Postgres is an Open Source project. We depend on the community to
donate resources so that we can continue to offer a great database
(IMHO, better than anything but Oracle and better than Oracle on a
couple of issues) for free. At a minimum, that participation must
include providing detailed and well-considered requests for changes.
Contributing code, documentation, and/or money is better and more
likely to realize your goals.

Your post is extremely useful, and will no doubt be seized upon by Red
Hat as strategic to their RHDB program if they know what's good for
them. However, it's a mistake to regard the Postgres project as if it
was a vendor, from whom one expects program improvements just because
one is a good customer.

Frankly, considering the Oracle DBAs you refer to who can't even be
bothered to join the mailing list ... I, for one, don't want them as
part of the Postgres product and don't feel that there is any reason
for the Postgres developers to consider their needs.

For anyone else who is lurking on the mailing list, though ... SPEAK
UP! nobody will address your needs if you never communicate them.

-Josh Berkus

John Proctor wrote:
>
> RE: 16 parameter limit
>
> There was a message posted in March regarding this. Bruce replied that this
> issue did not come up often. However, I think there is more to it than
> that. I think one reason that it does not come up is because most Oracle
> DBAs are not going to dig through mailing lists and take the time to post
> questions. Once they discover that PL/pgSQL != PL/SQL they just move on.

Actually, I said it didn't come up much, but I know of several heavy
PL/pgSQL users who do have trouble with the 16 parameter limit, and I am
looking into increasing it. If someone wants to do some legwork, go
ahead. I do think it needs to be increases. The lack of complains
makes it hard for me to advocate increasing it, especially if there is a
disk space penalty, but personally, I do think it needs increasing.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce,

> Actually, I said it didn't come up much, but I know of several heavy
> PL/pgSQL users who do have trouble with the 16 parameter limit, and I
> am
> looking into increasing it. If someone wants to do some legwork, go
> ahead. I do think it needs to be increases. The lack of complains
> makes it hard for me to advocate increasing it, especially if there
> is a
> disk space penalty, but personally, I do think it needs increasing.

Personally, as a heavy user of PL/pgSQL procedures, I'm not sure you
need to increase the *default* number of parameters. Postgres just
needs to implement a parameter number change as part of a documented
command-line compile-time option, i.e. "--with-parameters=32".
Currently, increasing the number of parameters requires altering the
C config files before compilation, a rather user-hostile process.

I've raised this point 3 or 4 times on this list now, and have not seen
a respons from you or Thomas on this suggestion. If I had the
skills, I'd do it myself and upload the changes, but C is not my
strong suit.

Also, what is the practical maximum number of parameters?

-Josh Berkus

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> Personally, as a heavy user of PL/pgSQL procedures, I'm not sure you
> need to increase the *default* number of parameters. Postgres just
> needs to implement a parameter number change as part of a documented
> command-line compile-time option, i.e. "--with-parameters=32".

I would not object to providing such a configure option; it seems a
reasonable thing to do. But the real debate here seems to be what
the default should be. The ACS people would like their code to run
on a "stock" Postgres installation, so they've been lobbying to change
the default, not just to make it fractionally easier to build a
non-default configuration.

> Also, what is the practical maximum number of parameters?

If you tried to make it more than perhaps 500, you'd start to see
index-tuple-too-big failures in the pg_proc indexes. Realistically,
though, I can't see people calling procedures with hundreds of
positionally-specified parameters --- such code would be unmanageably
error-prone.

I was surprised that people were dissatisfied with 16 (it was 8 not very
long ago...). Needing more strikes me as a symptom of either bad coding
practices or missing features of other sorts.

regards, tom lane

Tom,

> I was surprised that people were dissatisfied with 16 (it was 8 not
> very
> long ago...). Needing more strikes me as a symptom of either bad
> coding
> practices or missing features of other sorts.

No, not really. It's just people wanting to use PL/pgSQL procedures as
data filters. For example, I have a database with complex
dependancies and validation rules that I started under 7.0.3, when
RULES were not an option for such things and triggers were harder to
write. As a result, I have the interface push new records for, say,
the CLIENTS table through a PL/pgSQL procedure rather than writing to
the table directly. Since the table has 18 columns, I need (18 + 2
for session & user) 20 parameters for this procedure.

As John has discussed, this kind of data structure is relatively common
in both Oracle and Informix shops. As such, Postgres emulating this
ability allows DBAs from those worlds to consider moving to Postgres
and RHDB. While the same kind of business logic can be implemented
through Rules and Triggers, the Postgres structure for these things is
unique and as a result not very portable.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> Tom,
>> I was surprised that people were dissatisfied with 16 (it was 8 not
>> very long ago...). Needing more strikes me as a symptom of either bad
>> coding practices or missing features of other sorts.

> No, not really. It's just people wanting to use PL/pgSQL procedures as
> data filters. For example, I have a database with complex
> dependancies and validation rules that I started under 7.0.3, when
> RULES were not an option for such things and triggers were harder to
> write. As a result, I have the interface push new records for, say,
> the CLIENTS table through a PL/pgSQL procedure rather than writing to
> the table directly. Since the table has 18 columns, I need (18 + 2
> for session & user) 20 parameters for this procedure.

Yeah, but if we had slightly better support for rowtype parameters in
plpgsql, you could do it with *three* parameters: session, user, and
contents of record as a clients%rowtype structure. And it'd probably
be a lot easier to read, and more maintainable in the face of changes
to the clients table structure. This is why I say that needing lots
of parameters may be a symptom of missing features rather than an
indication that we ought to push up FUNC_MAX_ARGS.

regards, tom lane

Tom,

> Yeah, but if we had slightly better support for rowtype parameters in
> plpgsql, you could do it with *three* parameters: session, user, and
> contents of record as a clients%rowtype structure. And it'd probably
> be a lot easier to read, and more maintainable in the face of changes
> to the clients table structure. This is why I say that needing lots
> of parameters may be a symptom of missing features rather than an
> indication that we ought to push up FUNC_MAX_ARGS.

You're right for my databases. For that matter, better support for
rowtype is on the laundry list of PL/SQL compatibility issues.

However, we also want to support users who are porting their PL/SQL
applications, which may not be easily translated into %rowtype
paramters. As I've said before, all this requires is a good
compile-time option; increasing the default is unnecessary.

What do you (personally) think about trying to get RH involved in
expanding PL/pgSQL's capabilites as a way fo targeting Oracle's users
for RHDB?

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> However, we also want to support users who are porting their PL/SQL
> applications, which may not be easily translated into %rowtype
> paramters.

Well, probably the $64 question there is: what is Oracle's limit on
number of parameters?

> What do you (personally) think about trying to get RH involved in
> expanding PL/pgSQL's capabilites as a way fo targeting Oracle's users
> for RHDB?

Seems like a good idea in the abstract ... but the hard question is what
are you willing to see *not* get done in order to put cycles on plpgsql.
And there's not a large supply of cycles.

regards, tom lane

Tom,

> Seems like a good idea in the abstract ... but the hard question is
> what
> are you willing to see *not* get done in order to put cycles on
> plpgsql.
> And there's not a large supply of cycles.

Well, it's back to the idea of raising money, then.

-Josh

Tom Lane wrote:
> "Josh Berkus" <josh(at)agliodbs(dot)com> writes:
>
>>However, we also want to support users who are porting their PL/SQL
>> applications, which may not be easily translated into %rowtype
>> paramters.
>
> Well, probably the $64 question there is: what is Oracle's limit on
> number of parameters?

According to the Oracle 9 documentation the limit for number of
parameters to a function is 64K.

--Barry

The following patch adds --maxindfuncparams to configure to allow you to
more easily set the maximum number of function parameters and columns
in an index. (Can someone come up with a better name?)

The patch also removes --def_maxbackends, which Tom reported a few weeks
ago he wanted to remove. Can people review this? To test it, you have
to run autoconf.

Are we staying at 16 as the default? I personally think we can
increase it to 32 with little penalty, and that we should increase
NAMEDATALEN to 64.

---------------------------------------------------------------------------

Tom Lane wrote:
> "Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> > Personally, as a heavy user of PL/pgSQL procedures, I'm not sure you
> > need to increase the *default* number of parameters. Postgres just
> > needs to implement a parameter number change as part of a documented
> > command-line compile-time option, i.e. "--with-parameters=32".
>
> I would not object to providing such a configure option; it seems a
> reasonable thing to do. But the real debate here seems to be what
> the default should be. The ACS people would like their code to run
> on a "stock" Postgres installation, so they've been lobbying to change
> the default, not just to make it fractionally easier to build a
> non-default configuration.
>
> > Also, what is the practical maximum number of parameters?
>
> If you tried to make it more than perhaps 500, you'd start to see
> index-tuple-too-big failures in the pg_proc indexes. Realistically,
> though, I can't see people calling procedures with hundreds of
> positionally-specified parameters --- such code would be unmanageably
> error-prone.
>
> I was surprised that people were dissatisfied with 16 (it was 8 not very
> long ago...). Needing more strikes me as a symptom of either bad coding
> practices or missing features of other sorts.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

On the note of NAMEDATALEN, a view in the INFORMATION_SCHEMA
definition is exactly 2 characters over the current limit.

ADMINISTRABLE_ROLE_AUTHORIZATIONS

Not that it's a great reason, but it isn't a bad one for increasing
the limit ;)

--
Rod Taylor

> Are we staying at 16 as the default? I personally think we can
> increase it to 32 with little penalty, and that we should increase
> NAMEDATALEN to 64.

Bruce,

> The following patch adds --maxindfuncparams to configure to allow you
> to
> more easily set the maximum number of function parameters and columns
> in an index. (Can someone come up with a better name?)

How about simply --max_params ?

> Are we staying at 16 as the default? I personally think we can
> increase it to 32 with little penalty,

I'd vote for that. But then, you knew that. John Proctor wants 128.

>and that we should increase
> NAMEDATALEN to 64.

I don't even know that is.

-Josh

En Mon, 15 Apr 2002 23:19:45 -0400
"Rod Taylor" <rbt(at)zort(dot)ca> escribió:

> On the note of NAMEDATALEN, a view in the INFORMATION_SCHEMA
> definition is exactly 2 characters over the current limit.
>
> ADMINISTRABLE_ROLE_AUTHORIZATIONS
>
> Not that it's a great reason, but it isn't a bad one for increasing
> the limit ;)

http://archives.postgresql.org/pgsql-general/2002-01/msg00939.php

(Tom Lane says both SQL92 and SQL99 specify 128 as the maximun
identifier length)

Anyway, how does one measure the perfomance impact of such a change?
By merely changing the constant definition, or also by actually using
long identifiers? I can do that if it's of any help, for various values
perhaps.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Las cosas son buenas o malas segun las hace nuestra opinion" (Lisias)

Bruce Momjian writes:

> The following patch adds --maxindfuncparams to configure to allow you to
> more easily set the maximum number of function parameters and columns
> in an index. (Can someone come up with a better name?)

> Are we staying at 16 as the default? I personally think we can
> increase it to 32 with little penalty,

If you want to increase it, let's just increase it and not add any more
configure options. If someone wants more than 32 then we really need to
start talking about design issues.

--
Peter Eisentraut peter_e(at)gmx(dot)net

> > Are we staying at 16 as the default? I personally think we can
> > increase it to 32 with little penalty,
>
> If you want to increase it, let's just increase it and not add any more
> configure options. If someone wants more than 32 then we really need to
> start talking about design issues.

Why not give them the configure option? It's not good HCI to impose
arbitrary limits on people...?

We can default it to 32, since there's demand for it. If a particular user
decided to configure it higher, then they do that knowing that it may cause
performance degradation. It's good to give them that choice though.

Chris

On Mon, 15 Apr 2002 23:34:04 -0400
"Alvaro Herrera" <alvherre(at)atentus(dot)com> wrote:
> En Mon, 15 Apr 2002 23:19:45 -0400
> "Rod Taylor" <rbt(at)zort(dot)ca> escribió:
>
> > On the note of NAMEDATALEN, a view in the INFORMATION_SCHEMA
> > definition is exactly 2 characters over the current limit.
> >
> > ADMINISTRABLE_ROLE_AUTHORIZATIONS
> >
> > Not that it's a great reason, but it isn't a bad one for increasing
> > the limit ;)
>
> http://archives.postgresql.org/pgsql-general/2002-01/msg00939.php
>
> (Tom Lane says both SQL92 and SQL99 specify 128 as the maximun
> identifier length)
>
> Anyway, how does one measure the perfomance impact of such a change?
> By merely changing the constant definition, or also by actually using
> long identifiers?

Name values are stored NULL-padded up to NAMEDATALEN bytes, so
there is no need to actually use long identifiers, just change
the value of NAMEDATALEN, recompile and run some benchmarks
(perhaps OSDB? http://osdb.sf.net).

If you do decide to run some benchmarks (and some more data
would be good), please use the current CVS code. I sent in a
patch a little while ago that should somewhat reduce the
penalty for increasing NAMEDATALEN.

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC

Alvaro Herrera wrote:
> (Tom Lane says both SQL92 and SQL99 specify 128 as the maximun
> identifier length)
>
> Anyway, how does one measure the perfomance impact of such a change?
> By merely changing the constant definition, or also by actually using
> long identifiers? I can do that if it's of any help, for various values
> perhaps.

I think I would measure disk size change in a newly created database,
and run regression for various values. That uses a lot of identifier
lookups.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> > Anyway, how does one measure the perfomance impact of such a change?
> > By merely changing the constant definition, or also by actually using
> > long identifiers? I can do that if it's of any help, for various values
> > perhaps.
>
> I think I would measure disk size change in a newly created database,
> and run regression for various values. That uses a lot of identifier
> lookups.

With schemas, maybe there'd be less name lookups and comparisons anyway,
since there's more reliance on oids instead of names?

Chris

On Tue, 16 Apr 2002 11:35:57 +0800
"Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> wrote:
> > > Are we staying at 16 as the default? I personally think we can
> > > increase it to 32 with little penalty,
> >
> > If you want to increase it, let's just increase it and not add any more
> > configure options. If someone wants more than 32 then we really need to
> > start talking about design issues.
>
> Why not give them the configure option? It's not good HCI to impose
> arbitrary limits on people...?

It's not an arbitrary limit -- users can easily change pg_config.h.

> We can default it to 32, since there's demand for it. If a particular user
> decided to configure it higher, then they do that knowing that it may cause
> performance degradation. It's good to give them that choice though.

What if someone actually uses functions with more than 32
arguments? Their code will not longer be portable among
PostgreSQL installations, and they'll need to get the local
admin to recompile.

I could see adding a configure option if there was a justifiable
reason for using functions with more than 32 arguments -- but
IMHO that is quite a bizarre situation anyway, as Peter said.

My vote is to set the default # of function args to some
reasonable default (32 sounds good), and leave it at that.

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC

> What if someone actually uses functions with more than 32
> arguments? Their code will not longer be portable among
> PostgreSQL installations, and they'll need to get the local
> admin to recompile.
>
> I could see adding a configure option if there was a justifiable
> reason for using functions with more than 32 arguments -- but
> IMHO that is quite a bizarre situation anyway, as Peter said.
>
> My vote is to set the default # of function args to some
> reasonable default (32 sounds good), and leave it at that.

OK, agreed. Then they at least are forced to write functions that will work
on all Postgres 7.3 and above...

Chris

Neil Conway wrote:
> On Tue, 16 Apr 2002 11:35:57 +0800
> "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> wrote:
> > > > Are we staying at 16 as the default? I personally think we can
> > > > increase it to 32 with little penalty,
> > >
> > > If you want to increase it, let's just increase it and not add any more
> > > configure options. If someone wants more than 32 then we really need to
> > > start talking about design issues.
> >
> > Why not give them the configure option? It's not good HCI to impose
> > arbitrary limits on people...?
>
> It's not an arbitrary limit -- users can easily change pg_config.h.

Let me just point out that you have to change pg_config.h.in and run
configure _or_ change pg_config.h and _never_ run configure again. It
is this complexity that makes a configure option look acceptable.

Maybe we should pull some of the hard-coded, non-configure stuff from
pg_config.h into a separate file and just include it from pg_config.h.

> > We can default it to 32, since there's demand for it. If a particular user
> > decided to configure it higher, then they do that knowing that it may cause
> > performance degradation. It's good to give them that choice though.
>
> What if someone actually uses functions with more than 32
> arguments? Their code will not longer be portable among
> PostgreSQL installations, and they'll need to get the local
> admin to recompile.

It is usually C++ overloading functions that use lots of args, or
functions that pass every table column into the function. In those
cases, I can easily see 32 params.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Peter,

> If you want to increase it, let's just increase it and not add any
> more
> configure options. If someone wants more than 32 then we really need
> to
> start talking about design issues.

Actually, many Oracle DBAs use functions/procedures with up to 300
parameters. If we want them to take PostgreSQL seriously as an
alternative to Oracle, we need to be able to accommodate that, at the
very least through an accessable configure-time option.

Also, this is a very frequent request on the SQL list. The fact that
currently the defualt is 16 and pg_config.h is not documented anywhere,
is rather unfriendly to developers who like to use their functions as
pseudo-middleware.

John, please speak up here so the core team knows this isn't "just me."

-Josh Berkus

Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> My vote is to set the default # of function args to some
> reasonable default (32 sounds good), and leave it at that.

Bear in mind that s/32/16/ gives you the exact state of the discussion
when we raised the limit from 8 to 16 ;-)

Still, I do not really see the value of adding a configure argument.
Anyone who can't figure out how to tweak this in pg_config.h is probably
not ready to run a non-default configuration anyhow.

If the consensus is to raise the default from 16 to 32, I won't object.
Beyond that, I'd start asking questions about who's measured the
performance hit and what they found.

On the NAMEDATALEN part of the argument: SQL92 clearly expects that
NAMEDATALEN should be 128. But the first report of the performance
cost looked rather grim. Has anyone retried that experiment since
we tweaked hashname to not hash all the trailing zeroes?

regards, tom lane

Tom Lane writes:

> Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> > My vote is to set the default # of function args to some
> > reasonable default (32 sounds good), and leave it at that.
>
> Bear in mind that s/32/16/ gives you the exact state of the discussion
> when we raised the limit from 8 to 16 ;-)

How about this: We store the first 16 parameters in some fixed array for
fast access like now, and when you have more than 16 then 17 and beyond
get stored in some variable array in pg_proc. This way procedures with
few arguments don't lose any performance but we could support an
"infinite" number of parameters easily. It sounds kind of dumb, but
without some sort of break out of the fixed storage scheme we'll have this
argument forever.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Josh is exactly correct with regards to large oracle installs. I personally
have oracle functions that have around 70 to 80 params. I saw some
discussion that this is a design issue, as if to indicate design flaw.
However, I think it is good design, based on the tools at hand. I have
complex transactions to create, some involve 10 to 15 large tables. I also
have requirements of being accessed via perl, python, c, zope, ruby,
bash/sqlplus and possibly any other legacy app language that needs to
interface. Furthermore, I don't have time to teach every developer the
details of the data model, the order of inserts, which columns to update
under different conditions, etc. I also don't have time to build a
middleware interface in C and write wrappers in many languages.

My stored proc interface to a large and complex system is portable across any
programming language that supports calling stored procs. Furthermore, it
shields the developers from what most don't even care about. They know in
the end, that if they pass the right data to my stored proc (which is usally
just a hash of vars anyway, oracle supports pass by name) that all will be
fine. I also, know that I can change the implementation of the data model
and as long as I keep the "interface" the same then perl, python, ruby, zope,
etc all still work. That is good design. No sane DBA would give
insert/update/delete permissions on any table to any user other than owner.
That is the only way to guarantee data integrity.

I think some of the users here are coming from the perspective of simple
dynamic web content or a small dev environment where all of the developers
are multi-talented. However, try an enterprise database that may have 200 to
300 developers working on it over a 10 year lifetime or the merging of
multiple very large clients into a common system. I worked on the database
for the Olympics in Atlanta and Nagano (about 200 developers in Atlanta).
Database was DB/2 and all middleware in C. What a nightmare.

Bottomline. PL/SQL is one of the top reasons for Oracle's success. If you
are an Oracle shop then PL/SQL makes a better middleware layer than any other
language. Simple, fast, stable, single point of entry. What could be better.

However, none of the above is of any value if the performance penalty is
large. And PL/pgSQL needs much more that just the param number increased. I
am sorry if I irritated the group. My only purpose for starting this was to
help point out one of the top areas that PostgreSQL will need to address if
it wants to succeed in the enterprise. If that is not a goal, then my
requests are probably not all that valid.

On Tuesday 16 April 2002 12:06 am, Josh Berkus wrote:
> Peter,
>
> > If you want to increase it, let's just increase it and not add any
> > more
> > configure options. If someone wants more than 32 then we really need
> > to
> > start talking about design issues.
>
> Actually, many Oracle DBAs use functions/procedures with up to 300
> parameters. If we want them to take PostgreSQL seriously as an
> alternative to Oracle, we need to be able to accommodate that, at the
> very least through an accessable configure-time option.
>
> Also, this is a very frequent request on the SQL list. The fact that
> currently the defualt is 16 and pg_config.h is not documented anywhere,
> is rather unfriendly to developers who like to use their functions as
> pseudo-middleware.
>
> John, please speak up here so the core team knows this isn't "just me."
>
> -Josh Berkus

Tom,

> Still, I do not really see the value of adding a configure argument.
> Anyone who can't figure out how to tweak this in pg_config.h is
> probably
> not ready to run a non-default configuration anyhow.

I disagree *very* strongly. Given that the documentation on
pg_config.h was removed from the idocs and that Pater has made noises
about removing pg_config.h entirely, it is not a substitute for
command-line configure options.

> If the consensus is to raise the default from 16 to 32, I won't
> object.
> Beyond that, I'd start asking questions about who's measured the
> performance hit and what they found.

If you can suggest a reasonable test, I will test this at 32, 64, 128
and 256 parameters to settle this issue.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> How about this: We store the first 16 parameters in some fixed array for
> fast access like now, and when you have more than 16 then 17 and beyond
> get stored in some variable array in pg_proc.

<<itch>> What's this going to cost us in the function lookup code paths?

If we can do it with little or no performance cost (at least for the
"normal case" of fewer-than-N parameters) then I'm all ears.

regards, tom lane

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > How about this: We store the first 16 parameters in some fixed array for
> > fast access like now, and when you have more than 16 then 17 and beyond
> > get stored in some variable array in pg_proc.
>
> <<itch>> What's this going to cost us in the function lookup code paths?
>
> If we can do it with little or no performance cost (at least for the
> "normal case" of fewer-than-N parameters) then I'm all ears.

OK, I have an idea. Tom, didn't you just add code that allows the cache
to return multiple rows for a lookup? I think you did it for schemas.

What if we lookup on the first 16 params, then look at every matching
hit if there are more than 16 params supplied? Another idea would be to
hash the function arg types and look that up rather than looking for
exact matches of oidvector.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

On Mon, 15 Apr 2002 23:49:21 -0500
"John Proctor" <jproctor(at)prium(dot)net> wrote:
> However, none of the above is of any value if the performance penalty is
> large. And PL/pgSQL needs much more that just the param number increased.

John,

Could you elaborate on what enhancements you'd like to see in PL/pgSQL?

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC

On Tue, 2002-04-16 at 07:01, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > How about this: We store the first 16 parameters in some fixed array for
> > fast access like now, and when you have more than 16 then 17 and beyond
> > get stored in some variable array in pg_proc.
>
> <<itch>> What's this going to cost us in the function lookup code paths?
>
> If we can do it with little or no performance cost (at least for the
> "normal case" of fewer-than-N parameters) then I'm all ears.

Perhaps we could use the 16-th element as an indicator of 16-or-more
args. If it is 0 then there are <= 15 args if it is something else, then
this something else is hash of extra argument types that need to be
looked up separately.

Of course we will need some way of resolving multiple hash matches.

--------------
Hannu

OK, here goes.

1) More than 16 parameters.   This can be parameter configurable if
necessary, but up to 128 would cover 99.9%.

2) Better exception handling.  The procedure should be able to trap any data
related exception and decide what to do. No function should ever abort. It should raise a trappable exception and let me decide what to do.

3) Allow transactions inside of functions.   Mostly for incremental commits.
Each transaction shoud be implicitely started after any CrUD statement and
continue until a commit or rollback.

4) Allow autonomous transactions.  This is related to number 2.  In Oracle, I
can track every single exception and log it in a central table with details,
even if I rollback the current transaction or savepoint.   This is a must for
tracking every single database error in an application at the exact point of
failure.

5) Find a way to get rid of the requirement to quote the entire proc.   This
is very clumsy.   The PL/pgSQL interpreter should be able to do the quoting
and escape what it needs.

6) Allow function parameters to be specified by name and type during the definition. Even aliasing is cumbersome and error prone on large procs, especially during development when changes are frequent.

7) Allow function parameters to be passed by name, not just positional.  i.e.
get_employee_salary(emp_id => 12345, tax_year => 2001).

8) Add packages.  This is a great way to group related functions, create
reusable objects, like cursors, etc.

9) Allow anonymous PL/pgSQL blocks.   It should not be required to create a
function for every PL/pgSQL block.   Often, I just want to do something quick
and dirty or write complex blocks that I don't even want saved in the
database.  I can just keep then in a file and execute when necessary.

For those that have not seen Oracle PL/SQL, here is a complete proc that illustrates the simplicity and power of it.

create or replace
procedure bp_cmd_chn (
i_um_evt_lvl123_idn in um_evt_lvl123.um_evt_lvl123_idn%type,
i_chn_class_group_cd in code_chn_class_group.chn_class_group_cd%type
)
as

/* setup vars for footprinting exceptions */
v_prc error_log.prc%type := 'bp_cmd_chn';
v_opr error_log.opr%type := 'init';
v_obj error_log.obj%type := 'init';

/* local vars */
v_chn_status_cd um_vendor_chn.chn_status_cd%type;
v_dist_engine_idn dist_engine.dist_engine_idn%type;
v_dist_format_type_cd xrf_vendor_format_io.send_dist_format_type_cd%type;
v_io_type_cd xrf_vendor_format_io.send_io_type_cd%type;
v_app_user_name app_default_schema.user_name%type;
v_app_schema_name app_default_schema.app_schema_name%type;
v_send_process_type_cd xrf_vendor_format_io.send_process_type_cd%type;

/* parameterized cursor */
cursor cur_vnd_chn(
ci_um_evt_lvl123_idn number,
ci_chn_class_group_cd varchar2
) is
select umvnd.rdx_vendor_idn,
umvnd.chn_class_cd
from um_vendor_chn umvnd,
xrf_chn_class_group xchng
where umvnd.chn_class_cd = xchng.chn_class_cd
and umvnd.um_evt_lvl123_idn = ci_um_evt_lvl123_idn
and umvnd.chn_status_cd = 'PEND'
and xchng.chn_class_group_cd = ci_chn_class_group_cd;

begin

savepoint bp_cmd_chn;

/* open cursor with parameters into row object v_vnd_chn_rec */
for v_vnd_chn_rec in cur_vnd_chn(i_um_evt_lvl123_idn,
i_chn_class_group_cd) loop
/* nice clean select into syntax */
v_opr := 'select into';
v_obj := 'xrf_vendor_format_io';
select send_dist_format_type_cd,
send_io_type_cd,
send_process_type_cd
into v_dist_format_type_cd,
v_io_type_cd ,
v_send_process_type_cd
from xrf_vendor_format_io
where rdx_vendor_idn = v_vnd_chn_rec.rdx_vendor_idn
and chn_class_cd = v_vnd_chn_rec.chn_class_cd;

/* call procedure passing parms by name */
v_opr := 'call';
v_obj := 'dist_engine_ins';
dist_engine_ins(dist_engine_idn => v_dist_engine_idn,
pending_dt => sysdate,
source_idn => i_um_evt_lvl123_idn,
source_type => 'EVTLVL123',
dist_format_type_cd => v_dist_format_type_cd,
recipient_type_cd => 'VND',
io_type_cd => v_io_type_cd);


end loop;

/* Trap all exceptions, calling pkg_error.log_error with details.
This will start an autonymous transaction to log the error
then rollback the current savepoint and re-raise exception for
the caller
*/
exception
when others then
pkg_error.log_error (get_schema_name,v_pkg, v_prc, v_opr, v_obj, sqlcode, sqlerrm);
rollback to bp_cmd_chn;
raise;
end bp_cmd_chn;
/

On Tuesday 16 April 2002 12:04 pm, Neil Conway wrote:
> On Mon, 15 Apr 2002 23:49:21 -0500
>
> "John Proctor" <jproctor(at)prium(dot)net> wrote:
> > However, none of the above is of any value if the performance penalty is
> > large. And PL/pgSQL needs much more that just the param number
> > increased.
>
> John,
>
> Could you elaborate on what enhancements you'd like to see in PL/pgSQL?
>
> Cheers,
>
> Neil

I think that this list should definitely be stored in the cvs somewhere -
TODO.detail perhaps, Bruce?

It's good stuff.

Chris

----- Original Message -----
From: "John Proctor" <jproctor(at)prium(dot)net>
To: "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org>
Cc: <josh(at)agliodbs(dot)com>; <peter_e(at)gmx(dot)net>; <pgman(at)candle(dot)pha(dot)pa(dot)us>;
<tgl(at)sss(dot)pgh(dot)pa(dot)us>; <pgsql-patches(at)postgresql(dot)org>
Sent: Wednesday, April 17, 2002 2:22 PM
Subject: Re: [PATCHES] [SQL] 16 parameter limit

>
>
> OK, here goes.
>
> 1) More than 16 parameters. This can be parameter configurable if
> necessary, but up to 128 would cover 99.9%.
>
> 2) Better exception handling. The procedure should be able to trap any
data
> related exception and decide what to do. No function should ever abort.
It should raise a trappable exception and let me decide what to do.
>
> 3) Allow transactions inside of functions. Mostly for incremental commits.
> Each transaction shoud be implicitely started after any CrUD statement and
> continue until a commit or rollback.
>
> 4) Allow autonomous transactions. This is related to number 2. In Oracle,
I
> can track every single exception and log it in a central table with
details,
> even if I rollback the current transaction or savepoint. This is a must
for
> tracking every single database error in an application at the exact point
of
> failure.
>
> 5) Find a way to get rid of the requirement to quote the entire proc. This
> is very clumsy. The PL/pgSQL interpreter should be able to do the quoting
> and escape what it needs.
>
> 6) Allow function parameters to be specified by name and type during the
definition. Even aliasing is cumbersome and error prone on large procs,
especially during development when changes are frequent.
>
> 7) Allow function parameters to be passed by name, not just positional.
i.e.
> get_employee_salary(emp_id => 12345, tax_year => 2001).
>
> 8) Add packages. This is a great way to group related functions, create
> reusable objects, like cursors, etc.
>
> 9) Allow anonymous PL/pgSQL blocks. It should not be required to create a
> function for every PL/pgSQL block. Often, I just want to do something
quick
> and dirty or write complex blocks that I don't even want saved in the
> database. I can just keep then in a file and execute when necessary.
>
>
> For those that have not seen Oracle PL/SQL, here is a complete proc that
illustrates the simplicity and power of it.
>
> create or replace
> procedure bp_cmd_chn (
> i_um_evt_lvl123_idn in um_evt_lvl123.um_evt_lvl123_idn%type,
> i_chn_class_group_cd in code_chn_class_group.chn_class_group_cd%type
> )
> as
>
> /* setup vars for footprinting exceptions */
> v_prc error_log.prc%type := 'bp_cmd_chn';
> v_opr error_log.opr%type := 'init';
> v_obj error_log.obj%type := 'init';
>
> /* local vars */
> v_chn_status_cd um_vendor_chn.chn_status_cd%type;
> v_dist_engine_idn dist_engine.dist_engine_idn%type;
> v_dist_format_type_cd
xrf_vendor_format_io.send_dist_format_type_cd%type;
> v_io_type_cd xrf_vendor_format_io.send_io_type_cd%type;
> v_app_user_name app_default_schema.user_name%type;
> v_app_schema_name app_default_schema.app_schema_name%type;
> v_send_process_type_cd xrf_vendor_format_io.send_process_type_cd%type;
>
> /* parameterized cursor */
> cursor cur_vnd_chn(
> ci_um_evt_lvl123_idn number,
> ci_chn_class_group_cd varchar2
> ) is
> select umvnd.rdx_vendor_idn,
> umvnd.chn_class_cd
> from um_vendor_chn umvnd,
> xrf_chn_class_group xchng
> where umvnd.chn_class_cd = xchng.chn_class_cd
> and umvnd.um_evt_lvl123_idn = ci_um_evt_lvl123_idn
> and umvnd.chn_status_cd = 'PEND'
> and xchng.chn_class_group_cd = ci_chn_class_group_cd;
>
>
> begin
>
> savepoint bp_cmd_chn;
>
> /* open cursor with parameters into row object v_vnd_chn_rec */
> for v_vnd_chn_rec in cur_vnd_chn(i_um_evt_lvl123_idn,
> i_chn_class_group_cd) loop
> /* nice clean select into syntax */
> v_opr := 'select into';
> v_obj := 'xrf_vendor_format_io';
> select send_dist_format_type_cd,
> send_io_type_cd,
> send_process_type_cd
> into v_dist_format_type_cd,
> v_io_type_cd ,
> v_send_process_type_cd
> from xrf_vendor_format_io
> where rdx_vendor_idn = v_vnd_chn_rec.rdx_vendor_idn
> and chn_class_cd = v_vnd_chn_rec.chn_class_cd;
>
> /* call procedure passing parms by name */
> v_opr := 'call';
> v_obj := 'dist_engine_ins';
> dist_engine_ins(dist_engine_idn => v_dist_engine_idn,
> pending_dt => sysdate,
> source_idn => i_um_evt_lvl123_idn,
> source_type => 'EVTLVL123',
> dist_format_type_cd => v_dist_format_type_cd,
> recipient_type_cd => 'VND',
> io_type_cd => v_io_type_cd);
>
>
> end loop;
>
> /* Trap all exceptions, calling pkg_error.log_error with details.
> This will start an autonymous transaction to log the error
> then rollback the current savepoint and re-raise exception for
> the caller
> */
> exception
> when others then
> pkg_error.log_error (get_schema_name,v_pkg, v_prc, v_opr, v_obj,
sqlcode, sqlerrm);
> rollback to bp_cmd_chn;
> raise;
> end bp_cmd_chn;
> /
>
>
>
>
> On Tuesday 16 April 2002 12:04 pm, Neil Conway wrote:
> > On Mon, 15 Apr 2002 23:49:21 -0500
> >
> > "John Proctor" <jproctor(at)prium(dot)net> wrote:
> > > However, none of the above is of any value if the performance penalty
is
> > > large. And PL/pgSQL needs much more that just the param number
> > > increased.
> >
> > John,
> >
> > Could you elaborate on what enhancements you'd like to see in PL/pgSQL?
> >
> > Cheers,
> >
> > Neil
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

And can we move the discussion to a more appropriate place (-HACKERS?)?

Thanks.
LER

On Wed, 2002-04-17 at 09:29, Christopher Kings-Lynne wrote:
> I think that this list should definitely be stored in the cvs somewhere -
> TODO.detail perhaps, Bruce?
>
> It's good stuff.
>
> Chris
>
> ----- Original Message -----
> From: "John Proctor" <jproctor(at)prium(dot)net>
> To: "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org>
> Cc: <josh(at)agliodbs(dot)com>; <peter_e(at)gmx(dot)net>; <pgman(at)candle(dot)pha(dot)pa(dot)us>;
> <tgl(at)sss(dot)pgh(dot)pa(dot)us>; <pgsql-patches(at)postgresql(dot)org>
> Sent: Wednesday, April 17, 2002 2:22 PM
> Subject: Re: [PATCHES] [SQL] 16 parameter limit
>
>
> >
> >
> > OK, here goes.
> >
> > 1) More than 16 parameters. This can be parameter configurable if
> > necessary, but up to 128 would cover 99.9%.
> >
> > 2) Better exception handling. The procedure should be able to trap any
> data
> > related exception and decide what to do. No function should ever abort.
> It should raise a trappable exception and let me decide what to do.
> >
> > 3) Allow transactions inside of functions. Mostly for incremental commits.
> > Each transaction shoud be implicitely started after any CrUD statement and
> > continue until a commit or rollback.
> >
> > 4) Allow autonomous transactions. This is related to number 2. In Oracle,
> I
> > can track every single exception and log it in a central table with
> details,
> > even if I rollback the current transaction or savepoint. This is a must
> for
> > tracking every single database error in an application at the exact point
> of
> > failure.
> >
> > 5) Find a way to get rid of the requirement to quote the entire proc. This
> > is very clumsy. The PL/pgSQL interpreter should be able to do the quoting
> > and escape what it needs.
> >
> > 6) Allow function parameters to be specified by name and type during the
> definition. Even aliasing is cumbersome and error prone on large procs,
> especially during development when changes are frequent.
> >
> > 7) Allow function parameters to be passed by name, not just positional.
> i.e.
> > get_employee_salary(emp_id => 12345, tax_year => 2001).
> >
> > 8) Add packages. This is a great way to group related functions, create
> > reusable objects, like cursors, etc.
> >
> > 9) Allow anonymous PL/pgSQL blocks. It should not be required to create a
> > function for every PL/pgSQL block. Often, I just want to do something
> quick
> > and dirty or write complex blocks that I don't even want saved in the
> > database. I can just keep then in a file and execute when necessary.
> >
> >
> > For those that have not seen Oracle PL/SQL, here is a complete proc that
> illustrates the simplicity and power of it.
> >
> > create or replace
> > procedure bp_cmd_chn (
> > i_um_evt_lvl123_idn in um_evt_lvl123.um_evt_lvl123_idn%type,
> > i_chn_class_group_cd in code_chn_class_group.chn_class_group_cd%type
> > )
> > as
> >
> > /* setup vars for footprinting exceptions */
> > v_prc error_log.prc%type := 'bp_cmd_chn';
> > v_opr error_log.opr%type := 'init';
> > v_obj error_log.obj%type := 'init';
> >
> > /* local vars */
> > v_chn_status_cd um_vendor_chn.chn_status_cd%type;
> > v_dist_engine_idn dist_engine.dist_engine_idn%type;
> > v_dist_format_type_cd
> xrf_vendor_format_io.send_dist_format_type_cd%type;
> > v_io_type_cd xrf_vendor_format_io.send_io_type_cd%type;
> > v_app_user_name app_default_schema.user_name%type;
> > v_app_schema_name app_default_schema.app_schema_name%type;
> > v_send_process_type_cd xrf_vendor_format_io.send_process_type_cd%type;
> >
> > /* parameterized cursor */
> > cursor cur_vnd_chn(
> > ci_um_evt_lvl123_idn number,
> > ci_chn_class_group_cd varchar2
> > ) is
> > select umvnd.rdx_vendor_idn,
> > umvnd.chn_class_cd
> > from um_vendor_chn umvnd,
> > xrf_chn_class_group xchng
> > where umvnd.chn_class_cd = xchng.chn_class_cd
> > and umvnd.um_evt_lvl123_idn = ci_um_evt_lvl123_idn
> > and umvnd.chn_status_cd = 'PEND'
> > and xchng.chn_class_group_cd = ci_chn_class_group_cd;
> >
> >
> > begin
> >
> > savepoint bp_cmd_chn;
> >
> > /* open cursor with parameters into row object v_vnd_chn_rec */
> > for v_vnd_chn_rec in cur_vnd_chn(i_um_evt_lvl123_idn,
> > i_chn_class_group_cd) loop
> > /* nice clean select into syntax */
> > v_opr := 'select into';
> > v_obj := 'xrf_vendor_format_io';
> > select send_dist_format_type_cd,
> > send_io_type_cd,
> > send_process_type_cd
> > into v_dist_format_type_cd,
> > v_io_type_cd ,
> > v_send_process_type_cd
> > from xrf_vendor_format_io
> > where rdx_vendor_idn = v_vnd_chn_rec.rdx_vendor_idn
> > and chn_class_cd = v_vnd_chn_rec.chn_class_cd;
> >
> > /* call procedure passing parms by name */
> > v_opr := 'call';
> > v_obj := 'dist_engine_ins';
> > dist_engine_ins(dist_engine_idn => v_dist_engine_idn,
> > pending_dt => sysdate,
> > source_idn => i_um_evt_lvl123_idn,
> > source_type => 'EVTLVL123',
> > dist_format_type_cd => v_dist_format_type_cd,
> > recipient_type_cd => 'VND',
> > io_type_cd => v_io_type_cd);
> >
> >
> > end loop;
> >
> > /* Trap all exceptions, calling pkg_error.log_error with details.
> > This will start an autonymous transaction to log the error
> > then rollback the current savepoint and re-raise exception for
> > the caller
> > */
> > exception
> > when others then
> > pkg_error.log_error (get_schema_name,v_pkg, v_prc, v_opr, v_obj,
> sqlcode, sqlerrm);
> > rollback to bp_cmd_chn;
> > raise;
> > end bp_cmd_chn;
> > /
> >
> >
> >
> >
> > On Tuesday 16 April 2002 12:04 pm, Neil Conway wrote:
> > > On Mon, 15 Apr 2002 23:49:21 -0500
> > >
> > > "John Proctor" <jproctor(at)prium(dot)net> wrote:
> > > > However, none of the above is of any value if the performance penalty
> is
> > > > large. And PL/pgSQL needs much more that just the param number
> > > > increased.
> > >
> > > John,
> > >
> > > Could you elaborate on what enhancements you'd like to see in PL/pgSQL?
> > >
> > > Cheers,
> > >
> > > Neil
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Folks,

> 1) More than 16 parameters.   This can be parameter configurable if
> necessary, but up to 128 would cover 99.9%.
>
> 2) Better exception handling.  The procedure should be able to trap
> any data
> related exception and decide what to do. No function should ever
> abort. It should raise a trappable exception and let me decide what
> to do.
>
> 3) Allow transactions inside of functions.   Mostly for incremental
> commits.
> Each transaction shoud be implicitely started after any CrUD
> statement and
> continue until a commit or rollback.
>
> 4) Allow autonomous transactions.  This is related to number 2.  In
> Oracle, I
> can track every single exception and log it in a central table with
> details,
> even if I rollback the current transaction or savepoint.   This is a
> must for
> tracking every single database error in an application at the exact
> point of
> failure.
>
> 5) Find a way to get rid of the requirement to quote the entire proc.
>   This
> is very clumsy.   The PL/pgSQL interpreter should be able to do the
> quoting
> and escape what it needs.
>
> 6) Allow function parameters to be specified by name and type during
> the definition. Even aliasing is cumbersome and error prone on large
> procs, especially during development when changes are frequent.
>
> 7) Allow function parameters to be passed by name, not just
> positional.  i.e.
> get_employee_salary(emp_id => 12345, tax_year => 2001).
>
> 8) Add packages.  This is a great way to group related functions,
> create
> reusable objects, like cursors, etc.
>
> 9) Allow anonymous PL/pgSQL blocks.   It should not be required to
> create a
> function for every PL/pgSQL block.   Often, I just want to do
> something quick
> and dirty or write complex blocks that I don't even want saved in the
>
> database.  I can just keep then in a file and execute when necessary.

Also:

10) Allow declaration of all PostgreSQL data types, including custom
data types and domains, inside functions. Especially important are
Arrays, which are supported as parameters but not as declarations.

11) PL/pgSQL has functionality 100% analagous to cursors, with a
different syntax. While the PL/pgSQL record loop is easier to use, the
lack of support for standard cursor syntax mars the poratbility of
Oracle procedures to Postgres and vice-versa.

12) The biggie: Allowing the easy return of query results from a
procedure. This is currently supported through a rather difficult
workaround involving either the ROWTYPE datatype or a return Cursor.
Both approaches require the use of a procedural code loop on the
interface side to read the data being returned ... much clumsier than
just dumping the data ala PL/SQL or T-SQL. If implemented, this rowset
return would the the difference between a CREATE FUNCTION and a CREATE
PROCEDURE statement.

13) Allow the creation of multiple output parameters for PROCEDURES (as
opposed to FUNCTIONS) in the parameter declaration.

14) Procedures should have their own permissions, which supercede the
permissions on the tables being affected if the procedure is created by
the database owner, in the same way that Views can allow users to
Select data they would not be entitled to from the base tables. In
other words, if I declare "GRANT SELECT ON fn_modify_assignment TO
phpaccess", the user phpaccess should be able to run
fn_modify_assignment even if that user has no permissions on the
assignment table itself.

-Josh Berkus

P.S. I haven't brought up these issues before because there is no way I
can contribute any significant resources to completing them.

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

Added to TODO:

o Improve PL/PgSQL exception handling
o Allow PL/PgSQL parameters to be specified by name and type during
definition
o Allow PL/PgSQL function parameters to be passed by name,
get_employee_salary(emp_id => 12345, tax_year => 2001)
o Add PL/PgSQL packages

>
>
> OK, here goes.
>
> 1) More than 16 parameters. ? This can be parameter configurable if
> necessary, but up to 128 would cover 99.9%.

Done to 32.

>
> 2) Better exception handling. ?The procedure should be able to trap any data
> related exception and decide what to do. No function should ever abort. It should raise a trappable exception and let me decide what to do.

Added.

>
> 3) Allow transactions inside of functions. ? Mostly for incremental commits.
> Each transaction shoud be implicitely started after any CrUD statement and
> continue until a commit or rollback.

When we have subtransactions, we will be able to do this.

>
> 4) Allow autonomous transactions. ?This is related to number 2. ?In Oracle, I
> can track every single exception and log it in a central table with details,
> even if I rollback the current transaction or savepoint. ? This is a must for
> tracking every single database error in an application at the exact point of
> failure.

Same.

> 5) Find a way to get rid of the requirement to quote the entire proc. ? This
> is very clumsy. ? The PL/pgSQL interpreter should be able to do the quoting
> and escape what it needs.

This is pretty hard, especially because we have plug-in languages. I
don't see a way to do this.

>
> 6) Allow function parameters to be specified by name and type during the definition. Even aliasing is cumbersome and error prone on large procs, especially during development when changes are frequent.

Added,

>
> 7) Allow function parameters to be passed by name, not just positional. ?i.e.
> get_employee_salary(emp_id => 12345, tax_year => 2001).

Added.

>
> 8) Add packages. ?This is a great way to group related functions, create
> reusable objects, like cursors, etc.

Added.

>
> 9) Allow anonymous PL/pgSQL blocks. ? It should not be required to create a
> function for every PL/pgSQL block. ? Often, I just want to do something quick
> and dirty or write complex blocks that I don't even want saved in the
> database. ?I can just keep then in a file and execute when necessary.

I don't see the point here, except perhaps you want TEMP functions?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Added:

o Allow array declarations and other data types in PL/PgSQl
DECLARE
o Add PL/PgSQL PROCEDURES that can return multiple values

> Also:
>
> 10) Allow declaration of all PostgreSQL data types, including custom
> data types and domains, inside functions. Especially important are
> Arrays, which are supported as parameters but not as declarations.

Added

> 11) PL/pgSQL has functionality 100% analagous to cursors, with a
> different syntax. While the PL/pgSQL record loop is easier to use, the
> lack of support for standard cursor syntax mars the poratbility of
> Oracle procedures to Postgres and vice-versa.

Is this done?

>
> 12) The biggie: Allowing the easy return of query results from a
> procedure. This is currently supported through a rather difficult
> workaround involving either the ROWTYPE datatype or a return Cursor.
> Both approaches require the use of a procedural code loop on the
> interface side to read the data being returned ... much clumsier than
> just dumping the data ala PL/SQL or T-SQL. If implemented, this rowset
> return would the the difference between a CREATE FUNCTION and a CREATE
> PROCEDURE statement.

Done for 7.3.

>
> 13) Allow the creation of multiple output parameters for PROCEDURES (as
> opposed to FUNCTIONS) in the parameter declaration.

Added.

> 14) Procedures should have their own permissions, which supercede the
> permissions on the tables being affected if the procedure is created by
> the database owner, in the same way that Views can allow users to
> Select data they would not be entitled to from the base tables. In
> other words, if I declare "GRANT SELECT ON fn_modify_assignment TO
> phpaccess", the user phpaccess should be able to run
> fn_modify_assignment even if that user has no permissions on the
> assignment table itself.

Done, I think, for 7.3.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
>>12) The biggie: Allowing the easy return of query results from a
>>procedure. This is currently supported through a rather difficult
>>workaround involving either the ROWTYPE datatype or a return Cursor.
>> Both approaches require the use of a procedural code loop on the
>>interface side to read the data being returned ... much clumsier than
>>just dumping the data ala PL/SQL or T-SQL. If implemented, this rowset
>>return would the the difference between a CREATE FUNCTION and a CREATE
>>PROCEDURE statement.
>
>
> Done for 7.3.

Um, not done yet (PL/pgSQL table functions). Currently only SQL and C
functions supported. I've had an off-line discussion with Neil, and I
think he is working this item and plans to have it ready for 7.3.

CREATE PROCEDURE is not planned for 7.3 at all (I don't think; see the
CALL foo recent discussion).

It's not clear to me which one is meant by the above. "Dumping the data
ala PL/SQL or T-SQL" could mean either. PL/SQL supports table functions;
T-SQL only supports the CALL foo type capability. See:
http://archives.postgresql.org/pgsql-general/2002-08/msg00602.php
for a description of the difference.

Joe

Joe Conway wrote:
> Bruce Momjian wrote:
> >>12) The biggie: Allowing the easy return of query results from a
> >>procedure. This is currently supported through a rather difficult
> >>workaround involving either the ROWTYPE datatype or a return Cursor.
> >> Both approaches require the use of a procedural code loop on the
> >>interface side to read the data being returned ... much clumsier than
> >>just dumping the data ala PL/SQL or T-SQL. If implemented, this rowset
> >>return would the the difference between a CREATE FUNCTION and a CREATE
> >>PROCEDURE statement.
> >
> >
> > Done for 7.3.
>
> Um, not done yet (PL/pgSQL table functions). Currently only SQL and C
> functions supported. I've had an off-line discussion with Neil, and I
> think he is working this item and plans to have it ready for 7.3.

OK, added to 7.3 open items:

Allow PL/PgSQL functions to return sets

> CREATE PROCEDURE is not planned for 7.3 at all (I don't think; see the
> CALL foo recent discussion).

Right, on TODO.

> It's not clear to me which one is meant by the above. "Dumping the data
> ala PL/SQL or T-SQL" could mean either. PL/SQL supports table functions;
> T-SQL only supports the CALL foo type capability. See:
> http://archives.postgresql.org/pgsql-general/2002-08/msg00602.php
> for a description of the difference.

Not sure.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
>
> Added to TODO:
>
> o Improve PL/PgSQL exception handling

Exception handling? You're talking about nested transaction support and
catchable errors in the first place, and then (a year later) making use
of that functionality in the procedural languages, right?

> o Allow PL/PgSQL parameters to be specified by name and type during
> definition
> o Allow PL/PgSQL function parameters to be passed by name,
> get_employee_salary(emp_id => 12345, tax_year => 2001)

CREATE FUNCTION is in no way PL/pgSQL specific. PL/pgSQL simply works
around that lack with the ALIAS syntax in the DECLARE section.

> o Add PL/PgSQL packages

This really is a 100% PL/PgSQL problem.

Jan

--

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

Jan Wieck wrote:
> Bruce Momjian wrote:
> >
> > Added to TODO:
> >
> > o Improve PL/PgSQL exception handling
>
> Exception handling? You're talking about nested transaction support and
> catchable errors in the first place, and then (a year later) making use
> of that functionality in the procedural languages, right?

Uh, I guess. Not sure.

>
> > o Allow PL/PgSQL parameters to be specified by name and type during
> > definition
> > o Allow PL/PgSQL function parameters to be passed by name,
> > get_employee_salary(emp_id => 12345, tax_year => 2001)
>
> CREATE FUNCTION is in no way PL/pgSQL specific. PL/pgSQL simply works
> around that lack with the ALIAS syntax in the DECLARE section.

Text updated to:

o Allow parameters to be specified by name and type during
definition
o Allow function parameters to be passed by name,
get_employee_salary(emp_id => 12345, tax_year => 2001)

> > o Add PL/PgSQL packages
>
> This really is a 100% PL/PgSQL problem.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073