Assigning values within Stored Procedures
aoropeza
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 $$
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
Best Answer
-
Options
Bryan_H
Vertica Employee Administrator
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.1
Answers
Hi Angel,
This looks like a bug, could you open a support case?
Your code works also fine on 12.0.4.
Thank you for the assistance. Using the function clock_timestamp() behaves as required.