The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Error when swapping partition between tables.

Hi. I have a problem concerning table partition swap.

I'm using Vertica v9.2.0-0 and PDI 8.2 as ETL tool.
The last step of the etl job in question is trowing an error as it tries to swap partitions between the stage and dw schemas of the last table of the job. It works flawlessly for every other table.

  • Instruction:
    SELECT SWAP_PARTITIONS_BETWEEN_TABLES('dw.TABLE_A', 000000, 305000, 'stg.TABLE_A', true)

  • Error:
    [Vertica] [ VJDBC ] ( 7702 ) ERROR: Could not successfully moveout the table stg.TABLE_A. Operation cannot be completed with data in WOS

If I execute the instruction in the database manager just after the job fails, it works straight away.
Any ideas of what could be associated to this error?

Thank you very much.

Answers

  • Can you try forcing a MOVEOUT (SELECT DO_TM_TASK('moveout', 'table');) on the table? If that doesn't work, there could be something corrupt in the WOS that's unable to be released to disk. There are some edge-case reasons why this could be happening, and you'll probably need to work through support in order to resolve it. It's a bit overly complicated to work through here.

  • I will try it.
    Thank you very much.

  • I've tried but in the end I've realized the data was not corrupted. I've checked wos_row_count with

    select wos_row_count, wos_used_bytes from system;

    and noticed it couldn't empty the WOS before the swap_partition_between_tables instruction, possibly because the data comes from a very wide table.
    Then I've added a few seconds wait step before the swap_partition_between_tables command in the end of the job so it could give enough time to empty the WOS, and that solved the issue.

    Thank you very much!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.