Thread: returning multiple result sets from a stored procedure
I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms).
I would like to suggest adding this feature.
- It is very usefull
- It is supported by all other dbmss I have worked with.
- makes porting applications to postgres very difficult (we have used this feature in our stored procedures and now there is no easy way of porting to postgres)
Thanks and we are waiting
Excerpts from John Adams's message of jue sep 02 18:25:45 -0400 2010: > I noticed in postgres you cannot return multiple result sets from a stored > procedure (surprisingly as it looks like a very good dbms). If you're really intent about doing this, you can emulate it by returning a set of refcursors. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, Sep 2, 2010 at 7:17 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Excerpts from John Adams's message of jue sep 02 18:25:45 -0400 2010: >> I noticed in postgres you cannot return multiple result sets from a stored >> procedure (surprisingly as it looks like a very good dbms). > > If you're really intent about doing this, you can emulate it by > returning a set of refcursors. Also arrays can work pretty well, depending on how much data there is and where it's going. merlin
> I noticed in postgres you cannot return multiple result sets from a
> stored procedure (surprisingly as it looks like a very good dbms).
That feature has been on the TODO list for years. However, nobody has
stepped forward to either write it, or to fund working on it. If your
company has programmers or money to build this feature, it could
probably get done fairly quickly (as in, next version).
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
>> I noticed in postgres you cannot return multiple result sets from a
>> stored procedure (surprisingly as it looks like a very good dbms).
> That feature has been on the TODO list for years. However, nobody has
> stepped forward to either write it, or to fund working on it. If your
> company has programmers or money to build this feature, it could
> probably get done fairly quickly (as in, next version).
Part of the reason it's sat on TODO is lack of consensus about how such
a feature ought to look/work; particularly since most of the discussion
about it has considered that it'd go along with stored procedures
executing outside of transactions. It's not just a matter of needing to
find some programming manpower.
regards, tom lane
John,
> I honestly do not mean any offence, just out of curiosity.
>
> If you guys care about money and time why would you spend the best years
> of your life basically copying commercial products for free?
We don't do it to copy commercial products. We do it to build something
better than them.
> I do not have experience with open source and I kind of thought open
> source guys do not need or care about money and time.
It's a common misapprehension that open source software is somehow
produced for free. The press has contributed to this myth a great deal
by calling open source "socialism" and "altruism". What's actually true
about open source is that the organization which releases the product
(the open source project) is not necessarily the same organzation which
pays the developers. However, if you look at any mature, large open
source project you will find that at least 1/4 of its code contributors
are paid to work on the project by *someone*, and that those paid
developers account for 70% to 95% of the code. PostgreSQL is no
exception to this rule.
The three differences between an open source project and proprietary
software in terms of adding new features are:
a) it's "pay or play", which means that you have the option of writing
the new feature yourself instead of funding it in cash, and
b) the cost of developing new features if you choose to fund them is
much cheaper (generally a couple orders of magnitude cheaper) than
proprietary software because of the open market for developers and
greater efficiency of OSS development, and
c) it's *much* easier for multiple companies to contribute to the same
project if that project is open source than if it's proprietary.
Ultimately, however, if a feature is going to be added to any OSS
project, that feature is going to be paid for by someone, either in
money, time, or both.
It does help us to get feedback like the feedback you gave eariler, even
if you can't contribute to the project because it helps us prioritize
new features. But you should recognize that if you're not contributing
money or time to the project, you may have a long wait for the feature
*you* want.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
On 9/3/10 2:20 PM, Josh Berkus wrote: > However, if you look at any mature, large open > source project you will find that at least 1/4 of its code contributors > are paid to work on the project by *someone*, and that those paid > developers account for 70% to 95% of the code. Relevant link for this: http://apcmag.com/linux-now-75-corporate.htm -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com
On Fri, Sep 3, 2010 at 4:07 PM, Josh Berkus <josh@agliodbs.com> wrote: > >> I noticed in postgres you cannot return multiple result sets from a >> stored procedure (surprisingly as it looks like a very good dbms). > > That feature has been on the TODO list for years. However, nobody has > stepped forward to either write it, or to fund working on it. If your > company has programmers or money to build this feature, it could > probably get done fairly quickly (as in, next version). Also as mentioned upthread there are effective workarounds if you poke around a bit. This is a FAQ, and there are about 3-4 solid methods (if you search the archives) that cover most problems you would be looking at multiple results sets to solve. I suppose this is why there hasn't been more of an effort to do this earlier. People asking for this are typically dispossessed SQL server developers who haven't quite gotten used to the postgres way of things. Not that proper stored procedures wouldn't be great -- they would be -- but they are not the only way to solve these types of problems. merlin
2010/9/4 Merlin Moncure <mmoncure@gmail.com>: > On Fri, Sep 3, 2010 at 4:07 PM, Josh Berkus <josh@agliodbs.com> wrote: >> >>> I noticed in postgres you cannot return multiple result sets from a >>> stored procedure (surprisingly as it looks like a very good dbms). >> >> That feature has been on the TODO list for years. However, nobody has >> stepped forward to either write it, or to fund working on it. If your >> company has programmers or money to build this feature, it could >> probably get done fairly quickly (as in, next version). > > Also as mentioned upthread there are effective workarounds if you poke > around a bit. This is a FAQ, and there are about 3-4 solid methods > (if you search the archives) that cover most problems you would be > looking at multiple results sets to solve. I suppose this is why > there hasn't been more of an effort to do this earlier. People asking > for this are typically dispossessed SQL server developers who haven't > quite gotten used to the postgres way of things. Not that proper > stored procedures wouldn't be great -- they would be -- but they are > not the only way to solve these types of problems. I had a prototype that can do multirecordset. But implementation of non transact procedures needs a hundreds hours of work: * outer SPI * parametrization for non planner statements - for CALL statement * explicit transaction control for procedures. * client API support for multirecordset * better support for OUT variables. Regards Pavel Stehule > > merlin > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Sat, Sep 4, 2010 at 10:10 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> Also as mentioned upthread there are effective workarounds if you poke >> around a bit. This is a FAQ, and there are about 3-4 solid methods >> (if you search the archives) that cover most problems you would be >> looking at multiple results sets to solve. I suppose this is why >> there hasn't been more of an effort to do this earlier. People asking >> for this are typically dispossessed SQL server developers who haven't >> quite gotten used to the postgres way of things. Not that proper >> stored procedures wouldn't be great -- they would be -- but they are >> not the only way to solve these types of problems. > > I had a prototype that can do multirecordset. But implementation of > non transact procedures needs a hundreds hours of work: > > * outer SPI > * parametrization for non planner statements - for CALL statement > * explicit transaction control for procedures. > * client API support for multirecordset > * better support for OUT variables. Curious: is mulitset handling as you see it supported by the current v3 protocol? merlin
2010/9/4 Merlin Moncure <mmoncure@gmail.com>: > On Sat, Sep 4, 2010 at 10:10 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>> Also as mentioned upthread there are effective workarounds if you poke >>> around a bit. This is a FAQ, and there are about 3-4 solid methods >>> (if you search the archives) that cover most problems you would be >>> looking at multiple results sets to solve. I suppose this is why >>> there hasn't been more of an effort to do this earlier. People asking >>> for this are typically dispossessed SQL server developers who haven't >>> quite gotten used to the postgres way of things. Not that proper >>> stored procedures wouldn't be great -- they would be -- but they are >>> not the only way to solve these types of problems. >> >> I had a prototype that can do multirecordset. But implementation of >> non transact procedures needs a hundreds hours of work: >> >> * outer SPI >> * parametrization for non planner statements - for CALL statement >> * explicit transaction control for procedures. >> * client API support for multirecordset >> * better support for OUT variables. > > Curious: is mulitset handling as you see it supported by the current > v3 protocol? if you see multirecordset as setof cursors, then you don't need changes. But in my implementation, I did a few changes, if I remember well, because my implementation wasn't based on "setof" cursors. Pavel > > merlin >
On 04/09/10 17:16, Merlin Moncure wrote: > Curious: is mulitset handling as you see it supported by the current > v3 protocol? The manual says: > The response to a SELECT query (or other queries that return row sets, such as EXPLAIN or SHOW) normally consists of RowDescription,zero or more DataRow messages, and then CommandComplete. COPY to or from the frontend invokes special protocolas described in Section 46.2.5. All other query types normally produce only a CommandComplete message. > > Since a query string could contain several queries (separated by semicolons), there might be several such response sequencesbefore the backend finishes processing the query string. ReadyForQuery is issued when the entire string has beenprocessed and the backend is ready to accept a new query string. If a multiple return sets from a procedure are returned just like multiple return sets from multiple queries, that's already covered by the protocol. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> On 04/09/10 17:16, Merlin Moncure wrote:
>> Curious: is mulitset handling as you see it supported by the current
>> v3 protocol?
> If a multiple return sets from a procedure are returned just like
> multiple return sets from multiple queries, that's already covered by
> the protocol.
Well, the protocol says you can do it, but it would likely require
significant work to make clients deal with it sanely.
Also, the part of the protocol document Heikki is quoting is for the
legacy "simple query" mode. We deliberately designed this behavior
*out* of the extended query mode. So for example you couldn't use
out-of-line parameters with such a feature, unless there's a protocol
redesign.
regards, tom lane
On fre, 2010-09-03 at 16:18 -0400, Tom Lane wrote: > Part of the reason it's sat on TODO is lack of consensus about how > such a feature ought to look/work; particularly since most of the > discussion about it has considered that it'd go along with stored > procedures executing outside of transactions. I would probably be a mistake to tie these features together. They are tricky enough separately.
On 9/5/2010 2:05 PM, Heikki Linnakangas wrote: > On 04/09/10 17:16, Merlin Moncure wrote: >> Curious: is mulitset handling as you see it supported by the current >> v3 protocol? > > The manual says: > >> The response to a SELECT query (or other queries that return row sets, such as >> EXPLAIN or SHOW) normally consists of RowDescription, zero or more DataRow >> messages, and then CommandComplete. COPY to or from the frontend invokes >> special protocol as described in Section 46.2.5. All other query types >> normally produce only a CommandComplete message. >> >> Since a query string could contain several queries (separated by semicolons), >> there might be several such response sequences before the backend finishes >> processing the query string. ReadyForQuery is issued when the entire string >> has been processed and the backend is ready to accept a new query string. > > If a multiple return sets from a procedure are returned just like multiple > return sets from multiple queries, that's already covered by the protocol. > Just as a side note, libpqtypes can emulate this using composite arrays; a feature we abuse internally. It is actually the primary justification we had for developing that portion of libpqtypes; initially we stayed clear of arrays and composites. create table fork_t (fork_id, rev_id, size, block_ids int8[], ...) create table rev_t (rev_id, blah, blah, fork_t[]); /* this is my favorite part of libpqtypes */ PGarray arr; PQgetf(result, tup_num, "%rev_t[]", field_num, &arr); Now loop the array "arr" and getf(arr.res) for each rev_t, which allows you to getf each fork_t in the fork_t[], etc.... I *know* it is not pure multiset'n, but it sure gets the job done (in a completely different way, I know). However, I'm sure those reading this list can see the possiblities ;) Andrew Chernow eSilo, LLC.
<div style="font-family:times new roman,new york,times,serif;font-size:12pt"><div style="font-family: times new roman,newyork,times,serif; font-size: 12pt;"></div></div><p class="MsoNormal">OT: <br /><p class="MsoNormal">OFF TOPIC:<pclass="MsoNormal">I honestly do not mean any offence, just out of curiosity. <p class="MsoNormal">If you guys careabout money and time why would you spend the best years of your life basically copying commercial products for free?Because for a person with higher than average IQ far less than one percent of any program is creative and needs somethinking and the bulk of it is just a million stupid details. <br /><p class="MsoNormal">I just don't follow/understandyour thinking. Maybe I am naïve. <p class="MsoNormal">I do not have experience with open source and I kindof thought open source guys do not need or care about money and time. <br />John<br /><br /><div style="font-family:arial,helvetica,sans-serif; font-size: 13px;"><font face="Tahoma" size="2"><hr size="1" /><b><span style="font-weight:bold;">From:</span></b> Josh Berkus <josh@agliodbs.com><br /><b><span style="font-weight: bold;">To:</span></b>John Adams <john_adams_mail@yahoo.com><br /><b><span style="font-weight: bold;">Cc:</span></b>PostgreSQL-development <pgsql-hackers@postgreSQL.org><br /><b><span style="font-weight: bold;">Sent:</span></b>Fri, September 3, 2010 1:07:03 PM<br /><b><span style="font-weight: bold;">Subject:</span></b> Re:[HACKERS] returning multiple result sets from a stored procedure<br /></font><br /><br />> I noticed in postgres youcannot return multiple result sets from a<br />> stored procedure (surprisingly as it looks like a very good dbms).<br/><br />That feature has been on the TODO list for years. However, nobody has<br />stepped forward to either writeit, or to fund working on it. If your<br />company has programmers or money to build this feature, it could<br />probablyget done fairly quickly (as in, next version).<br /><br />-- <br /> -- Josh Berkus<br/> PostgreSQL Experts Inc.<br /><span> <ahref="http://www.pgexperts.com" target="_blank">http://www.pgexperts.com</a></span><br /></div><br />
2010/9/3 John Adams <john_adams_mail@yahoo.com>: > OT: > > OFF TOPIC: > > I honestly do not mean any offence, just out of curiosity. > > If you guys care about money and time why would you spend the best years of > your life basically copying commercial products for free? Because for a > person with higher than average IQ far less than one percent of any program > is creative and needs some thinking and the bulk of it is just a million > stupid details. > > I just don't follow/understand your thinking. Maybe I am naïve. > > I do not have experience with open source and I kind of thought open source > guys do not need or care about money and time. The work on PostgreSQL is adventure, and very good experience, very good school for me. It's job only for people who like programming, who like hacking, it isn't job for people, who go to office on 8 hours. Next I use PostgreSQL for my job - and hacking on PostgreSQL put me a perfect knowledge, perfect contacts to developers, and I can work together with best programmers on planet. and I can create some good things. Probably if I work on commercial projects I can have a better money - but life is only one, and money is important, but not on top for me - life have to be adventure! Regards Pavel Stehule > > John > > ________________________________ > From: Josh Berkus <josh@agliodbs.com> > To: John Adams <john_adams_mail@yahoo.com> > Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org> > Sent: Fri, September 3, 2010 1:07:03 PM > Subject: Re: [HACKERS] returning multiple result sets from a stored > procedure > > >> I noticed in postgres you cannot return multiple result sets from a >> stored procedure (surprisingly as it looks like a very good dbms). > > That feature has been on the TODO list for years. However, nobody has > stepped forward to either write it, or to fund working on it. If your > company has programmers or money to build this feature, it could > probably get done fairly quickly (as in, next version). > > -- > -- Josh Berkus > PostgreSQL Experts Inc. > http://www.pgexperts.com > >
Re: OT: OFF TOPIC: returning multiple result sets from a stored procedure
From
"David E. Wheeler"
Date:
On Sep 6, 2010, at 12:07 AM, Pavel Stehule wrote: > The work on PostgreSQL is adventure, and very good experience, very > good school for me. It's job only for people who like programming, who > like hacking, it isn't job for people, who go to office on 8 hours. > Next I use PostgreSQL for my job - and hacking on PostgreSQL put me a > perfect knowledge, perfect contacts to developers, and I can work > together with best programmers on planet. and I can create some good > things. Probably if I work on commercial projects I can have a better > money - but life is only one, and money is important, but not on top > for me - life have to be adventure! Could not have said it better myself. Best, David
On Fri, Sep 03, 2010 at 01:40:56PM -0700, John Adams wrote: > OT: > > OFF TOPIC: > I honestly do not mean any offence, just out of curiosity. > If you guys care about money and time why would you spend the best > years of your life basically copying commercial products for free? > Because for a person with higher than average IQ far less than one > percent of any program is creative and needs some thinking and the > bulk of it is just a million stupid details. It's difficult to answer a question when there are so many different wrong assumptions that underlie it. I'll take pieces of the questions, explicitly state the assumptions that underlie them, and explain what I mean by "wrong." "If you guys care about money" Here you're assuming that open source code development on large projects like PostgreSQL is done in people's spare time. In reality, 80-95% of such development is done by people who are paid by their workplace to do so. In the caseof PostgreSQL developers, this pay is at least comfortable, so your assumption that this is done uncompensated, interms of money, is simply wrong. For those who do development and are not directly compensated by their employer for doing so, there are other monetaryrewards, such as being able to put such projects on résumés/CVs, which in turn results in better job prospects,consulting fees for specialized knowledge, etc., etc. "and time why would you spend the best years of your life" That time's compensated, in many different ways, as illustrated above. Perhaps your life is in such desperate straitsthat you can devote time to nothing but acquiring money. If this is true, I feel very sorry for you. I feeleven sorrier for you if you are not in such desperate straits, but you are nevertheless devoting every waking hourto the pursuit of money. It's a sad and lonely way to waste your precious days of life. "basically copying" In a technological sense, FLOSS often leads the way and "products" catch up later if at all. FLOSS technologies arefrequently so much better than their proprietary counterparts that they kill existing markets (C compilers, e.g.),and cause markets in other technologies (dynamic languages, e.g.) never to form. "commercial products for free?" There's a lot of confusion about this word. "Commercial" means "of or pertaining to commerce." It has nothing to dowith whether the license is permissive like PostgreSQL's or extremely restrictive as it is with, say the Windows EULA. In future, if you wish to contrast licenses, it's free vs. proprietary, and if you wish to contrast usage, it'shobby vs. commerce vs. science, roughly speaking. "Because for a person with higher than average IQ far less than one percent of any program is creative and needs some thinking and the bulk of it is just a million stupid details." The difference between imagining something and actually accomplishing it is precisely those "million stupid details." The truly rewarding thing isn't dreaming up some wonderful dream. That's easy. The truly rewarding thingis in bringing that dream from a lonely and ethereal state to one that's shared and concrete, where it can in turnhelp spawn new dreams, which people then realize and share, and on and on and on. > I just don't follow/understand your thinking. Maybe I am naïve. You're that, clearly, along with being misinformed, young, and arrogant. Fortunately, all of these things but youth are fixable if you decide to do the work to fix them, and by the time you've done that work, your youth will also be waning ;) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Fri, Sep 3, 2010 at 4:40 PM, John Adams <john_adams_mail@yahoo.com> wrote: > If you guys care about money and time why would you spend the best years of > your life basically copying commercial products for free? I don't work for free. :-) There was a point at which this was just a hobby for me, but as it has since turned into a job, it's hard for me to say that the time I spent on it had no economic value. But it is also true that it was a great hobby. Working on PostgreSQL gave me an opportunity to work with some absolutely brilliant programmers, which is not something I've frequently gotten a chance to do in the course of my previous employment. And it's also fun to feel like you're contributing something back to a project that you've gotten so much out of. With respect to copying commerical products, we may be doing that to some extent, but it's not because we're sitting around going "oh, so what has Oracle done lately?". We tend to think about what PostgreSQL needs and work on that. Sometimes there's overlap, other times not. > Because for a > person with higher than average IQ far less than one percent of any program > is creative and needs some thinking and the bulk of it is just a million > stupid details. I haven't written a program that matched this expectation since I was in high school. And I think that was only because I wasn't as good a programmer then as I thought I was. My experience is that most programming requires a lot of careful thought and good design, and that doing this well is not easy. This is doubly true for a large, complex, and mature project like PostgreSQL, where changes need to be exceedingly carefully thought out. > I just don't follow/understand your thinking. Maybe I am naïve. > > I do not have experience with open source and I kind of thought open source > guys do not need or care about money and time. I try not to make money the center of my life, but I like to get paid as much as the next guy. Many of the regulars here derive a substantial portion of their income from PostgreSQL-related work of one kind or another. Even when my PostgreSQL development was a hobby, a big part of my job revolved around developing FOR PostgreSQL. Filing down some of the rough edges I encountered during that development was one of the things that drew me to the project (the other being the aforementioned really smart people). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
On Sun, Sep 5, 2010 at 2:26 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > On fre, 2010-09-03 at 16:18 -0400, Tom Lane wrote: >> Part of the reason it's sat on TODO is lack of consensus about how >> such a feature ought to look/work; particularly since most of the >> discussion about it has considered that it'd go along with stored >> procedures executing outside of transactions. > > I would probably be a mistake to tie these features together. They are > tricky enough separately. Hm, do you think it would be possible to request manual transaction state when setting up the procedure (or reserve that ability for the future)? merlin
OK, how much are we talking about?
________________________________
From: Josh Berkus <josh@agliodbs.com>
To: John Adams <john_adams_mail@yahoo.com>
Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Sent: Fri, September 3, 2010 1:07:03 PM
Subject: Re: [HACKERS] returning multiple result sets from a stored procedure
> I noticed in postgres you cannot return multiple result sets from a
> stored procedure (surprisingly as it looks like a very good dbms).
That feature has been on the TODO list for years. However, nobody has
stepped forward to either write it, or to fund working on it. If your
company has programmers or money to build this feature, it could
probably get done fairly quickly (as in, next version).
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
Hello 2010/9/9 John Adams <john_adams_mail@yahoo.com>: > OK, how much are we talking about? about 2 months for full time and 2 months for partial time - is my tip depends on set of implemented features regards Pavel Stehule > ________________________________ > From: Josh Berkus <josh@agliodbs.com> > To: John Adams <john_adams_mail@yahoo.com> > Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org> > Sent: Fri, September 3, 2010 1:07:03 PM > Subject: Re: [HACKERS] returning multiple result sets from a stored procedure > > >> I noticed in postgres you cannot return multiple result sets from a >> stored procedure (surprisingly as it looks like a very good dbms). > > That feature has been on the TODO list for years. However, nobody has > stepped forward to either write it, or to fund working on it. If your > company has programmers or money to build this feature, it could > probably get done fairly quickly (as in, next version). > > -- > -- Josh Berkus > PostgreSQL Experts Inc. > http://www.pgexperts.com > > > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote: > about 2 months for full time and 2 months for partial time - is my tip Two months full or two months partial? I'll take the latter, please! David
2010/9/9 David E. Wheeler <david@kineticode.com>: > On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote: > >> about 2 months for full time and 2 months for partial time - is my tip > > Two months full or two months partial? I'll take the latter, please! 2 months - basic implementation 1 months - cleaning and work on commiting ---- sum - 3 month ---- Regards Pavel p.s. I am working on basic syntax - CALL and OUT variables. But I'll not try to implement a transaction related features. Regards Pavel Stehule Two months full for basic implementation and two months partial > > David >
Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2010/9/9 David E. Wheeler <david@kineticode.com>:
>> On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote:
>>> about 2 months for full time and 2 months for partial time - is my tip
>>
>> Two months full or two months partial? I'll take the latter, please!
> 2 months - basic implementation
> 1 months - cleaning and work on commiting
> ---- sum - 3 month ----
And zero time spent on convincing -hackers that the design is good?
Not likely to get committed that way.
regards, tom lane
2010/9/9 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2010/9/9 David E. Wheeler <david@kineticode.com>: >>> On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote: >>>> about 2 months for full time and 2 months for partial time - is my tip >>> >>> Two months full or two months partial? I'll take the latter, please! > >> 2 months - basic implementation >> 1 months - cleaning and work on commiting >> ---- sum - 3 month ---- > > And zero time spent on convincing -hackers that the design is good? > Not likely to get committed that way. then I have to add 6 months more :) regards Pavel Stehule > > regards, tom lane >
2010/9/9 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2010/9/9 David E. Wheeler <david@kineticode.com>: >>> On Sep 9, 2010, at 12:12 AM, Pavel Stehule wrote: >>>> about 2 months for full time and 2 months for partial time - is my tip >>> >>> Two months full or two months partial? I'll take the latter, please! > >> 2 months - basic implementation >> 1 months - cleaning and work on commiting >> ---- sum - 3 month ---- > > And zero time spent on convincing -hackers that the design is good? > Not likely to get committed that way. > there are lot of questions - and I am not sure if procedures implementation can be done in one release cycle. The basic questions: * should be special catalog for procedures or we will use pg_proc? * how can be implemented OUT variables - the original implementation is simple - it's just pointer, but it's not directly possible inside postgres, because we use a MemoryContexts? * how can be implement a CALL statement - as plan statement or as command? * how can be implemented variables inside psql console, if we allows them? * how can be implement an overloading of procedures - can we use for selection OUT variables too? * what is procedure? It's like void function, or it can return status code like procedures in SQL/PSM (DB2)? --- As long years a stored procedures developer, I can say, so just minimal implementation of procedures can help with writing little bit more readable code for functions that return more then one scalar result. But other features can be nice too - explicit transaction control and unbind selects. But these features are killing gun. Regards Pavel Stehule > regards, tom lane >
Excerpts from Pavel Stehule's message of jue sep 09 14:29:57 -0400 2010: > * how can be implemented OUT variables - the original implementation > is simple - it's just pointer, but it's not directly possible inside > postgres, because we use a MemoryContexts? Why is this a problem? Just return a bunch of tuplestores, no? -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
2010/9/9 Alvaro Herrera <alvherre@commandprompt.com>: > Excerpts from Pavel Stehule's message of jue sep 09 14:29:57 -0400 2010: > >> * how can be implemented OUT variables - the original implementation >> is simple - it's just pointer, but it's not directly possible inside >> postgres, because we use a MemoryContexts? > > Why is this a problem? Just return a bunch of tuplestores, no? and what context do you use? And you will pack and unpack tuple when some field will be changed every time? this is an possibility to solve our performance problems with arrays or strings. Regards Pavel Stehule > > -- > Álvaro Herrera <alvherre@commandprompt.com> > The PostgreSQL Company - Command Prompt, Inc. > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
Pavel Stehule wrote: > there are lot of questions - and I am not sure if procedures > implementation can be done in one release cycle. The basic questions: > > * should be special catalog for procedures or we will use pg_proc? > * how can be implemented OUT variables - the original implementation > is simple - it's just pointer, but it's not directly possible inside > postgres, because we use a MemoryContexts? > * how can be implement a CALL statement - as plan statement or as command? > * how can be implemented variables inside psql console, if we allows them? > * how can be implement an overloading of procedures - can we use for > selection OUT variables too? > * what is procedure? It's like void function, or it can return status > code like procedures in SQL/PSM (DB2)? > > --- As long years a stored procedures developer, I can say, so just > minimal implementation of procedures can help with writing little bit > more readable code for functions that return more then one scalar > result. But other features can be nice too - explicit transaction > control and unbind selects. But these features are killing gun. I've often considered that the main distinction between a function and a procedure is that the former is intended to be invoked as a value-resulting expression while the latter is intended to be invoked as a non-value-resulting statement. The SQL standard uses separate FUNCTION and PROCEDURE for these. Since Pg's FUNCTION already seems to take on both roles, so overloading the meaning of the FUNCTION keyword, like what a C function or a Perl sub does, where returning VOID means procedure, then what is being added by a distinct PROCEDURE? Or is the VOID-returning FUNCTION going to be deprecated or discouraged at the same time? -- Darren Duncan
Darren Duncan <darren@darrenduncan.net> writes:
> Since Pg's FUNCTION already seems to take on both roles, so overloading the
> meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
> where returning VOID means procedure, then what is being added by a distinct
> PROCEDURE?
You might care to go back and re-read some of the extensive prior
threads about this, but to my mind the main thing that would justify
inventing a separate PROCEDURE facility is if procedures were to execute
outside the transaction system, so that they could start and stop
transactions for themselves. This is unlike a function which
necessarily executes inside an already-running transaction. Of course
a lot of questions would need to be answered about error-handling
behavior and so forth, but that's a capability that a LOT of people
have asked for.
> Or is the VOID-returning FUNCTION going to be deprecated or
> discouraged at the same time?
Certainly not.
regards, tom lane
2010/9/9 Darren Duncan <darren@darrenduncan.net>: > Pavel Stehule wrote: >> >> there are lot of questions - and I am not sure if procedures >> implementation can be done in one release cycle. The basic questions: >> >> * should be special catalog for procedures or we will use pg_proc? >> * how can be implemented OUT variables - the original implementation >> is simple - it's just pointer, but it's not directly possible inside >> postgres, because we use a MemoryContexts? >> * how can be implement a CALL statement - as plan statement or as command? >> * how can be implemented variables inside psql console, if we allows them? >> * how can be implement an overloading of procedures - can we use for >> selection OUT variables too? >> * what is procedure? It's like void function, or it can return status >> code like procedures in SQL/PSM (DB2)? >> >> --- As long years a stored procedures developer, I can say, so just >> minimal implementation of procedures can help with writing little bit >> more readable code for functions that return more then one scalar >> result. But other features can be nice too - explicit transaction >> control and unbind selects. But these features are killing gun. > > I've often considered that the main distinction between a function and a > procedure is that the former is intended to be invoked as a value-resulting > expression while the latter is intended to be invoked as a > non-value-resulting statement. The SQL standard uses separate FUNCTION and > PROCEDURE for these. > > Since Pg's FUNCTION already seems to take on both roles, so overloading the > meaning of the FUNCTION keyword, like what a C function or a Perl sub does, > where returning VOID means procedure, then what is being added by a distinct > PROCEDURE? Or is the VOID-returning FUNCTION going to be deprecated or > discouraged at the same time? > the overloading of function is based only on input parameters - because there are not entered output variables - it is just some record. But overloading of procedures, can be based on input and output variables. so I can to write CREATE PROCEDURE foo(OUT a int) ... and CREATE PROCEDURE foo(OUT a varchar) ... and then when I use a statement CALL is correct procedure selected CALL foo(textvariable) Regards Pavel > -- Darren Duncan > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Thu, Sep 9, 2010 at 4:17 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2010/9/9 Darren Duncan <darren@darrenduncan.net>: >> Pavel Stehule wrote: >>> >>> there are lot of questions - and I am not sure if procedures >>> implementation can be done in one release cycle. The basic questions: >>> >>> * should be special catalog for procedures or we will use pg_proc? >>> * how can be implemented OUT variables - the original implementation >>> is simple - it's just pointer, but it's not directly possible inside >>> postgres, because we use a MemoryContexts? >>> * how can be implement a CALL statement - as plan statement or as command? >>> * how can be implemented variables inside psql console, if we allows them? >>> * how can be implement an overloading of procedures - can we use for >>> selection OUT variables too? >>> * what is procedure? It's like void function, or it can return status >>> code like procedures in SQL/PSM (DB2)? >>> >>> --- As long years a stored procedures developer, I can say, so just >>> minimal implementation of procedures can help with writing little bit >>> more readable code for functions that return more then one scalar >>> result. But other features can be nice too - explicit transaction >>> control and unbind selects. But these features are killing gun. >> >> I've often considered that the main distinction between a function and a >> procedure is that the former is intended to be invoked as a value-resulting >> expression while the latter is intended to be invoked as a >> non-value-resulting statement. The SQL standard uses separate FUNCTION and >> PROCEDURE for these. >> >> Since Pg's FUNCTION already seems to take on both roles, so overloading the >> meaning of the FUNCTION keyword, like what a C function or a Perl sub does, >> where returning VOID means procedure, then what is being added by a distinct >> PROCEDURE? Or is the VOID-returning FUNCTION going to be deprecated or >> discouraged at the same time? >> > > the overloading of function is based only on input parameters - > because there are not entered output variables - it is just some > record. But overloading of procedures, can be based on input and > output variables. > > so I can to write > > CREATE PROCEDURE foo(OUT a int) > ... > > and > CREATE PROCEDURE foo(OUT a varchar) > ... > > and then when I use a statement CALL is correct procedure selected > > CALL foo(textvariable) That seems like a lot of complexity for no real benefit, to me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
2010/9/9 Tom Lane <tgl@sss.pgh.pa.us>: > Darren Duncan <darren@darrenduncan.net> writes: >> Since Pg's FUNCTION already seems to take on both roles, so overloading the >> meaning of the FUNCTION keyword, like what a C function or a Perl sub does, >> where returning VOID means procedure, then what is being added by a distinct >> PROCEDURE? > > You might care to go back and re-read some of the extensive prior > threads about this, but to my mind the main thing that would justify > inventing a separate PROCEDURE facility is if procedures were to execute > outside the transaction system, so that they could start and stop > transactions for themselves. This is unlike a function which > necessarily executes inside an already-running transaction. Of course > a lot of questions would need to be answered about error-handling > behavior and so forth, but that's a capability that a LOT of people > have asked for. > it's only one request from two mayor request * transaction handling * unbound SELECTs and multirecordset support and some more classic handling of OUT variables. Pavel >> Or is the VOID-returning FUNCTION going to be deprecated or >> discouraged at the same time? > > Certainly not. > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Tom Lane <tgl@sss.pgh.pa.us> wrote: > to my mind the main thing that would justify inventing a separate > PROCEDURE facility is if procedures were to execute outside the > transaction system, so that they could start and stop transactions > for themselves. That is the biggest distinction in my mind, too. Supporting multiple result sets just as if the queries were run as independent client-side statements would also be very important. I have seen implementations which support, for a single stored procedure, OUT parameters, a RETURN value, and multiple result sets -- all at the same time, as separate things. I haven't reviewed stored procedures in the SQL standard since an early draft proposal years ago, so I don't know what the current state of that is, but if PostgreSQL approaches this, it'd be nice to implement as many of the above as are not in conflict with requirements of the standard. -Kevin
2010/9/9 Robert Haas <robertmhaas@gmail.com>: > On Thu, Sep 9, 2010 at 4:17 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> 2010/9/9 Darren Duncan <darren@darrenduncan.net>: >>> Pavel Stehule wrote: >>>> >>>> there are lot of questions - and I am not sure if procedures >>>> implementation can be done in one release cycle. The basic questions: >>>> >>>> * should be special catalog for procedures or we will use pg_proc? >>>> * how can be implemented OUT variables - the original implementation >>>> is simple - it's just pointer, but it's not directly possible inside >>>> postgres, because we use a MemoryContexts? >>>> * how can be implement a CALL statement - as plan statement or as command? >>>> * how can be implemented variables inside psql console, if we allows them? >>>> * how can be implement an overloading of procedures - can we use for >>>> selection OUT variables too? >>>> * what is procedure? It's like void function, or it can return status >>>> code like procedures in SQL/PSM (DB2)? >>>> >>>> --- As long years a stored procedures developer, I can say, so just >>>> minimal implementation of procedures can help with writing little bit >>>> more readable code for functions that return more then one scalar >>>> result. But other features can be nice too - explicit transaction >>>> control and unbind selects. But these features are killing gun. >>> >>> I've often considered that the main distinction between a function and a >>> procedure is that the former is intended to be invoked as a value-resulting >>> expression while the latter is intended to be invoked as a >>> non-value-resulting statement. The SQL standard uses separate FUNCTION and >>> PROCEDURE for these. >>> >>> Since Pg's FUNCTION already seems to take on both roles, so overloading the >>> meaning of the FUNCTION keyword, like what a C function or a Perl sub does, >>> where returning VOID means procedure, then what is being added by a distinct >>> PROCEDURE? Or is the VOID-returning FUNCTION going to be deprecated or >>> discouraged at the same time? >>> >> >> the overloading of function is based only on input parameters - >> because there are not entered output variables - it is just some >> record. But overloading of procedures, can be based on input and >> output variables. >> >> so I can to write >> >> CREATE PROCEDURE foo(OUT a int) >> ... >> >> and >> CREATE PROCEDURE foo(OUT a varchar) >> ... >> >> and then when I use a statement CALL is correct procedure selected >> >> CALL foo(textvariable) > > That seems like a lot of complexity for no real benefit, to me. no, you can to specify a expected result type - it's very for some convert or import functions. So we expect so out procedures will supports to OUT parameters, then implementation of this mechanism has minimal overhead to current implementation. Just to add types of OUT parameters to searching algorithm. More - it is just consistent with overloading idea. Why the OUT parameters should be removed from procedure parameters? > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company >
Tom Lane wrote: > Darren Duncan <darren@darrenduncan.net> writes: >> Since Pg's FUNCTION already seems to take on both roles, so overloading the >> meaning of the FUNCTION keyword, like what a C function or a Perl sub does, >> where returning VOID means procedure, then what is being added by a distinct >> PROCEDURE? > > You might care to go back and re-read some of the extensive prior > threads about this, but to my mind the main thing that would justify > inventing a separate PROCEDURE facility is if procedures were to execute > outside the transaction system, so that they could start and stop > transactions for themselves. This is unlike a function which > necessarily executes inside an already-running transaction. Of course > a lot of questions would need to be answered about error-handling > behavior and so forth, but that's a capability that a LOT of people > have asked for. That is a very strong rationale in my mind to have clearly distinct kinds of routines, where one kind is implicitly entirely contained in a transaction and the other kind can cross transaction boundaries or control transactions. I support the separation on those grounds alone, though it also makes sense that the 2 kinds can have additional ways to distinguish them. -- Darren Duncan
2010/9/9 Darren Duncan <darren@darrenduncan.net>: > Tom Lane wrote: >> >> Darren Duncan <darren@darrenduncan.net> writes: >>> >>> Since Pg's FUNCTION already seems to take on both roles, so overloading >>> the meaning of the FUNCTION keyword, like what a C function or a Perl sub >>> does, where returning VOID means procedure, then what is being added by a >>> distinct PROCEDURE? >> >> You might care to go back and re-read some of the extensive prior >> threads about this, but to my mind the main thing that would justify >> inventing a separate PROCEDURE facility is if procedures were to execute >> outside the transaction system, so that they could start and stop >> transactions for themselves. This is unlike a function which >> necessarily executes inside an already-running transaction. Of course >> a lot of questions would need to be answered about error-handling >> behavior and so forth, but that's a capability that a LOT of people >> have asked for. > > That is a very strong rationale in my mind to have clearly distinct kinds of > routines, where one kind is implicitly entirely contained in a transaction > and the other kind can cross transaction boundaries or control transactions. > I support the separation on those grounds alone, though it also makes sense > that the 2 kinds can have additional ways to distinguish them. -- Darren > Duncan Functions should be under transaction always, but procedures when people like. There is "BEGIN ATOMIC ... END" block defined in SQL/PSM and procedure can be defined as ATOMIC or non ATOMIC. For me - most important difference is activation - function is activated from SELECT statement - and SELECT has plan - the result is hardly specified, procedure is activated by CALL statement - there are not plan - the result isn't limited. Regards Pavel Stehule > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>> so I can to write >>> >>> CREATE PROCEDURE foo(OUT a int) >>> ... >>> >>> and >>> CREATE PROCEDURE foo(OUT a varchar) >>> ... >>> >>> and then when I use a statement CALL is correct procedure selected >>> >>> CALL foo(textvariable) >> >> That seems like a lot of complexity for no real benefit, to me. > > no, you can to specify a expected result type - it's very for some > convert or import functions. So we expect so out procedures will > supports to OUT parameters, then implementation of this mechanism has > minimal overhead to current implementation. Just to add types of OUT > parameters to searching algorithm. > > More - it is just consistent with overloading idea. Why the OUT > parameters should be removed from procedure parameters? I think the question is whether there's something broken enough about the current system to warrant doing something different, and I guess my answer would be no. To be honest, I am already pretty unhappy with the changes that make it impossible to redefined foo(a int) as foo(anteater int), which is a perfectly reasonable thing to want to do but which is now forbidden because someone MIGHT have called the function as foo(a := 3), and I certainly don't want to make it any worse. Whether there are actually any stored queries that call the function this way (or at all) is doesn't matter: it's not allowed. So for a marginal notational convenience we have created dependency hell, where you must drop and recreate every dependent object to perform a trivial renaming. I think this is really quite horrible and would have argued against accepting this patch at the time if I'd realized what effect it was going to have. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
2010/9/9 Robert Haas <robertmhaas@gmail.com>: > On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>>> so I can to write >>>> >>>> CREATE PROCEDURE foo(OUT a int) >>>> ... >>>> >>>> and >>>> CREATE PROCEDURE foo(OUT a varchar) >>>> ... >>>> >>>> and then when I use a statement CALL is correct procedure selected >>>> >>>> CALL foo(textvariable) >>> >>> That seems like a lot of complexity for no real benefit, to me. >> >> no, you can to specify a expected result type - it's very for some >> convert or import functions. So we expect so out procedures will >> supports to OUT parameters, then implementation of this mechanism has >> minimal overhead to current implementation. Just to add types of OUT >> parameters to searching algorithm. >> >> More - it is just consistent with overloading idea. Why the OUT >> parameters should be removed from procedure parameters? > > I think the question is whether there's something broken enough about > the current system to warrant doing something different, and I guess > my answer would be no. To be honest, I am already pretty unhappy with > the changes that make it impossible to redefined foo(a int) as > foo(anteater int), which is a perfectly reasonable thing to want to do > but which is now forbidden because someone MIGHT have called the > function as foo(a := 3), and I certainly don't want to make it any > worse. Whether there are actually any stored queries that call the > function this way (or at all) is doesn't matter: it's not allowed. So > for a marginal notational convenience we have created dependency hell, > where you must drop and recreate every dependent object to perform a > trivial renaming. I think this is really quite horrible and would > have argued against accepting this patch at the time if I'd realized > what effect it was going to have. > yes, named parameters for functions created a new dependency. But this isn't possible for procedures. You can not to use a procedure inside view. So new dependency are not possible there. This important on procedures - it is little bit more outer from database. Pavel > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company >
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> to my mind the main thing that would justify inventing a separate
>> PROCEDURE facility is if procedures were to execute outside the
>> transaction system, so that they could start and stop transactions
>> for themselves.
> That is the biggest distinction in my mind, too. Supporting
> multiple result sets just as if the queries were run as independent
> client-side statements would also be very important. I have seen
> implementations which support, for a single stored procedure, OUT
> parameters, a RETURN value, and multiple result sets -- all at the
> same time, as separate things.
That seems rather overkill to me --- in particular, I don't understand
the point of a RETURN value when there can be no caller to return a
value to. Scalar OUT parameters could be sensible though; those could
be returned to the client as a one-row result set.
One point that has to be made is that returning multiple result sets
as if they were successive queries restricts the client to reading the
result sets serially; that is, you must read all of result A before you
can look at result B, etc. While that's frequently good enough, an
advantage of the return-some-cursors approach is that you can scan the
cursors in parallel. I'm not sure whether we need to provide that
flexibility in a procedure facility. One reason not to worry about it
is that you can't return a cursor if the procedure is outside any
transaction --- unless you make it a WITH HOLD cursor, which is mighty
expensive and should certainly not be the default behavior. It might
be sufficient to say that anyone needing that capability can return
names of WITH HOLD cursors as scalar OUT parameters, or use the existing
FUNCTION infrastructure.
regards, tom lane
2010/9/9 Robert Haas <robertmhaas@gmail.com>: > On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>>> so I can to write >>>> >>>> CREATE PROCEDURE foo(OUT a int) >>>> ... >>>> >>>> and >>>> CREATE PROCEDURE foo(OUT a varchar) >>>> ... >>>> >>>> and then when I use a statement CALL is correct procedure selected >>>> >>>> CALL foo(textvariable) >>> >>> That seems like a lot of complexity for no real benefit, to me. >> >> no, you can to specify a expected result type - it's very for some >> convert or import functions. So we expect so out procedures will >> supports to OUT parameters, then implementation of this mechanism has >> minimal overhead to current implementation. Just to add types of OUT >> parameters to searching algorithm. >> >> More - it is just consistent with overloading idea. Why the OUT >> parameters should be removed from procedure parameters? > > I think the question is whether there's something broken enough about > the current system to warrant doing something different, and I guess > my answer would be no. To be honest, I am already pretty unhappy with > the changes that make it impossible to redefined foo(a int) as > foo(anteater int), which is a perfectly reasonable thing to want to do > but which is now forbidden because someone MIGHT have called the > function as foo(a := 3), and I certainly don't want to make it any > worse. Whether there are actually any stored queries that call the > function this way (or at all) is doesn't matter: it's not allowed. So > for a marginal notational convenience we have created dependency hell, > where you must drop and recreate every dependent object to perform a > trivial renaming. I don't agree with you - this behave is because pg doesn't hold dependency between functions and preparsed SQL - so this is one the most simple protection. But if somebody appends a relations between views and functions to dictionary, then he can rechecks necessary views automatically. Regards Pavel Stehule I think this is really quite horrible and would > have argued against accepting this patch at the time if I'd realized > what effect it was going to have. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company >
Robert Haas <robertmhaas@gmail.com> writes:
> To be honest, I am already pretty unhappy with
> the changes that make it impossible to redefined foo(a int) as
> foo(anteater int), which is a perfectly reasonable thing to want to do
> but which is now forbidden because someone MIGHT have called the
> function as foo(a := 3), and I certainly don't want to make it any
> worse. Whether there are actually any stored queries that call the
> function this way (or at all) is doesn't matter: it's not allowed.
BTW, before anyone suggests it: it wouldn't really improve matters if
we were to allow renaming so long as we couldn't find any such calls in
stored queries. We don't have any ability to track calls occuring in
stored procedures, let alone on the client side; so a rename would still
put you at very substantial risk of breaking things.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > I don't understand the point of a RETURN value when there can be > no caller to return a value to. The version of the draft standard I last saw allowed something like: SET x = CALL sp(param_a, param_b); I seem to remember Sybase supported a return value as well as OUT parameters, too; I think there it was limited to integer values and was conventionally used to indicate overall success or failure of the procedure. -Kevin
Kevin Grittner wrote: > Tom Lane <tgl@sss.pgh.pa.us> wrote: >> to my mind the main thing that would justify inventing a separate >> PROCEDURE facility is if procedures were to execute outside the >> transaction system, so that they could start and stop transactions >> for themselves. > > That is the biggest distinction in my mind, too. Supporting > multiple result sets just as if the queries were run as independent > client-side statements would also be very important. I have seen > implementations which support, for a single stored procedure, OUT > parameters, a RETURN value, and multiple result sets -- all at the > same time, as separate things. I haven't reviewed stored procedures > in the SQL standard since an early draft proposal years ago, so I > don't know what the current state of that is, but if PostgreSQL > approaches this, it'd be nice to implement as many of the above as > are not in conflict with requirements of the standard. If it was reasonable I would go further in splitting and have at least 4 distinct kinds of routines, here listed in order of invocablility (each routine kind can invoke anything above it on the list but not anything below it): 1. Expression-invoked pure functions that only have IN parameters and can not directly see the database or have any side-effects and are always in a transaction. Most operators are of this kind. 2. Statement-invoked routines that are pure like #1 but also have OUT/INOUT parameters instead of resulting in a value like a function. The assignment operator is of this kind. 3. Routines that *can* see and update the database but are otherwise like #2, and are always in a transaction. The general case of a SELECT or DML or DDL are of this kind. 4. Routines that can cross transaction boundaries or control transactions but are otherwise like #2 or #3. Transaction control statements are of this kind. If I understand correctly, the existing Pg FUNCTION is essentially #3 and the proposed PROCEDURE is essentially #4. Maybe I just have to RTFM but I don't know if it is possible now to declare a Pg FUNCTION that it stays in the restrictions of #1 or #2. But if not, then I think it would be valuable to do so, for assisting reliability and performance. -- Darren Duncan
Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2010/9/9 Robert Haas <robertmhaas@gmail.com>:
>> That seems like a lot of complexity for no real benefit, to me.
> no, you can to specify a expected result type - it's very for some
> convert or import functions. So we expect so out procedures will
> supports to OUT parameters, then implementation of this mechanism has
> minimal overhead to current implementation. Just to add types of OUT
> parameters to searching algorithm.
It's *not* trivial, not at all. You are ignoring all of the semantic
implications. Should foo(IN x int, OUT y int) be considered different
from, and thus allowed to exist at the same time as, foo(IN x int,
OUT y float)? If so, how do you represent that in the catalogs?
Possibly more to the point, any such decision means that it'll be
impossible to call any stored procedure without fully specifying the
types of output arguments as well as input arguments, else the system
can't tell which procedure you meant to call. That doesn't sound like
a notational improvement to me.
I'm with Robert: this would be a huge extra complication for a
remarkably small amount of benefit.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > I'm with Robert: this would be a huge extra complication for a > remarkably small amount of benefit. This is probably heresy, but unless it's required by the standard or drop-dead simple to allow, I'd be fine with *not* supporting overloading of stored procedure names based on argument types at all. I can see the need for to support it for functions; I can't think where it would be all that useful for stored procedures. If unique stored procedure names were required, it seems we might be able to allow assignment casts on parameters, which might be more useful. I'm probably missing some good use case.... -Kevin
2010/9/9 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2010/9/9 Robert Haas <robertmhaas@gmail.com>: >>> That seems like a lot of complexity for no real benefit, to me. > >> no, you can to specify a expected result type - it's very for some >> convert or import functions. So we expect so out procedures will >> supports to OUT parameters, then implementation of this mechanism has >> minimal overhead to current implementation. Just to add types of OUT >> parameters to searching algorithm. > > It's *not* trivial, not at all. You are ignoring all of the semantic > implications. Should foo(IN x int, OUT y int) be considered different > from, and thus allowed to exist at the same time as, foo(IN x int, > OUT y float)? If so, how do you represent that in the catalogs? > Possibly more to the point, any such decision means that it'll be > impossible to call any stored procedure without fully specifying the > types of output arguments as well as input arguments, else the system > can't tell which procedure you meant to call. That doesn't sound like > a notational improvement to me. it can be a foo(int, int) and foo(int, float) in catalog. These lists are distinct so there are not a problem. > > I'm with Robert: this would be a huge extra complication for a > remarkably small amount of benefit. > I don't agree - you see a procedure like some void function, and it's not true Regards Pavel > regards, tom lane >
2010/9/9 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> I'm with Robert: this would be a huge extra complication for a
>> remarkably small amount of benefit.
>
> This is probably heresy, but unless it's required by the standard or
> drop-dead simple to allow, I'd be fine with *not* supporting
> overloading of stored procedure names based on argument types at
> all. I can see the need for to support it for functions; I can't
> think where it would be all that useful for stored procedures. If
> unique stored procedure names were required, it seems we might be
> able to allow assignment casts on parameters, which might be more
> useful.
>
> I'm probably missing some good use case....
for example - value transformation from / to bytea
CREATE FUNCTION send(int);
CREATE FUNCTION send(text);
CREATE FUNCTION recv(int);
CREATE FUNCTION recv(text)
then you can write
BEGIN send('ahoj'); send(10); recv(textvar); recv(numvar);
Regards
Pavel Stehule
>
> -Kevin
>
Tom Lane wrote: > One point that has to be made is that returning multiple result sets > as if they were successive queries restricts the client to reading the > result sets serially; that is, you must read all of result A before you > can look at result B, etc. One aspect that I don't really like about SQL contrasted with typical other languages is in how query results are typically returned "out of band" like the above describes, rather than explicitly either via an OUT/INOUT parameter or as a function result relation value. -- Darren Duncan
2010/9/9 Darren Duncan <darren@darrenduncan.net>: > Kevin Grittner wrote: >> >> Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> >>> to my mind the main thing that would justify inventing a separate >>> PROCEDURE facility is if procedures were to execute outside the >>> transaction system, so that they could start and stop transactions >>> for themselves. >> >> That is the biggest distinction in my mind, too. Supporting >> multiple result sets just as if the queries were run as independent >> client-side statements would also be very important. I have seen >> implementations which support, for a single stored procedure, OUT >> parameters, a RETURN value, and multiple result sets -- all at the >> same time, as separate things. I haven't reviewed stored procedures >> in the SQL standard since an early draft proposal years ago, so I >> don't know what the current state of that is, but if PostgreSQL >> approaches this, it'd be nice to implement as many of the above as >> are not in conflict with requirements of the standard. > > If it was reasonable I would go further in splitting and have at least 4 > distinct kinds of routines, here listed in order of invocablility (each > routine kind can invoke anything above it on the list but not anything below > it): > > 1. Expression-invoked pure functions that only have IN parameters and can > not directly see the database or have any side-effects and are always in a > transaction. Most operators are of this kind. > > 2. Statement-invoked routines that are pure like #1 but also have OUT/INOUT > parameters instead of resulting in a value like a function. The assignment > operator is of this kind. > > 3. Routines that *can* see and update the database but are otherwise like > #2, and are always in a transaction. The general case of a SELECT or DML or > DDL are of this kind. > > 4. Routines that can cross transaction boundaries or control transactions > but are otherwise like #2 or #3. Transaction control statements are of this > kind. > > If I understand correctly, the existing Pg FUNCTION is essentially #3 and > the proposed PROCEDURE is essentially #4. > Immutable functions are very near to #1. Actually PostgreSQL OUT parameters are implemented as returned one composite value. Regards Pavel > Maybe I just have to RTFM but I don't know if it is possible now to declare > a Pg FUNCTION that it stays in the restrictions of #1 or #2. But if not, > then I think it would be valuable to do so, for assisting reliability and > performance. > > -- Darren Duncan > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On tor, 2010-09-09 at 13:08 -0700, Darren Duncan wrote: > Since Pg's FUNCTION already seems to take on both roles, so > overloading the meaning of the FUNCTION keyword, like what a C > function or a Perl sub does, where returning VOID means procedure, > then what is being added by a distinct PROCEDURE? I'd just like to have the CALL statement, because CALL do_something(); looks better than SELECT do_something(); Small details ...
On tor, 2010-09-09 at 16:16 -0400, Tom Lane wrote: > You might care to go back and re-read some of the extensive prior > threads about this, but to my mind the main thing that would justify > inventing a separate PROCEDURE facility is if procedures were to > execute outside the transaction system, so that they could start and > stop transactions for themselves. Given what the SQL standard says, a "procedure" certainly has to be defined as syntactic sugar for "function returns void". Special transaction handling would then have to be an additional attribute of the procedure.
Peter Eisentraut wrote: > On tor, 2010-09-09 at 13:08 -0700, Darren Duncan wrote: >> Since Pg's FUNCTION already seems to take on both roles, so >> overloading the meaning of the FUNCTION keyword, like what a C >> function or a Perl sub does, where returning VOID means procedure, >> then what is being added by a distinct PROCEDURE? > > I'd just like to have the CALL statement, because > > CALL do_something(); > > looks better than > > SELECT do_something(); > > Small details ... I don't have a problem with the PERFORM keyword aside from it being more verbose than CALL, except that, with 8.4 anyway, PERFORM doesn't seem to work client-side. It seems that to invoke a VOID function foo client-side I still have to say "SELECT foo(...);" because saying "PERFORM foo(...);" is unfortunately a syntax error. -- Darren Duncan
Pavel Stehule wrote:
> 2010/9/9 Tom Lane <tgl@sss.pgh.pa.us>:
> > Darren Duncan <darren@darrenduncan.net> writes:
> >> Since Pg's FUNCTION already seems to take on both roles, so overloading the
> >> meaning of the FUNCTION keyword, like what a C function or a Perl sub does,
> >> where returning VOID means procedure, then what is being added by a distinct
> >> PROCEDURE?
> >
> > You might care to go back and re-read some of the extensive prior
> > threads about this, but to my mind the main thing that would justify
> > inventing a separate PROCEDURE facility is if procedures were to execute
> > outside the transaction system, so that they could start and stop
> > transactions for themselves. ?This is unlike a function which
> > necessarily executes inside an already-running transaction. ?Of course
> > a lot of questions would need to be answered about error-handling
> > behavior and so forth, but that's a capability that a LOT of people
> > have asked for.
> >
>
> it's only one request from two mayor request
>
> * transaction handling
> * unbound SELECTs and multirecordset support
>
> and some more classic handling of OUT variables.
I assume the current thought is that our "functions" would remain
unchanged and new "procedures" would allow either of these. I have
updated the "procedure" todo item to read:
Implement stored procedures
This might involve the control of transaction state and the return of multiple result sets
* PL/pgSQL stored procedure returning multiple result sets (SELECTs)? * Proposal: real procedures again
(8.4) * http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +