Can't change from SERIAL to IDENTITY columns in idempotent migrations

[roji]

Originally raised in #1089, attempted fix in #1092 broke non-idempotent migrations, so we're reverting it in #1156.

Our idempotent scripts run under PL/pgSQL, whereas non-idempotent scripts run in plain SQL. Unfortunately, when executing a function whose return value should be discarded, in PL/pgSQL we need to use PERFORM, whereas regular SQL requires SELECT. At the moment we can't distinguish between idempotent and non-idempotent in the migrations generator (dotnet/efcore#19250).

Another solution to this would be to run all migrations under PL/pgSQL.

[Slaviusz]

Is there any known workaround? Proposed downgrade to 3.0.1 does not work and we're currently blocked moving on from .NetCore 2.2 to 3.1 migration within our project because of migrations failing from SERIAL to IDENTITY with PERFORM.

[roji]

To work around this, edit the generated idempotent migration SQL and change the SELECT to PERFORM. It's a one-time operation that occurs when first changing from SERIAL to IDENTITY, once it's done you shouldn't need to do it again for other migration types.

[jhartmann123]

@roji You mentioned that the issue with PERFORM is reverted in 3.1.1 (#1089 (comment)). However, it's still in there. Looking at the git tree, the revert is only in dev and 3.0.2. Any chance of getting a quick 3.1.2 with the PERFORM issue being reverted?

[roji]

Shoot, sorry about that - I backported to 3.0 but apparently forgot to backport to 3.1...

I am releasing 3.1.1.1 with this fix (we try to more or less maintain version sync with EF Core, but they haven't yet released 3.1.2), this should help you all out.

Note that this kind of bug should no longer occur since we now have real migration tests which actually execute DDL on PostgreSQL. But that's on the 5.0 branch only...

[jhartmann123]

Perfect, everything works fine with 3.1.1.1. Thanks for the quick fix!

[YohDeadfall]

It's still blocked but by dotnet/efcore#14746.