Skip to main content
Question

New feature request - swap_tables

  • December 21, 2022
  • 2 replies
  • 9 views
Sergey_Cherepan_1
Forum|alt.badge.img+2

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!

    2 replies

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • December 21, 2022

    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)
    Sergey_Cherepan_1
    Forum|alt.badge.img+2
    • Sergey_Cherepan_1
    • Author
    • Participating Frequently
    • December 21, 2022

    Thanks! I did not know this functionality exist.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement