FR: migrate existing table with fresh table

[daadu]

With current implementation (create_hypertable with migrate_data => true), if table is large could be locked for long time and could also cause "deadlock" for certain condition, as the below quote from doc suggest:

WARNING:The use of the migrate_data argument to convert a non-empty table can lock the table for a significant amount of time, depending on how much data is in the table. It can also run into deadlock if foreign key constraints exist to other tables.

If you would like finer control over index formation and other aspects of your hypertable, follow these migration instructions instead.

When converting a normal SQL table to a hypertable, pay attention to how you handle constraints. A hypertable can contain foreign keys to normal SQL table columns, but the reverse is not allowed. UNIQUE and PRIMARY constraints must include the partitioning key.

The deadlock is likely to happen when concurrent transactions simultaneously try to insert data into tables that are referenced in the foreign key constraints and into the converting table itself. The deadlock can be prevented by manually obtaining SHARE ROW EXCLUSIVE lock on the referenced tables before calling create_hypertable in the same transaction, see PostgreSQL documentation for the syntax.

[daadu]

Related docs - https://docs.timescale.com/latest/getting-started/migrating-data#same-db