Заставить PostgreSQL использовать другую схему в функциях

Я хотел сделать функцию обновления для схемы в базе данных PG SQL. Тестовая функция приведена ниже. Он не работает, потому что он никогда не должен вызывать уведомление, но будет делать это при запуске test_schema_update('second').

CREATE OR REPLACE FUNCTION test_schema_update(my_schema_name VARCHAR(200)) 
RETURNS void AS
$__marker__$
DECLARE
    actualValue varchar(1000);
    testValue varchar(1000);
BEGIN
    EXECUTE 'SET search_path TO ' || quote_ident(my_schema_name);

    testValue := (SELECT max(value) FROM setting WHERE settingkey = 'libraryname');
    EXECUTE ('SELECT max(value) FROM setting WHERE settingkey = ''libraryname''')
        INTO actualValue;

    IF (actualValue != testValue)
    THEN
        RAISE NOTICE '% != %', actualValue, testValue;
        RAISE INFO 'Schema was: %', current_schema();
    END IF;

    RESET search_path;
END;
$__marker__$ LANGUAGE plpgsql;

test_schema_update('first');
test_schema_update('second');
Проблема в том, что PG SQL, похоже, анализирует операторы SELECT только один раз за сеанс, а затем таблицы фиксируются к определенной схеме. Интересно то, что вы получите Schema was: second.

Так есть ли способ сброса SELECT анализа утверждений или какой-то другой способ обойти вот это?

Примечание: все функции создания схем(ALTER TABLE, CREATE TABLE...) работать отлично. Похоже, что затронуты только функции манипулирования данными(SELECT, INSERT, UPDATE).

Решение

До: 

IF (
    SELECT max(id) FROM dimtime
)
THEN
    INSERT INTO dimtime SELECT * FROM public.src_dimtime;
END IF;

После: 

EXECUTE ('
    SELECT max(id) FROM dimtime
')
INTO testInt;
IF (testInt IS NULL)
THEN
    EXECUTE 'INSERT INTO dimtime SELECT * FROM public.src_dimtime';
END IF;

Edit : проблема возникает в PostgreSQL 9.2, но, похоже, не возникает в 9.3. Может быть, его починили?

1 3
postgresql plpgsql dynamic-sql

1 ответ:

Такого поведения следует ожидать. Причина в том, что PL/pgSQL использует кэширование плана для операторов SQL, внутренне используя стандартные подготовленные операторы.

Согласно документации:

Поскольку каждое выражение и команда SQL сначала выполняются в функции, интерпретатор PL/pgSQL создает подготовленный план выполнения (используя Функции менеджера SPI SPI_prepare и SPI_saveplan). Последующий посещения этого выражения или команды повторно использовать подготовленный план.

Это также причина, почему plpgsql-функции часто быстрее , чем простые SQL-функции для сложных операций:

Подготовленные операторы сохраняются на время существования сеанса , а не только транзакции (но недействительны при изменении базовых объектов, это безопасно при параллельном доступе). документация один раз и снова:

После того, как PL/pgSQL составил план выполнения для конкретной команды в функция, она будет повторно использовать этот план для жизни базы данных соединение . Это обычно выигрыш для производительности, но это может вызвать некоторые проблемы возникают при динамическом изменении схемы базы данных.

Жирный акцент мой.

Если вы хотите "изменить" схему имени таблицы, вы действительно будете ссылаться на совершенно другую таблицу и нужда использовать динамический SQL с EXECUTE, который каждый раз генерирует новый план (со всеми преимуществами и недостатками):

Поскольку PL / pgSQL сохраняет планы выполнения таким образом, команды SQL, которые появляются непосредственно в функции PL/pgSQL должны ссылаться на те же таблицы и столбцы при каждом выполнении; то есть вы не можете использовать параметр как имя таблицы или столбца в команде SQL. Чтобы обойти это ограничение, вы можете создавать динамические команды с помощью PL/pgSQL EXECUTE ведомость-ценой построения нового плана исполнения на каждой казни.

Прочитайте соответствующую главу руководства. Она довольно всеобъемлюща.

Пример кода

Вам не нужен динамический SQL для вашего добавленного примера кода, и один оператор будет быстрее: 

INSERT INTO dimtime  -- you may want list columns
SELECT *             -- here as well
FROM   public.src_dimtime
WHERE  NOT EXISTS (SELECT 1 FROM dimtime);
4