Migrating a rails2 app to rails 3, difficulty in getting thinking sphinx indexing to run

Question

A rails 2.3.10 is being migrated to rails 3.2.18. It revolves entirely around full text indexing. The rails 2 version was running on postgresql 9.0.4, while the migrated version is running on 9.3.5

Data tables are ported over to the new rails 3 versions without errors. I do have a couple of models which need some ironing out, but are NOT objects related to this question/cases.

rake ts:configure 

generates the sphinx.conf file. It looks slightly different from a native rails3 sphinx/thinking-sphinx app.

source filodiretto_core_0
{
  type = pgsql
  sql_host = localhost
  sql_user = jerdvo
  sql_pass = 
  sql_db = fna_development
  sql_query_pre = SET TIME ZONE UTC
  sql_query = SELECT "filodirettos"."id" * 24 + 0 AS "id", "filodirettos"."titolo" AS "titolo", "filodirettos"."domanda" AS "domanda", "filodirettos"."data" AS "data", "filodirettos"."risposta" AS "risposta", "filodirettos"."id" AS "sphinx_internal_id", 'Filodiretto' AS "sphinx_internal_class", 0 AS "sphinx_deleted" FROM "filodirettos"  WHERE ("filodirettos"."id" BETWEEN $start AND $end) GROUP BY "filodirettos"."id", "filodirettos"."titolo", "filodirettos"."domanda", "filodirettos"."data", "filodirettos"."risposta", "filodirettos"."id"
  sql_query_range = SELECT COALESCE(MIN("filodirettos"."id"), 1), COALESCE(MAX("filodirettos"."id"), 1) FROM "filodirettos" 
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = sphinx_deleted
  sql_attr_string = sphinx_internal_class
  sql_query_info = SELECT "filodirettos".* FROM "filodirettos"  WHERE ("filodirettos"."id" = ($id - 0) / 24)
}

index filodiretto_core
{
  type = plain
  path = /Users/jerdvo/r/fna/shared/sphinx/filodiretto_core
  docinfo = extern
  charset_type = utf-8
  min_infix_len = 2
  enable_star = 1
  source = filodiretto_core_0
}

However when I launch

rake ts:index

all indices are failing

indexing index 'filodiretto_core'...
ERROR: source 'filodiretto_core_0': unknown type 'pgsql'; skipping.
ERROR: index 'filodiretto_core': failed to configure some of the sources, will not index.
[...] skipping non-plain index 'filodiretto'...

The relative '_index.rb' file states

ThinkingSphinx::Index.define :filodiretto, :with => :active_record do
    indexes titolo
    indexes domanda
    indexes data
    indexes risposta
end

I've confronted a pg_dump file with a native rails3 app. The only difference is at the top of the file, the version coming from rails 2 (and postgres 9.0.4) states (at the beginning)

--
-- Name: crc32(text); Type: FUNCTION; Schema: public; Owner: jerdvo
--

CREATE FUNCTION crc32(word text) RETURNS bigint
    LANGUAGE plpgsql IMMUTABLE
    AS $$
          DECLARE tmp bigint;
          DECLARE i int;
          DECLARE j int;
          DECLARE byte_length int;
          DECLARE word_array bytea;

I don't know where this 'pgsql' error is coming from.

Answer 1

It looks like your copy of Sphinx was not installed with PostgreSQL support. You should re-install it with the appropriate flag - either --with-pgsql if compiling manually, or --pgsql when installing by Homebrew. MySQL support is also required as well, but that may already be applied automatically?