Thanks Dave, will keep that in mind - what you've suggested makes sense.
--
Pat
On 06/10/2009, at 6:14 PM, Dave Pirotte wrote:
>
> Pat,
>
> I think it is better to cast the default when necessary, rather than
> cast the clause in that method. I had the same problem.. specifically
> with the subclass_crc32s in TS's generated select clause. The problem
> was solved by adding the following to postgresql_adapter.rb @ line 42:
>
> default = '0::bigint' if default == 0
>
> This might be more properly written as...
>
> default = "#{default}::bigint" if default.is_a?(Fixnum)
>
> That should take care of models whose crc32s are out of normal int
> range.
>
> Cheers,
> Dave
>
> On Oct 5, 7:26 pm, Pat Allan <[email protected]> wrote:
>> 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
>>
>> ...
>>
>> 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
-~----------~----~----~----~------~----~------~--~---
