Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    Kentaro Hayashi
    @kenhys

    @Oniry
    The document about indexes with weight is not available yet, but if you have interested in this feature, the following example will help you to understand it.

    table_create Bigram TABLE_PAT_KEY ShortText \
      --default_tokenizer TokenBigramSplitSymbolAlpha \
      --normalizer NormalizerAuto
    
    table_create Diaries TABLE_HASH_KEY ShortText
    column_create Diaries title COLUMN_SCALAR ShortText
    column_create Diaries content COLUMN_SCALAR Text
    
    column_create Bigram titles COLUMN_INDEX|WITH_POSITION  Diaries title
    column_create Bigram contents COLUMN_INDEX|WITH_POSITION  Diaries content
    
    load --table Diaries
    [
      {
        "_key": "2013-02-06",
        "title": "groonga",
        "content": "I found it that is a fast fulltext search engine!"
      },
      {
        "_key": "2013-02-07",
        "title": "mroonga",
        "content": "I found mroonga that is a MySQL storage engine to use groonga!"
      },
    ]

    Then execute queries like this:

    select Diaries \
      --match_columns 'Bigram.titles * 10 || Bigram.contents' \
      --query 'groonga' \
    --output_columns 'title,_score' --sort_keys -_score --output_pretty yes

    ┌───────┬──────┐
    │title │_score│
    ├───────┼──────┤
    │groonga│10 │
    │mroonga│1 │
    └───────┴──────┘

    select Diaries \
      --match_columns 'Bigram.titles || Bigram.contents' \
      --query 'groonga' \
    --output_columns 'title,_score' --sort_keys -_score --output_pretty yes

    ┌───────┬──────┐
    │title │_score│
    ├───────┼──────┤
    │groonga│1 │
    │mroonga│1 │
    └───────┴──────┘

    If groonga is contained title and content column, it became to the same score without * 10 weight.

    @Oniry I hope it will help.
    anthonydafc
    @anthonydafc

    Hello, i'm trying to setup pgroonga in postgres 11, and it is the second time i get the same error when creating extension : ERROR: ERREUR: n'a pas pu charger la bibliothèque « C:/Program Files/PostgreSQL/11/lib/pgroonga.dll » : unknown error 127

    SQL state: XX000

    any idea
    anthonydafc
    @anthonydafc
    Another question concernng full text search. can we use &~ operator with scoring ? thanks for your replies
    Sutou Kouhei
    @kou
    How did you install PGroonga?
    &~ supports score.
    anthonydafc
    @anthonydafc
    i downloaded the version for postgres 11 x64, pasted folders to postgresql directory and did create extension pgroonga
    Sutou Kouhei
    @kou

    Umm. It's too rough instruction. I couldn't reproduce your case with the instruction.
    I try the following and it works well:

    1.Install PostgreSQL 11.3 by installer: https://www.enterprisedb.com/thank-you-downloading-postgresql?anid=1256619

    1. Download PGroonga binary for PostgreSQL 11: https://github.com/pgroonga/pgroonga/releases/download/2.1.9/pgroonga-2.1.9-postgresql-11.2-1-x64.zip
    2. Extract the PGroonga binary to C:\Program Files\PostgreSQL\11 (I needed to enable administrator permission)
    3. I run psql from startup menu
    4. Connect to postgres database with postgres user
    5. Create pgroonga_test database by CREATE DATABASE pgroonga_test;
    6. Change database by \c pgroonga_test
    7. Install PGroonga by CREATE EXTENSION pgroonga; (It doesn't report any error)
    8. Run the following SQL in tutorial https://pgroonga.github.io/tutorial/
    CREATE TABLE memos (
      id integer,
      content text
    );
    CREATE INDEX pgroonga_content_index ON memos USING pgroonga (content);
    INSERT INTO memos VALUES (1, 'PostgreSQL is a relational database management system.');
    INSERT INTO memos VALUES (2, 'Groonga is a fast full text search engine that supports all languages.');
    INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga as index.');
    INSERT INTO memos VALUES (4, 'There is groonga command.');
    SET enable_seqscan = off;
    SELECT * FROM memos WHERE content &@ 'engine';
    --  id |                                content                                 
    -- ----+------------------------------------------------------------------------
    --   2 | Groonga is a fast full text search engine that supports all languages.
    -- (1 row)

    So, it will be your environment specific issue.

    anthonydafc
    @anthonydafc
    @kou ok works better with latest version of poostgres 11 (i was using 11.1)
    anthonydafc
    @anthonydafc
    @kou SELECT , pgroonga_score(tableoid, ctid) AS score
    FROM score_memos
    where content &~ '.
    groonga'
    if a use this it will give me 0 scorer on every line found
    Sutou Kouhei
    @kou
    anthonydafc
    @anthonydafc
    same thing with seqscan disabled
    Sutou Kouhei
    @kou
    Please show the result of EXPLAIN ANALYZE VERBOSE SELECT ...;.
    anthonydafc
    @anthonydafc
    Seq Scan on public.score_memos (cost=10000000000.00..10000000657.70 rows=1 width=44) (actual time=0.087..0.107 rows=3 loops=1)
    Sutou Kouhei
    @kou
    Please show whole result. Don't omit anything.
    anthonydafc
    @anthonydafc
    Sorry
    "Seq Scan on public.score_memos (cost=10000000000.00..10000000657.70 rows=1 width=44) (actual time=0.087..0.107 rows=3 loops=1)"
    " Output: id, content, pgroonga_score(tableoid, ctid)"
    " Filter: (score_memos.content &~ '.*groonga'::text)"
    " Rows Removed by Filter: 1"
    "Planning Time: 0.162 ms"
    "Execution Time: 0.152 ms"
    Sutou Kouhei
    @kou
    Please show the output of the following:
    \d score_memos
    anthonydafc
    @anthonydafc
    Sorry but how does that work ?
    Sutou Kouhei
    @kou
    It's a psql command.
    anthonydafc
    @anthonydafc

    base_roonga_test-# \d score_memos
    Table ½ public.score_memos ╗
    Colonne | Type | Collationnement | NULL-able | Par dÚfaut
    ---------+---------+-----------------+-----------+------------
    id | integer | | not null |
    content | text | | |
    Index :
    "score_memos_pkey" PRIMARY KEY, btree (id)
    "pgroonga_score_memos_content_index" pgroonga (content)

    base_roonga_test-#

    Sutou Kouhei
    @kou
    \di pgroonga_score_memos_content_index
    anthonydafc
    @anthonydafc
                               Liste des relations
    SchÚma | Nom | Type | PropriÚtaire | Table
    --------+------------------------------------+-------+--------------+-------------
    public | pgroonga_score_memos_content_index | index | postgres | score_memos
    (1 ligne)
    Sutou Kouhei
    @kou
    select * from pg_catalog.pg_indexes where indexname = 'pgroonga_score_memos_content_index';
    anthonydafc
    @anthonydafc
    "public" "score_memos" "pgroonga_score_memos_content_index" "CREATE INDEX pgroonga_score_memos_content_index ON public.score_memos USING pgroonga (content)"
    Sutou Kouhei
    @kou

    I got it.
    You need to use pgroonga_text_regexp_ops_v2 operator class https://pgroonga.github.io/reference/#text-regexp-ops-v2 by searching with regular expression with index.

    CREATE INDEX pgroonga_score_memos_content_index ON public.score_memos USING pgroonga (content pgroonga_text_regexp_ops_v2);

    See also: https://pgroonga.github.io/reference/operators/regular-expression-v2.html#usage

    anthonydafc
    @anthonydafc
    @kou Works great. Many thanks
    anthonydafc
    @anthonydafc

    I have a question concerning the score. If i use an inner join i get a sql message saying : ERROR: ERREUR: la colonne « tableoid » n'existe pas
    LINE 1: select f.fid_recherchefichiers, pgroonga_score(tableoid, cti...
    ^
    HINT: Il existe une colonne nommée « tableoid » pour la table « p » mais elle ne peut pas être référencée dans cette partie de la requête.

    SQL state: 42703
    Character: 48

    anthonydafc
    @anthonydafc
    the tableoid doesn't exist
    Sutou Kouhei
    @kou
    Could you open an issue from here https://github.com/pgroonga/pgroonga/issues and write full SQLs (CREATE TABLE, CREATE INDEX, INSERT, SELECT, ...) to reproduce the case?
    anthonydafc
    @anthonydafc
    ok, have you ever had issues concerning lock tables with groonga ???
    Sutou Kouhei
    @kou
    If your PostgreSQL is crashed, lock may be remained.
    anthonydafc
    @anthonydafc
    what do u mean ?
    Sutou Kouhei
    @kou
    Did you talk about http://groonga.org/docs/reference/commands/lock_clear.html related?
    If you didn't, you don't need to understand my answer.
    (I hope that you explain what you want to know with concrete examples. Concrete examples are easy to understand.)
    anthonydafc
    @anthonydafc
    Sorry, but i have had a few problems, with the database been locket, while executing tasks. the lof files that a pgrn table has been locked and only way to work around is to delete pgrn files and reindex...is there any easier way or an explanation to these locks ?
    Sutou Kouhei
    @kou
    Could you open an issue from here https://github.com/pgroonga/pgroonga/issues and attach your ${PGDATA}/pgroonga.log to the issue? pgroonga.log will include the details for this case.
    anthonydafc
    @anthonydafc
    Hello, have you a problem with the download section on https://pgroonga.github.io/, because the link announce "not found"
    i'm looking for the windows versions
    Kentaro Hayashi
    @kenhys
    @anthonydafc you can download here. https://github.com/pgroonga/pgroonga/releases/tag/2.2.0
    Sutou Kouhei
    @kou
    I've fixed these links.
    weldomha
    @weldomha
    hi all, pgroonga 11 debian
    there is no package following this :
    doesn't follow debian directory tree for postgres
    any idea how to install pgroonga 11 on debian ??
    weldomha
    @weldomha
    without compelling the source