pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed16 parameter limit
43+ messages / 14 participants
[nested] [flat]
* 16 parameter limit
@ 2002-04-03 02:50 John Proctor <jproctor@prium.net>
0 siblings, 2 replies; 43+ messages in thread
From: John Proctor @ 2002-04-03 02:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-04 17:20 Josh Berkus <josh@agliodbs.com>
parent: John Proctor <jproctor@prium.net>
1 sibling, 0 replies; 43+ messages in thread
From: Josh Berkus @ 2002-04-04 17:20 UTC (permalink / raw)
To: John Proctor <jproctor@prium.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-05 02:40 Bruce Momjian <pgman@candle.pha.pa.us>
parent: John Proctor <jproctor@prium.net>
1 sibling, 1 reply; 43+ messages in thread
From: Bruce Momjian @ 2002-04-05 02:40 UTC (permalink / raw)
To: John Proctor <jproctor@prium.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-05 16:30 Josh Berkus <josh@agliodbs.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 43+ messages in thread
From: Josh Berkus @ 2002-04-05 16:30 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; John Proctor <jproctor@prium.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-05 16:46 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 43+ messages in thread
From: Josh Berkus @ 2002-04-05 16:46 UTC (permalink / raw)
To: John Proctor <jproctor@prium.net>; +Cc: pgsql-sql
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 were
talking about (e.g. Oracle DBAs who can't be bothered to read the
mailing list) and you (who obviously *can* be bothered to read the
mailing 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 get
projects that are more intersted in comprehensive design as they are
in 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 that
real, CORBA-compliant business objects are seldom implemented properly
outside of (perpetually unfinished) Open Source projects. Certainly
the advantage of a SQL scripting language is that the person who
understands the database best (the DBA) can implement business rules
without learning a completely different programming lanuguage. This
was the reason for the termendous popularity of 4GL in its day (BTW,
there is 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 believe that Great Bridge
made a mistake trying to take on Oracle last year, and that Red Hat is
more astute going after the MS 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 to
go:
1. Try to get the current major commercial PostgreSQL implementation,
Red Hat's RHDB, interested in your suggestions. Michael Evans at Red
Hat is in charge of RHDB. As far as I know, however, all of their
development effort is cocentrated on the issues of replication, user
interface, and backup/recovery.
2. Work with me and other PL/pgSQL users to develop a laundry list of
PL/pgSQL improvements, and then recruit or hire a programmer or
programmers to implement them. I would be happy to contribute specs
and testing to such a project, but I don't know enough C to do
anything useful. Preferably, we would find someone interested in
being the ne PL/pgSQL lead so that the language can continue to move
forward indefinitely with a strong advocate and targeted goals for
every Postgres release.
-Josh Berkus
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-05 18:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 43+ messages in thread
From: Tom Lane @ 2002-04-05 18:21 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; John Proctor <jproctor@prium.net>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-05 22:29 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 43+ messages in thread
From: Josh Berkus @ 2002-04-05 22:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; John Proctor <jproctor@prium.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-05 23:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 43+ messages in thread
From: Tom Lane @ 2002-04-05 23:18 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; John Proctor <jproctor@prium.net>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-05 23:26 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 43+ messages in thread
From: Josh Berkus @ 2002-04-05 23:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; John Proctor <jproctor@prium.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-05 23:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 43+ messages in thread
From: Tom Lane @ 2002-04-05 23:33 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; John Proctor <jproctor@prium.net>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-05 23:51 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 43+ messages in thread
From: Josh Berkus @ 2002-04-05 23:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; John Proctor <jproctor@prium.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: 16 parameter limit
@ 2002-04-06 05:35 Barry Lind <barry@xythos.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 43+ messages in thread
From: Barry Lind @ 2002-04-06 05:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; Bruce Momjian <pgman@candle.pha.pa.us>; John Proctor <jproctor@prium.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 02:58 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 3 replies; 43+ messages in thread
From: Bruce Momjian @ 2002-04-16 02:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; John Proctor <jproctor@prium.net>; PostgreSQL-patches <pgsql-patches@postgresql.org>
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
Attachments:
/pgpatches/maxbackends (text/plain, 4.5K) download
Index: configure.in
===================================================================
RCS file: /cvsroot/pgsql/configure.in,v
retrieving revision 1.178
diff -c -r1.178 configure.in
*** configure.in 14 Apr 2002 17:23:20 -0000 1.178
--- configure.in 16 Apr 2002 01:47:00 -0000
***************
*** 215,229 ****
AC_SUBST(default_port)
#
! # Maximum number of allowed connections (--with-maxbackends), default 32
#
! AC_MSG_CHECKING([for default soft limit on number of connections])
! PGAC_ARG_REQ(with, maxbackends, [ --with-maxbackends=N set default maximum number of connections [32]],
[],
! [with_maxbackends=32])
! AC_MSG_RESULT([$with_maxbackends])
! AC_DEFINE_UNQUOTED([DEF_MAXBACKENDS], [$with_maxbackends],
! [The default soft limit on the number of concurrent connections, i.e., the default for the postmaster -N switch (--with-maxbackends)])
#
--- 215,229 ----
AC_SUBST(default_port)
#
! # Maximum number of index/function parameters (--with-maxindfuncparams), default 16
#
! AC_MSG_CHECKING([maximum number of index/function parameters])
! PGAC_ARG_REQ(with, maxindfuncparams, [ --with-maxindfuncparams=N maximum number of index/function parameters [16]],
[],
! [with_maxindfuncparams=16])
! AC_MSG_RESULT([$with_maxindfuncparams])
! AC_DEFINE_UNQUOTED([MAXINDFUNCPARAMS], [$with_maxindfuncparams],
! [The maximum number of index/function parameters])
#
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.65
diff -c -r1.65 guc.c
*** src/backend/utils/misc/guc.c 3 Apr 2002 05:39:32 -0000 1.65
--- src/backend/utils/misc/guc.c 16 Apr 2002 01:47:02 -0000
***************
*** 408,419 ****
*/
{
"max_connections", PGC_POSTMASTER, PGC_S_DEFAULT, &MaxBackends,
! DEF_MAXBACKENDS, 1, INT_MAX, NULL, NULL
},
{
"shared_buffers", PGC_POSTMASTER, PGC_S_DEFAULT, &NBuffers,
! DEF_NBUFFERS, 16, INT_MAX, NULL, NULL
},
{
--- 408,419 ----
*/
{
"max_connections", PGC_POSTMASTER, PGC_S_DEFAULT, &MaxBackends,
! 32, 1, INT_MAX, NULL, NULL
},
{
"shared_buffers", PGC_POSTMASTER, PGC_S_DEFAULT, &NBuffers,
! 64, 16, INT_MAX, NULL, NULL
},
{
Index: src/include/pg_config.h.in
===================================================================
RCS file: /cvsroot/pgsql/src/include/pg_config.h.in,v
retrieving revision 1.21
diff -c -r1.21 pg_config.h.in
*** src/include/pg_config.h.in 10 Apr 2002 22:47:09 -0000 1.21
--- src/include/pg_config.h.in 16 Apr 2002 01:47:03 -0000
***************
*** 77,87 ****
#undef DEF_PGPORT_STR
/*
! * Default soft limit on number of backend server processes per postmaster;
! * this is just the default setting for the postmaster's -N switch.
! * (--with-maxbackends=N)
*/
! #undef DEF_MAXBACKENDS
/* --enable-nls */
#undef ENABLE_NLS
--- 77,86 ----
#undef DEF_PGPORT_STR
/*
! * The maximum number of columns in an index and the maximum number of
! * parameters to a function. This controls the length of oidvector.
*/
! #undef MAXINDFUNCPARAMS
/* --enable-nls */
#undef ENABLE_NLS
***************
*** 107,121 ****
*/
/*
- * Default number of buffers in shared buffer pool (each of size BLCKSZ).
- * This is just the default setting for the postmaster's -B switch.
- * Perhaps it ought to be configurable from a configure switch.
- * NOTE: default setting corresponds to the minimum number of buffers
- * that postmaster.c will allow for the default MaxBackends value.
- */
- #define DEF_NBUFFERS (DEF_MAXBACKENDS > 8 ? DEF_MAXBACKENDS * 2 : 16)
-
- /*
* Size of a disk block --- this also limits the size of a tuple.
* You can set it bigger if you need bigger tuples (although TOAST
* should reduce the need to have large tuples, since fields can now
--- 106,111 ----
***************
*** 162,169 ****
* switch statement in fmgr_oldstyle() in src/backend/utils/fmgr/fmgr.c.
* But consider converting such functions to new-style instead...
*/
! #define INDEX_MAX_KEYS 16
! #define FUNC_MAX_ARGS INDEX_MAX_KEYS
/*
* System default value for pg_attribute.attstattarget
--- 152,159 ----
* switch statement in fmgr_oldstyle() in src/backend/utils/fmgr/fmgr.c.
* But consider converting such functions to new-style instead...
*/
! #define INDEX_MAX_KEYS MAXINDFUNCPARAMS
! #define FUNC_MAX_ARGS MAXINDFUNCPARAMS
/*
* System default value for pg_attribute.attstattarget
* Re: [PATCHES] [SQL] 16 parameter limit
@ 2002-04-16 03:19 Rod Taylor <rbt@zort.ca>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
2 siblings, 1 reply; 43+ messages in thread
From: Rod Taylor @ 2002-04-16 03:19 UTC (permalink / raw)
To: Hackers List <pgsql-hackers@postgresql.org>
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.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 03:25 Josh Berkus <josh@agliodbs.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
2 siblings, 0 replies; 43+ messages in thread
From: Josh Berkus @ 2002-04-16 03:25 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; John Proctor <jproctor@prium.net>; PostgreSQL-patches <pgsql-patches@postgresql.org>
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [PATCHES] [SQL] 16 parameter limit
@ 2002-04-16 03:34 Alvaro Herrera <alvherre@atentus.com>
parent: Rod Taylor <rbt@zort.ca>
0 siblings, 2 replies; 43+ messages in thread
From: Alvaro Herrera @ 2002-04-16 03:34 UTC (permalink / raw)
To: Rod Taylor <rbt@zort.ca>; +Cc: pgsql-hackers@postgresql.org
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)
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 03:34 Peter Eisentraut <peter_e@gmx.net>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
2 siblings, 2 replies; 43+ messages in thread
From: Peter Eisentraut @ 2002-04-16 03:34 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; John Proctor <jproctor@prium.net>; PostgreSQL-patches <pgsql-patches@postgresql.org>
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 03:35 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 1 reply; 43+ messages in thread
From: Christopher Kings-Lynne @ 2002-04-16 03:35 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; John Proctor <jproctor@prium.net>; PostgreSQL-patches <pgsql-patches@postgresql.org>
> > 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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [PATCHES] [SQL] 16 parameter limit
@ 2002-04-16 03:42 Neil Conway <nconway@klamath.dyndns.org>
parent: Alvaro Herrera <alvherre@atentus.com>
1 sibling, 0 replies; 43+ messages in thread
From: Neil Conway @ 2002-04-16 03:42 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@atentus.com>; +Cc: rbt@zort.ca; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [PATCHES] [SQL] 16 parameter limit
@ 2002-04-16 03:44 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Alvaro Herrera <alvherre@atentus.com>
1 sibling, 1 reply; 43+ messages in thread
From: Bruce Momjian @ 2002-04-16 03:44 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@atentus.com>; +Cc: Rod Taylor <rbt@zort.ca>; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [PATCHES] [SQL] 16 parameter limit
@ 2002-04-16 03:49 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 43+ messages in thread
From: Christopher Kings-Lynne @ 2002-04-16 03:49 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Alvaro Herrera <alvherre@atentus.com>; +Cc: Rod Taylor <rbt@zort.ca>; pgsql-hackers@postgresql.org
> > 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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 03:52 Neil Conway <nconway@klamath.dyndns.org>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 3 replies; 43+ messages in thread
From: Neil Conway @ 2002-04-16 03:52 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: peter_e@gmx.net; pgman@candle.pha.pa.us; tgl@sss.pgh.pa.us; josh@agliodbs.com; jproctor@prium.net; pgsql-patches@postgresql.org
On Tue, 16 Apr 2002 11:35:57 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.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@rogers.com>
PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 03:57 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Neil Conway <nconway@klamath.dyndns.org>
2 siblings, 0 replies; 43+ messages in thread
From: Christopher Kings-Lynne @ 2002-04-16 03:57 UTC (permalink / raw)
To: Neil Conway <nconway@klamath.dyndns.org>; +Cc: peter_e@gmx.net; pgman@candle.pha.pa.us; tgl@sss.pgh.pa.us; josh@agliodbs.com; jproctor@prium.net; pgsql-patches@postgresql.org
> 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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 03:57 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Neil Conway <nconway@klamath.dyndns.org>
2 siblings, 0 replies; 43+ messages in thread
From: Bruce Momjian @ 2002-04-16 03:57 UTC (permalink / raw)
To: Neil Conway <nconway@klamath.dyndns.org>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; peter_e@gmx.net; tgl@sss.pgh.pa.us; josh@agliodbs.com; jproctor@prium.net; pgsql-patches@postgresql.org
Neil Conway wrote:
> On Tue, 16 Apr 2002 11:35:57 +0800
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.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@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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 04:06 Josh Berkus <josh@agliodbs.com>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 1 reply; 43+ messages in thread
From: Josh Berkus @ 2002-04-16 04:06 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; John Proctor <jproctor@prium.net>; PostgreSQL-patches <pgsql-patches@postgresql.org>
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 04:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Neil Conway <nconway@klamath.dyndns.org>
2 siblings, 2 replies; 43+ messages in thread
From: Tom Lane @ 2002-04-16 04:13 UTC (permalink / raw)
To: Neil Conway <nconway@klamath.dyndns.org>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; peter_e@gmx.net; pgman@candle.pha.pa.us; josh@agliodbs.com; jproctor@prium.net; pgsql-patches@postgresql.org
Neil Conway <nconway@klamath.dyndns.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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [PATCHES] [SQL] 16 parameter limit
@ 2002-04-16 04:36 Peter Eisentraut <peter_e@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 43+ messages in thread
From: Peter Eisentraut @ 2002-04-16 04:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Neil Conway <nconway@klamath.dyndns.org>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; josh@agliodbs.com; jproctor@prium.net; PostgreSQL Development <pgsql-hackers@postgresql.org>
Tom Lane writes:
> Neil Conway <nconway@klamath.dyndns.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@gmx.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 04:49 John Proctor <jproctor@prium.net>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 43+ messages in thread
From: John Proctor @ 2002-04-16 04:49 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Peter Eisentraut <peter_e@gmx.net>; Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; PostgreSQL-patches <pgsql-patches@postgresql.org>
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 04:50 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 43+ messages in thread
From: Josh Berkus @ 2002-04-16 04:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Neil Conway <nconway@klamath.dyndns.org>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; peter_e@gmx.net; pgman@candle.pha.pa.us; josh@agliodbs.com; jproctor@prium.net; pgsql-patches@postgresql.org
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@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [PATCHES] [SQL] 16 parameter limit
@ 2002-04-16 05:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 43+ messages in thread
From: Tom Lane @ 2002-04-16 05:01 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Neil Conway <nconway@klamath.dyndns.org>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; josh@agliodbs.com, jproctor@prium.net, PostgreSQL Development <pgsql-hackers@postgresql.org>
Peter Eisentraut <peter_e@gmx.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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [PATCHES] [SQL] 16 parameter limit
@ 2002-04-16 05:06 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 43+ messages in thread
From: Bruce Momjian @ 2002-04-16 05:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Neil Conway <nconway@klamath.dyndns.org>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; josh@agliodbs.com, jproctor@prium.net, PostgreSQL Development <pgsql-hackers@postgresql.org>
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.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@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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-16 16:04 Neil Conway <nconway@klamath.dyndns.org>
parent: John Proctor <jproctor@prium.net>
0 siblings, 1 reply; 43+ messages in thread
From: Neil Conway @ 2002-04-16 16:04 UTC (permalink / raw)
To: John Proctor <jproctor@prium.net>; +Cc: josh@agliodbs.com; peter_e@gmx.net; pgman@candle.pha.pa.us; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
On Mon, 15 Apr 2002 23:49:21 -0500
"John Proctor" <jproctor@prium.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@rogers.com>
PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [PATCHES] [SQL] 16 parameter limit
@ 2002-04-16 19:12 Hannu Krosing <hannu@tm.ee>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 43+ messages in thread
From: Hannu Krosing @ 2002-04-16 19:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Neil Conway <nconway@klamath.dyndns.org>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; josh@agliodbs.com, jproctor@prium.net, PostgreSQL Development <pgsql-hackers@postgresql.org>
On Tue, 2002-04-16 at 07:01, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-17 06:22 John Proctor <jproctor@prium.net>
parent: Neil Conway <nconway@klamath.dyndns.org>
0 siblings, 3 replies; 43+ messages in thread
From: John Proctor @ 2002-04-17 06:22 UTC (permalink / raw)
To: Neil Conway <nconway@klamath.dyndns.org>; +Cc: josh@agliodbs.com; peter_e@gmx.net; pgman@candle.pha.pa.us; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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@prium.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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-17 14:29 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: John Proctor <jproctor@prium.net>
2 siblings, 1 reply; 43+ messages in thread
From: Christopher Kings-Lynne @ 2002-04-17 14:29 UTC (permalink / raw)
To: John Proctor <jproctor@prium.net>; Neil Conway <nconway@klamath.dyndns.org>; +Cc: josh@agliodbs.com; peter_e@gmx.net; pgman@candle.pha.pa.us; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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@prium.net>
To: "Neil Conway" <nconway@klamath.dyndns.org>
Cc: <josh@agliodbs.com>; <peter_e@gmx.net>; <pgman@candle.pha.pa.us>;
<tgl@sss.pgh.pa.us>; <pgsql-patches@postgresql.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@prium.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@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-17 14:41 Larry Rosenman <ler@lerctr.org>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 43+ messages in thread
From: Larry Rosenman @ 2002-04-17 14:41 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: John Proctor <jproctor@prium.net>; Neil Conway <nconway@klamath.dyndns.org>; josh@agliodbs.com; peter_e@gmx.net; pgman@candle.pha.pa.us; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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@prium.net>
> To: "Neil Conway" <nconway@klamath.dyndns.org>
> Cc: <josh@agliodbs.com>; <peter_e@gmx.net>; <pgman@candle.pha.pa.us>;
> <tgl@sss.pgh.pa.us>; <pgsql-patches@postgresql.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@prium.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@postgresql.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@postgresql.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@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-04-17 16:08 Josh Berkus <josh@agliodbs.com>
parent: John Proctor <jproctor@prium.net>
2 siblings, 1 reply; 43+ messages in thread
From: Josh Berkus @ 2002-04-17 16:08 UTC (permalink / raw)
To: John Proctor <jproctor@prium.net>; Neil Conway <nconway@klamath.dyndns.org>; +Cc: josh@agliodbs.com; peter_e@gmx.net; pgman@candle.pha.pa.us; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-08-14 02:43 Bruce Momjian <pgman@candle.pha.pa.us>
parent: John Proctor <jproctor@prium.net>
2 siblings, 1 reply; 43+ messages in thread
From: Bruce Momjian @ 2002-08-14 02:43 UTC (permalink / raw)
To: John Proctor <jproctor@prium.net>; +Cc: Neil Conway <nconway@klamath.dyndns.org>; josh@agliodbs.com; peter_e@gmx.net; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-08-14 02:48 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 43+ messages in thread
From: Bruce Momjian @ 2002-08-14 02:48 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: John Proctor <jproctor@prium.net>; Neil Conway <nconway@klamath.dyndns.org>; peter_e@gmx.net; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-08-14 03:28 Joe Conway <mail@joeconway.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 43+ messages in thread
From: Joe Conway @ 2002-08-14 03:28 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; John Proctor <jproctor@prium.net>; Neil Conway <nconway@klamath.dyndns.org>; peter_e@gmx.net; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-08-14 03:36 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 43+ messages in thread
From: Bruce Momjian @ 2002-08-14 03:36 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Josh Berkus <josh@agliodbs.com>; John Proctor <jproctor@prium.net>; Neil Conway <nconway@klamath.dyndns.org>; peter_e@gmx.net; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-08-14 18:30 Jan Wieck <JanWieck@Yahoo.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 43+ messages in thread
From: Jan Wieck @ 2002-08-14 18:30 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: John Proctor <jproctor@prium.net>; Neil Conway <nconway@klamath.dyndns.org>; josh@agliodbs.com; peter_e@gmx.net; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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@Yahoo.com #
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: [SQL] 16 parameter limit
@ 2002-08-14 18:34 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 43+ messages in thread
From: Bruce Momjian @ 2002-08-14 18:34 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: John Proctor <jproctor@prium.net>; Neil Conway <nconway@klamath.dyndns.org>; josh@agliodbs.com; peter_e@gmx.net; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 43+ messages in thread
end of thread, other threads:[~2002-08-14 18:34 UTC | newest]
Thread overview: 43+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-03 02:50 16 parameter limit John Proctor <jproctor@prium.net>
2002-04-04 17:20 ` Josh Berkus <josh@agliodbs.com>
2002-04-05 02:40 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-05 16:30 ` Josh Berkus <josh@agliodbs.com>
2002-04-05 18:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-05 22:29 ` Josh Berkus <josh@agliodbs.com>
2002-04-05 23:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-05 23:26 ` Josh Berkus <josh@agliodbs.com>
2002-04-05 23:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-05 23:51 ` Josh Berkus <josh@agliodbs.com>
2002-04-06 05:35 ` Barry Lind <barry@xythos.com>
2002-04-16 02:58 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-16 03:19 ` Rod Taylor <rbt@zort.ca>
2002-04-16 03:34 ` Alvaro Herrera <alvherre@atentus.com>
2002-04-16 03:42 ` Neil Conway <nconway@klamath.dyndns.org>
2002-04-16 03:44 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-16 03:49 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-04-16 03:25 ` Josh Berkus <josh@agliodbs.com>
2002-04-16 03:34 ` Peter Eisentraut <peter_e@gmx.net>
2002-04-16 03:35 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-04-16 03:52 ` Neil Conway <nconway@klamath.dyndns.org>
2002-04-16 03:57 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-04-16 03:57 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-16 04:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-16 04:36 ` Peter Eisentraut <peter_e@gmx.net>
2002-04-16 05:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-16 05:06 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-16 19:12 ` Hannu Krosing <hannu@tm.ee>
2002-04-16 04:50 ` Josh Berkus <josh@agliodbs.com>
2002-04-16 04:06 ` Josh Berkus <josh@agliodbs.com>
2002-04-16 04:49 ` John Proctor <jproctor@prium.net>
2002-04-16 16:04 ` Neil Conway <nconway@klamath.dyndns.org>
2002-04-17 06:22 ` John Proctor <jproctor@prium.net>
2002-04-17 14:29 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-04-17 14:41 ` Larry Rosenman <ler@lerctr.org>
2002-04-17 16:08 ` Josh Berkus <josh@agliodbs.com>
2002-08-14 02:48 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-08-14 03:28 ` Joe Conway <mail@joeconway.com>
2002-08-14 03:36 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-08-14 02:43 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-08-14 18:30 ` Jan Wieck <JanWieck@Yahoo.com>
2002-08-14 18:34 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-04-05 16:46 Re: 16 parameter limit Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox