Move to IDENTITY column for ordering?

[CumpsD]

ordering BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

Seems to be here:

• https://github.com/AkkaNetContrib/Akka.Persistence.PostgreSql/blob/195b93873dcf7d6587fb3eecb2d4ca32f2942b5c/src/Akka.Persistence.PostgreSql/Journal/PostgreSqlQueryExecutor.cs#L38
• README install script & migration script

As to the why:

• https://stackoverflow.com/questions/55300370/postgresql-serial-vs-identity
• https://www.depesz.com/2017/04/10/waiting-for-postgresql-10-identity-columns/

If you want I can make a PR

Downside: PostgreSQL 10 and up only

[Aaronontheweb]

@akkadotnet/core is there a way we can add this retroactively? Always want to avoid breaking changes if possible.

[to11mtm]

@akkadotnet/core is there a way we can add this retroactively? Always want to avoid breaking changes if possible.

I think this is a little tricky since we would want to have this be 'opt-in' based on the version of Postgres. It could probably be handled via a (new) configuration option as far as changing upthe create table statement.

I'm not sure what the script we would need to migrate would look like (Postgres is not something I've really worked with in detail outside of linq2db usage, so I don't know how the type conversions work out...) But that might be more of a 'nice to have' than necessity, unless changing the type causes issues with the Other SQL used.

I did go ahead and create a related issue in Akka.Persistence.Linq2db since we will probably want to do the same there.

[IgorFedchenko]

Does this change apply to only database table creation script? In this case, other queries will keep working without any changes, right? If so - yeah, we can give an option to use BIGINT IDENTITY when database is first time created, and that's it. Probably we could even not provide any migration script - Persistence queries will work with either column types equally. Or I am wrong and we use BIGSERIAL type somewhere else explicitly?

[Arkatufus]

I agree with @IgorFedchenko, if we do make this an opt-in feature and since it only affects table creation, we don't need to provide any migration tools.