Marten attempts to recreate database on startup when DatabaseSchemaName is set to position

[DawidHoppeVolue]

I've noticed a problem when a schema is named position (potentially other keywords might suffer from similar issue).

I created a small repro project - there's a dockerfile with db setup and a sample Marten app. When you run for the first time everything is fine - db gets created, async daemon runs. On second and every subsequent run, when async daemons attempts to run (it takes a while, I don't know why) it fails, because apparently it is trying to recreate existing database:

 Exception data:
          Severity: ERROR
          SqlState: 42P07
          MessageText: relation "pk_mt_events_stream_and_version" already exists
          File: index.c
          Line: 897
          Routine: index_create
         --- End of inner exception stack trace ---
         at Marten.StoreOptions.Weasel.Core.Migrations.IMigrationLogger.OnFailure(DbCommand command, Exception ex) in /_/src/Marten/StoreOptions.cs:line 306
         at Weasel.Postgresql.PostgresqlMigrator.executeDelta(SchemaMigration migration, DbConnection conn, AutoCreate autoCreate, IMigrationLogger logger, CancellationToken ct) in /_/src/Weasel.Postgresql/PostgresqlMigrator.cs:line 131
         at Weasel.Core.Migrations.DatabaseBase`1.executeMigration(ISchemaObject[] schemaObjects, CancellationToken ct) in /_/src/Weasel.Core/Migrations/DatabaseBase.cs:line 506
         at Weasel.Core.Migrations.DatabaseBase`1.executeMigration(ISchemaObject[] schemaObjects, CancellationToken ct) in /_/src/Weasel.Core/Migrations/DatabaseBase.cs:line 506
         at Weasel.Core.Migrations.DatabaseBase`1.generateOrUpdateFeature(Type featureType, IFeatureSchema feature, CancellationToken token, Boolean skipPostProcessing) in /_/src/Weasel.Core/Migrations/DatabaseBase.cs:line 485
         at Weasel.Core.Migrations.DatabaseBase`1.ensureStorageExistsAsync(IList`1 types, Type featureType, CancellationToken token) in /_/src/Weasel.Core/Migrations/DatabaseBase.cs:line 449
         at JasperFx.Events.Daemon.JasperFxAsyncDaemon`3.StartHighWaterDetectionAsync() in /_/src/JasperFx.Events/Daemon/JasperFxAsyncDaemon.cs:line 361
         at JasperFx.Events.Daemon.JasperFxAsyncDaemon`3.StartAgentAsync(String shardName, CancellationToken token) in /_/src/JasperFx.Events/Daemon/JasperFxAsyncDaemon.cs:line 165
         at Polly.ResiliencePipeline.<>c__2`1.<<ExecuteAsync>b__2_0>d.MoveNext()
      --- End of stack trace from previous location ---
         at Polly.Outcome`1.GetResultOrRethrow()
         at Polly.ResiliencePipeline.ExecuteAsync[TState](Func`3 callback, TState state, CancellationToken cancellationToken)
         at Marten.Events.Daemon.Coordination.ProjectionCoordinator.tryStartAgent(CancellationToken stoppingToken, IProjectionDaemon daemon, ShardName name, IProjectionSet set) in /_/src/Marten/Events/Daemon/Coordination/ProjectionCoordinator.cs:line 318

When you change DatabaseSchemaName in Program.cs:71 to some other value, like custom it works as expected. Postgres allow such schema name, so Marten should as well.

MartenMinimalRepro.zip

[mysticmind]

@DawidHoppeVolue position is a unreserved keyword in postgres so it is not a good option to pick as a schema name. You can't dafely use it since it can trip function names. The reason Marten schema diff trips because, the schema info fetched will position under double quotes so this is identified as a difference in schema so it is trying to apply again.

We do maintain a list of reserved keywords as manually keyed in list in Weasel, will add position to the list. so that it is processed properly. But in the interim, please use a suffix or prefix or something so the schema name is not specifically position.

[mysticmind]

As an additional note, SELECT word, catcode, catdesc FROM pg_get_keywords() ORDER BY word; provides the list of all keywords, will look at leveraging this to see how best to handle reserved/unreserved keywords better.

[DawidHoppeVolue]

Hi @mysticmind, thank you for your response. I think handling of such cases should be improved - from my perspective if Postgres allows it, then a package built on top of it should also do so.

The reason Marten schema diff trips because, the schema info fetched will position under double quotes so this is identified as a difference in schema so it is trying to apply again.

For me sounds like a candidate for improvement - shouldn't it always compare "escaped" names to avoid such issues?

As an additional suggestion, I think it should be clarified in this article what is the current limitation for names.