Assigning values within Stored Procedures

aoropeza

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

Bryan_H

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.

VValdar

Hi Angel,

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

aoropeza

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