hangfire-postgres / hangfire.postgresql Goto Github PK

https://github.com/SEEK-Jobs/Hangfire.PostgreSql/commit/25c11b7fab847108abe3304c690b90405739d435

Npgsql > 3.x removed support for writing a parameter with an IEnumerable value, use .ToList()/.ToArray() instead

Hi,

There was schema update in 1.5.4 version
ALTER TABLE [HangFire].[Server] ALTER COLUMN [Id] NVARCHAR (100) NOT NULL;
commit (HangfireIO/Hangfire@d903a29)

The server id is now autogenerated and can be more then 50 character length.

Proof: https://discuss.hangfire.io/t/server-bootstrapper-error-hangfire-server-serverbootstrapper/1232

Because of time difference with remote database server some tests (such as Hangfire.PostgreSql.Tests.PostgreSqlWriteOnlyTransactionFacts.ExpireJob_SetsJobExpirationData) are failing. It can also happen inconsistently due to some network lag.

Hangfire db initialization fails with the following error with Npgsql 3.1.6.0. This is because Hangfire uses NpgsqlException.BaseMessage property which is no longer available.

System.MissingMethodException: Method 'NpgsqlException.get_BaseMessage' not found.
at Hangfire.PostgreSql.PostgreSqlStorage..ctor (System.String nameOrConnectionString, Hangfire.PostgreSql.PostgreSqlStorageOptions options) <0xb0b038d8 + 0x00157> in :0
at Hangfire.PostgreSql.PostgreSqlStorage..ctor (System.String nameOrConnectionString) <0xb0b02ff0 + 0x00043> in :0
at MTI.Startup.Configure (IApplicationBuilder app) <0xb123b308 + 0x00187> in :0
at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&)
at System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) <0xb53e7270 + 0x0009e> in :0

MessageText property should be used instead

Issue details

Hello,
I using Hangfire and try migrate to new PostgreSQL (Using Skytools or Londiste)
get this error:

su postgres -c "londiste3 /etc/skytools/londiste-master.ini add-table --all"
2017-10-26 15:34:07,996 4722 INFO Table added: hangfire.counter
2017-10-26 15:34:08,015 4722 INFO Table added: hangfire.hash
2017-10-26 15:34:08,030 4722 INFO Table added: hangfire.job
2017-10-26 15:34:08,039 4722 INFO Table added: hangfire.jobparameter
2017-10-26 15:34:08,048 4722 INFO Table added: hangfire.jobqueue
2017-10-26 15:34:08,056 4722 INFO Table added: hangfire.list
2017-10-26 15:34:08,062 4722 ERROR Primary key missing on table: hangfire.lock
Can i added Primary key to table: hangfire.lock?

Hangfire.PostgreSql 1.4.8.1

Started seeing a lot of these errors popping up.

Could not place a lock on the resource 'HangFire:locks:schedulepoller' and 'HangFire:recurring-jobs:lock': Lock timeout
Can anyone offer suggestions on how to resolve this?

What is going on? Need some suggestions.

Error occurred during execution of 'DelayedJobScheduler' process. Execution will be retried (attempt 15 of 2147483647) in 00:03:41 seconds. Hangfire.PostgreSql.PostgreSqlDistributedLockException: Could not place a lock on the resource 'HangFire:locks:schedulepoller': Lock timeout. at Hangfire.PostgreSql.PostgreSqlDistributedLock.PostgreSqlDistributedLock_Init_Transaction(String resource, TimeSpan timeout, IDbConnection connection, PostgreSqlStorageOptions options) at Hangfire.PostgreSql.PostgreSqlConnection.AcquireDistributedLock(String resource, TimeSpan timeout) at Hangfire.Server.DelayedJobScheduler.EnqueueNextScheduledJob(BackgroundProcessContext context) at Hangfire.Server.DelayedJobScheduler.Execute(BackgroundProcessContext context) at Hangfire.Server.AutomaticRetryProcess.Execute(BackgroundProcessContext context)

