Thread: Re: [SQL] 16 parameter limit
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 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
Bruce Momjian writes: > The following patch adds --maxindfuncparams to configure to allow you to > more easily set the maximum number of function parameters and columns > in an index. (Can someone come up with a better name?) > Are we staying at 16 as the default? I personally think we can > increase it to 32 with little penalty, If you want to increase it, let's just increase it and not add any more configure options. If someone wants more than 32 then we really need to start talking about design issues. -- Peter Eisentraut peter_e@gmx.net
> > Are we staying at 16 as the default? I personally think we can > > increase it to 32 with little penalty, > > If you want to increase it, let's just increase it and not add any more > configure options. If someone wants more than 32 then we really need to > start talking about design issues. Why not give them the configure option? It's not good HCI to impose arbitrary limits on people...? We can default it to 32, since there's demand for it. If a particular user decided to configure it higher, then they do that knowing that it may cause performance degradation. It's good to give them that choice though. Chris
On 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
> 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
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
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
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
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
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
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
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
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
letme 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
errorprone 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
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 >
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
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
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
letme 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
errorprone 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
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
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
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
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 #
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