Ah, I am an idiot, please ignore my last message as I missed the comment about: use_64_bit: true being put in the sphinx.yml file.
I dropped that in and it casted them as bigints. So now I am running the index. If it blows up again I'll post back here. Thanks again, ~ Tom On Aug 15, 12:46 pm, Tom Cocca <[email protected]> wrote: > Hi Pat, > > I dropped this in my Gemfile: > > gem 'thinking-sphinx', :git => "git://github.com/freelancing-god/ > thinking-sphinx.git", :branch => "rails3" > > ran a bundle install and a bundle exec rake ts:conf and the datetimes > are still being case as INT (see below) > > source mls_property_core_0 > { > type = pgsql > sql_host = postgres.domain.com > sql_user = db_user > sql_pass = db_pass > sql_db = mls_properties > sql_port = 6432 > sql_query_pre = SET TIME ZONE 'UTC' > sql_query = SELECT "properties"."id" * 1::INT8 + 0 AS "id" , > "properties"."description" AS "description", > "properties"."street_number" AS "street_number", > "properties"."street_name" AS "street_name", "properties"."mls_id" AS > "mls_id", "states"."abbreviation" AS "state", "towns"."name" AS > "town", "offices"."mls_id" AS "list_office_mls", "offices"."name" AS > "list_office_name", "agents"."mls_id" AS "list_agent_mls", > agents.first_name || ' ' || agents.last_name AS "list_agent_name", > "sale_offices_properties"."mls_id" AS "sale_office_mls", > "sale_offices_properties"."name" AS "sale_office_name", > "sale_agents_properties"."mls_id" AS "sale_agent_mls", > properties.town_id || 'x' || properties.neighborhood_id AS > "town_neighborhood", (properties.full_bathrooms + > properties.half_bathrooms + properties.quarter_bathrooms + > properties.three_quarter_bathrooms) AS "total_bathrooms", > "properties"."zip" AS "zip_code", "properties"."id" AS > "sphinx_internal_id", 0 AS "sphinx_deleted", 1814387568 AS > "class_crc", "properties"."neighborhood_id" AS "neighborhood_id", > "properties"."state_id" AS "state_id", "properties"."town_id" AS > "town_id", "properties"."property_type_id" AS "property_type_id", > "properties"."status_id" AS "status_id", "properties"."service_id" AS > "service_id", "properties"."bedrooms" AS "bedrooms", > "properties"."full_bathrooms" AS "full_bathrooms", > "properties"."half_bathrooms" AS "half_bathrooms", > "properties"."price" AS "price", "properties"."square_footage" AS > "square_footage", "properties"."acres" AS "acres", > "properties"."year_built" AS "year_built", > "properties"."parking_spaces" AS "parking_spaces", > "properties"."taxes" AS "taxes", "properties"."parking_available" AS > "parking_available", "properties"."waterfront" AS "waterfront", > "properties"."beachfront" AS "beachfront", "properties"."basement" AS > "basement", "properties"."bank_owned" AS "bank_owned", > "properties"."opt_out" AS "opt_out", cast(extract(epoch from > "properties"."mls_created_at") as int) AS "mls_created_at", > cast(extract(epoch from "properties"."mls_updated_at") as int) AS > "mls_updated_at", > array_to_string(array_accum(COALESCE(cast(extract(epoch from > "open_houses"."start_time") as int), '0')), ',') AS > "open_house_start", "properties"."id" AS "property_id" FROM > "properties" LEFT OUTER JOIN "states" ON "states"."id" = > "properties"."state_id" LEFT OUTER JOIN "towns" ON "towns"."id" = > "properties"."town_id" LEFT OUTER JOIN "offices" ON "offices"."id" = > "properties"."list_office_id" LEFT OUTER JOIN "agents" ON > "agents"."id" = "properties"."list_agent_id" LEFT OUTER JOIN "offices" > "sale_offices_properties" ON "sale_offices_properties"."id" = > "properties"."sale_office_id" LEFT OUTER JOIN "agents" > "sale_agents_properties" ON "sale_agents_properties"."id" = > "properties"."sale_agent_id" LEFT OUTER JOIN "open_houses" ON > "open_houses"."property_id" = "properties"."id" WHERE > ("properties"."id" >= $start AND "properties"."id" <= $end) GROUP BY > "properties"."id", "properties"."description", > "properties"."street_number", "properties"."street_name", > "properties"."mls_id", "states"."abbreviation", "towns"."name", > "offices"."mls_id", "offices"."name", "agents"."mls_id", > "sale_offices_properties"."mls_id", "sale_offices_properties"."name", > "sale_agents_properties"."mls_id", "properties"."zip", > "properties"."id", "properties"."neighborhood_id", > "properties"."state_id", "properties"."town_id", > "properties"."property_type_id", "properties"."status_id", > "properties"."service_id", "properties"."bedrooms", > "properties"."full_bathrooms", "properties"."half_bathrooms", > "properties"."price", "properties"."square_footage", > "properties"."acres", "properties"."year_built", > "properties"."parking_spaces", "properties"."taxes", > "properties"."parking_available", "properties"."waterfront", > "properties"."beachfront", "properties"."basement", > "properties"."bank_owned", "properties"."opt_out", > "properties"."mls_created_at", "properties"."mls_updated_at", > "properties"."id", list_agent_name, town_neighborhood, total_bathrooms > sql_query_range = SELECT COALESCE(MIN("id"), 1::bigint), > COALESCE(MAX("id"), 1::bigint) FROM "properties" > sql_attr_uint = sphinx_internal_id > sql_attr_uint = sphinx_deleted > sql_attr_uint = class_crc > sql_attr_uint = neighborhood_id > sql_attr_uint = state_id > sql_attr_uint = town_id > sql_attr_uint = property_type_id > sql_attr_uint = status_id > sql_attr_uint = service_id > sql_attr_uint = bedrooms > sql_attr_uint = full_bathrooms > sql_attr_uint = half_bathrooms > sql_attr_uint = price > sql_attr_uint = square_footage > sql_attr_uint = year_built > sql_attr_uint = parking_spaces > sql_attr_uint = property_id > sql_attr_bool = parking_available > sql_attr_bool = waterfront > sql_attr_bool = beachfront > sql_attr_bool = basement > sql_attr_bool = bank_owned > sql_attr_bool = opt_out > sql_attr_timestamp = mls_created_at > sql_attr_timestamp = mls_updated_at > sql_attr_float = acres > sql_attr_float = taxes > sql_attr_multi = uint open_house_start from field > sql_query_info = SELECT * FROM "properties" WHERE "id" = (($id - > 0) / 1) > > } > > I did not change anything w/ regards to the index block: > > define_index do > indexes description, street_number, street_name, mls_id > indexes state.abbreviation, :as => :state > indexes town.name, :as => :town > indexes list_office.mls_id, :as => :list_office_mls > indexes list_office.name, :as => :list_office_name > indexes list_agent.mls_id, :as => :list_agent_mls > indexes "agents.first_name || ' ' || agents.last_name", :as > => :list_agent_name > indexes sale_office.mls_id, :as => :sale_office_mls > indexes sale_office.name, :as => :sale_office_name > indexes sale_agent.mls_id, :as => :sale_agent_mls > indexes "properties.town_id || 'x' || > properties.neighborhood_id", :as => :town_neighborhood > indexes "(properties.full_bathrooms + properties.half_bathrooms + > properties.quarter_bathrooms + > properties.three_quarter_bathrooms)", :as => :total_bathrooms > indexes zip, :as => :zip_code > group_by 'list_agent_name', 'town_neighborhood', 'total_bathrooms' > > set_property :enable_star => true > set_property :min_prefix_len => 3 > > has neighborhood_id, state_id, town_id, property_type_id, > status_id, service_id > has bedrooms, full_bathrooms, half_bathrooms, price, > square_footage, acres, year_built, parking_spaces, taxes > has parking_available, waterfront, beachfront, basement, > bank_owned, opt_out > has mls_created_at, mls_updated_at > has open_houses.start_time, :as => :open_house_start > has (:id), :as => :property_id > end > > Do I need to change anything to get those 3 datetimes to cast as > BIGINT? > > Any thoughts? > > Thanks, > ~ Tom > > On Aug 11, 2:59 am, Pat Allan <[email protected]> wrote: > > > > > > > > > Hi Tom > > > I think the datetime columns could be bigints - which aren't handled as > > such by default in TS. There has been a patch which could help this though > > - it's not in 2.0.5, and I haven't released a new TS just yet... so, can > > you use the rails3 branch of the github repo as your source? > > > git://github.com/freelancing-god/thinking-sphinx.git > > > If so, try setting use_64_bit to true in your sphinx.yml file - this will > > ensure all datetime values are casted to bigints instead of ints. > > > Cheers > > > -- > > Pat > > > On 10/08/2011, at 2:49 AM, Tom Cocca wrote: > > > > Hello, > > > > I am running thinking-sphinx 2.0.5, Sphinx 0.9.9-id64-release (r2117) > > > and PG 9.0.2 and I am getting the following error when I run rake > > > ts:rebuild > > > > Generating Configuration to /home/deploy/rails/generic_updater/ > > > releases/20110809141657/config/production.sphinx.conf > > > Sphinx 0.9.9-id64-release (r2117) > > > Copyright (c) 2001-2009, Andrew Aksyonoff > > > > using config file '/home/deploy/rails/generic_updater/releases/ > > > 20110809141657/config/production.sphinx.conf'... > > > indexing index 'mls_property_core'... > > > ERROR: index 'mls_property_core': sql_range_query: ERROR: integer out > > > of range > > > (DSN=pgsql://db_user:*[email protected]:6432/mls_properties). > > > total 423927 docs, 187383324 bytes > > > total 757.711 sec, 247301 bytes/sec, 559.48 docs/sec > > > distributed index 'mls_property' can not be directly indexed; > > > skipping. > > > total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg > > > total 638 writes, 1.737 sec, 953.1 kb/call avg, 2.7 msec/call avg > > > Started successfully (pid 3592). > > > > The Index looks as follows: > > > > define_index do > > > indexes description, street_number, street_name, mls_id > > > indexes state.abbreviation, :as => :state > > > indexes town.name, :as => :town > > > indexes list_office.mls_id, :as => :list_office_mls > > > indexes list_office.name, :as => :list_office_name > > > indexes list_agent.mls_id, :as => :list_agent_mls > > > indexes "agents.first_name || ' ' || agents.last_name", :as > > > => :list_agent_name > > > indexes sale_office.mls_id, :as => :sale_office_mls > > > indexes sale_office.name, :as => :sale_office_name > > > indexes sale_agent.mls_id, :as => :sale_agent_mls > > > indexes "properties.town_id || 'x' || > > > properties.neighborhood_id", :as => :town_neighborhood > > > indexes "(properties.full_bathrooms + properties.half_bathrooms + > > > properties.quarter_bathrooms + > > ... > > read more » -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
