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

["Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca>]

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