Re: Full Stored Procedure Support, any time soon ?

Hi,

My firm is considering a very large move from PAID RDBMS to Postgresql. 
The lack of full support for Stored Procedures is currently a deal breaker.

We use many features, like:
- output parameters
- Callable statements to call the procs
- Multiple result sets
- nested procedure calls

Is there in the *near* future the possibility of adding support for these?

Simple functions are _not_ enough. EnterpriseDB seems to have such support but it is a payed for feature.

Any help will be greatly appreciated.

Many thanks

Noeldr 

On 11/30/2013 12:17 PM, Noel Diaz wrote:
>
> My firm is considering a very large move from PAID RDBMS to Postgresql.
> The lack of full support for Stored Procedures is currently a deal
> breaker.
>
> We use many features, like:
> - output parameters
> - Callable statements to call the procs
> - Multiple result sets
> - nested procedure calls
>
> Is there in the *near* future the possibility of adding support for these?

we're doing much the same, moving from that big O to Postgres, but we
opted to STOP using stored procedures and instead reimplement our core
business logic as a middleware service (which happens to be in Java but
really could be in most any language that has decent database and
messaging and/or RPC support). we find its a lot easier to debug the
middleware than complex problems buried deep in stored procedures. We
still use SOME postgres functions, for performance optimization,
triggers (for partitioning) and so forth, but we don't have the bulk of
our business logic there. the client applications are not allowed ANY
database access, they have to go through the middleware.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

On 30 Nov 2013, at 21:17, Noel Diaz <zerg2k(at)yahoo(dot)com> wrote:

> Hi,

Welcome to the list!

> My firm is considering a very large move from PAID RDBMS to Postgresql.
> The lack of full support for Stored Procedures is currently a deal breaker.

We’re not intimately familiar with your paid RDBMS, so perhaps you could elaborate on what _exactly_ you’re missing? I’m going to wager a guess to what you mean though, hopefully that helps clear things up.

As far as people here are concerned, procedures are merely functions that don’t return anything. We have those.

> We use many features, like:
> - output parameters

Functions have IN, OUT, INOUT and VARIADIC parameters. They also have return parameters. How does that not cover what you require?

http://www.postgresql.org/docs/9.2/static/sql-createfunction.html

> - Callable statements to call the procs

Not sure what you mean here. Obviously functions can be called, otherwise what would be their point?

You can use either select my_func() for functions that return a single value or select … from my_func() for functions that return result sets.

> - Multiple result sets

Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets?

Functions can handle multiple result sets internally. You can loop through as many result sets in your function as you like.

If the functions return a refcursor or a set of records, they can even _return_ multiple result sets, but you’ll have to cast those function result rows to a meaningful type before being able to use them.

> - nested procedure calls

Function calls can be nested, they even support recursion.

> Simple functions are _not_ enough.

Why not? What don’t they do that you require?

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Alban, 

Thank you very much for the Link.

Let me see if I can explain from the beginning:

I see that output parameters are possible in functions as well as return values!! So that is a plus!

When I mentioned "Callable Statements" I was referring to the way we call the procs from java : http://jdbc.postgresql.org/documentation/91/callproc.html

I was not sure that "cstm.prepareCall" was allowed with functions (all examples I saw used preparedStatements) but after further digging further it seems possible as the link above indicates. This is another plus! 

When I mentioned "multiple result sets",  That is EXACTLY what it means 
You can create a procedure that does something like this:

SELECT * FROM customers WHERE ... ; /* any complex query */
SELECT * FROM orders  ............;   /* any other set that DOES NOT needs to be joined to the above*/
.... And so on                                 /* Sometimes this is useful because intermediate results do not need to be sent back to the clients etc */ 

With that we can, in ONE round trip,  process data and we use the cstmt.getMoreResults  / cstmt.getResultSet Pattern like so: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.apdv.java.doc%2Fdoc%2Ftjvjdmlt.htm

Hopefully I shed some light in my incomplete description

Thank you for the prompt reply,

Noeldr

On Saturday, November 30, 2013 4:33 PM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

On 30 Nov 2013, at 21:17, Noel Diaz <zerg2k(at)yahoo(dot)com> wrote:

> Hi,

Welcome to the list!

> My firm is considering a very large move from PAID RDBMS to Postgresql.
> The lack of full support for Stored Procedures is currently a deal breaker.

We’re not intimately familiar with your paid RDBMS, so perhaps you could elaborate on what _exactly_ you’re missing? I’m going to wager a guess to what you mean though, hopefully that helps clear things up.

As far as people here are concerned, procedures are merely functions that don’t return anything. We have those.

> We use many features, like:
> - output parameters

Functions have IN, OUT, INOUT and VARIADIC parameters. They also have return parameters. How does that not cover what you require?

http://www.postgresql.org/docs/9.2/static/sql-createfunction.html

> - Callable statements to call the procs

Not sure what you mean here. Obviously functions can be called, otherwise what would be their point?

You can use either select my_func() for functions that return a single value or select … from my_func() for functions that return result sets.

> - Multiple result sets

Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets?

Functions can handle multiple result sets internally. You can loop through as many result sets in your function as you like.

If the functions return a refcursor or a set of records, they can even _return_ multiple result sets, but you’ll have to cast those function result rows to a meaningful type before being able to use them.

> - nested procedure calls

