12.10. Поддержка psql
Информацию об объектах конфигурации текстового поиска можно получить в psql с помощью следующего набора команд:
\dF{d,p,t}[+] [ШАБЛОН]
Необязательный + в этих командах включает более подробный вывод.
В необязательном параметре ШАБЛОН может указываться имя объекта текстового поиска (возможно, дополненное схемой). Если ШАБЛОН не указан, выводится информация обо всех видимых объектах. ШАБЛОН может содержать регулярное выражение с разными масками для схемы и объекта. Это иллюстрируют следующие примеры:
=> \dF *fulltext*
List of text search configurations
Schema | Name | Description
--------+--------------+-------------
public | fulltext_cfg |
=> \dF *.fulltext*
List of text search configurations
Schema | Name | Description
----------+----------------------------
fulltext | fulltext_cfg |
public | fulltext_cfg |
Возможны следующие команды:
\dF[+] [ШАБЛОН]Список конфигураций текстового поиска (добавьте
+для дополнительных сведений).=> \dF russian List of text search configurations Schema | Name | Description ------------+---------+------------------------------------ pg_catalog | russian | configuration for russian language => \dF+ russian Text search configuration "pg_catalog.russian" Parser: "pg_catalog.default" Token | Dictionaries -----------------+-------------- asciihword | english_stem asciiword | english_stem email | simple file | simple float | simple host | simple hword | russian_stem hword_asciipart | english_stem hword_numpart | simple hword_part | russian_stem int | simple numhword | simple numword | simple sfloat | simple uint | simple url | simple url_path | simple version | simple word | russian_stem\dFd[+] [ШАБЛОН]Список словарей текстового поиска (добавьте
+для дополнительных сведений).=> \dFd List of text search dictionaries Schema | Name | Description -----------+----------------+------------------------------------------- pg_catalog | danish_stem | snowball stemmer for danish language pg_catalog | dutch_stem | snowball stemmer for dutch language pg_catalog | english_stem | snowball stemmer for english language pg_catalog | finnish_stem | snowball stemmer for finnish language pg_catalog | french_stem | snowball stemmer for french language pg_catalog | german_stem | snowball stemmer for german language pg_catalog | hungarian_stem | snowball stemmer for hungarian language pg_catalog | italian_stem | snowball stemmer for italian language pg_catalog | norwegian_stem | snowball stemmer for norwegian language pg_catalog | portuguese_stem| snowball stemmer for portuguese language pg_catalog | romanian_stem | snowball stemmer for romanian language pg_catalog | russian_stem | snowball stemmer for russian language pg_catalog | simple | simple dictionary: just lower case and ... pg_catalog | spanish_stem | snowball stemmer for spanish language pg_catalog | swedish_stem | snowball stemmer for swedish language pg_catalog | turkish_stem | snowball stemmer for turkish language\dFp[+] [ШАБЛОН]Список анализаторов текстового поиска (добавьте
+для дополнительных сведений).=> \dFp List of text search parsers Schema | Name | Description ------------+---------+--------------------- pg_catalog | default | default word parser => \dFp+ Text search parser "pg_catalog.default" Method | Function | Description -----------------+----------------+------------- Start parse | prsd_start | Get next token | prsd_nexttoken | End parse | prsd_end | Get headline | prsd_headline | Get token types | prsd_lextype | Token types for parser "pg_catalog.default" Token name | Description -----------------+------------------------------------------ asciihword | Hyphenated word, all ASCII asciiword | Word, all ASCII blank | Space symbols email | Email address entity | XML entity file | File or path name float | Decimal notation host | Host hword | Hyphenated word, all letters hword_asciipart | Hyphenated word part, all ASCII hword_numpart | Hyphenated word part, letters and digits hword_part | Hyphenated word part, all letters int | Signed integer numhword | Hyphenated word, letters and digits numword | Word, letters and digits protocol | Protocol head sfloat | Scientific notation tag | XML tag uint | Unsigned integer url | URL url_path | URL path version | Version number word | Word, all letters (23 rows)\dFt[+] [ШАБЛОН]Список шаблонов текстового поиска (добавьте
+для дополнительных сведений).=> \dFt List of text search templates Schema | Name | Description ----------+---------+---------------------------------------------------- pg_catalog|ispell |ispell dictionary pg_catalog|simple |simple dictionary: just lower case and check for ... pg_catalog|snowball |snowball stemmer pg_catalog|synonym |synonym dictionary: replace word by its synonym pg_catalog|thesaurus|thesaurus dictionary: phrase by phrase substitution
49.6. Executor
The executor takes the plan created by the planner/optimizer and recursively processes it to extract the required set of rows. This is essentially a demand-pull pipeline mechanism. Each time a plan node is called, it must deliver one more row, or report that it is done delivering rows.
To provide a concrete example, assume that the top node is a MergeJoin node. Before any merge can be done two rows have to be fetched (one from each subplan). So the executor recursively calls itself to process the subplans (it starts with the subplan attached to lefttree). The new top node (the top node of the left subplan) is, let's say, a Sort node and again recursion is needed to obtain an input row. The child node of the Sort might be a SeqScan node, representing actual reading of a table. Execution of this node causes the executor to fetch a row from the table and return it up to the calling node. The Sort node will repeatedly call its child to obtain all the rows to be sorted. When the input is exhausted (as indicated by the child node returning a NULL instead of a row), the Sort code performs the sort, and finally is able to return its first output row, namely the first one in sorted order. It keeps the remaining rows stored so that it can deliver them in sorted order in response to later demands.
The MergeJoin node similarly demands the first row from its right subplan. Then it compares the two rows to see if they can be joined; if so, it returns a join row to its caller. On the next call, or immediately if it cannot join the current pair of inputs, it advances to the next row of one table or the other (depending on how the comparison came out), and again checks for a match. Eventually, one subplan or the other is exhausted, and the MergeJoin node returns NULL to indicate that no more join rows can be formed.
Complex queries can involve many levels of plan nodes, but the general approach is the same: each node computes and returns its next output row each time it is called. Each node is also responsible for applying any selection or projection expressions that were assigned to it by the planner.
The executor mechanism is used to evaluate all four basic SQL query types: SELECT, INSERT, UPDATE, and DELETE. For SELECT, the top-level executor code only needs to send each row returned by the query plan tree off to the client. INSERT ... SELECT, UPDATE, and DELETE are effectively SELECTs under a special top-level plan node called ModifyTable.
INSERT ... SELECT feeds the rows up to ModifyTable for insertion. For UPDATE, the planner arranges that each computed row includes all the updated column values, plus the TID (tuple ID, or row ID) of the original target row; this data is fed up to the ModifyTable node, which uses the information to create a new updated row and mark the old row deleted. For DELETE, the only column that is actually returned by the plan is the TID, and the ModifyTable node simply uses the TID to visit each target row and mark it deleted.
A simple INSERT ... VALUES command creates a trivial plan tree consisting of a single Result node, which computes just one result row, feeding that up to ModifyTable to perform the insertion.