I tried to add a new "custom" sequence to my Marten DB to get a user ID for new users (during registration).
我尝试添加一个新的“自定义”序列到我的Marten DB中,以获取新用户的用户ID(在注册过程中)。
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.
问题出在上面的代码中:在第一次运行时:将userid_seq添加到数据库。但在第二次开始时,它试图再次添加-但序列已经存在,因此失败。试图找到一种方法来确定序列是否已添加--但找不到任何--ExtendedSchemaObject始终为空。否则-我认为这不是我的错,因为方法名称ApplyAllDatabaseChangesOnStartup建议我应该由内置的迁移工具来确定。
Any suggestion how to solve this problem?
有什么建议可以解决这个问题吗?
Thanks!
谢谢!
更多回答
优秀答案推荐
Use a lower-case sequence name (and make your life easy by always only using lower-case names in PG)
使用小写的序列名称(并且始终只使用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.
有一点背景:如果在pg中不用双引号将标识符括起来,它会自动转换为小写。类似地,当查询序列(或任何其他对象)时,它被转换为其小写形式,除非它被双引起来。
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:
回到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.
创建序列的代码不会添加双引号,因此您的标识符会转换为小写。当您查询nextval时,标识符也不会用双引号引起来,因此将使用其小写版本,并且序列将返回所需的值。
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.
下次运行应用程序时,查看序列是否存在的代码将按原样使用标识符,因此包含大写字母。该名称下没有序列,因此将再次使用已存在的小写名称创建该序列。
更多回答
Ahh I see. As I used other SQL servers - it is not obvious for me that PG likes (only) lower-cased names... but your advice works! Thanks!
啊,我明白了。就像我使用其他SQL服务器一样-对我来说,PG(只)喜欢小写的名称并不明显……但你的建议奏效了!谢谢!
I wonder as the Sequence
class is part of the Marten lib - is there any chance to gives us a warning, or do this trick behind the scene?
我想知道,因为Sequence类是Marten库的一部分--有没有机会给我们一个警告,或者在幕后做这个把戏?
@ZoltanHernyak see how the reported issue gets resolved
@ZoltanHernyak查看报告的问题是如何解决的
我是一名优秀的程序员,十分优秀!