Error occurred during execution of 'RecurringJobScheduler' process. Execution will be retried (attempt 13 of 2147483647) in 00:02:44 seconds. Hangfire.PostgreSql.PostgreSqlDistributedLockException: Could not place a lock on the resource 'HangFire:recurring-jobs:lock': Lock timeout. at Hangfire.PostgreSql.PostgreSqlDistributedLock.PostgreSqlDistributedLock_Init_Transaction(String resource, TimeSpan timeout, IDbConnection connection, PostgreSqlStorageOptions options) at Hangfire.PostgreSql.PostgreSqlDistributedLock..ctor(String resource, TimeSpan timeout, IDbConnection connection, PostgreSqlStorageOptions options) at Hangfire.PostgreSql.PostgreSqlConnection.AcquireDistributedLock(String resource, TimeSpan timeout) at Hangfire.Server.RecurringJobScheduler.Execute(BackgroundProcessContext context) at Hangfire.Server.AutomaticRetryProcess.Execute(BackgroundProcessContext context)

Are there any plans to support Hangfire 1.4.0?

@frankhommers, I'm looking that you are fighting with MSBuild-based build scripts taken from Hangfire. They were pretty awful, and I've changed them recently with psake-based tools (results described in this PR).

As a result, I've built the Hangfire.Build project to simplify the build automation and you can see the new and beautiful build scripts:

• https://github.com/HangfireIO/Hangfire.Dashboard.Authorization/blob/master/psake-project.ps1
• https://github.com/HangfireIO/Hangfire/blob/master/psake-project.ps1

Hope it will help you.

Queue name length is now hardcoded at 20 characters, which is pretty short. Hangfire.SqlServer fixed this back in 2015 by bumping it up to 100. I see no problem doing the same thing here.

Relevant Hangfire issue: HangfireIO/Hangfire/issues/369

Hi,

I've been trying to implement Hangfire.PostgreSql, with hangfire 1.6.14 and .net core. This process have been most troublesome, and I'm now wondering if anyone else have managed to get this to work.

Thanks,

Server Error in '/' Application.

Npgsql > 3.x removed support for writing a parameter with an IEnumerable value, use .ToList()/.ToArray() instead

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NotSupportedException: Npgsql > 3.x removed support for writing a parameter with an IEnumerable value, use .ToList()/.ToArray() instead

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

