Statements table functions

[fljdin]

A refactor could be made by using table functions returning statements per relations, in that way, it could be easier to separate looping over staging tables and statement execution like I suggested in #24.

Example with db_indexes_statements table function:

CREATE FUNCTION db_indexes_statements(
  plugin         name,
  pgstage_schema name    DEFAULT NAME 'pgsql_stage'
) RETURNS TABLE (statement text)
  LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SET search_path = pg_catalog AS
$$DECLARE
BEGIN
  /* loop through all index columns */
  FOR <needed variables> IN <index columns query>
  LOOP
    stmt := format('CREATE INDEX') -- put any variables into this dynamic statement
    -- do more logic...

    return next;
  END LOOP;
END; $$;

CREATE FUNCTION db_migrate_indexes(
  plugin         name,
  pgstage_schema name    DEFAULT NAME 'pgsql_stage'
) RETURNS integer
  LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SET search_path = pg_catalog AS
$$DECLARE
  rc integer := 0;
BEGIN
  FOR stmt IN SELECT statement 
                FROM db_indexes_statements(plugin => 'mssql_server', pgstage_schema => 'pgsql_stage') 
  LOOP
    EXECUTE stmt;
  EXCEPTION
    WHEN others THEN
      -- printing exception detail
      -- inserting exception into migrate_log table
  END LOOP;
END; $$;

With this uncorrelated system, it becomes possible to perform migration by external tools without calling neither db_migrate nor any db_migrate_* methods.

External tools in any language may consume db_migrator API (staging tables and statement table-functions as proposed) to execute statements and catch exceptions. From my point of view, it will provide a better way to speed-up migration with multiple processes.

[laurenz]

Yes, that sounds viable.

Perhaps it would be a good idea to provide an additional function that would execute the SQL statement, print the exception detail and log it. That could be handy for people who want to parallelize migrating indexes.

db_migrate_indexes would then become a very simple wrapper.

[fljdin]

I have made a working branch with unit testing

• New execute_statement(text,name,name,text) function, used by db_migrate_* methods
• New construct_indexes_statements(name, name) table-function
• db_migrate_indexes() becomes a wrapper that links theses new functions together

https://github.com/cybertec-postgresql/db_migrator/compare/master..26-statements-functions

[laurenz]

I am a bit confused by the two branches 26-statements-functions and 26-execute_statement. Are these two implementations of the same thing? Which branch should I review?

[fljdin]

Branch 26-execute_statement is a complete implementation for a generic execution method (with errors handling), independent from this current issue (or not?).

The second one was an experimentation, attached to this issue. I just realized that a bit tricky to do that way, please forgive me. I'll clean existing branches and work on an unique one.