insert or update on table "state" violates foreign key constraint "state_jobid_fkey"

[aamir0]

Using Hangfire version 1.9.5, found an issue when trying to Enqueue Hangfire jobs.

The jobs are being enqueued within a TransactionScope that I manage. I originally got the following error:

System.ArgumentException: The transaction specified for TransactionScope has a different IsolationLevel than the value requested for the scope. (Parameter 'transactionOptions')
   at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption, TransactionOptions transactionOptions, TransactionScopeAsyncFlowOption asyncFlowOption)
   at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption, TransactionOptions transactionOptions)
   at Hangfire.PostgreSql.PostgreSqlStorage.CreateTransaction(Nullable`1 isolationLevel)
   at Hangfire.PostgreSql.PostgreSqlStorage.UseTransaction[T](DbConnection dedicatedConnection, Func`3 func, Nullable`1 isolationLevel)
   at Hangfire.PostgreSql.PostgreSqlConnection.CreateExpiredJob(Job job, IDictionary`2 parameters, DateTime createdAt, TimeSpan expireIn)
   at Hangfire.Client.CoreBackgroundJobFactory.<>c__DisplayClass14_0.<Create>b__2(Int32 _)
   at Hangfire.Client.CoreBackgroundJobFactory.RetryOnException[T](Int32& attemptsLeft, Func`2 action)
--- End of stack trace from previous location ---
   at Hangfire.Client.CoreBackgroundJobFactory.RetryOnException[T](Int32& attemptsLeft, Func`2 action)
   at Hangfire.Client.CoreBackgroundJobFactory.Create(CreateContext context)
   at Hangfire.Client.BackgroundJobFactory.<>c__DisplayClass12_0.<CreateWithFilters>b__0()
   at Hangfire.Client.BackgroundJobFactory.InvokeClientFilter(IClientFilter filter, CreatingContext preContext, Func`1 continuation)
   at Hangfire.Client.BackgroundJobFactory.<>c__DisplayClass12_1.<CreateWithFilters>b__2()
   at Hangfire.Client.BackgroundJobFactory.CreateWithFilters(CreateContext context, IEnumerable`1 filters)
   at Hangfire.Client.BackgroundJobFactory.Create(CreateContext context)
   at Hangfire.BackgroundJobClient.Create(Job job, IState state)
   --- End of inner exception stack trace ---
   at Hangfire.BackgroundJobClient.Create(Job job, IState state)
   at Hangfire.BackgroundJobClientExtensions.Create[T](IBackgroundJobClient client, Expression`1 methodCall, IState state)
   at Hangfire.BackgroundJobClientExtensions.Enqueue[T](IBackgroundJobClient client, Expression`1 methodCall)

After finding that jobs are created using IsolationLevel = ReadCommitted, I updated my transactions to do the same. I now get the following error whenever trying to enqueue jobs:

Npgsql.PostgresException (0x80004005): 23503: insert or update on table "state" violates foreign key constraint "state_jobid_fkey"

DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) in /_/Dapper/SqlMapper.cs:line 2858
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 581
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 452
   at Hangfire.PostgreSql.PostgreSqlWriteOnlyTransaction.<>c__DisplayClass8_0.<SetJobState>b__0(IDbConnection con)
   at Hangfire.PostgreSql.PostgreSqlWriteOnlyTransaction.<Commit>b__4_0(DbConnection connection, DbTransaction transaction)
   at Hangfire.PostgreSql.PostgreSqlStorage.<>c__DisplayClass23_0.<UseTransaction>b__0(DbConnection connection, DbTransaction transaction)
   at Hangfire.PostgreSql.PostgreSqlStorage.<>c__DisplayClass24_0`1.<UseTransaction>b__0(DbConnection connection)
   at Hangfire.PostgreSql.PostgreSqlStorage.UseConnection[T](DbConnection dedicatedConnection, Func`2 func)
   at Hangfire.PostgreSql.PostgreSqlStorage.UseTransaction[T](DbConnection dedicatedConnection, Func`3 func, Func`1 transactionScopeFactory)
   at Hangfire.PostgreSql.PostgreSqlStorage.UseTransaction(DbConnection dedicatedConnection, Action`2 action, Func`1 transactionScopeFactory)
   at Hangfire.PostgreSql.PostgreSqlWriteOnlyTransaction.Commit()
   at Hangfire.Client.CoreBackgroundJobFactory.<>c__DisplayClass14_0.<Create>b__3(Int32 attempt)
   at Hangfire.Client.CoreBackgroundJobFactory.<>c__DisplayClass15_0.<RetryOnException>b__0(Int32 attempt)
   at Hangfire.Client.CoreBackgroundJobFactory.RetryOnException[T](Int32& attemptsLeft, Func`2 action)
--- End of stack trace from previous location ---
   at Hangfire.Client.CoreBackgroundJobFactory.RetryOnException[T](Int32& attemptsLeft, Func`2 action)
   at Hangfire.Client.CoreBackgroundJobFactory.RetryOnException(Int32& attemptsLeft, Action`1 action)
   at Hangfire.Client.CoreBackgroundJobFactory.Create(CreateContext context)
   at Hangfire.Client.BackgroundJobFactory.<>c__DisplayClass12_0.<CreateWithFilters>b__0()
   at Hangfire.Client.BackgroundJobFactory.InvokeClientFilter(IClientFilter filter, CreatingContext preContext, Func`1 continuation)
   at Hangfire.Client.BackgroundJobFactory.<>c__DisplayClass12_1.<CreateWithFilters>b__2()
   at Hangfire.Client.BackgroundJobFactory.CreateWithFilters(CreateContext context, IEnumerable`1 filters)
   at Hangfire.Client.BackgroundJobFactory.Create(CreateContext context)
   at Hangfire.BackgroundJobClient.Create(Job job, IState state)
  Exception data:
    Severity: ERROR
    SqlState: 23503
    MessageText: insert or update on table "state" violates foreign key constraint "state_jobid_fkey"
    Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
    SchemaName: hangfire
    TableName: state
    ConstraintName: state_jobid_fkey
    File: d:\pginstaller_12.auto\postgres.windows-x64\src\backend\utils\adt\ri_triggers.c
    Line: 2474
    Routine: ri_ReportViolation

I get the foreign key violation even when starting with a fresh database. This appears to be a new issue, as I'd previously had success with enqueuing jobs, even while using the default IsolationLevel.

.NET 6 Visual Studio 2022 (17.0.5) Hangfire 1.7.28 Hangfire.AspNetCore 1.7.28 Hangfire.PostgreSql 1.9.5 Microsoft.EntityFrameworkCore 6.0.1 Npgsql.EntityFrameworkCore.PostgreSQL 6.0.2

[azygis]

Could you please provide a small project to reproduce the issue?

[aamir0]

I'll see if I can create a small reproducible example.

In the meantime, it appears to only be an issue when running locally in VS (and have seen it on others' machines as well), but we don't see this issue when the application is running in Docker/k8s on our servers.

[aamir0]

The issue appears after a Hangfire upgrade that I'd done recently:

Hangfire: 1.7.19 -> 1.7.28 Hangfire.AspNetCore: 1.7.19 -> 1.7.28 Hangfire.PostgreSql: 1.8.1 -> 1.9.4

The issue is still present after upgrading Hangfire.PostgreSql again from 1.9.4 -> 1.9.5

[azygis]

Did you have any chance to make a repro project? I'm running the application in VS almost every full working day and I have never experienced the issue (although still on Npgsql5). Though the postgres instance is running in docker container, not windows itself.

[aamir0]

Haven't yet created a repro project, but found that I don't see the issue when I don't try to Enqueue within another TransactionScope.

Additionally, when I add Include Error Detail=true to my connection string, I get the following error message from trying to Enqueue.

Npgsql.PostgresException (0x80004005): 23503: insert or update on table "state" violates foreign key constraint "state_jobid_fkey"

DETAIL: Key (jobid)=(2) is not present in table "job".
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) in /_/Dapper/SqlMapper.cs:line 2858
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 581
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 452
   at Hangfire.PostgreSql.PostgreSqlWriteOnlyTransaction.<>c__DisplayClass8_0.<SetJobState>b__0(IDbConnection con)
   at Hangfire.PostgreSql.PostgreSqlWriteOnlyTransaction.<Commit>b__4_0(DbConnection connection, DbTransaction transaction)
   at Hangfire.PostgreSql.PostgreSqlStorage.<>c__DisplayClass23_0.<UseTransaction>b__0(DbConnection connection, DbTransaction transaction)
   at Hangfire.PostgreSql.PostgreSqlStorage.<>c__DisplayClass24_0`1.<UseTransaction>b__0(DbConnection connection)
   at Hangfire.PostgreSql.PostgreSqlStorage.UseConnection[T](DbConnection dedicatedConnection, Func`2 func)
   at Hangfire.PostgreSql.PostgreSqlStorage.UseTransaction[T](DbConnection dedicatedConnection, Func`3 func, Func`1 transactionScopeFactory)
   at Hangfire.PostgreSql.PostgreSqlStorage.UseTransaction(DbConnection dedicatedConnection, Action`2 action, Func`1 transactionScopeFactory)
   at Hangfire.PostgreSql.PostgreSqlWriteOnlyTransaction.Commit()
   at Hangfire.Client.CoreBackgroundJobFactory.<>c__DisplayClass14_0.<Create>b__3(Int32 attempt)
   at Hangfire.Client.CoreBackgroundJobFactory.<>c__DisplayClass15_0.<RetryOnException>b__0(Int32 attempt)
   at Hangfire.Client.CoreBackgroundJobFactory.RetryOnException[T](Int32& attemptsLeft, Func`2 action)
--- End of stack trace from previous location ---
   at Hangfire.Client.CoreBackgroundJobFactory.RetryOnException[T](Int32& attemptsLeft, Func`2 action)
   at Hangfire.Client.CoreBackgroundJobFactory.RetryOnException(Int32& attemptsLeft, Action`1 action)
   at Hangfire.Client.CoreBackgroundJobFactory.Create(CreateContext context)
   at Hangfire.Client.BackgroundJobFactory.<>c__DisplayClass12_0.<CreateWithFilters>b__0()
   at Hangfire.Client.BackgroundJobFactory.InvokeClientFilter(IClientFilter filter, CreatingContext preContext, Func`1 continuation)
   at Hangfire.Client.BackgroundJobFactory.<>c__DisplayClass12_1.<CreateWithFilters>b__2()
   at Hangfire.Client.BackgroundJobFactory.CreateWithFilters(CreateContext context, IEnumerable`1 filters)
   at Hangfire.Client.BackgroundJobFactory.Create(CreateContext context)
   at Hangfire.BackgroundJobClient.Create(Job job, IState state)
  Exception data:
    Severity: ERROR
    SqlState: 23503
    MessageText: insert or update on table "state" violates foreign key constraint "state_jobid_fkey"
    Detail: Key (jobid)=(2) is not present in table "job".
    SchemaName: hangfire
    TableName: state
    ConstraintName: state_jobid_fkey
    File: d:\pginstaller_12.auto\postgres.windows-x64\src\backend\utils\adt\ri_triggers.c
    Line: 2474
    Routine: ri_ReportViolation

I'm running PostgreSQL on Windows directly when debugging locally in VS.

[azygis]

That's weird. The exception data after the stack trace emits job id 2, while the detail at the beginning of exception complains about id 3.

[aamir0]

Apologies, that's from an error I made while copying and printing out the error. The original error message had the jobid consistent in both places, and I updated my post to fix that error

[azygis]

Aha, suspected as much.. Well, really hope to get a repro at some point so I can investigate it further. I'm really unsure how exactly you manage the transactions.

[VisualBean]

I am experiencing the same, after trying to upgrade the nuget package.

Essentially I am doing the following SomeClass.cs

public async Task DoThingAsync()
{
  using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
  {
      //Do thing in database
     await specificJobQueuer.EnqueueAsync();
  }
}

SpecificJobQueuer.cs

public async Task EnqueueAsync()
{
  jobClient.Enqueue<SomeJob>(job => job.ExecuteAsync("hello", CancellationToken.None);
}

Its the jobClient.Enqueue that fails with The transaction specified for TransactionScope has a different IsolationLevel than the value requested for the scope. (Parameter 'transactionOptions')

Ive tried different isolation levels but it hasnt worked yet. I had to go back down to 1.8.6.

[TheAngryByrd]

👋 Hey, we're experience similar behavior.

I was able to create a repro here.

You should be able to comment in and out the lines to show the specific errors. I've left comments in there as to which error should produce which.

Additionally you can change the package version to an older version to see the behavior isn't the same. The earlier these issues show up is 1.9.2.

[domagojmedo]

Yea, I have the same issue with transactions, any info on a workaround or fix?

[frabe1579]

Ouch! Same problem here!

Hangfire.AspNetCore Version 1.7.32 Hangfire.Console Version 1.4.2 Hangfire.PostgreSql Version 1.9.9

[ZlobnyiSerg]

I can confirm that downgrading from Hangfire.PostgreSql Version 1.9.9 to 1.8.6 helps. Still waiting for fix (

[frankhommers]

@azygis or @vytautask , do you have time to dive into this issue? I am really occupied currently.

[azygis]

Sorry, not in the near future. 😑

[ryan-mccartney-cs]

Been banging my head against this issue all day, same state as AngryBryd mentioned above. Tried reverting the version of Hangfire.PostgreSql back to 1.8.6 as other commenters have suggested and it works. Hangfire would not successfully resolve an EnqueuedJob of any kind on any version later than 1.9.9.

[pprometey]

I have the same problem. Previously IsolationLevel was ReadUncommited. Then I made Serializable, after that this error appeared. Now it does not matter what IsolationLevel I would not set, this error appears. Rollback to version 1.8.6 solves this problem.

[azygis]

Thanks, @TheAngryByrd, your repro helped narrow it down. Apparently we had some mismatch across a few places where ambient transaction is used. After #275 is merged and published, library should respect the isolation level from outside hangfire.