Обсуждение: 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
basedonmisinformation, which would be good to correct.
 

First, some prefaces:  PL/pgSQL does not currently have a real devotedproject head.  It was mostly the brainchild of
JanWieck, who Ibelieve developed it as a "side effect" of creating PL/pgTCL.  So oneof the reasons that the capabilites
ofPL/pgSQL have been limited isthat nobody with the required skills has stepped forward from thecommunity to take
PL/pgSQLto the next stage of development.  The 6core developers are a little busy.
 

Second, with the robustness of Java, J2EE, C++, and Perl::DBI, Ibelieve that it has long been assumed by the core
developersand amajority of the community that any large application would beprogrammed using a seperate middleware
langaugeand full-blown n-tierdevelopment.  Thus, for a lot of people, if PL/pgSQL is adequate forcomplex triggers and
rules,it is sufficient; if you need incapsulatedbusiness logic, use Perl or Java.
 

I'm not putting this forward as what I necessarily believe in, but thelogic that drives the current "lightweight"
natureof PL/pgSQL ascompared with PL/SQL.  It's an open-source project, though ... hire aC programmer and you can
changethat.
 

>  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 theycan't handle using mailing lists as your knowledge
base,IMNSHO theyhave 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 oftargeting MS SQL Server as their main
competitor,something they paidthe price for.  I still reccommend Oracle to some (but very few) of mycustomers who need
someof the add-ons that come with Oracle and havemore 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 goodrapid development approach for intranet apps, and
relativelysecure. I just don't try to get PL/pgSQL to do anything it can't, and do myerror 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 wouldhappily give you all of his PL/pgSQL development notes
sothat you canexpand 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 thePostgres team's shortage of skilled manpower.
Wannadonate aprogrammer?  I'd love to see cursor and error handling in PL/pgSQLimproved, and I can't think that anybody
wouldobject.
 

> 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/pgSQLis already miles ahead of Transact-SQL.  Further,
Microsoftis notimproving the procedural elements of T-SQL in new versions because MSwants you to use .NET objects and
notstored procedures that might beportable to another platform.  Perhaps more importantly, MS did notwrite T-SQL
(Sybasedid), 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 todonate resources so that we can continue to offer a
greatdatabase(IMHO, better than anything but Oracle and better than Oracle on acouple of issues) for free.  At a
minimum,that participation mustinclude providing detailed and well-considered requests for changes. Contributing code,
documentation,and/or money is better and morelikely to realize your goals.
 

Your post is extremely useful, and will no doubt be seized upon by RedHat as strategic to their RHDB program if they
knowwhat's good forthem.  However, it's a mistake to regard the Postgres project as if itwas a vendor, from whom one
expectsprogram improvements just becauseone is a good customer. 
 

Frankly, considering the Oracle DBAs you refer to who can't even bebothered to join the mailing list ... I, for one,
don'twant them aspart of the Postgres product and don't feel that there is any reasonfor the Postgres developers to
considertheir needs.    
 

For anyone else who is lurking on the mailing list, though ... SPEAKUP!  nobody will address your needs if you never
communicatethem.
 

-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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

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 youneed to increase the *default* number of
parameters. Postgres justneeds to implement a parameter number change as part of a documentedcommand-line compile-time
option,i.e. "--with-parameters=32". Currently, increasing the number of parameters requires altering theC config files
beforecompilation, a rather user-hostile process.  
 

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

Also, what is the practical maximum number of parameters?

-Josh Berkus

John,

>    Thanks for replying.   I fear that I may have come off as whiny or
>  
> ungrateful.  I hope that I did not.   I am extremely happy with
>  PostgreSQL 
> and would like to see it take root in enterprise business.   

Sorry.  I tried to seperate my criticisms of the people you weretalking about (e.g. Oracle DBAs who can't be bothered
toread themailing list) and you (who obviously *can* be bothered to read themailing list).  Maybe *I* wasn't clear
enough.

>I work
>  with 
> oracle, sybase and mssql every day.   Oracle is a nice database, but
>  it is 
> expensive and I am not interested in their new direction (Java this,
>  Java 
> that).   9i has about 2GB of jar files that I don't even want or
>  need, but I 
> still have to back them up because I am afraid to delete them.

Hey! I like Java, personally.  It's just that I don't often getprojects that are more intersted in comprehensive design
asthey arein cost savings ... thus I do a *lot* of PL/pgSQL + PHP combinations.
 