[NotSupportedException: Npgsql > 3.x removed support for writing a parameter with an IEnumerable value, use .ToList()/.ToArray() instead]
   Npgsql.TypeHandlerRegistry.get_Item(Type type) +1000
   Npgsql.TypeHandlerRegistry.get_Item(Object value) +260
   Npgsql.NpgsqlParameter.ResolveHandler(TypeHandlerRegistry registry) +170
   Npgsql.NpgsqlParameter.Bind(TypeHandlerRegistry registry) +12
   Npgsql.NpgsqlCommand.ValidateAndCreateMessages(CommandBehavior behavior) +236
   Npgsql.NpgsqlCommand.ExecuteDbDataReaderInternal(CommandBehavior behavior) +202
   Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +4
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +12
   Dapper.<QueryImpl>d__11`1.MoveNext() +370
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +387
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) +379
   Hangfire.PostgreSql.PostgreSqlMonitoringApi.GetTimelineStats(NpgsqlConnection connection, Dictionary`2 keyMaps) +169
   Hangfire.PostgreSql.PostgreSqlMonitoringApi.GetHourlyTimelineStats(NpgsqlConnection connection, String type) +253
   Hangfire.PostgreSql.PostgreSqlMonitoringApi.<HourlySucceededJobs>b__3c(NpgsqlConnection connection) +11
   Hangfire.PostgreSql.PostgreSqlMonitoringApi.UseConnection(Func`2 action) +75
   Hangfire.PostgreSql.PostgreSqlMonitoringApi.HourlySucceededJobs() +59
   Hangfire.Dashboard.Pages.HomePage.Execute() +253
   Hangfire.Dashboard.RazorPage.TransformText(String body) +26
   Hangfire.Dashboard.RazorPage.ToString() +7
   Hangfire.Dashboard.RazorPageDispatcher.Dispatch(RequestDispatcherContext context) +103
   Hangfire.Dashboard.<>c__DisplayClass8.<UseHangfireDashboard>b__4(IDictionary`2 env) +349
   Microsoft.Owin.Mapping.<Invoke>d__0.MoveNext() +273
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.<RunApp>d__5.MoveNext() +187
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Mapping.<Invoke>d__0.MoveNext() +566
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.<RunApp>d__5.MoveNext() +187
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Security.Infrastructure.<Invoke>d__0.MoveNext() +561
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.<RunApp>d__5.MoveNext() +187
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +58
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.<DoFinalWork>d__2.MoveNext() +185
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.StageAsyncResult.End(IAsyncResult ar) +69
   Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.IntegratedPipelineContext.EndFinalWork(IAsyncResult ar) +64
   System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +380
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155```

If used with Hangfire.Console, an exception is thrown when scheduling jobs.
Disabling Console, no error is thrown. Console seems to work correctly with other storages (or maybe a similar problem with Mongo).

Exception:

Hangfire.BackgroundJobClientException: Background job creation failed. See inner exception for details. ---> System.InvalidCastException: Unable to cast object of type 'Hangfire.PostgreSql.PostgreSqlWriteOnlyTransaction' to type 'Hangfire.Storage.JobStorageTransaction'.
at Hangfire.Console.States.ConsoleApplyStateFilter.OnStateApplied(ApplyStateContext context, IWriteOnlyTransaction transaction)
at Hangfire.States.StateMachine.ApplyState(ApplyStateContext initialContext)
at Hangfire.Client.CoreBackgroundJobFactory.Create(CreateContext context)
at Hangfire.Client.BackgroundJobFactory.<>c__DisplayClass7_0.b__0()
at Hangfire.Client.BackgroundJobFactory.InvokeClientFilter(IClientFilter filter, CreatingContext preContext, Func`1 continuation)
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 ... (hidden)"

Packages:
Hangfire 1.6.9
Hangfire.Core 1.6.9
Hangfire.PostgreSql 1.4.6
Hangfire.Console 1.3.0

Hello, Frank,

I've just released Hangfire 1.3.0 and it contains some breaking changes (:frowning:) for storage providers. Here are the required changes:

• Remove Common.Logging dependency from NuSpec file.
• Update Hangfire.Core reference to 1.3.0 version.
• Replace Common.Logging namespaces with Hangfire.Logging.
• Replace LogManager.* methods with LogProvider.* methods.

I've tried to make a pull request, but Hangfire.PostgreSql.csproj file does not want to open because:

The imported project "C:\Users\odinserj\Documents\GitHub\Hangfire.PostgreSql\src\Common\HangFire.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. C:\Users\odinserj\Documents\GitHub\Hangfire.PostgreSql\tests\Hangfire.PostgreSql.Tests\Hangfire.PostgreSql.Tests.csproj

Hi,

I have a long running job, which timeouts after 30minutes with an exception. Basically I cannot run longer jobs. Maybe some setting is missing? Thats the only information I can see in Hangfire dashboard:

