Thread: Stored procedures returning rowsets

Stored procedures returning rowsets

From
Jarosław Nozderko
Date:
Hi,

 I've searched list archives, but the
problem I'm interested in seems not to
be frequently mentioned. I've read in
7.3 development doc about table functions.
However, are there any plans to implement
procedures/functions returning arbitrary
rowsets (not only from existing table)
that may be handled without those restrictions,
as well as return code, as it is, for
example, in Sybase ASE ?

Regards,
Jarek

Jaroslaw Nozderko
GSM +48 601131870 / Kapsch (22) 6075013
jaroslaw.nozderko@polkomtel.com.pl
IT/CCBS/RS - Analyst Programmer


Re: Stored procedures returning rowsets

From
Neil Conway
Date:
Jarosław Nozderko <jaroslaw.nozderko@polkomtel.com.pl> writes:
>  I've searched list archives, but the
> problem I'm interested in seems not to
> be frequently mentioned. I've read in
> 7.3 development doc about table functions.
> However, are there any plans to implement
> procedures/functions returning arbitrary
> rowsets (not only from existing table)

Yes -- read the proposals on -hackers and -patches sent in by Joe
Conway by more information. As of recent CVS, you can call a table
function and specify an "anonymous composite type", which basically
tells the function (at runtime) what kind of data it should return.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: Stored procedures returning rowsets

From
Joe Conway
Date:
Jarosław Nozderko wrote:
> Hi,
>
>  I've searched list archives, but the
> problem I'm interested in seems not to
> be frequently mentioned. I've read in
> 7.3 development doc about table functions.
> However, are there any plans to implement
> procedures/functions returning arbitrary
> rowsets (not only from existing table)
> that may be handled without those restrictions,
> as well as return code, as it is, for
> example, in Sybase ASE ?
>

I see Neil answered your question, but I'll add to it a bit. If you're
looking for something like the way Sybase stored procedures work (I
haven't used Sybase but I presume it is similar to MS SQL Server), you
won't see it in 7.3, at least. In other words, do you want to do:

    exec sp_my_proc_name

and have it return un arbitrarily formed result set?

We have had some discussions regarding that, but decided in favor of
table functions because they are much more useful in many ways. You can
join them with other tables, and apply selection criteria to their
output. And the anonymous composite type feature recently added will
improve the flexibility of the table function approach greatly. Also
there is a patch waiting to be accepted which will allow the creation of
named composite types which are not tables or views.

But, I do agree that sometimes the MSSQL/Sybase approach is very useful.
Maybe for 7.4 if enough people can be convinced. There have been recent
discussions regarding implementing "CREATE PROCEDURE" and "CALL
my_procedure" which are steps in this direction.

Joe



Re: Stored procedures returning rowsets

From
Jarosław Nozderko
Date:
Hi Joe and Neil,

> I see Neil answered your question, but I'll add to it a bit.
> If you're
> looking for something like the way Sybase stored procedures work (I
> haven't used Sybase but I presume it is similar to MS SQL
> Server), you

I've heard that MS SQL Server was modeled after Sybase, but I'm
not sure if this is true.

> won't see it in 7.3, at least. In other words, do you want to do:
>
>     exec sp_my_proc_name
>
> and have it return un arbitrarily formed result set?
>

It would be nice.

> We have had some discussions regarding that, but decided in favor of
> table functions because they are much more useful in many
> ways. You can
> join them with other tables, and apply selection criteria to their
> output. And the anonymous composite type feature recently added will
> improve the flexibility of the table function approach greatly. Also
> there is a patch waiting to be accepted which will allow the
> creation of
> named composite types which are not tables or views.
>
> But, I do agree that sometimes the MSSQL/Sybase approach is
> very useful.
> Maybe for 7.4 if enough people can be convinced. There have
> been recent
> discussions regarding implementing "CREATE PROCEDURE" and "CALL
> my_procedure" which are steps in this direction.
>
> Joe
>

In my opinion, it's perfectly normal and very usful to retrieve
arbitrary data from database. Stored procedures are really helpful
here, for the following reasons:
- code is stored on the server side, compiled, optimized, etc.
  It's much more efficient than planning and optimizing each incoming
  query,
- query is a business logic - if it's located in each client,
  it's harder to maintain the whole system,
- even the calls to procedures/functions are usually much shorter
  than underlying queries - it may decrease network traffic.

Perhaps not all these factors are always important, but in big and
heavy loaded systems it's really unimaginable to send "raw" queries.
I work with billing system of the cell phone operator and that's
definitely a good example of such situation.
I think this is the major drawback (there are not many of them :))
of Postgres comparing to commercial databases. I know about
several cases where Postgres was considered to be used and was
rejected just for this reason.
Anyway, it's great product :)

Regards,
Jarek

Jaroslaw Nozderko
GSM +48 601131870 / Kapsch (22) 6075013
jaroslaw.nozderko@polkomtel.com.pl
IT/CCBS/RS - Analyst Programmer

Re: Stored procedures returning rowsets

From
Michael Meskes
Date:
On Mon, Aug 12, 2002 at 10:55:55AM +0200, Jaros?aw Nozderko wrote:
> > looking for something like the way Sybase stored procedures work (I
> > haven't used Sybase but I presume it is similar to MS SQL
> > Server), you
>
> I've heard that MS SQL Server was modeled after Sybase, but I'm
> not sure if this is true.

Actually MS bought their code base from Sybase AFAIK, so yes, MSSQL is
modelled after Sybase. :-)

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Re: Stored procedures returning rowsets

From
Mark Kirkwood
Date:
Previously

 >Perhaps not all these factors are always important, but in big and
 >heavy loaded systems it's really unimaginable to send "raw" queries.

Interesting,

I have heard this often... mainly from SQL Server folk....
I find it hard to understand what is bad about "raw" sql.... here is why :

As I understand it, every query goes through the stages of
parse,execute, [and possibly fetch(es)].
Stored procedures can only save you the parse phase (at best) - which is
usually a tiny component of the total cost/elapsed time (and when it is
not, then you probably have a data warehouse with an ad-hoc query tool
issueing complex queries - which is not ameniable to the use of
procedures either).

Furthur several databases managers save (or provide methods to) the
parse tree for a query class (Oracle and DB2 for instance), so in these
cases there is zero difference in performance between "raw" and
"procedured" sql.

I dont want to do a "bash SQL Server" thing here (as its quite a good
product)...but I do wonder if it has a very expensive parse phase.

regards

Mark



Re: Stored procedures returning rowsets

From
"Mario Weilguni"
Date:
> As I understand it, every query goes through the stages of
> parse,execute, [and possibly fetch(es)].
> Stored procedures can only save you the parse phase (at best) - which is
> usually a tiny component of the total cost/elapsed time (and when it is
> not, then you probably have a data warehouse with an ad-hoc query tool

I'm not so sure about this, parsing can be quite expensive for small result
sets.



Re: Stored procedures returning rowsets

From
"Jaroslaw Nozderko"
Date:
Hi Mark,

>
> Previously
>
>  >Perhaps not all these factors are always important, but in big and
>  >heavy loaded systems it's really unimaginable to send "raw" queries.
>
> Interesting,
>
> I have heard this often... mainly from SQL Server folk....
> I find it hard to understand what is bad about "raw" sql....
> here is why :
>
> As I understand it, every query goes through the stages of
> parse,execute, [and possibly fetch(es)].

I think there is also plan/optimize stage between parse and execute...
And it may be expensive. Another advantage of stored procedures
is possibility of encapsulating business logic on server side only.

Regards,
Jarek

Jaroslaw Nozderko
GSM +48 601131870 / Kapsch (22) 6075013
jaroslaw.nozderko@polkomtel.com.pl
IT/CCBS/RS - Analyst Programmer

Re: Stored procedures returning rowsets

From
Gregory Seidman
Date:
Jaroslaw Nozderko sez:
} Hi Mark,
}
} >
} > Previously
} >
} >  >Perhaps not all these factors are always important, but in big and
} >  >heavy loaded systems it's really unimaginable to send "raw" queries.
} >
} > Interesting,
} >
} > I have heard this often... mainly from SQL Server folk....
} > I find it hard to understand what is bad about "raw" sql....
} > here is why :
} >
} > As I understand it, every query goes through the stages of
} > parse,execute, [and possibly fetch(es)].
}
} I think there is also plan/optimize stage between parse and execute...
} And it may be expensive. Another advantage of stored procedures
} is possibility of encapsulating business logic on server side only.

Excapsulating business logic on the DB server seems to be the best reason
I've heard. In fact, I am in the process of writing a large web application
and, more and more, I find that I want to write server-side plpgsql
functions to encapsulate transactions, simplifying the programmatic
interaction with the database to SELECT Func(args, ...) for the most part,
particularly for updates.