>    Anyway, I understand your point about using a 3GL for the middle
>  tier data 
> access, but I don't see that happening on large projects.   I know
>  there are 
> many java projects that did that and still do, but I haven't seen
>  many that 
> worked out well.   Most large projects that I have seen and I am
>  involved in 
> use a complete stored procedure interface.  The reasons are simple.
>    The 
> DBAs can design the database and provide the interface.   The
>  developers only 
> need to understand the interface exposed by the DBA (stored procs).
>    It also 
> allows for multiple languages to use the same interface, which is
>  very 
> important when integrating with legacy systems.   Also, compiled
>  PL/SQL runs 
> much faster than java/jdbc, and is much faster to develop and debug.
>    I can 
> also minimize bugs by not allowing developers to write directly to
>  tables.   
> I know that if my procedure interface is correct and few changes are
>  needed 
> to the data layer then changes to the display layer do not effect the
>  
> database.   However, this same logic does not seem to hold well with
>  the 
> middle layer.   Middle layers tend to follow changes in the display
>  layer 
> which increases potential for bugs.

Well, that's not the theory of n-tier development, but I'll agree thatreal, CORBA-compliant business objects are seldom
implementedproperlyoutside of (perpetually unfinished) Open Source projects.  Certainlythe advantage of a SQL scripting
languageis that the person whounderstands the database best (the DBA) can implement business ruleswithout learning a
completelydifferent programming lanuguage.  Thiswas the reason for the termendous popularity of 4GL in its day
(BTW,thereis a 4GL interpreter for PostgreSQL).
 

> It has been my experience that most large oracle projects done by
>  good 
> professionals use PL/SQL extensively.   That is why I think
>  PostgreSQL will 
> not penetrate this market unless it can allow developers to continue
>  using a 
> model that has proven to be successful.

Yup.  You gotta do what the customers want, or you don't capture them. Let me also reiterate my statement that I
believethat Great Bridgemade a mistake trying to take on Oracle last year, and that Red Hat ismore astute going after
theMS SQL market.
 

> Please note that I am not someone just sitting around waiting for
>  others to 
> spend their time building my needs.   I am involved in some other
>  projects 
> regarding open source.   I am also not complaining.   I just didn't
>  want 
> people to think that lack of questions regarding this meant lack of
>  need in 
> large companies.   It is just that those people are not even looking
>  at 
> PostgreSQL right now.   My only reason for bringing it up is my
>  desire to see 
> PostgreSQL move forward and so that I may (for selfish reasons) use
>  it in 
> some professional projects one day.

See my first paragraph.

If you want to pursue this (and I wish you would!) there's two ways togo:

1. Try to get the current major commercial PostgreSQL implementation,Red Hat's RHDB, interested in your suggestions.
MichaelEvans at RedHat is in charge of RHDB.  As far as I know, however, all of theirdevelopment effort is cocentrated
onthe issues of replication, userinterface, and backup/recovery.
 

2. Work with me and other PL/pgSQL users to develop a laundry list ofPL/pgSQL improvements, and then recruit or hire a
programmerorprogrammers to implement them.  I would be happy to contribute specsand testing to such a project, but I
don'tknow enough C to doanything useful.  Preferably, we would find someone interested inbeing the ne PL/pgSQL lead so
thatthe language can continue to moveforward indefinitely with a strong advocate and targeted goals forevery Postgres
release.

-Josh Berkus

"Josh Berkus" <josh@agliodbs.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 asdata filters.  For example, I have a database
withcomplexdependancies and validation rules that I started under 7.0.3, whenRULES were not an option for such things
andtriggers were harder towrite.  As a result, I have the interface push new records for, say,the CLIENTS table through
aPL/pgSQL procedure rather than writing tothe table directly.  Since the table has 18 columns, I need (18 + 2for
session& user) 20 parameters for this procedure.  
 

As John has discussed, this kind of data structure is relatively commonin both Oracle and Informix shops.  As such,
Postgresemulating thisability allows DBAs from those worlds to consider moving to Postgresand RHDB.   While the same
kindof business logic can be implementedthrough Rules and Triggers, the Postgres structure for these things isunique
andas a result not very portable.
 

-Josh Berkus



______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.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@agliodbs.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 forrowtype is on the laundry list of PL/SQL
compatibilityissues.
 

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

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

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.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@agliodbs.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@agliodbs.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