Skip site navigation (1) Skip section navigation (2)

Re: [SQL] 16 parameter limit

From: John Proctor <jproctor(at)prium(dot)net>
To: "Josh Berkus" <josh(at)agliodbs(dot)com>,Peter Eisentraut <peter_e(at)gmx(dot)net>,Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Josh Berkus <josh(at)agliodbs(dot)com>,PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [SQL] 16 parameter limit
Date: 2002-04-16 04:49:21
Message-ID: 200204160446.g3G4kha06293@slxmail01.prium.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patchespgsql-sql
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

In response to

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2002-04-16 04:50:21
Subject: Re: [SQL] 16 parameter limit
Previous:From: Tom LaneDate: 2002-04-16 04:43:34
Subject: Re: ANSI Compliant Inserts

pgsql-patches by date

Next:From: Josh BerkusDate: 2002-04-16 04:50:21
Subject: Re: [SQL] 16 parameter limit
Previous:From: Tom LaneDate: 2002-04-16 04:43:34
Subject: Re: ANSI Compliant Inserts

pgsql-sql by date

Next:From: Josh BerkusDate: 2002-04-16 04:50:21
Subject: Re: [SQL] 16 parameter limit
Previous:From: Tom LaneDate: 2002-04-16 04:47:12
Subject: Re: please advise on column data type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group