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" <jppellet...@e-djuster.com>
To: <pgadmin-support@postgresql.org>
Cc: "Allen Vachon" <avac...@e-djuster.com>; "Mark Rollins"
<mroll...@e-djuster.com>; "Dave Sugden" <dsug...@e-djuster.com>; "Fabio
Katz" <fk...@e-djuster.com>; "Benoit Rouleau" <broul...@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
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
