PostgreSql - outboxrecord table not being created

[KenBerg75]

Symptoms

using the following:

NServicebus.NHibernate 6.0.1 NServicebus 5.1.2 Npgsql 2.2.3 The Outbox sample project located Here

When using PostgreSql 9.4 and enabling Outbox, the outboxrecord table fails to get created.

This issue manifests itself when the OutboxCleaner executes, or the OutboxPersister does a TryGet - you will see the following error:

could not execute query
[ SELECT this_.Id as Id1_0_, this_.MessageId as MessageId1_0_, this_.Dispatched as Dispatched1_0_, this_.DispatchedAt as Dispatch4_1_0_, this_.TransportOperations as Transpor5_1_0_ FROM outboxrecord this_ WHERE this_.MessageId = :p0 ]
  Name:cp0 - Value:4a92a6df-ec62-44c5-8792-612454448e54
[SQL: SELECT this_.Id as Id1_0_, this_.MessageId as MessageId1_0_, this_.Dispatched as Dispatched1_0_, this_.DispatchedAt as Dispatch4_1_0_, this_.TransportOperations as Transpor5_1_0_ FROM outboxrecord this_ WHERE this_.MessageId = :p0]

With the inner exception:

ERROR: 42P01: relation "outboxrecord" does not exist

Digging deeper into the NHibernate code, we can find the following exception being burried in the NHibernate.Tool.hbm2ddl.SchemaUpdate class [Execute method] when it is called via the Installer.cs to create/update the schema:

ERROR: 42804: column "dispatched" is of type boolean but default expression is of type integer

This being the root cause of why the outboxrecord table is not created.

Then looking at the OutboxEntityMap we can see the issue:

Property(p => p.Dispatched, pm =>
            {
                pm.Column(c =>
                {
                    c.Default(0);
                    c.NotNullable(true);
                });
                pm.Index("OutboxRecord_Dispatched_Index");
            });

This can be easily reproduced by taking the NServiceBus sample above and pointing it at a PostgreSql database instead of SqlServer.

The solution which is tested working would be to update the mapping above to:

Property(p => p.Dispatched, pm =>
            {
                pm.Column(c =>
                {
                    c.Default(true);
                    c.NotNullable(true);
                });
                pm.Index("OutboxRecord_Dispatched_Index");
            });

Who's affected

All users using Outbox and PostgreSql

[johnsimons]

@KenBerg75 yes please submit a PR

[johnsimons]

Fixed in https://github.com/Particular/NServiceBus.NHibernate/commit/d7c16a20a3bf6796dd7b9105c9d2996c605b0da5