Error when swapping partition between tables.

HilarioNeto

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.

Vertica_Curtis

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.

HilarioNeto

I will try it.
Thank you very much.

HilarioNeto

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!

alalikin

Hi!
I have almost the same issue:
SELECT
SWAP_PARTITIONS_BETWEEN_TABLES(BDE_team.aa_tst_orders_stg_stg, Date('2021-10-10'), Date('2021-10-13'), BDE_team.aa_tst_orders_stg)
Vertica:
SQL Error [4566] [42V01]: [Vertica]VJDBC ERROR: Relation "BDE_team" does not exist

Both table exist and I can select smth from them.

could you please help me with that ?

Jim_Knicely

Put the table names in single quotes...

Example:

dbadmin=> CREATE TABLE t1 (c INT NOT NULL) PARTITION BY (c);
CREATE TABLE

dbadmin=> CREATE TABLE t2 (c INT NOT NULL) PARTITION BY (c);
CREATE TABLE

dbadmin=> INSERT INTO t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;
 OUTPUT
--------
      3
(1 row)

dbadmin=> INSERT INTO t2 SELECT 4;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT swap_partitions_between_tables('t1', 1, 2, 't2');
                                 swap_partitions_between_tables
-------------------------------------------------------------------------------------------------
 2 partition values from table t1 and 0 partition values from table t2 are swapped at epoch 20.

(1 row)

dbadmin=> SELECT * FROM t1;
 c
---
 3
(1 row)

dbadmin=> SELECT * FROM t2;
 c
---
 4
 2
 1
(3 rows)

alalikin

@Jim_Knicely said:
Put the table names in single quotes...

Example:

dbadmin=> CREATE TABLE t1 (c INT NOT NULL) PARTITION BY (c);
CREATE TABLE

dbadmin=> CREATE TABLE t2 (c INT NOT NULL) PARTITION BY (c);
CREATE TABLE

dbadmin=> INSERT INTO t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;
 OUTPUT
--------
      3
(1 row)

dbadmin=> INSERT INTO t2 SELECT 4;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT swap_partitions_between_tables('t1', 1, 2, 't2');
                                 swap_partitions_between_tables
-------------------------------------------------------------------------------------------------
 2 partition values from table t1 and 0 partition values from table t2 are swapped at epoch 20.

(1 row)

dbadmin=> SELECT * FROM t1;
 c
---
 3
(1 row)

dbadmin=> SELECT * FROM t2;
 c
---
 4
 2
 1
(3 rows)

Thanks! it worked