This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Executing stored procedures

If you have EXECUTE privileges on a stored procedure, you can execute it with a CALL statement that specifies the procedure and its IN arguments.

If you have EXECUTE privileges on a stored procedure, you can execute it with a CALL statement that specifies the procedure and its IN arguments.

Syntax

CALL stored_procedure_name();

For example, the stored procedure raiseXY() is defined as:

The following procedure echoes its inputs as a result set:

=> CREATE PROCEDURE echo_int_varchar(INOUT x INT, INOUT y VARCHAR) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE NOTICE 'This procedure outputs a result set of its inputs:';
END
$$;

=> CALL echo_int_varchar(3, 'a string');
NOTICE 2005:  This procedure outputs a result set of its inputs:
 x |    y
---+----------
 3 | a string
(1 row)

You can execute an anonymous (unnamed) procedure with DO. This requires no privileges:

=> DO $$
BEGIN
    RAISE NOTICE '% ran an anonymous procedure', current_user();
END;
$$;

NOTICE 2005:  Bob ran an anonymous procedure

Transaction semantics

A procedure takes on one of two types depending on how it's called:

  • Nested: A procedure that is called in the body of some other procedure.
  • Top-level: A non-nested stored procedure. A procedure called directly by a user (with either CALL or DO) or trigger is top-level.

For example, if A() calls B(), then A() would be a top-level procedure and B() would be a nested procedure in the context of A()'s execution. If B() is called directly by the user or a trigger, it would be a top-level procedure.

Similarly, if some procedure C() calls A(), then C() would be the top-level procedure and A() and B() would both be nested procedures in the context of C()'s execution.

Automatic commits

Executing a top-level procedure opens a new transaction, which is committed if its execution (including those of any nested procedures) succeeds and rolled back otherwise. If an ongoing transaction exists in the current session before execution, Vertica commits that transaction before calling the procedure. Nested procedures do not start their own transactions.

For example, when A() is called, the following occurs:

  1. Before executing A(), commit any ongoing transaction.
  2. Start a new transaction for the execution of A().
  3. Execute A().
  4. A() calls B().
  5. If A() and B() both successfully execute without errors, commit the transaction. Otherwise, any changes performed by A() and B() are rolled back.

Manual commits

You can also manually commit changes in the middle of a stored procedure. If execution fails after the commit, the committed changes persist even after the automatic rollback.

In this example, manual_commit() inserts two values into a table and then commits. The third insert attempts to insert a CHAR into an INT column, which causes the stored procedure to fail and triggers an automatic rollback. This rollback does not affect the first two inserts because they were manually committed:

=> CREATE TABLE numbers (n INT);

=> CREATE PROCEDURE manualcommit() AS
$$
BEGIN
    PERFORM INSERT INTO numbers VALUES(1);
    PERFORM INSERT INTO numbers VALUES(2);
    PERFORM COMMIT;
    PERFORM INSERT INTO numbers VALUES('a');
END;
$$;

=> CALL manualcommit();

ERROR 3681:  Invalid input syntax for integer: "a"
CONTEXT:  PL/vSQL procedure manualcommit line 6 at static SQL

=> SELECT * FROM numbers;
 n
---
 1
 2
(2 rows)

Session semantics

Operations that modify the session persist after execution of the stored procedure, including ALTER SESSION and SET statements.

Triggers execute in their own, isolated session.

Limiting runtime

You can set the maximum runtime of a procedure with session parameter RUNTIMECAP.

This example sets the runtime of all stored procedures to one second for duration of session and runs an anonymous procedure with an infinite loop. Vertica terminates the procedure after it runs for more than one second:

=> SET SESSION RUNTIMECAP '1 SECOND';

=> DO $$
BEGIN
    LOOP
    END LOOP;
END;
$$;

ERROR 0:  Query exceeded maximum runtime
HINT:  Change the maximum runtime using SET SESSION RUNTIMECAP

Execution security and privileges

By default, stored procedures execute with the privileges of the caller (invoker), so callers must have the necessary privileges on the catalog objects accessed by the stored procedure. You can allow callers to execute the procedure with the privileges, default roles, user parameters, and user attributes (RESOURCE_POOL, MEMORY_CAP_KB, TEMP_SPACE_CAP_KB, RUNTIMECAP) of the definer by specifying DEFINER for the SECURITY option.

For example, the following procedure inserts a value into table s1.t1. If the DEFINER has the required privileges (USAGE on the schema and INSERT on table), this requirement is waived for callers.

=> CREATE PROCEDURE insert_into_s1_t1(IN x int, IN y int)
LANGUAGE PLvSQL
SECURITY DEFINER AS $$
BEGIN
    PERFORM INSERT INTO s1.t1 VALUES(x,y);
END;
$$;

A procedure with SECURITY DEFINER effectively executes the procedure as that user, so changes to the database appear to be performed by the procedure's definer rather than its caller.

Execution privileges for nested stored procedures

A stored procedure cannot call stored procedures that require additional privileges. For example, if a stored procedure executes with privileges A, B, and C, it cannot call a stored procedure that requires privileges C, D, and E.

For details on nested stored procedures, see Scope and structure.

Examples

In this example, this table:

records(i INT, updated_date TIMESTAMP DEFAULT sysdate, updated_by VARCHAR(128) DEFAULT current_user())

Contains the following content:

=> SELECT * FROM records;
 i |        updated_date        | updated_by
---+----------------------------+------------
 1 | 2021-08-27 15:54:05.709044 | Bob
 2 | 2021-08-27 15:54:07.051154 | Bob
 3 | 2021-08-27 15:54:08.301704 | Bob
(3 rows)

Bob creates a procedure to update the table and uses the SECURITY DEFINER option and grants EXECUTE on the procedure to Alice. Alice can now use the procedure to update the table without any additional privileges:

=> GRANT EXECUTE ON PROCEDURE update_records(int,int) to Alice;
GRANT PRIVILEGE

=> \c - Alice
You are now connected as user "Alice".

=> CALL update_records(99,1);
 update_records
---------------
             0
(1 row)

Because calls to update_records() effectively run the procedure as Bob, Bob is listed as the updater of the table rather than Alice:

=> SELECT * FROM records;
 i  |        updated_date        | updated_by
----+----------------------------+------------
 99 | 2021-08-27 15:55:42.936404 | Bob
  2 | 2021-08-27 15:54:07.051154 | Bob
  3 | 2021-08-27 15:54:08.301704 | Bob
(3 rows)

1 - Triggers

You can automate the execution of stored procedures with triggers.

You can automate the execution of stored procedures with triggers. A trigger listens to database events and executes its associated stored procedure when the events occur. You can use triggers with CREATE SCHEDULE to implement Scheduled execution.

Individual triggers can be enabled and disabled with ENABLE_TRIGGER, and can be manually executed with EXECUTE_TRIGGER.

1.1 - Scheduled execution

Stored procedures can be scheduled to execute automatically with the privileges of the trigger definer.

Stored procedures can be scheduled to execute automatically with the privileges of the trigger definer. You can use this to automate various tasks, like logging database activity, revoking privileges, creating roles, or loading data.

Enabling and disabling scheduling

Scheduling can be toggled at the database level with the EnableStoredProcedureScheduler configuration parameter:

-- Enable scheduler
=> SELECT SET_CONFIG_PARAMETER('EnableStoredProcedureScheduler', 1);

-- Disable scheduler
=> SELECT SET_CONFIG_PARAMETER('EnableStoredProcedureScheduler', 0);

You can toggle an individual schedule ENABLE_TRIGGER, or disable it by dropping the schedule's associated trigger.

Scheduling a stored procedure

The general workflow for implementing scheduled execution for a single stored procedure is as follows:

  1. Create a stored procedure.

  2. Create a schedule. A schedule can either use a list of timestamps for one-off triggers or a cron expression for recurring events.

  3. Create a trigger, associating it with the stored procedure and trigger.

  4. (Optional) Manually execute the trigger to test it.

One-off triggers

One-off triggers run a finite number of times.

The following example creates a trigger that revokes privileges on the customer_dimension table from the user Bob after 24 hours:

  1. Create a stored procedure to revoke privileges from Bob:

    => CREATE OR REPLACE PROCEDURE revoke_all_on_table(table_name VARCHAR, user_name VARCHAR)
LANGUAGE PLvSQL
AS $$
BEGIN
    EXECUTE 'REVOKE ALL ON ' || QUOTE_IDENT(table_name) || ' FROM ' || QUOTE_IDENT(user_name);
END;
$$;

  2. Create a schedule with a timestamp for 24 hours later:

    => CREATE SCHEDULE 24_hours_later USING DATETIMES('2022-12-16 12:00:00');

  3. Create a trigger with the stored procedure and schedule:

    => CREATE TRIGGER revoke_trigger ON SCHEDULE 24_hours_later EXECUTE PROCEDURE revoke_all_on_table('customer_dimension', 'Bob') AS DEFINER;

Recurring triggers

Recurring triggers run at a recurring date or time specified by a cron expression:

=> CREATE SCHEDULE every_saturday_12am USING cron '0 0 * * 6';
=> CREATE SCHEDULE every_5_minutes USING CRON '*/5 * * * *';
=> CREATE SCHEDULE every_hour_from_12pm_to_6pm USING CRON '0 12-17 * * *';

The following example creates a weekly trigger that logs to the USER_COUNT table the number of users in the database:

=> SELECT * FROM USER_COUNT;
  total  |           timestamp
---------+-------------------------------
     293 | 2022-12-04 00:00:00.346664-00
     302 | 2022-12-11 00:00:00.782242-00
     301 | 2022-12-18 00:00:00.144633-00
     301 | 2022-12-25 00:00:00.548832-00
(4 rows)

  1. Create the table to log the user counts:

    => CREATE TABLE USER_COUNT(total INT, timestamp TIMESTAMPTZ)

  2. Create the stored procedure to log to the table:

    => CREATE OR REPLACE PROCEDURE log_user_count()
LANGUAGE PLvSQL
AS $$
DECLARE
    num_users int := SELECT count (user_id) FROM users;
    timestamp datetime := SELECT NOW();
BEGIN
    PERFORM INSERT INTO USER_COUNT VALUES(num_users, timestamp);
END;
$$;

  3. Create the schedule for 12:00 AM on Sunday:

    => CREATE SCHEDULE weekly_sunday USING CRON '0 0 * * 0';

  4. Create the trigger with the stored procedure and schedule:

    => CREATE TRIGGER user_log_trigger ON SCHEDULE weekly_sunday EXECUTE PROCEDURE log_user_count() AS DEFINER;

Viewing upcoming schedules

Schedules are managed and coordinated by the Active Scheduler Node (ASN). If the ASN goes down, a different node is automatically designated as the new ASN. To view scheduled tasks, query SCHEDULER_TIME_TABLE on the ASN.

  1. Determine the ASN with ACTIVE_SCHEDULER_NODE:

    => SELECT active_scheduler_node();
active_scheduler_node
-----------------------
 initiator
(1 row)

  2. On the ASN, query SCHEDULER_TIME_TABLE:

    => SELECT * FROM scheduler_time_table;

  schedule_name  | attached_trigger | scheduled_execution_time
-----------------+------------------+--------------------------
 daily_1am_gmt   | log_user_actions | 2022-12-15 01:00:00-00
 24_hours_later  | revoke_trigger   | 2022-12-16 12:00:00-00