PostgreSQL : Документация: 17: 42.5. Обращение к базе данных из PL/Tcl : Компания Postgres Professional

RU
EN
RU EN

42.5. Обращение к базе данных из PL/Tcl
Пред.	Наверх	Глава 42. PL/Tcl — процедурный язык Tcl	Начало	След.

42.5. Обращение к базе данных из PL/Tcl #

В этом разделе для обозначения необязательных элементов в описании синтаксиса используются знаки вопроса (а не квадратные скобки), как это принято в Tcl. Имеются следующие команды для доступа к базе данных из тела функции PL/Tcl:

spi_exec ?-count n? ?-array имя? команда ?тело-цикла?

Выполняет команду SQL, заданную в виде строки. В случае ошибки в этой команде выдаётся ошибка в Tcl. В противном случае spi_exec возвращает число обработанных командой строк (выбранных, добавленных, изменённых или удалённых), либо ноль, если эта команда — служебный оператор. Кроме того, если команда — оператор SELECT, значения выбранных столбцов помещаются в переменные Tcl, как описано ниже.

Необязательный аргумент -count указывает функции spi_exec остановиться после получения n строк, как если бы запрос включал предложение LIMIT. Если n равно нулю, запрос выполняется до конца, так же, как без указания -count.

Если в качестве команды выполняется оператор SELECT, значения результирующих столбцов помещаются в переменные Tcl, названные по именам столбцов. Если передаётся -array, значения столбцов вместо этого становятся элементами названного ассоциативного массива, индексами в котором становятся имена столбцов. Кроме того, в элементе с именем «.tupno» сохраняется номер текущей строки в результирующем наборе (отсчитывая от нуля), если только это имя не занято одним из столбцов результата.

Если в качестве команды выполняется SELECT без указания скрипта тело-цикла, в переменных Tcl или элементах массива сохраняется только первая строка результатов; оставшиеся строки (если они есть), игнорируются. Если запрос не возвращает строки, не сохраняется ничего. (Этот случай можно отследить, проверив результат spi_exec.) Например, команда:

spi_exec "SELECT count(*) AS cnt FROM pg_proc"

присвоит переменной $cnt в Tcl число строк, содержащихся в системном каталоге pg_proc.

Если передаётся необязательный аргумент тело-цикла, заданный в нём блок скрипта Tcl будет выполняться для каждой строки результата запроса. (Аргумент тело-цикла игнорируется, если целевая команда — не SELECT.) При этом значения столбцов текущей строки сохраняются в переменных Tcl или элементах массива перед каждой итерацией этого цикла. Например, код:

spi_exec -array C "SELECT * FROM pg_class" {
    elog DEBUG "have table $C(relname)"
}

будет выводить в журнал сообщение для каждой строки pg_class. Это работает подобно другим конструкциям циклов в Tcl; в частности, команды continue и break в теле цикла будут действовать обычным образом.

Если в столбце результата запроса выдаётся NULL, целевая переменная для неё не устанавливается, и оказывается «неустановленной».

spi_prepare query список-типов

Подготавливает и сохраняет план запроса для последующего выполнения. Сохранённый план будет продолжать существование до завершения текущего сеанса.

Запрос может принимать параметры, то есть местозаполнители для значений, которые будут передаваться, когда план будет собственно выполняться. В строке запроса эти параметры обозначаются как $1 ... $n. Если в запросе используются параметры, нужно задать имена типов этих параметров в виде списка Tcl. (Если параметры отсутствуют, задайте пустой список_типов.)

Функция spi_prepare возвращает идентификатор запроса, который может использоваться в последующих вызовах spi_execp. Пример приведён в описании spi_execp.

spi_execp ?-count n? ?-array имя? ?-nulls строка? ид-запроса ?список-значений? ?тело-цикла?

Выполняет запрос, ранее подготовленный функцией spi_prepare. В качестве ид_запроса передаётся идентификатор, возвращённый функцией spi_prepare. Если в запросе задействуются параметры, необходимо указать список-значений. Это должен быть принятый в Tcl список параметров. Он должен иметь ту же длину, что и список типов параметров, ранее переданный spi_prepare. Опустите список-значений, если у запроса нет параметров.

