Skip to main content
Question

Assigning values within Stored Procedures

  • February 20, 2024
  • 3 replies
  • 6 views

aoropeza
Forum|alt.badge.img+1

I have a stored procedure where, at the conclusion of execution, I must display the elapsed time. To accomplish this, I have created two variables named start_process and end_process. However, upon completion of execution, the value returned by these variables is identical. This issue arose after our upgrade to Vertica 23.3.0-2; such behavior was not observed in version 11.1.

Has anything changed, or am I overlooking something?

DO $$
DECLARE
    start_process timestamp;
    end_process timestamp;
    y timestamp;
BEGIN
    start_process := select now();
    raise info 'start_process: %', start_process;
    perform SELECT SLEEP(10);
    end_process := select now();
    raise info 'end_process: %', end_process;
END;
$$

[2024-02-19 18:20:32] [V0002][2005] start_process: 2024-02-19 18:20:22.162039
[2024-02-19 18:20:32] [V0002][2005] end_process: 2024-02-19 18:20:22.162039

3 replies

VValdar
Forum|alt.badge.img+1
  • Participating Frequently
  • February 20, 2024

Hi Angel,

This looks like a bug, could you open a support case?
Your code works also fine on 12.0.4.


Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • February 20, 2024

Behavior changed in 23.3.0 where stored procedures, including nested procedures, run in a single transaction, where previously each statement auto-committed. This means that "now()" will return the same timestamp unless there is an explicit PERFORM COMMIT in the stored procedure, and then it will still report the same timestamp each time it is called in the new transaction. Please see the release note at https://docs.vertica.com/23.3.x/en/new-features/23.3/stored-procedures/
To get elapsed time for the script, use "clock_timestamp()" instead of "now()" to get the system time rather than the transaction time.


aoropeza
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • February 21, 2024

Thank you for the assistance. Using the function clock_timestamp() behaves as required.