The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

New feature request - swap_tables

I found today very interesting users activity.
Users are creating tables with partition on INT column, and populated partitioning column with single value - always with 1.
On question why they are doing it, explanation is - they prepare data in separate table with identical structure, and then swap all data between tables using swap_partitions.

In theory, one can just rename tables and be good.

It appears, Vertica DDL is autocommiting. It is not possible to do two table renames in single transaction. As result, there is a small time between two table renames when table does not exist.
Swap partitions between table is a cleaner solution to a problem, does not have any time when table does not exist.
I kinda agree this is a good solution to a problem, though a very creative one.

Question - why we cannot make this a legit activity. Please file new feature request - swap_tables.
It should work with or without partitioning, and require identical table structure similar to swap_partitions.

Result of swap_tables(A,B) is that content of A and B are swapped in single transaction.

Let me know what you think.
Thanks for reading!

Best Answer

  • moshegmosheg Vertica Employee Administrator
    Answer ✓

    Thank you for your question.
    It is possible to use one statement like ALTER TABLE...RENAME TO to swap tables within the same schema, without actually moving data as mentioned here:

    Please let me know if this is helpful.

    -- The following example swaps the data in tables T1 and T2 through intermediary table temp:
    -- t1 to temp
    -- t2 to t1
    -- temp to t2
    DROP TABLE IF EXISTS temp, t1, t2;
    CREATE TABLE t1 (original_name varchar(24));
    CREATE TABLE t2 (original_name varchar(24));
    INSERT INTO t1 VALUES ('original name t1');
    INSERT INTO t2 VALUES ('original name t2');
    ALTER TABLE t1, t2, temp RENAME TO temp, t1, t2;
    SELECT * FROM t1, t2;
    --   original_name   |  original_name
    -- ------------------+------------------
    --  original name t2 | original name t1
    -- (1 row)


Leave a Comment

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