Hangfire.PostgreSql.PostgreSqlDistributedLockException
Could not release a lock on the resource 'HangFire:IDataStreamService.RebuildElasticSearchIndexes'. Lock does not exists.
Hangfire.PostgreSql.PostgreSqlDistributedLockException: Could not release a lock on the resource 'HangFire:IPortfolioService.RebuildPortfolioSearchIndexes'. Lock does not exists.
at Hangfire.PostgreSql.PostgreSqlDistributedLock.Dispose()
at Hangfire.Server.BackgroundJobPerformer.InvokePerformFilter(IServerFilter filter, PerformingContext preContext, Func`1 continuation)

I noticed there is a separate package for this for .NET Core support.

Why is this? Do you guys need help with your project.json for supporting .NET 4.5.2 and .NET Core 1.1?

Hi,

I'm new to Hangfire, so probably I'm messing up somewhere. I have the Hangfire configured like in: https://github.com/HangfireIO/Hangfire#installation

but instead of:

config.UseSqlServerStorage("<connection string or its name>");

I have:

config.UsePostgreSqlStorage("Server=127.0.0.1;Port=5432;User Id=postgres;Password=pwd;Database=Hangfire");

So I created an Hangfire Database in my DB.

And then, I'm building and running my project. It is ok. Creating all tables in Hangfire DB at my postgres. It is working great.

But then, when I'm trying:

  BackgroundJob.Enqueue(() => HubService.SendPushNotificationToUsers(threadParticipants,  messageApi.SenderId, messageApi.SenderName, messageApi.ThreadId, messageApi.Content));

I'm receiving an exception with the InnerMessage:

 "Timeout while getting a connection from pool." postgres

Am I missing something?

Thanks in regards,

Is the license for this really GPL 3? So if I use this then I have to open source my entire cloud app?

Hangfire itself is just LGPL which means I have to keep my modifications open (totally fine with) but I can use in my app that is closed.

Having a slight issue getting started.
Dashboard loads proper on initial run but errors out on page refresh or trying to navigate the top bar options. I've enabled owin error page. The error is

Exception: Timeout while getting a connection from pool.
Npgsql.NpgsqlConnectorPool.RequestConnector(NpgsqlConnection Connection)

I've tried my local connection string as well as our staging one.
The hangfire schema and tables get created. Not sure why the pool request timeout on page refresh etc. Hoping for some suggestions.

Happens sometimes when using with Hangfire.Console:

Npgsql.PostgresException: 40001: could not serialize access due to read/write dependencies among transactions

   at Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
   at Npgsql.NpgsqlDataReader.<NextResult>d__31.MoveNext()
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<Execute>d__70.MoveNext()
   at Npgsql.NpgsqlCommand.<ExecuteNonQuery>d__83.MoveNext()
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader)
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command)
   at Hangfire.PostgreSql.PostgreSqlConnection.SetRangeInHash(String key, IEnumerable`1 keyValuePairs)
   at Hangfire.Console.Server.ConsoleServerFilter.OnPerforming(PerformingContext filterContext)
   at Hangfire.Server.BackgroundJobPerformer.InvokePerformFilter(IServerFilter filter, PerformingContext preContext, Func<PerformedContext> continuation)

One would expect the latest items to be succeeded and processed at the top. Out of the box the storage provider shows the order in ascending order, instead of descending order

Hi, i have an error with this provider - i see a blank white screen instead of the Hangfire's dashboard and i don't have any error messages.

Connection string:
<add name="hangfire_db" connectionString="Host=localhost;Port=5432;Username=postgres;Password=123456;Database=hangfire_dev;Pooling=false"/>

Configuration code:

app.UseErrorPage();

var storage = new PostgreSqlStorage(ConfigurationManager.ConnectionStrings["hangfire_db"].ConnectionString);
GlobalConfiguration.Configuration.UseStorage(storage);

app.UseHangfireDashboard("/hangfire", options);

Database schema was created successfully. When i navigate to /hangfire i see nothing.

Any plans to support CoreCLR because the main Hangfire project does? Thanks 😄

Hi,

I am honestly not well-acquainted with PostgreSQL but have developed transaction-safe queues for distributed (multi-consumer) access on SQL Server. It seems your fetching statement fetchJobSqlTemplate in PostgreSqlJobQueue.cs fails under concurrent load leading to jobs dequeued multiple times. You may use optimistic locking or a SELECT FOR UPDATE statement to let a farm of applications run safely.

More info: http://www.postgresql.org/docs/9.1/static/explicit-locking.html

Firstly, thanks for the extension to Hangfire.

This hasn't had any updates in almost a year, is it still maintained?

