Does not work with PostgreSQL 8.4 #33

Open
duritong opened this Issue Feb 18, 2013 · 17 comments

Projects

None yet
@duritong

Although #32 fixed issues with PostgreSQL it still does not work with PostgreSQL 8.4, which for example ships with RHEL 6.

Error:

$ rake jobs:work --trace
[Worker(host:foo.localdomain pid:8232)] Starting job worker
rake aborted!
PG::Error: ERROR: SELECT FOR UPDATE/SHARE is not allowed in subqueries
: UPDATE "delayed_jobs" SET locked_at = '2013-02-18 09:58:32.257303', locked_by = 'host:foo.localdomain pid:8232' WHERE id IN (SELECT id FROM "delayed_jobs" WHERE ((run_at <= '2013-02-18 09:58:32.207834' AND (locked_at IS NULL OR locked_at < '2013-02-18 07:58:32.207910') OR locked_by = 'host:foo.localdomain pid:8232') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1 FOR UPDATE) RETURNING *
/home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/connection_adapters/postgresql_adapter.rb:1153:in async_exec' /home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/connection_adapters/postgresql_adapter.rb:1153:inexec_no_cache'
/home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/connection_adapters/postgresql_adapter.rb:662:in exec_query' /home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/connection_adapters/abstract_adapter.rb:280:inlog'
/home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activesupport-3.2.12/lib/active_support/notifications/instrumenter.rb:20:in instrument' /home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/connection_adapters/abstract_adapter.rb:275:inlog'
/home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/connection_adapters/postgresql_adapter.rb:661:in exec_query' /home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/connection_adapters/postgresql_adapter.rb:1248:inselect'
/home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in select_all' /home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/connection_adapters/abstract/query_cache.rb:63:inselect_all'
/home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/querying.rb:38:in find_by_sql' /home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/explain.rb:33:inlogging_query_plan'
/home/duritong/.rvm/gems/ree-1.8.7-2012.02@enclouder/gems/activerecord-3.2.12/lib/active_record/querying.rb:37:in `find_by_sql'

I could not find any requirements regarding PostgreSQL Version. It would be good if you would support the latest database version on the latest major enterprise linux distribution.

+1 same problem for me on 8.4. It was working, I'm going to try reverting to a previous version.

+1 Same happens on PG8.4 on Ubuntu (https://gist.github.com/shlomizadok/5024263)

Contributor

I have a branch that uses more generic SQL. I did it to support SQL server, but I'm wondering if it fixes this issue as well. Can you test with: https://github.com/gaslight/delayed_job_active_record

Collaborator
sferik commented Mar 1, 2013

This should be fixed by #38 (thanks @superchris). Can you please verify the fix by pointing to the this repo in your Gemfile:

gem 'delayed_job_active_record', :git => 'git://github.com/collectiveidea/delayed_job_active_record.git'

Once the fix is verified, I will push a new version of the gem.

@sferik @superchris , I am sorry - it didn't work on my end (see error here: https://gist.github.com/shlomizadok/5065137 )

Collaborator
sferik commented Apr 2, 2013

Can you please verify whether this is fixed in the latest gem version (0.4.4)?

I am sorry. It is not fixed on version 0.4.4

rake aborted!
PG::Error: ERROR:  SELECT FOR UPDATE/SHARE is not allowed in subqueries
: UPDATE "delayed_jobs" SET locked_at = '2013-04-06 15:23:52.063967', locked_by = 'host:ip-10-194-110-3 pid:8340' WHERE id IN (SELECT  id FROM "delayed_jobs"  WHERE ((run_at <= '2013-04-06 15:23:51.651133' AND (locked_at IS NULL OR locked_at < '2013-04-06 11:23:51.651174') OR locked_by = 'host:ip-10-194-110-3 pid:8340') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1 FOR UPDATE) RETURNING *
kuboon commented Apr 15, 2013

Thanks, @superchris, it was fixed for me:

gem 'delayed_job_active_record', :git => 'https://github.com/gaslight/delayed_job_active_record'

Sorry @superchris -- it is not working for me :(

[Worker(host:ip-10-194-110-3 pid:13577)] Starting job worker
rake aborted!
PG::Error: ERROR:  SELECT FOR UPDATE/SHARE is not allowed in subqueries
: UPDATE "delayed_jobs" SET locked_at = '2013-04-17 20:43:14.018158', locked_by = 'host:ip-10-194-110-3 pid:13577' WHERE id IN (SELECT  id FROM "delayed_jobs"  WHERE ((run_at <= '2013-04-17 20:43:13.540058' AND (locked_at IS NULL OR locked_at < '2013-04-17 16:43:13.540120') OR locked_by = 'host:ip-10-194-110-3 pid:13577') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1 FOR UPDATE) RETURNING *

Doesn't work for me either=(

mongeta commented Jun 26, 2013

I'm having the same issue, and I can't not upgrade this postgreSQL ...

@superchris suggestion fixed the issue for one of my sites. FreeBSD, PostgreSQL 8.4, Rails 3.2.13
Had to change the https protocol to git in order for it to clone to the server.

 gem 'delayed_job_active_record', :git => 'git://github.com/gaslight/delayed_job_active_record'
panterch commented Oct 9, 2013

This works for PostgreSQL 8.4, Rails 4.0.0

gem 'delayed_job_active_record', :git => 'git@github.com:panter/delayed_job_active_record.git'

Many thanks @panter , your fork saved me a lot of time. Runs on the following:

  • Rails 4.1 (AR 4.0.1)
  • Ubuntu 10.4
  • PG 8.4

I've submitted a pull request #88 to check the postgresql version and only use the optimized sql for 9.0 and above

Contributor
csmuc commented May 4, 2014

I'd prefer a configuration option to turn off the optimized version. The optimized version for MySQL floods my mysqld.log like this:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

@csmuc csmuc added a commit to csmuc/delayed_job_active_record that referenced this issue May 4, 2014
@csmuc csmuc Extract code, which handles optimized SQL statements, into methods.
Can be used to more easily monkey-patch the code to always use the default SQP statement (see #33)
ec5fc2b
@csmuc csmuc added a commit to csmuc/delayed_job_active_record that referenced this issue May 4, 2014
@csmuc csmuc Can be used to more easily monkey-patch the code to always use the de…
…fault SQL statement (see #33)
44ae164
@csmuc csmuc added a commit to csmuc/delayed_job_active_record that referenced this issue May 4, 2014
@csmuc csmuc Extract reserve methods.
Can be used to more easily monkey-patch the code to always use the default SQL statement (see #33)
ac72892
@csmuc csmuc added a commit to csmuc/delayed_job_active_record that referenced this issue Aug 19, 2014
@csmuc csmuc Extract reserve methods.
Can be used to more easily monkey-patch the code to always use the default SQL statement (see #33)
390209b
@csmuc csmuc added a commit to csmuc/delayed_job_active_record that referenced this issue Aug 19, 2014
@csmuc csmuc Extract reserve methods.
Can be used to more easily monkey-patch the code to always use the default SQL statement (see #33)
0eecb8f
patleb commented Feb 20, 2015

There is a quick fix that works 1333391

@danhodge danhodge added a commit to swipely/delayed_job_active_record that referenced this issue Jun 11, 2015
@csmuc @danhodge csmuc + danhodge Extract reserve methods.
Can be used to more easily monkey-patch the code to always use the default SQL statement (see #33)
b95148d
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment