Обсуждение: PL/pgSQL TODO
Bruce, Tom, Peter: I was just checking the TODO list and noticed that none of my suggestions or John Proctors suggestions regarding PL/pgSQL improvements had made it on to the list. Is this because our ideas have been rejected, or because nobody has time to deal with the TODO list? -- -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> I was just checking the TODO list and noticed that none of my suggestions or
> John Proctors suggestions regarding PL/pgSQL improvements had made it on to
> the list. Is this because our ideas have been rejected, or because nobody
> has time to deal with the TODO list?
Bruce normally maintains the TODO list, and he's been on vacation all
this month (won't be back till June 1, I believe). It's just lagging
a bit ...
regards, tom lane
Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: > > I was just checking the TODO list and noticed that none of my suggestions or > > John Proctors suggestions regarding PL/pgSQL improvements had made it on to > > the list. Is this because our ideas have been rejected, or because nobody > > has time to deal with the TODO list? > > Bruce normally maintains the TODO list, and he's been on vacation all > this month (won't be back till June 1, I believe). It's just lagging > a bit ... I have reached this message and I your TODO item discussion was: http://groups.google.com/groups?hl=en&lr=&threadm=20020517114156.Q20428-100000%40megazone23.bigpanda.com&rnum=18&prev=/groups%3Fq%3DJosh%2BBerkus%2Bgroup:comp.databases.postgresql.*%26start%3D10%26hl%3Den%26lr%3D%26scoring%3Dd%26selm%3D20020517114156.Q20428-100000%2540megazone23.bigpanda.com%26rnum%3D18 And I assume the TODO item is: * Allow DEFERRABLE UNIQUE constraints Added to TODO under Referential Integrity. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce, > I have reached this message and I your TODO item discussion was: > >http://groups.google.com/groups?hl=en&lr=&threadm=20020517114156.Q20428-100000%40megazone23.bigpanda.com&rnum=18&prev=/groups%3Fq%3DJosh%2BBerkus%2Bgroup:comp.databases.postgresql.*%26start%3D10%26hl%3Den%26lr%3D%26scoring%3Dd%26selm%3D20020517114156.Q20428-100000%2540megazone23.bigpanda.com%26rnum%3D18 Nope. Wrong discussion. I'm referring to a 14-item list John Proctor and I composed to enhance PL/pgSQL and make it equal to Oracle's PL/SQL. I'll dig through my sent mail and find it. Not that the statement-deferrable constraints would be a bad thing, at all. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Bruce, BTW, thank you for adding this one: > And I assume the TODO item is: > > * Allow DEFERRABLE UNIQUE constraints > > Added to TODO under Referential Integrity. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
OK, found it, attached, from April 17. It is still sitting in my "review" email box to be summarized into the TODO list. In fact, I have ~30 emails from that thread that i have to re-read and summarize. Tom is right, I would have reviewed it by now if it wasn't for my vacation. --------------------------------------------------------------------------- Josh Berkus wrote: > Bruce, > > > I have reached this message and I your TODO item discussion was: > > > > > http://groups.google.com/groups?hl=en&lr=&threadm=20020517114156.Q20428-100000%40megazone23.bigpanda.com&rnum=18&prev=/groups%3Fq%3DJosh%2BBerkus%2Bgroup:comp.databases.postgresql.*%26start%3D10%26hl%3Den%26lr%3D%26scoring%3Dd%26selm%3D20020517114156.Q20428-100000%2540megazone23.bigpanda.com%26rnum%3D18 > > Nope. Wrong discussion. I'm referring to a 14-item list John Proctor > and I composed to enhance PL/pgSQL and make it equal to Oracle's > PL/SQL. I'll dig through my sent mail and find it. > > Not that the statement-deferrable constraints would be a bad thing, at > all. > > -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 > -- 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 From pgsql-patches-owner+M3698@postgresql.org Wed Apr 17 09:09:11 2002 Return-path: <pgsql-patches-owner+M3698@postgresql.org> Received: from postgresql.org (postgresql.org [64.49.215.8]) by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3HD9AS20404 for <pgman@candle.pha.pa.us>; Wed, 17 Apr 2002 09:09:10 -0400 (EDT) Received: from postgresql.org (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with SMTP id 655364763CB; Wed, 17 Apr 2002 08:51:04 -0400 (EDT) Received: from slxmail01.prium.net (unknown [64.2.173.194]) by postgresql.org (Postfix) with ESMTP id 0EDB7475A59 for <pgsql-patches@postgresql.org>; Wed, 17 Apr 2002 02:21:47 -0400 (EDT) Received: from there ([192.168.1.199]) by slxmail01.prium.net (8.11.0/8.8.7) with SMTP id g3H6JKa29940; Wed, 17 Apr 2002 02:19:25 -0400 Message-ID: <200204170619.g3H6JKa29940@slxmail01.prium.net> Content-Type: text/plain; charset="iso-8859-1" From: John Proctor <jproctor@prium.net> To: Neil Conway <nconway@klamath.dyndns.org> Subject: Re: [PATCHES] [SQL] 16 parameter limit Date: Wed, 17 Apr 2002 01:22:14 -0500 X-Mailer: KMail [version 1.3.2] cc: josh@agliodbs.com, peter_e@gmx.net, pgman@candle.pha.pa.us, tgl@sss.pgh.pa.us, pgsql-patches@postgresql.org References: <web-1371993@davinci.ethosmedia.com> <200204160446.g3G4kha06293@slxmail01.prium.net> <20020416120437.3e68fcff.nconway@klamath.dyndns.org> In-Reply-To: <20020416120437.3e68fcff.nconway@klamath.dyndns.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Precedence: bulk Sender: pgsql-patches-owner@postgresql.org Status: OR 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 ---------------------------(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
Bruce, > OK, found it, attached, from April 17. It is still sitting in my > "review" email box to be summarized into the TODO list. In fact, I > have > ~30 emails from that thread that i have to re-read and summarize. There should be a follow-up e-mail from me listing 4-5 more items. If you can't find it, I will. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Josh Berkus wrote: > Bruce, > > > OK, found it, attached, from April 17. It is still sitting in my > > "review" email box to be summarized into the TODO list. In fact, I > > have > > ~30 emails from that thread that i have to re-read and summarize. > > There should be a follow-up e-mail from me listing 4-5 more items. If > you can't find it, I will. Yep, got that one too, attached. -- 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 From pgsql-patches-owner+M3724@postgresql.org Thu Apr 18 08:28:59 2002 Return-path: <pgsql-patches-owner+M3724@postgresql.org> Received: from postgresql.org (postgresql.org [64.49.215.8]) by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g3ICSwS21973 for <pgman@candle.pha.pa.us>; Thu, 18 Apr 2002 08:28:58 -0400 (EDT) Received: from postgresql.org (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with SMTP id AE09F47626E; Thu, 18 Apr 2002 08:19:00 -0400 (EDT) Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id B492C4763E9 for <pgsql-patches@postgresql.org>; Wed, 17 Apr 2002 12:08:55 -0400 (EDT) Received: from [63.195.55.98] (account <josh@agliodbs.com>) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1374310; Wed, 17 Apr 2002 09:08:58 -0700 From: "Josh Berkus" <josh@agliodbs.com> Subject: Re: [PATCHES] [SQL] 16 parameter limit 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 X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Wed, 17 Apr 2002 09:08:58 -0700 Message-ID: <web-1374310@davinci.ethosmedia.com> In-Reply-To: <200204170619.g3H6JKa29940@slxmail01.prium.net> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Precedence: bulk Sender: pgsql-patches-owner@postgresql.org Status: OR 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Josh Berkus wrote: > Bruce, > > BTW, thank you for adding this one: > > > And I assume the TODO item is: > > > > * Allow DEFERRABLE UNIQUE constraints > > > > Added to TODO under Referential Integrity. > > -Josh Not that it has anything to do with referential integrity though. It's more related to btree index implementation:-) But would be cool. 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: > Josh Berkus wrote: > > Bruce, > > > > BTW, thank you for adding this one: > > > > > And I assume the TODO item is: > > > > > > * Allow DEFERRABLE UNIQUE constraints > > > > > > Added to TODO under Referential Integrity. > > > > -Josh > > Not that it has anything to do with referential integrity > though. It's more related to btree index implementation :-) > > But would be cool. Well, deferrablility I think of as part of constraints, though you are right that it is btree that needs the fixing. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026