Context does not work with Postgres with regular json fields

[dennispaagman]

Currently when you try to set something in the context with PG you will get the following error:

PG::UndefinedFunction: ERROR: operator does not exist: json = json LINE 1: ...pdated_at=(CASE WHEN ("acidic_job_values"."value" IS NOT DIS... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

it seems Postgres cannot compare json values directly, as it needs to do that for the upsert as the json field is used in the unique_by.

If you change the json field to jsonb it works fine.

I saw that the noticed gem checks explicitly for jsonb support in their migration, maybe that's something you can incorporate as well:

https://github.com/excid3/noticed/blob/c0f63170bd8d4651684778650a1a07c07e04b58e/db/migrate/20231215190233_create_noticed_tables.rb#L7-L11

if t.respond_to?(:jsonb)
  t.jsonb :params
else
  t.json :params
end

Locally I ran the whole test suite on postgres with jsonb and there are two additional failures, but they seem to be related to how queries are formatted a bit differently with bind variables in Postgres.

.F

Failure:
AcidicJob::BasicsTest#test_workflow_with_db_IO_then_error_on_attempt_1_but_then_success_needs_idempotency_check_that_can_be_selective [test/acidic_job/basics_test.rb:493]:
Expected /SELECT\ 1\ AS\ one\ FROM\ "test_objects"\ LIMIT\ ?/ to match "SELECT 1 AS one FROM "test_objects" LIMIT $1".

bin/rails test /Users/dennis/Code/gems/acidic_job/test/acidic_job/basics_test.rb:429

..F

Failure:
AcidicJob::BasicsTest#test_workflow_with_database_IO_then_error_on_attempt_1_but_then_success_needs_idempotency_check [test/acidic_job/basics_test.rb:424]:
Expected /SELECT\ 1\ AS\ one\ FROM\ "test_objects"\ LIMIT\ ?/ to match "SELECT 1 AS one FROM "test_objects" LIMIT $1".

bin/rails test /Users/dennis/Code/gems/acidic_job/test/acidic_job/basics_test.rb:363

[fractaledmind]

I'm sorry for the late reply. I could have sworn I had already replied to this, but apparently not. This will be fixed in the next release candidate.

[fractaledmind]

@dennispaagman Can you also test if changing the schema type to text resolves the problem? There is actually no reason to store the values as JSON. We don't do anything with them in JSON. I think that was a copy/paste that shouldn't be there.