search

Betterment / delayed

a multi-threaded, SQL-driven ActiveJob backend used at Betterment to process millions of background jobs per day
MIT License
154 stars 8 forks source link

Error while reserving job(s): PG::SyntaxError: ERROR: syntax error at or near "SKIP" #20

Closed vijirajtrekhealth closed 1 year ago

vijirajtrekhealth commented 1 year ago

When i run 'rake delayed:work' to start the worker process, i am getting this error.

D, [2022-10-07T17:38:17.245081 #20659] DEBUG -- : 2022-10-07T17:38:17+0530: [Worker(host:Vs-MacBook-Pro.local pid:20659)] Starting Delayed::Worker

D, [2022-10-07T17:38:17.372640 #20659] DEBUG -- : Delayed::Job Load (0.5ms) UPDATE "delayed_jobs" SET locked_at = '2022-10-07 12:08:17.367212', locked_by = 'host:Vs-MacBook-Pro.local pid:20659' WHERE ctid = ANY (ARRAY (SELECT ctid FROM "delayed_jobs" WHERE (((run_at <= '2022-10-07 12:08:17.340440' AND (locked_at IS NULL OR locked_at < '2022-10-07 12:02:47.340461')) OR locked_by = 'host:Vs-MacBook-Pro.local pid:20659') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 5 FOR UPDATE SKIP LOCKED)) RETURNING *

D, [2022-10-07T17:38:17.373088 #20659] DEBUG -- : 2022-10-07T17:38:17+0530: [Worker(host:Vs-MacBook-Pro.local pid:20659)] Error while reserving job(s): PG::SyntaxError: ERROR: syntax error at or near "SKIP"

LINE 1: ...ER BY priority ASC, run_at ASC LIMIT 5 FOR UPDATE SKIP LOCKE...

My postgresql version is :

$ postgres -V

postgres (PostgreSQL) 14.3

Anyone have any idea why i am getting this error ? 

                                                         ^
jmileham commented 1 year ago

Are you sure the PostgreSQL server you are connecting to is also on a recent version? From a quick Google appears that PG <=9.4 would exhibit this behavior but it can't be easily explained on a more modern DB. Another thing to check would be any form of connection multiplexing tech you may be deploying that may be older than either the server or the client. Finally, maybe the PG C libraries that your pg gem is built against are older than the version of PG command line client you have.

vijirajtrekhealth commented 1 year ago

yes, i am using the latest version of postgresql. I will have to test it in another system.

jmileham commented 1 year ago

It would be a good idea to audit your machine for outdated libpq and rebuild your pg gem as well.

vijirajtrekhealth commented 1 year ago

ok, i will try to update those and check

smudge commented 1 year ago

Closing this because I haven't been able to reproduce it, but happy to reopen if someone encounters it and can provide steps. 👍