Consider exposing migration idempotency to MigrationsSqlGenerator

[roji]

Currently, MigrationSqlGenerator is unaware of whether it's generating SQL for idempotent scripts or others. Idempotency is only known to Migrator, which uses it to generate and IF statement around the migration. However, in some cases the migration SQL itself must vary based on whether the script is idempotent or not. Here's the problematic scenario.

In PostgreSQL, one normally can't do conditions or control flow in bare SQL (like in SQL Server). However, one can switch to PL/pgSQL, which is the standard PostgreSQL procedural language for writing stored functions/procedures, so our idempotent scripts look like this:

DO $$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "migration_id" = '20191209121340_identity') THEN
    -- Migration code...
    END IF;
END $$;

The fact that normal migrations run in bare SQL but idempotent migrations run in PL/pgSQL has some consequences. For example, when executing a function whose return value should be discarded, in PL/pgSQL we need to do:

PERFORM setval('blogs_id_seq', nextval('blogs_id_old_seq'), false);

Whereas in normal SQL we have to do:

SELECT setval('blogs_id_seq', nextval('blogs_id_old_seq'), false);

Another approach to this would be to run all migrations within PL/pgSQL, but I'm not sure what the consequences of that are, and would like to avoid it if possible (but we should discuss).

Original issue: npgsql/efcore.pg#1089

[roji]

Small note: at least for the moment there's a pretty reasonable workaround on my side (edit the idempotent script and change PERFORM to SELECT), and the migration where this is necessary is typically a one-time transition thing.

So this is not urgent.

[bricelam]

Duplicate of #14746