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
-~----------~----~----~----~------~----~------~--~---
