OT: OFF TOPIC: 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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)agliodbs(dot)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

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.

John

________________________________
From: Josh Berkus <josh(at)agliodbs(dot)com>
To: John Adams <john_adams_mail(at)yahoo(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)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

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(at)agliodbs(dot)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(at)gmail(dot)com>:
> On Fri, Sep 3, 2010 at 4:07 PM, Josh Berkus <josh(at)agliodbs(dot)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(at)postgresql(dot)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(dot)stehule(at)gmail(dot)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(at)gmail(dot)com>:
> On Sat, Sep 4, 2010 at 10:10 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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 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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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.

2010/9/3 John Adams <john_adams_mail(at)yahoo(dot)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(at)agliodbs(dot)com>
> To: John Adams <john_adams_mail(at)yahoo(dot)com>
> Cc: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)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
>
>

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 case of PostgreSQL
developers, this pay is at least comfortable, so your assumption
that this is done uncompensated, in terms of money, is simply
wrong.

For those who do development and are not directly compensated by
their employer for doing so, there are other monetary rewards,
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 straits that you
can devote time to nothing but acquiring money. If this is true,
I feel very sorry for you. I feel even sorrier for you if you are
not in such desperate straits, but you are nevertheless devoting
every waking hour to 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 are frequently 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 do with 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's hobby 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 thing is in bringing that dream
from a lonely and ethereal state to one that's shared and
concrete, where it can in turn help 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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)yahoo(dot)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(at)gmx(dot)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(at)agliodbs(dot)com>
To: John Adams <john_adams_mail(at)yahoo(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)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(at)yahoo(dot)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(at)agliodbs(dot)com>
> To: John Adams <john_adams_mail(at)yahoo(dot)com>
> Cc: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)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(at)postgresql(dot)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(at)kineticode(dot)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(dot)stehule(at)gmail(dot)com> writes:
> 2010/9/9 David E. Wheeler <david(at)kineticode(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2010/9/9 David E. Wheeler <david(at)kineticode(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2010/9/9 David E. Wheeler <david(at)kineticode(dot)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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

2010/9/9 Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)commandprompt(dot)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(at)darrenduncan(dot)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(at)darrenduncan(dot)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(at)postgresql(dot)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(dot)stehule(at)gmail(dot)com> wrote:
> 2010/9/9 Darren Duncan <darren(at)darrenduncan(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Darren Duncan <darren(at)darrenduncan(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com>:
> On Thu, Sep 9, 2010 at 4:17 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2010/9/9 Darren Duncan <darren(at)darrenduncan(dot)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(at)darrenduncan(dot)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(at)darrenduncan(dot)net>:
> Tom Lane wrote:
>>
>> Darren Duncan <darren(at)darrenduncan(dot)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(at)postgresql(dot)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(dot)stehule(at)gmail(dot)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(at)gmail(dot)com>:
> On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>

2010/9/9 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Thu, Sep 9, 2010 at 4:29 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Robert Haas <robertmhaas(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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(dot)stehule(at)gmail(dot)com> writes:
> 2010/9/9 Robert Haas <robertmhaas(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2010/9/9 Robert Haas <robertmhaas(at)gmail(dot)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(dot)Grittner(at)wicourts(dot)gov>:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)darrenduncan(dot)net>:
> Kevin Grittner wrote:
>>
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> > Darren Duncan <darren(at)darrenduncan(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +