Re: Stored procedures returning rowsets

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(dot)nozderko(at)polkomtel(dot)com(dot)pl
IT/CCBS/RS - Analyst Programmer

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

Gregory Seidman <gss+pg(at)cs(dot)brown(dot)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

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

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(dot)nozderko(at)polkomtel(dot)com(dot)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