CODEWITHSUNDEEP
c#postgresqlevent-sourcingmarten
Zoltan Hernyak
Zoltan Hernyak

Reputation: 1239

Marten - using sequences - add new sequence fails on 2nd run

I tried to add a new "custom" sequence to my Marten DB to get a user ID for new users (during registration).

builder.Services.AddMarten(o =>
{
    o.Connection(builder.Configuration.GetConnectionString("MartenPg"));
    if (builder.Environment.IsDevelopment())
        o.AutoCreateSchemaObjects = AutoCreate.All;

    o.Storage.ExtendedSchemaObjects.Add(new Sequence("userId_seq"));
}).ApplyAllDatabaseChangesOnStartup().UseLightweightSessions();

Later I was able to access the next sequence value as:

var userId = session.Query<Int64>("select nextval('userId_seq')").First();

The problem is in the upper code: for the 1st run it works: adds the userId_seq to the database. But on the 2nd start it tries to add again - but the sequence already exists so it fails. Tried to find a way to determine if the sequence is already added or not - but cannot find any - the ExtendedSchemaObjects is always empty. Otherwise - I think it is not my fault as the method name ApplyAllDatabaseChangesOnStartup suggests me it should be determined by the the built-in migration tool.

Any suggestion how to solve this problem?

Thanks!

Upvotes: 0

Views: 196

Answers (1)

JGH
JGH

Reputation: 17846

Use a lower-case sequence name (and make your life easy by always only using lower-case names in PG)

A bit of background: if you don't double quote an identifier in PG, it is automatically converted to a lowercase. Similarly, when querying the sequence (or any other object), it is converted to its lowercase form unless it is double quoted.

CREATE SEQUENCE TEST;
SELECT NEXTVAL('test'); --OK
SELECT NEXTVAL('teST'); --OK
SELECT NEXTVAL('"test"'); --OK, lower-case name is found
SELECT NEXTVAL('"TEST"'); --ERROR, nothing found.

CREATE SEQUENCE "TEST2";
SELECT NEXTVAL('test2');  --ERROR, nothing found.
SELECT NEXTVAL('"TEST2"'); --OK, double quoted name with upper case is found

Back to marten/weasel:

Code to create the sequence does not add double quotes, so your identifier is converted to lower case.
When you query the nextval, the identifier is also not double-quoted so its lower case version is used and the sequence returns the desired value.

Next time you run your app, the code that looks if the sequence exists or not uses the identifier as-is, so containing an upper-case. There is no sequence under that name so the sequence is created again, using again the lower-cased name, which already exists.

Upvotes: 2

Related Questions