The Counter table tends to grow very large for some reason. Can older items be purged or maybe we can use an ENUM value for the key field to condense the storage somewhat? Thoughts?

Idea:

• Upper box -> Convert to separate field
• Lower box -> Convert to enum

Hi there. Any plans to use C# 6.0? Is there any technical impediments?

Install-Package : Could not install package 'Hangfire.PostgreSql 1.4.3'. You are trying to install this package into a project that targets '.NETFramework,Version=v4.5', but the package does not contain any
assembly references or content files that are compatible with that framework. For more information, contact the package author.
At line:1 char:1

• Install-Package Hangfire.PostgreSql -Version 1.4.3

• - CategoryInfo          : NotSpecified: (:) [Install-Package], InvalidOperationException
  - FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PowerShell.Commands.InstallPackageCommand
  

During app startup I am configuring recurring jobs. I started to get these errors (see stacktrace bellow).
Not sure if its related, but I also have disabled Pooling in connection string.
Using version: 1.4.7.1
.NET Framework 4.6.1
Any ideas?

   Npgsql.<DoReadMessage>d__147.MoveNext() +250
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13891908
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult() +54
   Npgsql.<ReadMessage>d__146.MoveNext() +752
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13891908
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult() +54
   Npgsql.<NextResult>d__32.MoveNext() +4423
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13891908
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   Npgsql.NpgsqlDataReader.NextResult() +211
   Npgsql.<Execute>d__71.MoveNext() +1477
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13891908
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult() +54
   Npgsql.<ExecuteNonQuery>d__84.MoveNext() +417
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +13891908
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   Npgsql.NpgsqlCommand.ExecuteNonQuery() +246
   Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) +132
   Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) +1023
   Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) +123
   Hangfire.PostgreSql.<>c__DisplayClass22_1.<SetRangeInHash>b__0(NpgsqlConnection con, NpgsqlTransaction trx) +172
   Hangfire.PostgreSql.PostgreSqlWriteOnlyTransaction.Commit() +287
   Hangfire.RecurringJobManager.AddOrUpdate(String recurringJobId, Job job, String cronExpression, RecurringJobOptions options) +697
   Hangfire.RecurringJobManagerExtensions.AddOrUpdate(IRecurringJobManager manager, String recurringJobId, Job job, String cronExpression, TimeZoneInfo timeZone) +37```

I dont know why but when i try to run my web application using Hangfire.PostgreSql i get the same error:
"Unable to connect to PostgreSQL server: FATAL: connection limit exceeded for non-superusers". I have read the postgresql.conf i and have the default vale : max 100 connections so i dont think that this is the problemm. Here is my initialization code in startup.cs:

app.UseHangfireServer(new BackgroundJobServerOptions(), new Hangfire.PostgreSql.PostgreSqlStorage("DefaultConnection"));

I have found the error. It was because when i installed your library using nuget package, it updates by itself my old version of npgsql 3.2.1 and it breaks my code because i was using 3.0.1. Now, i am thinking to update all my old code into the newest version before traying to use your library again.

Thank you very much

Pretty please 😄

Thanks in advance :)

@frankhommers, I've noticed the following items in the recent release:

1. Breaking - Use 'Hangfire.Core' 1.3.4.
2. Breaking - Use 'Npgsql' 2.2.4.1.

Why have you updated to Hangfire 1.3.4? Was there any breaking change in Hangfire.Core itself? Btw, if you internalize the Npgsql dependency, there will be no breaking changes on updates.

This is related to #31. I have version 1.4.3 of Hangfire.PostgreSql.NetCore with 1.6.4 of Hangfire. I noticed that I keep getting this error and scheduled jobs are not executed until I cleanup lock table. This happens when application crashes while scheduled job being executed.

Now, I took a look at PostgreSqlDistributedLock and in different from SqlServerDistributedLock (which uses sp_getapplock) it use custom implementation of distributed lock, which on acquire lock writes in transaction new row into lock table and when lock released it deletes the row. Obviously, if application would crash when the row exists in DB, no one will release lock/delete row.

My question is, is it intentional not to maintain lock timeout (which may prevent those situation with application crash)?

Hi,
I'm trying to keep up-to-date with NpgSql as there's great reasons to use some of its new features in the v3 branch (core support if nothing else), but Hangfire.PostgreSql is incompatible with it.

Any chance you could upgrade your packages and fix the probably relatively minor issues blocking this?

Cheers.

When upgrading to version 4 of the database schema, Install.v4.sql relies on checking if the schema table contains '4'. This doesn't seem to happen.

In testing the query in PostgreSqlObjectsInstaller:

var bumpVersionSql = string.Format(
   "INSERT INTO \"{0}\".\"schema\"(\"version\") " +
   "SELECT @version \"version\" WHERE NOT EXISTS (SELECT @previousVersion FROM \"{0}\".\"schema\")", schemaName);

I discovered that this only inserts a schema number if there is no existing number already.

The result seems to be that script v4 runs every time and fails after the first time.

after the first time a cron task is run I see this in the dashboard and the task will never run again.

Open to discussion before anyone switches it :)

For some reason I can't get the active servers to change from 0. When I switch my connection to a sqlite connection, the active server's changes to 1 using the same exact code. Here's my code:

using System;
using System.Configuration;
using Hangfire;
using Hangfire.PostgreSql;
using Hangfire.SQLite;
using Microsoft.Owin;
using Npgsql;
using Owin;

[assembly: OwinStartupAttribute(typeof(HangfirePOC.Startup))]
namespace HangfirePOC
{
    public partial class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            ConfigureAuth(app);
            SetupPostgreSqlConnection();
            //SetupSqliteConnection();
            app.UseHangfireDashboard();
            app.UseHangfireServer(new BackgroundJobServerOptions
            {
                WorkerCount = Environment.ProcessorCount - 1,
            });
        }

        private static void SetupSqliteConnection()
        {
            GlobalConfiguration.Configuration.UseSQLiteStorage("Data Source=c:\\users\\me\\documents\\hangfire.sqlite;Pooling=True;Version=3");
        }

        private static void SetupPostgreSqlConnection()
        {
            var connection =
                new NpgsqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["hangfiredb"].ConnectionString)
                {
                    Pooling = false
                };
            var storage = new PostgreSqlStorage(connection.ConnectionString);
            GlobalConfiguration.Configuration.UseStorage(storage);
        }
    }
}

In order to support scenarios where multiple systems may share Databases (but operate in separate schemas), or even for more control, it would be nice to be able to configure the schema where the Hangfire tables are installed, and maybe to prefix them so they don't collide with other tables if inserted into an existing schema.

Hi,

Was curious as to why Npgsql Enlist is not supported. As stated in PostgreSqlStorage but latest Npgsql says that it supports TransactionScope.

I've just updated to the latest version and still see some common errors occurring. Are we to expect to see these happen often?

2017-05-10 12:01:00.568 [199] INFO Hangfire.PostgreSql.PostgreSqlStorage - Start installing Hangfire SQL objects...
2017-05-10 12:01:00.599 [199] ERROR Hangfire.PostgreSql.PostgreSqlStorage - Error while executing install/upgrade

Npgsql.PostgresException (0x80004005): 42701: column "updatecount" of relation "counter" already exists

Npgsql.PostgresException (0x80004005): 42701: column "acquired" of relation "lock" already exists

also I'm hoping I start to see less of the following occur going forward. I mentioned these in an issue #31 before and it was closed off so I was hoping these wouldn't be popping up anymore.

I hope these don't have anything to do with retrying failed jobs. We have been seeing that retries weren't working. If a job failed, it got added to the list of retires but retry attempt count never passed 1.

Seems like retry never happen if failed to place a lock on specified resource

Retry attempt 1 of 3: Failure sending mail.

Error occurred during execution of 'RecurringJobScheduler' process. Execution will be retried (attempt 155 of 2147483647) in 00:05:00 seconds.
Hangfire.PostgreSql.PostgreSqlDistributedLockException: Could not place a lock on the resource 'HangFire:recurring-jobs:lock': Lock timeout.

Error occurred during execution of 'DelayedJobScheduler' process. Execution will be retried (attempt 162 of 2147483647) in 00:05:00 seconds.
Hangfire.PostgreSql.PostgreSqlDistributedLockException: Could not place a lock on the resource 'HangFire:locks:schedulepoller': Lock timeout.

After merging #42 the compilation on AppVeyor fails with the following message:

powershell.exe : Error reading 'C:\projects\hangfire-postgresql-lel5h\src\Hangfire.PostgreSql.NetCore\project.json' at line 16 column 21 : TODO: unknown keyword platform
At line:4 char:1
+ powershell.exe -NoProfile -ExecutionPolicy unrestricted -Command "& { ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (Error reading '...eyword platform:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

  TODO: unknown keyword platform

This needs fixing. After the fix, all following unit tests should be made to pass.

Since updating to 1.4.8.1 recently I'm seeing messages like this every couple of minutes in the logs now:

Error occurred during execution of 'RecurringJobScheduler' process. Execution will be retried (attempt #2) in 00:00:04 seconds. System.TimeoutException: SetRangeInHash experienced timeout while trying to execute transaction
   at Hangfire.PostgreSql.PostgreSqlConnection.SetRangeInHash(String key, IEnumerable`1 keyValuePairs)
   at Hangfire.Server.RecurringJobScheduler.TryScheduleJob(JobStorage storage, IStorageConnection connection, String recurringJobId, IReadOnlyDictionary`2 recurringJob)
   at Hangfire.Server.RecurringJobScheduler.Execute(BackgroundProcessContext context)
   at Hangfire.Server.ServerProcessExtensions.Execute(IServerProcess process, BackgroundProcessContext context)
   at Hangfire.Server.AutomaticRetryProcess.Execute(BackgroundProcessContext context)

It seems like things are processing correctly, however I just wanted to make sure that this was to be expected or if this indicated something else might be going on that I'm not aware of. The app in question only rarely has a heavy load - the CPU/memory/number of connections on the database are next to nothing when this is happening as well, which also seems to be cause for concern.

See title ;-)

For now I added @bherila and @vytautask as collaborators.

Very often I see in the code the following constructions:
"SELECT SMTH FROM " + _options.SchemaName + ".tableName"
which produces a lot of memory allocations. The obvious solution is to replace concatenation by interpolation. But performance will be remain almost same because query strings is being built for every method call. I propose to cache query strings somehow in order to reduce number of allocations.
For example, create a PostgresSqlQueryProvider which will be accepts PostgreSqlStorageOptions and it will produce sql queries from templates.
The api will be: provider.BuildQuery("SELECT SMTH FROM {schemaName}.tableName")

Let's discuss :)

The following tests are now failing on .net core testing project:

Hangfire.PostgreSql.Tests.PostgreSqlConnectionFacts.GetJobData_ReturnsResult_WhenJobExists

and

Hangfire.PostgreSql.Tests.PostgreSqlWriteOnlyTransactionFacts.ExpireJob_SetsJobExpirationData

I'm using this awesome nuget package for a project to run a background service in a web service. One of the background jobs updates several other tables in the same database. The code that does this disposes of the connection object after each call. The problem I have is that I keep receiving exceptions about "Operation in Progress" that come from Hangfire.PostgreSql, and am wondering if there may be something I need to configure or use to avoid this.

Here are some screenshots of the exception:

They are two different exceptions, but seem to be related. I'm wondering if this may not even be an issue with Hangfire.PostgreSql, but with NPGSQL. I see this PR, which talks about two issues that sound a bit like this, but can't tell if it has been released yet.

Do you all have any ideas on ways to mitigate this?

Let me know if you need additional detail.

http://hmm.rs/Hangfire.PostgreSql throws a 404. Both this repository and the nuget project page point to it.