Thread: Text Search Configuration Mapping displayed out of order, pgAdmin 1.10.0

Text Search Configuration Mapping displayed out of order, pgAdmin 1.10.0

From

"Jean-Pierre Pelletier"

Date:

11 November 2009, 17:43:20

Hi,

We've noticed that pgAdmin displays the mapping of text search 
configurations out of order
after using pg_dump & pg_restore with pgAdmin 1.10.0 against PostgreSQL 
8.3.8 on Windows Server 2008 64 bits.

Thanks,
Jean-Pierre Pelletier

Steps to reproduce:

CREATE SCHEMA my_text_search_configuration;
CREATE TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ( PARSER = "default"
);
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR asciihword 
WITH english_stem,simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR asciiword 
WITH english_stem,simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR email WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR file WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR float WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR host WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR hword WITH 
english_stem,simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR 
hword_asciipart WITH english_stem,simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR 
hword_numpart WITH simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR hword_part 
WITH english_stem,simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR int WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR numhword 
WITH simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR numword WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR sfloat WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR uint WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR url WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR url_path 
WITH simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR version WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR word WITH 
english_stem,simple;

pg_dump --schema my_text_search_configuration

-- move schema out of the way to restore in same database
ALTER SCHEMA my_text_search_configuration RENAME TO 
my_text_search_configuration_old;

pg_restore

psql properly displays the configuration

\dF+ my_text_search_configuration.mytsconfig_english
Text search configuration "my_text_search_configuration.mytsconfig_english"
Parser: "pg_catalog.default"     Token      |    Dictionaries
-----------------+---------------------asciihword      | english_stem,simpleasciiword       | english_stem,simpleemail
        | simplefile            | simplefloat           | simplehost            | simplehword           |
english_stem,simplehword_asciipart| english_stem,simplehword_numpart   | simplehword_part      | english_stem,simpleint
           | simplenumhword        | simplenumword         | simplesfloat          | simpleuint            | simpleurl
          | simpleurl_path        | simpleversion         | simpleword            | english_stem,simple
 

but after the restore, pgAdmin displays the following:

CREATE TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ( PARSER = "default"
);
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR asciihword 
WITH simple,english_stem;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR asciiword 
WITH english_stem,simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR email WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR file WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR float WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR host WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR hword WITH 
english_stem,simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR 
hword_asciipart WITH english_stem,simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR 
hword_numpart WITH simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR hword_part 
WITH simple,english_stem;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR int WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR numhword 
WITH simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR numword WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR sfloat WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR uint WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR url WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR url_path 
WITH simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR version WITH 
simple;
ALTER TEXT SEARCH CONFIGURATION 
my_text_search_configuration.mytsconfig_english ADD MAPPING FOR word WITH 
simple,english_stem;

Re: Text Search Configuration Mapping displayed out of order, pgAdmin 1.10.0

From

"Jean-Pierre Pelletier"

Date:

13 November 2009, 15:09:26

To reverse engineer the definition of a text search configuration, pgAdmin 
uses a query with an incomplete ORDER BY such as:

SELECT  (SELECT t.alias FROM pg_catalog.ts_token_type(cfgparser) AS t WHERE 
t.tokid = maptokentype) AS tokenalias,  dictname
FROM  pg_ts_config_map
  LEFT OUTER JOIN pg_ts_config  ON mapcfg=pg_ts_config.oid
  LEFT OUTER JOIN pg_ts_dict  ON mapdict=pg_ts_dict.oid
WHERE  mapcfg = 3743899::oid
ORDER BY  1;

The ORDER BY is missing column "mapseqno" from catalog "pg_ts_config_map".

As explained before, in our case, the problem was only exposed after 
reloading the text search configuration using pg_restore.

----- Original Message ----- 
From: "Jean-Pierre Pelletier" <jppelletier@e-djuster.com>
To: <pgadmin-support@postgresql.org>
Cc: "Allen Vachon" <avachon@e-djuster.com>; "Mark Rollins" 
<mrollins@e-djuster.com>; "Dave Sugden" <dsugden@e-djuster.com>; "Fabio 
Katz" <fkatz@e-djuster.com>; "Benoit Rouleau" <brouleau@e-djuster.com>
Sent: Wednesday, November 11, 2009 1:06 PM
Subject: [pgadmin-support] Text Search Configuration Mapping displayed out 
of order, pgAdmin 1.10.0


