Sequence migrations fails after updating from 2.1.0 to 2.1.2

[eduardocampano]

After upgrading from 2.1.0 to 2.1.2 migrations started failing when deployed to servers using 9.x.

Looking at the logs of migrations I see

CREATE SEQUENCE "AspNetUserClaims_Id_seq" AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE;

Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "AS"

Looks like the code checking version considers null as a valid "at least" version always and if not set using the new SetPostgresVersion setting it fails with 9.x.

bool VersionAtLeast(int major, int minor)
 => _postgresVersion is null || new Version(major, minor) <= _postgresVersion;

I was able to fix the issue but wanted to report the bug as it breaks existing code.

[austindrenski]

The design of the compatibility checks is intended to assume the most recent version of PostgreSQL unless otherwise specified, which is why null always passes.

It looks like the docs have a note about supporting the new IDENTITY columns, but not warning about needing to specify an explicit version to opt out of those changes.

I can update the docs to include a breaking change warning for 2.1 and 2.2.

Related

#607
#611
#612
#639

[eduardocampano]

Thanks @austindrenski, looks good, I guess we will need to be extra careful when updating.

Just out of curiosity, is it not possible to retrieve the server version and set it by default with SetPostgresVersion? This would avoid the need of having to pass in the version number from CI/CD.

[austindrenski]

Just out of curiosity, is it not possible to retrieve the server version and set it by default with SetPostgresVersion?

There was some discussion about that initially, but for a couple of reasons, the EF Core team encourages explicit user opt-in/opt-out in situations like this.

[roji]

Closing as the provider is behaving as expected.

[diegomgarcia]

Hi guys I've upgraded and this stills doesn't work fully as expected, I'm using different contexts with different schemas, só as I've noted when generating on a different schema it doesn't put the schema on the OWNED BY part of the SQL giving the follow error when trying to update:

Failed executing DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE model.services ALTER COLUMN number SET DEFAULT (nextval('services_number_seq'));
ALTER SEQUENCE services_number_seq OWNED BY services.number;

Npgsql.PostgresException (0x80004005): 42P01: relation "services" does not exist