Необязательный аргумент -nulls принимает строку из пробелов и символов 'n', которые отмечают, в каких параметрах spi_execp передаются значения NULL. Если присутствует, эта строка должна иметь ту же длину, что и список-значений. В случае её отсутствия значения всех параметров считаются отличными от NULL.

Не считая отличий в способе передачи запроса и параметров, spi_execp работает так же, как spi_exec. Параметры -count, -array и тело-цикла задаются так же, и так же передаётся возвращаемое значение.

Взгляните на пример функции на PL/Tcl, использующей подготовленный план:

CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$
    if {![ info exists GD(plan) ]} {
        # подготовить сохранённый план при первом вызове
        set GD(plan) [ spi_prepare \
                "SELECT count(*) AS cnt FROM t1 WHERE num >= \$1 AND num <= \$2" \
                [ list int4 int4 ] ]
    }
    spi_execp -count 1 $GD(plan) [ list $1 $2 ]
    return $cnt
$$ LANGUAGE pltcl;

Обратные косые черты внутри строки запроса, передаваемой функции spi_prepare, нужны для того, чтобы маркеры $n передавались функции spi_prepare как есть, а не заменялись при подстановке переменных Tcl.

subtransaction команда

Скрипт Tcl, который содержит команда, выполняется в подтранзакции SQL. Если этот скрипт возвращает ошибку, вся подтранзакция откатывается назад, а затем в окружающий код Tcl возвращается ошибка. За дополнительными подробностями и примером обратитесь к Разделу 42.9.

quote строка

Дублирует все вхождения апострофа и обратной косой черты в заданной строке. Это можно использовать для защиты строк, которые будут вставляться в команды SQL, передаваемые в spi_exec или spi_prepare. Например, представьте, что при выполнении такой команды SQL:

"SELECT '$val' AS ret"

переменная языка Tcl val содержит doesn't. Это приведёт к формированию такой окончательной строки команды:

SELECT 'doesn't' AS ret

при разборе которой в процессе spi_exec или spi_prepare возникнет ошибка. Чтобы этот запрос работал правильно, итоговая команда должна выглядеть так:

SELECT 'doesn''t' AS ret

Получить её в PL/Tcl можно так:

"SELECT '[ quote $val ]' AS ret"

Преимуществом spi_execp является то, что для неё заключать значения параметров в кавычки подобным образом не нужно, так как параметры никогда не разбираются в составе строки команды SQL.

elog уровень сообщение

Выдаёт служебное сообщение или сообщение об ошибке. Возможные уровни сообщений: DEBUG (ОТЛАДКА), LOG (СООБЩЕНИЕ), INFO (ИНФОРМАЦИЯ), NOTICE (ЗАМЕЧАНИЕ), WARNING (ПРЕДУПРЕЖДЕНИЕ), ERROR (ОШИБКА) и FATAL (ВАЖНО). С уровнем ERROR выдаётся ошибка; если она не перехватывается окружающим кодом Tcl, она распространяется в вызывающий запрос, что приводит к прерыванию текущей транзакции или подтранзакции. По сути то же самое делает команда error языка Tcl. Сообщение уровня FATAL прерывает транзакцию и приводит к завершению текущего сеанса. (Вероятно, нет обоснованной причины использовать этот уровень ошибок в функциях PL/Tcl, но он поддерживается для полноты.) При использовании других уровней происходит просто вывод сообщения с заданным уровнем важности. Будут ли сообщения определённого уровня передаваться клиенту и/или записываться в журнал, определяется конфигурационными переменными log_min_messages и client_min_messages. За дополнительными сведениями обратитесь к Главе 19 и Разделу 42.8.

Пред.	Наверх	След.
42.4. Глобальные данные в PL/Tcl	Начало	42.6. Триггерные функции на PL/Tcl

39.7. Rules Versus Triggers
Prev	Up	Chapter 39. The Rule System	Home	Next

39.7. Rules Versus Triggers

