Re: [SQL] 16 parameter limit

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