explicit cast error on postgres 9.3 (Ubuntu 14.04) about que HOT 16 CLOSED

Something's messed up in your setup. The queue column is text, not int.

On Monday, January 5, 2015, Solomon S. [email protected] wrote:

error when running 'rake que:work'

("class":"PG::UndefinedFunction","message":"ERROR: operator does not exist: integer = text\nLINE 7: WHERE queue = $1::text\n ^\nHINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.\n")

I was able to resolve this be making the following change in
/lib/que/sql.rb (line 10)

WHERE queue = $1::text

to

WHERE queue = $1::int4

—
Reply to this email directly or view it on GitHub
#70.

from que.

I have the environment variable QUE_QUEUE= default so that it picks up the active job queue. Other than moving to a newer version of the postgres server nothing has changed. Any thing in particular I should investigate?

from que.

I'm not sure what would cause this - can you put together a self-contained example for us to use to reproduce?

Was the Postgres upgrade to 9.4? I haven't tested Que with it yet, but I don't recall anything in the changelog that would affect this?

from que.

Ah, I see in the issue title that it's PG 9.3, never mind about that part.

from que.

I wiped out the table, undid the change I made to the local gem, and reran the migration, now this is the error:

I, [2015-01-06T11:19:05.608925 #5932]  INFO -- : {"lib":"que","hostname":"ITSsseal","pid":5932,"thread":74612400,"event":"job_errored","error":{"class":"PG::UndefinedFunction","message":"ERROR:  operator does not exist: text = integer\nLINE 7:           WHERE queue = $1::int4\n
      ^\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.\n"},"job":null}

postgres version is 9.3+154

from que.

Looks like reverting the local gem didn't stick, I fixed that and now it is working. It seems that que uses the DATABASE_URL and not database.yml for connection? This was causing it to not see the correct database as I use inheritance in my database.yml and specify different databases per enviroment.

Is this the intended behavior and is this documented (I might have just missed it)

from que.

Que uses DATABASE_URL in the specs, but in actual use it piggybacks on
whatever connection pool you give it - and if you haven't explicitly given
it one to use, the Railtie will hook it up to ActiveRecord's pool. So it'll
use whatever is yielded by
::ActiveRecord::Base.connection_pool.with_connection.

If you want it to connect to a different database than whatever
ActiveRecord is using, you can give it a custom connection (see
https://github.com/chanks/que/blob/master/docs/using_plain_connections.md)

• this isn't recommended, though, because then your jobs won't be covered
  by the same transactions as the rest of your data, and things will be less
  safe. I'm not sure what your exact setup is, but you should try to keep Que
  on the same connection pool as ActiveRecord if possible.

On Tue, Jan 6, 2015 at 11:33 AM, Solomon S. [email protected]
wrote:

Looks like reverting the local gem didn't stick, I fixed that and now it
is working. It seems that que uses the DATABASE_URL and not database.yml
for connection? This was causing it to not see the correct database as I
use inheritance in my database.yml and specify different databases per
enviroment.

—
Reply to this email directly or view it on GitHub
#70 (comment).

from que.

Here is my database.yml, I specify the database_url via enviroment variable, then switch out the database per enviroment and user running the instance. I was includeing everything BUT the database in the DATABASE_URL (since I was specifying it later) but when I added it to the DATABASE_URL que started working. Note that evertyhing else worked as expected with the first setup. I know this is an edge case, but does not seem to violate any rails guidelines.

development:
  url: <%= ENV['DATABASE_URL'] %>
  database: <%= "#{ENV['DATABASE_USER']}-development" %>

test: &test
  url: <%= ENV['DATABASE_URL'] %>
  database: <%= "#{ENV['DATABASE_USER']}-test" %>

production:
  url: <%= ENV['DATABASE_URL'] %>

I have resolved this as I documented above, however I do not know if this warrants any further investigation. Close this issue if you think this was a one off problem.

from que.

Sorry, I'm confused by what exactly you changed to make it work.

The error you are having indicates that something it sending the "queue" argument to PostgreSQL as an integer, not a string. If that can be reproduced by an incorrect database.yml, that sounds like a bug that should be fixed.

from que.

I would modify DATABASE_URL with URI.parse to switch databases, that should allow you to cleanly modify the database part of the URL.

I still think there might be some other bug happening in que that would cause this.

from que.

Que doesn't do anything with database.yml - it just assumes that ActiveRecord has working connections and borrows them as needed. I guess it's possible that, if you're waiting until some time after startup to give ActiveRecord a database name and therefore establish a connection (I didn't know that ActiveRecord had that functionality?) and Que's worker pool starts up before ActiveRecord is ready, that would cause problems. But it's probably a bad idea to use ActiveRecord like that in most cases in the first place, so if you're able to define the database name in database.yml that's probably what you should do.

As for the queue column being created with the integer datatype - I'm not sure what would cause that if you hadn't already hacked Que or manually modified the table in PG. Since it's never come up before and you're not able to reproduce it now, I don't think there's a lot we can do to investigate it, so I'm going to close this for now. But if it happens again to you or anyone else, or (better yet) if anyone is able to make a self-contained reproduction of how to cause it, I'll gladly look into it.

from que.

Yeah, if you see this again, it would be nice to get the output of the following command from psql. Looks like something happened to convert the queue column to an int in your system.

tanga_dev=# \d+ que_jobs
                                                          Table "public.que_jobs"
   Column    |           Type           |                         Modifiers                         | Storage  | Stats target | Description
-------------+--------------------------+-----------------------------------------------------------+----------+--------------+-------------
 priority    | smallint                 | not null default 100                                      | plain    |              |
 run_at      | timestamp with time zone | not null default now()                                    | plain    |              |
 job_id      | bigint                   | not null default nextval('que_jobs_job_id_seq'::regclass) | plain    |              |
 job_class   | text                     | not null                                                  | extended |              |
 args        | json                     | not null default '[]'::json                               | extended |              |
 error_count | integer                  | not null default 0                                        | plain    |              |
 last_error  | text                     |                                                           | extended |              |
 queue       | text                     | not null default ''::text                                 | extended |              |
 created_at  | timestamp with time zone | default now()                                             | plain    |              |
Indexes:
    "que_jobs_pkey" PRIMARY KEY, btree (queue, priority, run_at, job_id)
Has OIDs: no

from que.

I seem to be getting this same issue on PostgreSQL 9.4.1 using Postgres.app - though I only get this when running my test suite, my development database stays intact.

lumia_connection_test=# \d+ que_jobs
                                                                    Table "public.que_jobs"
   Column    |            Type             |                                 Modifiers                                  | Storage  | Stats target | Description 
-------------+-----------------------------+----------------------------------------------------------------------------+----------+--------------+-------------
 queue       | integer                     | not null default nextval('que_jobs_queue_seq'::regclass)                   | plain    |              | 
 priority    | smallint                    | not null default 100                                                       | plain    |              | 
 run_at      | timestamp without time zone | not null default '2015-02-27 16:53:27.918708'::timestamp without time zone | plain    |              | 
 job_id      | bigint                      | not null default 0                                                         | plain    |              | 
 job_class   | text                        | not null                                                                   | extended |              | 
 args        | json                        | not null default '[]'::json                                                | extended |              | 
 error_count | integer                     | not null default 0                                                         | plain    |              | 
 last_error  | text                        |                                                                            | extended |              | 
Indexes:
    "que_jobs_pkey" PRIMARY KEY, btree (queue)

Not entirely sure what's causing it, but whenever I run my test suite it re-converts the column to int4 from text (after manually changing it to text)

PG::UndefinedFunction at /dashboard/competition_winners ERROR: operator does not exist: integer = text LINE 3: WHERE queue = $1::text ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The only way I have been able to fix the problem is by dropping my test database and re-migrating.

from que.

Something has redefined your queue column as an integer, set up a sequence for it, and declared it the primary key. I suspect Rails/ActiveRecord, since the run_at default has also been changed to a static value instead of now(). Have you used the Rails rake tasks for loading the schema, maybe?

from que.

My test suite utilises a MySQL and a PG database. This is the only possibly cause I can think of, reading up I see it utilises available AR connections. Here's a look into my rails_helper file for database cleaner incase this could effect

  config.before(:suite) do
    DatabaseCleaner.strategy = :truncation
    DatabaseCleaner.clean_with :truncation

    DatabaseCleaner[:active_record, { model: Admin }].strategy = :truncation
    DatabaseCleaner[:active_record, { model: Admin }].clean_with :truncation
  end

  config.after(:all) do
    DatabaseCleaner.clean
    DatabaseCleaner[:active_record, { model: Admin }].clean
  end

  config.after(:each) do
    DatabaseCleaner.clean
    DatabaseCleaner[:active_record, { model: Admin }].clean
  end

In my test that causes the issue, I do load in seeds to the MySQL db in a before block
before { load Rails.root.join('db', 'seeds.rb') }

This would cause AR to connect to MySQL and create records.

from que.

And you're trying to have the same schema across Postgres and MySQL?

Anyways, I expect this is a byproduct of not setting schema_format to :sql, like we suggest doing in the README?

from que.