Note:This topic was updated for 9.3.1.
ArcSDE XML columns need the database management system (DBMS) to include text search capabilities. Beginning with PostgreSQL 8.3.0, text search functionality is included with the PostgreSQL DBMS. Several different dictionaries are included with the DBMS installation. If the ones you need are not there, you can create dictionaries.
You should consult the Full Text Search chapter in the PostgreSQL documentation for information on text search in PostgreSQL and its configuration.
After everything is set up, you need to configure the geodatabase to store XML and maintain the text indexes.
These steps are described in the following sections.
Database configuration
The default text search configuration is set by the default_text_search_config parameter in the postgresql.conf file. Upon database cluster creation, this parameter is set to either pgcatalog.simple or to a configuration file with a setting that corresponds to the lc_ctype locale you specified when the cluster was initialized if a configuration matching that locale exists. This applies to all the databases in the cluster. If you want to use a different configuration file for a database, you can alter the database to do so.
You can use psql to get information about the text search configuration objects in the DBMS. If you type \dF at the psql prompt, it returns a list of the text search configurations included with PostgreSQL. If the configuration file you want is not included, you can build one using the text search parser and template database objects. Consult the PostgreSQL documentation for full information on their text search functionality and settings.
DBTUNE settings for ArcSDE XML columns
The parameters that deal with XML columns for PostgreSQL largely define in what tablespace a table or index is stored and the fill factor for indexes. Two of these, XML_INDEX_TAGS_TABLE and XML_INDEX_TAGS_INDEX, must be uncommented in the dbtune.sde file and values supplied for them prior to geodatabase creation if you want to customize the storage of the sde_xml_index_tags table and indexes and the indexes of the sde_xml_indexes table.
There are two XML parameters for PostgreSQL that do not specify table or index storage. They are XML_IDX_FULLTEXT_UPDATE_METHOD and XML_DOC_UNCOMPRESSED_TYPE.
XML_IDX_FULLTEXT_UPDATE_METHOD defines how the full-text index is updated as a result of changes made to the xml_doc_val column in the XML document table (sde_xml_doc<n>) and the text_tag column of the index table of an XML column (sde_xml_idx<n>). This parameter can be set to either AUTOMATIC or MANUAL. set to AUTOMATIC, a trigger created on the XML side tables updates indexes as rows are inserted. If the value for XML_IDX_FULLTEXT_UPDATE_METHOD is not set to AUTOMATIC, it is assumed to be MANUAL. MANUAL indicates you will update the indexes manually.
The XML_DOC_UNCOMPRESSED_TYPE configuration parameter determines how the contents of XML documents will be stored. You can use either BINARY (a compressed storage type) or TEXT (an uncompressed storage type). If you use BINARY, data is stored as bytea data type. If you use TEXT, the data will be either Unicode or ASCII, depending on whether your database is set to store Unicode data (UTF-8) or not. If you are using ArcIMS Metadata Services, it is usually best to specify the BINARY type. However, if you are interested in building an index on the XML column in addition to the one created for the Metadata Service, you may want to use the uncompressed TEXT type so the information can be read directly.
For definitions of all the XML parameters for PostgreSQL, see the XML parameter section of
PostgreSQL DBTUNE configuration parameters.
