Foreign table removal management

[fljdin]

Hi,

Currently, foreign table removal is performed at the same time as table creation and data loading. The materialize_foreign_table method consists of the following steps:

• Foreign table renaming (ALTER FOREIGN TABLE %I.%I RENAME TO %I)
• Creation of identical table, with partitioning defined or not (CREATE TABLE %I.%I (LIKE %I.%I))
• Creation of table partitions if required (CREATE TABLE %1$I.%3$I PARTITION OF %1$I.%2$I %4$s)
• Load data or not (INSERT INTO %I.%I SELECT * FROM %I.%I)
• Delete foreign table (DROP FOREIGN TABLE %I.%I)

If only one of these steps is unsuccessful, the foreign table remains unchanged.

However, with the db_migrate_tables method, the foreign table is removed if any error occurs.
In the event that you wish to repeat the migration of an erroneous table, you need to rebuild the foreign table, which can be tedious, although it is now possible with the low-level function construct_foreign_tables_statements.

How about postponing the removal of foreign tables until the end of the migration, with a call to the db_migrate_finish method?

[laurenz]

I agree that db_migrate_tables had better not drop the foreign table if the migration fails.

I am not sure what we should do with any foreign tables left behind after the migration.
One way would be to just leave them, the other way would be to drop all tables in pgstage_schema.tables.

Both would be acceptable to me, although the second option is probably nicer.