Assigning values within Stored Procedures

aoropezaaoropeza Vertica Customer

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 $$
    start_process timestamp;
    end_process timestamp;
    y timestamp;
    start_process := select now();
    raise info 'start_process: %', start_process;
    perform SELECT SLEEP(10);
    end_process := select now();
    raise info 'end_process: %', end_process;

[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

Best Answer

  • Bryan_HBryan_H Vertica Employee Administrator
    Answer ✓

    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
    To get elapsed time for the script, use "clock_timestamp()" instead of "now()" to get the system time rather than the transaction time.


  • VValdarVValdar Vertica Employee Employee
    edited February 20

    Hi Angel,

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

  • aoropezaaoropeza Vertica Customer

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file