Function calls can be nested, they even support recursion.

> Simple functions are _not_ enough.

Why not? What don’t they do that you require?

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Alban Hertroys wrote on 30.11.2013 22:34:
>> - Multiple result sets
>
> Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets?

Yes, basically something like this:

create procedure foobar()
begin
select * from table_1;
select * from table_2
end;

I know that at least SQL Server and MySQL can do that. Maybe others as well (Sybase most probably).

But I always failed do see the actual advantage of that because the results can't be "used" any further (e.g. in a JOIN or a subselect).
I also don't understand why having a single procedure doing a lot of stuff is better than having several procedures (or functions) doing one defined thing.

From a JDBC point of view the code simply asks the Statement whether it has any more result sets
and loops over this until all results are returned.

Thomas

Thomas, RIGHT ON!

Sometimes I have seen this feature used to hide multiple round trips and intermediate result processing from the clients
Some others is simply because the client is generating a predefined data structure that has little resemblance to the schema and this "feature" becomes convenient
Some others is "ad-hoc" tools that allow to see multiple datasets in the UI, etc...

Anyway I just saw this in the TODO list: (http://wiki.postgresql.org/wiki/Todo) :

_ 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
* Gathering specs and discussion on feature (post 9.1)

Does anybody know if any the committers are working on that list?

On Saturday, November 30, 2013 5:21 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:

Alban Hertroys wrote on 30.11.2013 22:34:

>> - Multiple result sets
>
> Since you’re talking about procedures, you can’t possibly mean that those return multiple result sets?

Yes, basically something like this:

create procedure foobar()
begin
  select * from table_1;
  select * from table_2
end;

I know that at least SQL Server and MySQL can do that. Maybe others as well (Sybase most probably).

But I always failed do see the actual advantage of that because the results can't be "used" any further (e.g. in a JOIN or a subselect).
I also don't understand why having a single procedure doing a lot of stuff is better than having several procedures (or functions) doing one defined thing.

From a JDBC point of view the code simply asks the Statement whether it has any more result sets
and loops over this until all results are returned.

Thomas

On 11/30/2013 2:30 PM, Noel Diaz wrote:
> This might involve the control of transaction state and the return
> of multiple result sets
>
> * PL/pgSQL stored procedure returning multiple result sets
> (SELECTs)?
> <http://archives.postgresql.org/pgsql-general/2008-10/msg00454.php>
> * Proposal: real procedures again (8.4)
> <http://archives.postgresql.org/pgsql-hackers/2007-10/msg01375.php>
> * http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php
> * Gathering specs and discussion on feature (post 9.1)
> <http://archives.postgresql.org/pgsql-hackers/2011-04/msg01149.php>
>
> Does anybody know if any the committers are working on that list?

I don't think the libpq API will allow that first one, a SQL query can
only return a single recordset of rows that all have the same fields.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

John R Pierce wrote on 30.11.2013 23:47:
> On 11/30/2013 2:30 PM, Noel Diaz wrote:
>> This might involve the control of transaction state and the return of multiple result sets
>>
>> * PL/pgSQL stored procedure returning multiple result sets (SELECTs)? <http://archives.postgresql.org/pgsql-general/2008-10/msg00454.php>
>> * Proposal: real procedures again (8.4) <http://archives.postgresql.org/pgsql-hackers/2007-10/msg01375.php>
>> * http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php
>> * Gathering specs and discussion on feature (post 9.1) <http://archives.postgresql.org/pgsql-hackers/2011-04/msg01149.php>
>>
>> Does anybody know if any the committers are working on that list?
>
> I don't think the libpq API will allow that first one, a SQL query can only return a single recordset
>of rows that all have the same fields.

I don't think that's a limitation. The client will still iterate through one result set at a time.

The "potential" result sets would need to be somehow "prepared to be sent" on the server. When the client has exhausted one result set, it requests the next one (or asks if there _is_ a next one). Only then the server starts streaming the results. That way libpg would only deal with a single result at a time.

I don't think SQL Server or MySQL actually send all results at once (definitely not when using JDBC)

On 11/30/2013 3:27 PM, Thomas Kellerer wrote:
> I don't think that's a limitation. The client will still iterate
> through one result set at a time.

PQexec etc return a PGresult *

the various PQxxxxx functions that accept PGresult * as an argument,
each only return info about the single resultset, like PQntuples,
PQnfields, PQgetvalue, PQfname, PQfnumber, etc... I guess we'd need
some new functions to return the number of resultsets, and choose a
resultset by index number, then those above functions would refer to the
'currently chosen' PQresult ? or at least a res =
PQnextresult(PGresult *res), which returns 0 if there is no next resultset ?

--
john r pierce 37N 122W
somewhere on the middle of the left coast

Noel Diaz wrote
> T_ 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
> * Gathering specs and discussion on feature (post 9.1)
>
> Does anybody know if any the committers are working on that list?

I have not seen any recent discussions on the list pertaining to
implementing callable stored procedures. Thus nothing like this has a
chance to make it out with the next release 6+ months from now. The
earliest possible implementation would be 1.5 years from now and that is
only if someone decides to dedicate the next year solely on this feature.

Some "foundation" work has been ongoing recently, notably "background
workers", which is how these things are likely to play out.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Full-Stored-Procedure-Support-any-time-soon-tp5780999p5781040.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.