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

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(at)e-djuster(dot)com>
To: <pgadmin-support(at)postgresql(dot)org>
Cc: "Allen Vachon" <avachon(at)e-djuster(dot)com>; "Mark Rollins"
<mrollins(at)e-djuster(dot)com>; "Dave Sugden" <dsugden(at)e-djuster(dot)com>; "Fabio
Katz" <fkatz(at)e-djuster(dot)com>; "Benoit Rouleau" <brouleau(at)e-djuster(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>