Odd that I couldn't reproduce this - but good to know you've got it
figured out. I've created an issue, and will fix this when I have the
time.
Cheers
--
Pat
On 05/10/2009, at 10:03 PM, Lucho_patton wrote:
>
> Hi Pat. 1 solution found.
>
> line 43 --- lib/thinking_sphinx/adapters/postgresql_adapter.rb
>
> "COALESCE(#{clause}, #{default})"
>
> as
>
> "COALESCE(CAST(#{clause} as bigint), #{default})"
>
> and rake ts:in works.
>
> Thanks anyway Pat.
>
> On Sep 30, 5:57 pm, Pat Allan <[email protected]> wrote:
>> Can you try sphinx 0.9.9 rc2, see if that fixes the problem?
>>
>> --
>> Pat
>>
>> On 30/09/2009, at 11:49 PM, Lucho_patton wrote:
>>
>>
>>
>>> same error Pat.
>>
>>> source noticia_core_0
>>> {
>>> type = pgsql
>>> sql_host = localhost
>>> sql_user = ardp
>>> sql_pass = ardp
>>> sql_db = pvsdev
>>> sql_query = SELECT "noticias"."id" * 1 + 0 AS "id" ,
>>> "noticias"."titulo" AS "titulo", "noticias"."id" AS
>>> "sphinx_internal_id", CAST(4271728138 as bigint) AS "class_crc",
>>> COALESCE('4271728138', 0) AS "subclass_crcs", 0 AS "sphinx_deleted"
>>> FROM "noticias" WHERE "noticias"."id" >= $start AND
>>> "noticias"."id"
>>> <= $end GROUP BY "noticias"."id", "noticias"."titulo",
>>> "noticias"."id"
>>> sql_query_range = SELECT COALESCE(MIN("id"), 1), COALESCE(MAX
>>> ("id"),
>>> 1) FROM "noticias"
>>> sql_range_step = 10000000
>>> sql_attr_uint = sphinx_internal_id
>>> sql_attr_uint = class_crc
>>> sql_attr_uint = sphinx_deleted
>>> sql_attr_multi = uint subclass_crcs from field
>>> sql_query_info = SELECT * FROM "noticias" WHERE "id" = (($id - 0) /
>>> 1)
>>> }
>>
>>> On Sep 30, 4:07 pm, Pat Allan <[email protected]> wrote:
>>>> Hmm, this may be a bug in PostgreSQL, as I've just tried 0.9.9-rc1
>>>> with PG 8.4.0, and it indexes without any problems...
>>
>>>> Can you edit the SQL query, replacing '4271728138 AS "class_crc"'
>>>> with
>>>> 'CAST(4271728138 as bigint) AS "class_crc"', then runindexinglike
>>>> so:
>>>> rake ts:index INDEX_ONLY=true
>>
>>>> Is there still an error?
>>
>>>> --
>>>> Pat
>>
>>>> On 30/09/2009, at 9:11 PM, Luis Andres Mancilla Avila wrote:
>>
>>>>> version:
>>
>>>>> sphinx: 0.9.9-rc1 (r1566)
>>>>> postgresql: 8.3.8
>>
>>>>> 2009/9/30 Pat Allan <[email protected]>
>>
>>>>> Hmm, I can't actually reproduce the problem... what versions of
>>>>> Sphinx
>>>>> and PostgreSQL are you using?
>>
>>>>> --
>>>>> Pat
>>
>>>>> On 30/09/2009, at 5:59 PM, Luis Andres Mancilla Avila wrote:
>>
>>>>>> ok man. so i'll be waiting. because i did not found any answer
>>>>>> for
>>>>>> my problem.
>>
>>>>>> thanks Pat.
>>
>>>>>> 2009/9/30 Pat Allan <[email protected]>
>>
>>>>>> It looks like "Notica" as a CRC code is larger than a signed
>>>>> integer.
>>>>>> I'll look into PostgreSQL, see if we can convince it to treat it
>>>>> as an
>>>>>> unsigned integer instead. Hopefully I can get a fix done in the
>>>>>> next
>>>>>> 24 hours.
>>
>>>>>> --
>>>>>> Pat
>>
>>>>>> On 30/09/2009, at 5:44 PM, Lucho_patton wrote:
>>
>>>>>>> that's my query
>>
>>>>>>> sql_query = SELECT "noticias"."id" * 1 + 0 AS "id" ,
>>>>>>> "noticias"."titulo" AS "titulo", "noticias"."id" AS
>>>>>>> "sphinx_internal_id", 4271728138 AS "class_crc", COALESCE
>>>>>>> ('4271728138', 0) AS "subclass_crcs", 0 AS "sphinx_deleted" FROM
>>>>>>> "noticias" WHERE "noticias"."id" >= $start AND
>>>>> "noticias"."id" <=
>>>>>>> $end GROUP BY "noticias"."id", "noticias"."titulo",
>>>>> "noticias"."id"
>>
>>>>>>> sql_query_range = SELECT COALESCE(MIN("id"), 1), COALESCE(MAX
>>>>> ("id"),
>>>>>>> 1) FROM "noticias"
>>
>>>>>>> help please
>>
>>>>>>> On Sep 30, 11:38 am, Pat Allan <[email protected]>
>>>>>>> wrote:
>>>>>>>> Hi Luis
>>
>>>>>>>> What's the sql_query in your notica_core source in the config
>>>>> file?
>>>>>>>> Are you using fixtures?
>>
>>>>>>>> --
>>>>>>>> Pat
>>
>>>>>>>> On 30/09/2009, at 5:34 PM, Lucho_patton wrote:
>>
>>>>>>>>> /usr/bin/indexer --config "/home/lmancilla/Public/pvm/config/
>>>>>>>>> development.sphinx.conf" --all
>>
>>>>>>>>> using config file '/home/lmancilla/Public/pvm/config/
>>>>>>>>> development.sphinx.conf'...
>>>>>>>>> indexingindex 'noticia_core'...
>>>>>>>>> ERROR: index 'noticia_core': sql_range_query: ERROR: value
>>>>>>>>> "4271728138" isoutofrangefor type integer
>>>>>>>>> (DSN=pgsql://ardp:*...@localhost:5432/pvsdev).
>>
>>>>>>>>> there is a solution for this?
>>
>>>>>>>>> thanks.
>>
>>>>>>>>> On Aug 5, 8:25 am, Pat Allan <[email protected]>
>>>>>>>>> wrote:
>>>>>>>>>> Firstly: the reason the multiplication exists is to ensure
>>>>> Sphinx
>>>>>>>>>> has
>>>>>>>>>> completely unique ids across all documents.
>>
>>>>>>>>>> That said, the problem's still annoying - but 987074798 * 3
>>>>>>>>>> is
>>>>>>>>>> larger
>>>>>>>>>> than the maximum for a *signed* integer, so I'm wondering if
>>>>>> that's
>>>>>>>>>> the problem. Can you provide the error output, just so it's
>>>>> clear
>>>>>>>>>> whether it's Sphinx or PostgreSQL that's causing the problem?
>>
>>>>>>>>>> --
>>>>>>>>>> Pat
>>
>>>>>>>>>> On 05/08/2009, at 11:23 AM, uros wrote:
>>
>>>>>>>>>>> I'm having the same problem and don't know how to get over
>>>>>>>>>>> it.
>>>>>> I'm
>>>>>>>>>>> using sphinx with postgres on windows.
>>
>>>>>>>>>>> My problem is a model, that has id '987074798' and higher.
>>>>>>>>>>> Currently
>>>>>>>>>>> I'm not using any fixtures, but I did in the past, so that
>>>>>>>>>>> probably
>>>>>>>>>>> the cause for high values in ps sequences for primary keys
>>>>>>>>>>> in
>>>>>> most
>>>>>>>>>>> tables.
>>
>>>>>>>>>>> Anyway, the funny thing is if I index only this model, then
>>>>>>>>>>> index is
>>>>>>>>>>> built correctly. But if I index several models, it fails
>>>>>> building
>>>>>>>>>>> index for this model with the following message:
>>>>>>>>>>> ERROR: index 'section_core': sql_range_query: ERROR:
>>>>>> integeroutof
>>>>>>>>>>> range
>>
>>>>>>>>>>> After looking at the generated *.sphinx.conf, I think that
>>>>>> problem
>>>>>>>>>>> might be in recalculations of the ID field in the sql_query
>>>>>>>>>>> setting:
>>>>>>>>>>> 'SELECT "sections"."id" * 3 + 1 AS "id"
>>>>>>>>>>> It seems as this multiplication by 3 is causing the problem,
>>>>>>>>>>> because
>>>>>>>>>>> when I index only problematic model, I get no errors and
>>>>>>>>>>> index
>>>>>> is
>>>>>>>>>>> built. Altough it is strange because calculated value
>>>>> (987074798
>>>>>>>>>>> *3)
>>>>>>>>>>> doesn't exceed 4 bytes for uint.
>>
>>>>>>>>>>> I must say I'm new to TS in sphinx itself, so any help would
>>>>> be
>>>>>>>>>>> appreciated!
>>
>>>>>>>>>>> On Jul 30, 7:56 pm, David <[email protected]> wrote:
>>>>>>>>>>>> Thanks for the reply Pat.
>>
>>>>>>>>>>>> I finally figuredoutwhat's going on. And I think I have a
>>>>>>>>>>>> few
>>>>>>>>>>>> extra
>>>>>>>>>>>> gray hairs now too!
>>
>>>>>>>>>>>> Apparently, rails migrations use a column type of
>>>>>>>>>>>> "serial" in
>>>>>>>>>>>> postgres
>>>>>>>>>>>> for table ids. These serial fields are 4 bytes (ergo they
>>>>>>>>>>>> go
>>>>>>>>>>>> from 1
>>>>>>>>>>>> to
>>>>>>>>>>>> 2147483647). However, loading fixtures picks random numbers
>>>>>> like
>>>>>>>>>>>> 541702176 which is much bigger than 4 bytes and which is
>>>>>>>>>>>> why
>>>>>>>>>>>> sphinx's
>>>>>>>>>>>> indexer was flippingout.
>>
>>>>>>>>>>>> I guess the solution would be to either A) use a bigger
>>>>> column
>>>>>>>>>>>> type
>>>>>>>>>>>> or
>>>>>>>>>>>> B) don't use fixtures in production (or if you must, give
>>>>> your
>>>>>>>>>>>> fixtures an id).
>>
>>>>>>>>>>>> I hope that helps anyone else with the same problem that
>>>>>> searches
>>>>>>>>>>>> google.
>>
>>>>>>>>>>>> On Jul 29, 4:57 am, Pat Allan <[email protected]>
>>>>>> wrote:
>>
>>>>>>>>>>>>> Yeah, it was the first problem thatout-of-the-ordinary...
>>>>>>>>>>>>> although
>>>>>>>>>>>>> I'm surprised the id causing the problem was so small
>>>>>>>>>>>>> (it's
>>>>>> not
>>>>>>>>>>>>> anywhere close to hitting the maximum value of a 32-bit
>>>>>>>>>>>>> integer).
>>>>>>>>>>>>> Still, using normal ids is definitely the best way to go.
>>
>>>>>>>>>>>>> Good to know you've got it all sorted.
>>
>>>>>>>>>>>>> --
>>>>>>>>>>>>> Pat
>>
>>>>>>>>>>>>> On 28/07/2009, at 6:44 PM, David wrote:
>>
>>>>>>>>>>>>>> I actually solved the hanging problem. I properly indexed
>>>>> my
>>>>>>>>>>>>>> tables.
>>
>>>>>>>>>>>>>> I also can solve the first problem by changing the ids to
>>>>>>>>>>>>>> reasonable
>>>>>>>>>>>>>> numbers. Setting "sql_range_step: 10000000" does not seem
>>>>> to
>>>>>>>>>>>>>> solve the
>>>>>>>>>>>>>> problem.
>>
>>>>>>>>>>>>>> The id in question is 541702176.
>>
>>>>>>>>>>>>>> Thanks.
>>
>>>>>>>>>>>>>> On Jul 28, 9:46 am, David <[email protected]> wrote:
>>>>>>>>>>>>>>> Hi guys,
>>
>>>>>>>>>>>>>>> I guess this is not really a thinking sphinx problem
>>>>>>>>>>>>>>> but I
>>>>>> am
>>>>>>>>>>>>>>> having
>>>>>>>>>>>>>>> sometroubleindexing my tables (when I run "rake
>>>>> ts:index").
>>
>>>>>>>>>>>>>>> The first error I get is:
>>
>>>>>>>>>>>>>>> ERROR: index 'user_core': sql_range_query: ERROR:
>>>>>> integerout
>>>>>>>>>>>>>>> of
>>>>>>>>>>>>>>> range
>>>>>>>>>>>>>>> (DSN=pgsql://root:*...@localhost:5432/
>>>>>>>>>>>>>>> robertson_scholars_development).
>>
>>>>>>>>>>>>>>> The second error I get is that it hangs whenindexinga
>>>>> model:
>>
>>>>>>>>>>>>>>> distributed index 'assignment' can not be directly
>>>>> indexed;
>>>>>>>>>>>>>>> skipping.
>>>>>>>>>>>>>>> indexingindex 'custom_field_type_core'...
>>
>>>>>>>>>>>>>>> It just freezes. There's only 20 records in the table.
>>
>>>>>>>>>>>>>>> I know when I run "index .... --all" I get the same
>>>>> problems
>>>>>>>>>>>>>>> so
>>>>>>>>>>>>>>> that
>>>>>>>>>>>>>>> leads me to believe something's up with sphinx and not
>>>>>>>>>>>>>>> TS.
>>>>>>>>>>>>>>> However,
>>>>>>>>>>>>>>> any help on how I can debug these errors would be highly
>>>>>>>>>>>>>>> appreciated.
>>
>>>>>>>>>>>>>>> Thanks.
>>
>>>>>>>>>>>>>>> David
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