It is not, however, clear to me the difference between a stored procedure
which can be CALL'd and a function which must be SELECT'd. Can anyone
explain why the distinction is important?

} Regards,
} Jarek
--Greg


Re: Stored procedures returning rowsets

From
Tom Lane
Date:
Gregory Seidman <gss+pg@cs.brown.edu> writes:
> It is not, however, clear to me the difference between a stored procedure
> which can be CALL'd and a function which must be SELECT'd. Can anyone
> explain why the distinction is important?

As of 7.3 we should have reasonable support for functions returning
rowsets.  The main thing that a CALL-type interface could offer is the
ability for a single procedure call to return *multiple* rowsets.
You can sort of fake this now by returning multiple cursors, but it's
awkward.

The real problem with anything like this is figuring out how to
declare/bind/access output parameters from a CALL.  That doesn't
fit into SQL very well, since SQL has no such thing as a global
variable ...

            regards, tom lane

Re: Stored procedures returning rowsets

From
Joe Conway
Date:
Gregory Seidman wrote:
> Excapsulating business logic on the DB server seems to be the best reason
> I've heard. In fact, I am in the process of writing a large web application
> and, more and more, I find that I want to write server-side plpgsql
> functions to encapsulate transactions, simplifying the programmatic
> interaction with the database to SELECT Func(args, ...) for the most part,
> particularly for updates.
>
> It is not, however, clear to me the difference between a stored procedure
> which can be CALL'd and a function which must be SELECT'd. Can anyone
> explain why the distinction is important?

A SELECT'd function needs a predetermined row structure so the planner
can deal with column names of the output, joins to other row sources
(i.e. tables/views/table functions), and WHERE clause criteria. So you
must know what is to be returned (column names and types) at least in
time to specify it in the SELECT statement (ala the new anonymous
composite type just committed) Note this capability is new in the
upcoming 7.3.

A CALL'd stored procedure is stand-alone. It isn't combined with any
other row sources, you can't get just specific columns by name, and you
can't apply a WHERE clause. However, because of all these restrictions,
you also don't need to know the return row structure in advance. A
CALL'd stored procedure can produce a different structure given
different inputs, or even multiple resultsets with different structures
on one call. This can be useful (at least) while debugging your stored
proc. There are some front end report writers targeted at MS SQL Server
which can deal with these ambiguities. This capability has been
discussed, but is not planned for 7.3. And there is no agreement that it
will ever be implemented -- so if you're interested, make your voice
heard ;-).

Joe


Re: Stored procedures returning rowsets

From
Mark Kirkwood
Date:
Jaroslaw Nozderko wrote:

>Hi Mark,
>
>
>I think there is also plan/optimize stage between parse and execute...
>And it may be expensive. Another advantage of stored procedures
>is possibility of encapsulating business logic on server side only.
>
>Regards,
>Jarek
>
>Jaroslaw Nozderko
>GSM +48 601131870 / Kapsch (22) 6075013
>jaroslaw.nozderko@polkomtel.com.pl
>IT/CCBS/RS - Analyst Programmer
>
Hi Jarek,

Yes, I should have mentioned where plan and optimize fit in, I thinking
about your very point with the comment referring to the "saving of the
parse tree" - I kind of assumed that the plan information was implied
here (but ..er .. yes didnt point it out explicitly)...

Plan and optimize are typically only expensive for complex queries...
and high speed on-line business applications are usually designed use as
simple queries as possible - (optimization issues are part of this
reason). It is quite interesting to examine the TPC A|B|C benchmark full
disclosure reports - there is clearly considerable effort put into
keeping the data access simple.

Your other point - business logic in the server....is very interesting -
I have been thinking about this recently: here goes -

<rant>

1 Vendor Lock In

Every database verdor does server languages differently, so you get
vendor lock in of
your business rules - which inhibits your ability to switch backends.
(like when you get terrible service from your database vender).

2 Business Logic In The Application Server

I remember when folk started putting business logic in the database -
primarily to avoid network calls in client server applications, and to
provide some reusability and  limited encapsulation of logic.
These days application servers are the rule, and they are typically
"close" to your database server, so network is not an issue.
Re-usability and encapsulation and usually *much better* supported
within application servers than within database server languages.

</rant>

So I am thinking that the primary reasons for the creation  of extensive
database server based languages are no longer applicable today.
However, I am not suggesting the removal of database server languages,
but I think their importance in the scheme of things has diminished.

best wishes

Mark