> Hi,
>
> We've noticed that pgAdmin displays the mapping of text search 
> configurations out of order
> after using pg_dump & pg_restore with pgAdmin 1.10.0 against PostgreSQL 
> 8.3.8 on Windows Server 2008 64 bits.
>
> Thanks,
> Jean-Pierre Pelletier
>
> Steps to reproduce:
>
> CREATE SCHEMA my_text_search_configuration;
> CREATE TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english (
>  PARSER = "default"
> );
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR asciihword 
> WITH english_stem,simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR asciiword 
> WITH english_stem,simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR email WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR file WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR float WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR host WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR hword WITH 
> english_stem,simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR 
> hword_asciipart WITH english_stem,simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR 
> hword_numpart WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR hword_part 
> WITH english_stem,simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR int WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR numhword 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR numword 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR sfloat 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR uint WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR url WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR url_path 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR version 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR word WITH 
> english_stem,simple;
>
> pg_dump --schema my_text_search_configuration
>
> -- move schema out of the way to restore in same database
> ALTER SCHEMA my_text_search_configuration RENAME TO 
> my_text_search_configuration_old;
>
> pg_restore
>
> psql properly displays the configuration
>
> \dF+ my_text_search_configuration.mytsconfig_english
> Text search configuration 
> "my_text_search_configuration.mytsconfig_english"
> Parser: "pg_catalog.default"
>      Token      |    Dictionaries
> -----------------+---------------------
> asciihword      | english_stem,simple
> asciiword       | english_stem,simple
> email           | simple
> file            | simple
> float           | simple
> host            | simple
> hword           | english_stem,simple
> hword_asciipart | english_stem,simple
> hword_numpart   | simple
> hword_part      | english_stem,simple
> int             | simple
> numhword        | simple
> numword         | simple
> sfloat          | simple
> uint            | simple
> url             | simple
> url_path        | simple
> version         | simple
> word            | english_stem,simple
>
> but after the restore, pgAdmin displays the following:
>
> CREATE TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english (
>  PARSER = "default"
> );
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR asciihword 
> WITH simple,english_stem;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR asciiword 
> WITH english_stem,simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR email WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR file WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR float WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR host WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR hword WITH 
> english_stem,simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR 
> hword_asciipart WITH english_stem,simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR 
> hword_numpart WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR hword_part 
> WITH simple,english_stem;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR int WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR numhword 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR numword 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR sfloat 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR uint WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR url WITH 
> simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR url_path 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR version 
> WITH simple;
> ALTER TEXT SEARCH CONFIGURATION 
> my_text_search_configuration.mytsconfig_english ADD MAPPING FOR word WITH 
> simple,english_stem;
>
>
> -- 
> Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>

Re: Text Search Configuration Mapping displayed out of order, pgAdmin 1.10.0

From

Guillaume Lelarge

Date:

16 November 2009, 17:15:09

Le vendredi 13 novembre 2009 à 17:09:33, Jean-Pierre Pelletier a écrit :
> To reverse engineer the definition of a text search configuration, pgAdmin
> uses a query with an incomplete ORDER BY such as:
>
> SELECT
>    (SELECT t.alias FROM pg_catalog.ts_token_type(cfgparser) AS t WHERE
> t.tokid = maptokentype) AS tokenalias,
>    dictname
> FROM
>    pg_ts_config_map
>
>    LEFT OUTER JOIN pg_ts_config
>    ON mapcfg=pg_ts_config.oid
>
>    LEFT OUTER JOIN pg_ts_dict
>    ON mapdict=pg_ts_dict.oid
> WHERE
>    mapcfg = 3743899::oid
> ORDER BY
>    1;
>
> The ORDER BY is missing column "mapseqno" from catalog "pg_ts_config_map".
>
> As explained before, in our case, the problem was only exposed after
> reloading the text search configuration using pg_restore.
>

Sorry that I didn't find the time to work on this till today. You're right on
the bug and the fix. I commited your fix, thanks a lot.


--
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com