Forum: Managing VoltDB

Post: Drop all stored procedures in one go?

Drop all stored procedures in one go?
cfogelberg
Aug 11, 2016
Is is possible to drop all stored procedures in a single command? E.g.
DROP PROCEDURE *;
or
DROP ALL PROCEDURE;


The motivation for this is that we are making widespread schema changes to our schema and stored procedures. We have to drop the tables to modify them because they are DR'ed and our version of DR does not allow modification of DR'ed tables. In order to drop the tables we have to drop the stored procedures associated with them. Rather than dropping just the necessary stored procedures we have decided to drop them all because we would need to drop dozens and there is too much chance one will be missed, thus causing problems with a migration being executed by others (not the developers).
cfogelberg
Aug 11, 2016
// Edit for clarity

We've since discovered a better way of doing the table modifications: disabling and then re-enabling the DR after table modification. That removes the importance behind this question, it's now an academic one :)
bballard
Aug 11, 2016
Hi, as you mentioned, VoltDB v6.3+ supports online changes to DR tables, but if you are using an older version there a few ways you can do it which all involve at least a partial disruption.

1. If you are using v6.3 or later, follow the steps in Using VoltDB section for Passive DR (https://docs.voltdb.com/UsingVoltDB/DbRepHowToPassive.php#DbRepPassiveUpdateSchema) or XDCR (https://docs.voltdb.com/UsingVoltDB/DbRepHowToActive.php#DbRepActiveUpdateSchema).

2. You can always make any changes to any tables using a maintenance window process. This involves temporarily stopping the master and replica clusters.

3. If you are using version 6.2 or earlier, the following is a partial workaround:
If tables are empty: Drop the table from both schemas and re-create it (with additional columns) and make it a DR table.

If tables are not empty, you can use CSV files to copy the data:

On the master cluster:
- Take a single-table snapshot in CSV format e.g. "voltadmin save --format=csv --tables=<tablename> <folder> <prefix>"
- DROP TABLE <tablename>;
- CREATE TABLE <tablename> ( ... with changes);
- DR TABLE <tablename>;

On the replica cluster:
- DROP TABLE <tablename>;
- CREATE TABLE <tablename> ( ... with changes);
- DR TABLE <tablename>; (replication will resume for this table at this point)

On the master cluster:
- Create a procedure that accepts the original columns of the table, and inserts into the modified table. For example:
create procedure insert_vote partition on table votes column phone_number as insert into votes (phone_number, state, contestant_number) values (?,?,?);
- Use csvloader to reload the data, invoking the new stored procedure which can accept the CSV file format and insert into the modified table. For example:
csvloader -f <prefix>-VOTES-host_0.csv -p insert_vote;
- Drop the procedure if no longer needed.