New feature request - swap_tables

Hi,
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: https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/RenamingTables.htm

    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');
    COMMIT;
    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)
    

Answers

Leave a Comment

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