Many things that can be done using triggers can also be implemented using the PostgreSQL rule system. One of the things that cannot be implemented by rules are some kinds of constraints, especially foreign keys. It is possible to place a qualified rule that rewrites a command to NOTHING if the value of a column does not appear in another table. But then the data is silently thrown away and that's not a good idea. If checks for valid values are required, and in the case of an invalid value an error message should be generated, it must be done by a trigger.

In this chapter, we focused on using rules to update views. All of the update rule examples in this chapter can also be implemented using INSTEAD OF triggers on the views. Writing such triggers is often easier than writing rules, particularly if complex logic is required to perform the update.

For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is fired once for each affected row. A rule modifies the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must re-determine what to do many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right.

Here we show an example of how the choice of rules versus triggers plays out in one situation. There are two tables:

CREATE TABLE computer (
    hostname        text,    -- indexed
    manufacturer    text     -- indexed
);

CREATE TABLE software (
    software        text,    -- indexed
    hostname        text     -- indexed
);

Both tables have many thousands of rows and the indexes on hostname are unique. The rule or trigger should implement a constraint that deletes rows from software that reference a deleted computer. The trigger would use this command:

DELETE FROM software WHERE hostname = $1;

Since the trigger is called for each individual row deleted from computer, it can prepare and save the plan for this command and pass the hostname value in the parameter. The rule would be written as:

CREATE RULE computer_del AS ON DELETE TO computer
    DO DELETE FROM software WHERE hostname = OLD.hostname;

Now we look at different types of deletes. In the case of a:

DELETE FROM computer WHERE hostname = 'mypc.local.net';

the table computer is scanned by index (fast), and the command issued by the trigger would also use an index scan (also fast). The extra command from the rule would be:

DELETE FROM software WHERE computer.hostname = 'mypc.local.net'
                       AND software.hostname = computer.hostname;

Since there are appropriate indexes set up, the planner will create a plan of

Nestloop
  ->  Index Scan using comp_hostidx on computer
  ->  Index Scan using soft_hostidx on software

So there would be not that much difference in speed between the trigger and the rule implementation.

With the next delete we want to get rid of all the 2000 computers where the hostname starts with old. There are two possible commands to do that. One is:

DELETE FROM computer WHERE hostname >= 'old'
                       AND hostname <  'ole'

The command added by the rule will be:

DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole'
                       AND software.hostname = computer.hostname;

with the plan

Hash Join
  ->  Seq Scan on software
  ->  Hash
    ->  Index Scan using comp_hostidx on computer

The other possible command is:

DELETE FROM computer WHERE hostname ~ '^old';

which results in the following executing plan for the command added by the rule:

Nestloop
  ->  Index Scan using comp_hostidx on computer
  ->  Index Scan using soft_hostidx on software

This shows, that the planner does not realize that the qualification for hostname in computer could also be used for an index scan on software when there are multiple qualification expressions combined with AND, which is what it does in the regular-expression version of the command. The trigger will get invoked once for each of the 2000 old computers that have to be deleted, and that will result in one index scan over computer and 2000 index scans over software. The rule implementation will do it with two commands that use indexes. And it depends on the overall size of the table software whether the rule will still be faster in the sequential scan situation. 2000 command executions from the trigger over the SPI manager take some time, even if all the index blocks will soon be in the cache.

The last command we look at is:

DELETE FROM computer WHERE manufacturer = 'bim';

Again this could result in many rows to be deleted from computer. So the trigger will again run many commands through the executor. The command generated by the rule will be:

DELETE FROM software WHERE computer.manufacturer = 'bim'
                       AND software.hostname = computer.hostname;

The plan for that command will again be the nested loop over two index scans, only using a different index on computer:

Nestloop
  ->  Index Scan using comp_manufidx on computer
  ->  Index Scan using soft_hostidx on software

In any of these cases, the extra commands from the rule system will be more or less independent from the number of affected rows in a command.

The summary is, rules will only be significantly slower than triggers if their actions result in large and badly qualified joins, a situation where the planner fails.

Prev	Up	Next
39.6. Rules and Command Status	Home	Chapter 40. Procedural Languages

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

42.5. Обращение к базе данных из PL/Tcl #

39.7. Rules Versus Triggers