Vertica: move_partitions_to_table()

If you have a load in multiple steps (say data is first loaded in a staging schema, then moved to another one), using MOVE_PARTITIONS_TO_TABLE() is the best option. This is very fast, as you do not need to actually issue INSERT or DELETE statements or deal with the delete vectors. Vertica just moves in one go a block of data, and you are done.

When you issue this function, Vertica will first issue a moveout to empty the WOS before moving data. This does make sense, as if data is loaded in memory, you still want to make sure it is moved with the rest of the of the partition already on disk.

Since at least Vertica 7.0.1-1, using this function might give you the error message:

select move_partitions_to_table ('stg.A', 0, 24, 'vault.A')
ERROR: A Moveout operation is already in progress on projection vault.B_node0002

As you can see, a moveout on table A fails because of a moveout on table B, which does not seem to make sense.

It has actually been confirmed that it does not make sense, and it actually is a bug in Vertica 7.0.1-1, which should be fixed in theory in the next service pack.

In the mean time, you can work around the bug by issuing INSERTs/DELETEs, or have your code checks if a moveout is already running before issuing the move_partitions_to_table() statement.

EDIT: The hotfix 7.1.1-1 (11/2014) fixes the bug.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s