Marten: AssertDatabaseMatchesConfiguration fails due to Hilo not behaving when int used as Document Id

[tonykaralis]

Problem

1. Create and configure document store where at least one document is using an int as its Id property
2. Export sql DDL script and run in Database
3. Call AssertDatabaseMatchesConfiguration (this throws an exception even though no changes were made to schema)

Environment

• Asp.Net Core 2.2
• PostgreSql v11

Details

Exception thrown:

Message:   Expected: null
  But was:  <Marten.Schema.SchemaValidationException: Configuration to Schema Validation Failed! These changes detected:

-- data.mt_get_next_hi Diff
DROP FUNCTION data.mt_get_next_hi(entity character varying) cascade;

-- data.mt_get_next_hi Diff
CREATE OR REPLACE FUNCTION data.mt_get_next_hi(entity varchar) RETURNS int AS $$
DECLARE
    current_value bigint;
    next_value bigint;
BEGIN
    select hi_value into current_value from data.mt_hilo where entity_name = entity;
    IF current_value is null THEN
        insert into data.mt_hilo (entity_name, hi_value) values (entity, 0);
        next_value := 0;
    ELSE
        next_value := current_value + 1;
        update data.mt_hilo set hi_value = next_value where entity_name = entity;
    END IF;

    return next_value;
END
$$ LANGUAGE plpgsql;

   at Marten.Storage.TenantSchema.AssertDatabaseMatchesConfiguration()
   at MartenSchemaOps.SchemaValidator.AssertSchemaMatchesConfiguration()
   at DataSchema.Tests.SchemaValidation.AlignmentTests.Schema_Is_Valid() in

Function that Marten created in the DB

-- FUNCTION: data.mt_get_next_hi(character varying)

-- DROP FUNCTION data.mt_get_next_hi(character varying);

CREATE OR REPLACE FUNCTION data.mt_get_next_hi(
    entity character varying)
    RETURNS integer
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
    current_value bigint;
    next_value bigint;
BEGIN
    select hi_value into current_value from data.mt_hilo where entity_name = entity;
    IF current_value is null THEN
        insert into data.mt_hilo (entity_name, hi_value) values (entity, 0);
        next_value := 0;
    ELSE
        next_value := current_value + 1;
        update data.mt_hilo set hi_value = next_value where entity_name = entity;
    END IF;

    return next_value;
END
$BODY$;

ALTER FUNCTION data.mt_get_next_hi(character varying)
    OWNER TO dbadmin;

StoreOptions configuration

 var options = new StoreOptions
            {                    
                AutoCreateSchemaObjects = AutoCreate.None,
                DatabaseSchemaName = schema,
                //plv8 used for the patching api, only works if plv8 installed.
                PLV8Enabled = false
            };

            //configure the ddl script generated
            options.DdlRules.TableCreation = CreationStyle.CreateIfNotExists;

            //critical to deserializing interfaces etc
            options.Serializer(SetupCustomSerializer(customBinder));

            options.Schema.For<BoostOption>();
            options.Schema.For<Country>();
            options.Schema.For<Tool>();
            options.Schema.For<BenchMark>().ForeignKey<Project>(x => x.ProjectId);

            options.Schema.For<BullseyeTerm>();
            options.Schema.For<PccCompetitor>().ForeignKey<BrandCategory>(x => x.BrandCategoryId)
                                        .ForeignKey<Country>(x => x.CountryId);
            options.Schema.For<CategoryTerm>().ForeignKey<BrandCategory>(x => x.BrandCategoryId);
            options.Schema.For<DistractorImage>().ForeignKey<BrandCategory>(x => x.BrandCategoryId)
                                        .ForeignKey<Country>(x => x.CountryId);

            options.Schema.For<BrandCategory>();
            options.Schema.For<IBrand>().AddSubClassHierarchy(typeof(Brand))
                                  .ForeignKey<BrandCategory>(x => x.BrandCategoryId)
                                  .ForeignKey<Country>(x => x.CountryId);

            options.Schema.For<ItemCategory>();
            options.Schema.For<IItem>().AddSubClassHierarchy(typeof(Item))
                               .ForeignKey<Project>(x => x.ProjectId)
                               .ForeignKey<Country>(x => x.CountryId)
                               .UseOptimisticConcurrency(true);

            options.Schema.For<ItemTagCategory>();
            options.Schema.For<ItemTag>().ForeignKey<ItemTagCategory>(x => x.ItemTagCategoryId);

            options.Schema.For<Quote>();
            options.Schema.For<ProjectStatus>();
            options.Schema.For<ProjectBrand>();
            options.Schema.For<Project>().ForeignKey<ProjectStatus>(x => x.ProjectStatusId)
                                           .ForeignKey<Quote>(x => x.QuoteId)
                                           .UseOptimisticConcurrency(true)
                                           .FullTextIndex();
            options.Schema.For<ProjectTaskManager>().ForeignKey<Project>(x => x.ProjectId);

            options.Schema.For<SurveyStatus>();
            options.Schema.For<Survey>().ForeignKey<SurveyStatus>(x => x.SurveyStatusId)
                                  .ForeignKey<Project>(x => x.ProjectId)
                                  .Duplicate(x => x.ProjectNumber);

            options.Schema.For<RespondentIdMapping>().ForeignKey<Survey>(x => x.SurveyId)
                                               .ForeignKey<Project>(x => x.ProjectId)
                                               .ForeignKey<Country>(x => x.MarketId);

            options.Schema.For<IQuestionData>().AddSubClassHierarchy(DocumentStoreConfigExtensions.GetQuestionDataTypes())
                                                                .ForeignKey<Survey>(x => x.SurveyId)
                                                                .ForeignKey<Country>(x => x.CountryId)
                                                                .ForeignKey<RespondentIdMapping>(x => x.RespMapId)
                                                                .UseOptimisticConcurrency(true);

Thanks to @barryhagan for pointing out the following:

I believe the problem is the use of HiLo. The sql for that function uses RETURN int which becomes RETURN integer on the server and there is no handling of this in CanonicizeSql(). Submit an issue, there is an easy fix. Workaround is to not use int Ids.

As promised, @oskardudycz .

[oskardudycz]

@tonykaralis @barryhagan should be fixed with https://github.com/JasperFx/marten/pull/1348

[tonykaralis]

Thanks for sorting this so fast @oskardudycz. Any idea when 3.8 will be rolled out?

[oskardudycz]

I think that I'll change this to 3.7.1 then we could release asap I got approval from one of other maitainers to not block your reaching production 😄

[tonykaralis]

I'd buy you a beer if I could mate. You are a legend.

[oskardudycz]

I hope that that we'll have the chance someday 😄 👍

[tonykaralis]

My partner is Polish, and I have not visited yet. I am planning some time next year, it would be great to grab one then if we can make it happen.

[oskardudycz]

Sure, feel free to reach me 👍