Forum: Building VoltDB Applications

Post: Stored procedure calling another one?

Stored procedure calling another one?
chbussler
Apr 9, 2010
Hi,

is it possible that a stored procedure calls another one? If so, what is the transaction semantics of this situation, i.e., is it one or
two transactions that are taking place?


Thanks,

Christoph
Stored procedure calling another stored procedure
tcallaghan
Apr 9, 2010
Christoph,

You should not call a stored procedure from within a stored procedure. While it is technically possible, the cost of doing it would far outweigh any benefit. From within the first stored procedure you would need to incur all the overhead of connecting to the cluster, authenticating, and invoking the second stored procedure (you are, essentially, putting much of a client application within it). There is also the issue of additional threads if you are using our Java client library. Remember too that while the first stored procedure is setting up to call the second it is blocking all other execution on its partition.

Basically, you need to duplicate your "shared" stored procedure logic.

Let me know if this makes sense.

-Tim
Ok
chbussler
Apr 9, 2010
Christoph,

You should not call a stored procedure from within a stored procedure. While it is technically possible, the cost of doing it would far outweigh any benefit. From within the first stored procedure you would need to incur all the overhead of connecting to the cluster, authenticating, and invoking the second stored procedure (you are, essentially, putting much of a client application within it). There is also the issue of additional threads if you are using our Java client library. Remember too that while the first stored procedure is setting up to call the second it is blocking all other execution on its partition.

Basically, you need to duplicate your "shared" stored procedure logic.

Let me know if this makes sense.

-Tim

Hi Tim,

thanks for the clarification and based on that I need to structure the reuse of logic differently; no problem.

Thanks,
Christoph
Calling a stored procedure
rbetts
Apr 12, 2010
Christoph,

You should not call a stored procedure from within a stored procedure. While it is technically possible, the cost of doing it would far outweigh any benefit. From within the first stored procedure you would need to incur all the overhead of connecting to the cluster, authenticating, and invoking the second stored procedure (you are, essentially, putting much of a client application within it). There is also the issue of additional threads if you are using our Java client library. Remember too that while the first stored procedure is setting up to call the second it is blocking all other execution on its partition.

Basically, you need to duplicate your "shared" stored procedure logic.

Let me know if this makes sense.

-Tim

Calling a stored procedure from with a stored procedure is not technically feasible.

1. Calling the second procedure's run method directly will not function as expected.

2. Invoking a service over a socket introduces blocking behavior that defeats one of the main design points of volt procedures.

3. VoltDB ensures serializability of procedure invocations. This prohibits the second (nested) invocation from completing before the first procedure.