16 parameter limit

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@candle.pha.pa.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@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 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@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 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@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

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

-- 
  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, Pennsylvania 19026

Here is an email I sent to patches, minus the patch. I am sending to
hackers for comments.

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

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.

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

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@zort.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@gmx.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@atentus.com> wrote:

En Mon, 15 Apr 2002 23:19:45 -0400
"Rod Taylor" <rbt@zort.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@rogers.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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026