Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

View. Правила вставки

Posted Июнь 16th, 2009 by blow
in

В продолжение вопроса, поднятого тут (Наследование и уникальные индексы). Если вкратце - от предложенного в postgres наследования пришлось отказаться и делать все через связи таблиц и view.
Мне очень захотелось попробовать скрыть на БД за view всю эту структурную кашу с наследованием. Разобрался с обновлением родительских таблиц, и с удалением. А вот на вставке данных возникла проблема.
Код правила на вставку такой:

CREATE OR REPLACE RULE test_child_insert AS ON INSERT TO test_child DO INSTEAD (
INSERT INTO test_parent VALUES(NEW.id,NEW.parent_attr);
INSERT INTO test_child_tab VALUES(NEW.id,NEW.child_attr);
);
(полный код можно посмотреть по ссылке выше, одно дополнение - PK для таблиц я сделал bigserial).
Для того, чтобы подставилось значение автоинкремента для родительской таблицы, нужно убрать NEW.id из первого инсерта.
Но вот каким образом значение этого же автоинкремента подставить во второй инсерт - для меня загадка. Знаю, что в postgres есть такая вещь как  returning , но вот как ее применить здесь понять пока не смог.

P.S. я с postgres только знакомлюсь, прошу сильно не пинать. И прошу прощения за слегка сумбурное изложение проблемы, не высыпаюсь в последнее время (((

‹ вопрос по postgre 8.4 и returns table statement Как работает ALTER в Postgresql? ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

мне кажется, такие вещи лучше

On Июнь 16th, 2009 msr says:

мне кажется, такие вещи лучше делать с помощью триггеров.

Тем не менее вашу задачку можно решить например так

INSERT INTO test_parent VALUES(NEXTVAL('test_seq'),NEW.parent_attr);
INSERT INTO test_child_tab VALUES(currval('test_seq'),NEW.child_attr);

если иногда нужно будет сохранять переданное значение поля id, то вариант другой:
для начала нужно установить в конфигурационном файле параметр custom_variable_classes='vars'
после чего правило можно записать так:

SELECT set_config('vars.next_test_id_val',COALESCE(new.id,NEXTVAL('test_seq')),false);
INSERT INTO test_parent VALUES((SELECT current_setting('vars.next_test_id_val'))::bigint,NEW.parent_attr);
INSERT INTO test_child_tab VALUES((SELECT current_setting('vars.next_test_id_val'))::bigint,NEW.child_attr);

в качестве альтернативы можно создать табличку для хранения таких промежуточных значений, но, повторюсь, в триггере это все естественнее и проще

Спасибо, первый вариант -

On Июнь 17th, 2009 blow says:

Спасибо, первый вариант - самое то.
По поводу триггеров. Честно, не представляю, как это можно реализовать на них.
Может я не тот путь выбираю, но насколько я понимаю этот процесс, на триггерах делать прийдется так:
1.Если при вставке в родительскую таблицу не указано значение автоинкрементного поля, выбрать его из последовательности.
2.Вернуть выбранное значение в правило.
3. Вставить строку с полученным значением в наследуемую таблицу.

Так вот сам процесс возврата (п.2) мне и непонятен (я не знаю, как это сделать).
Если это возможно, то можно обойтись и без дополнительных тригеров, т.к. триггер, описанный в п.1 уже создан в postgres (при создании автоинкрементного поля).

Я забыл, что у вас там

On Июнь 17th, 2009 msr says:

Я забыл, что у вас там несколько дочерних таблиц.

триггер, описанный в п.1 уже создан в postgres (при создании автоинкрементного поля).

это не триггер, просто дефолтное значение поля

***

On Июнь 18th, 2009 blow says:


это не триггер, просто дефолтное значение поля

Извиняюсь, перепутал с Oracle.

А все таки, насколько надежна вот такая конструкция? :

CREATE OR REPLACE VIEW test_parent AS SELECT * FROM test_parent_tab;
CREATE OR REPLACE RULE test_parent_insert AS ON INSERT TO test_parent DO INSTEAD INSERT INTO test_parent_tab(id,parent_attr) VALUES(NEXTVAL(test_parent_tab_id_seq),NEW.parent_attr);
 
CREATE OR REPLACE VIEW test_child AS SELECT v1.*,t1.child_attr FROM test_child_tab t1, test_parent v1 WHERE t1.id=v1.id;
CREATE OR REPLACE RULE test_child_insert AS ON INSERT TO test_child DO INSTEAD (
INSERT INTO test_parent (parent_attr) VALUES(NEW.parent_attr);
INSERT INTO test_child_tab (id,child_attr) VALUES(CURRVAL(test_parent_tab_id_seq),NEW.child_attr);
);

Интересует в первую очередь, а не возникнут ли проблемы при конкурентном доступе? Сейчас делаю тесты на jmeter, думаю, к понедельнику буду знать ответ. Но может кто-то сталкивался?

вы по-моему переборщили с

On Июнь 19th, 2009 msr says:

вы по-моему переборщили с использованием правил, даже стандартный механизм переписали :)
Я имею в виду дефолтное значение поля id на главной таблице

Чтобы реализовать автоинкремент не нужно писать вьюшку и правило к ней, просто поставить по дефолту NEXTVAL(test_parent_tab_id_seq).

Проблема во втором правиле может возникнуть из-за (странной) особенности функции currval, которая возвращает последнее значение сиквенса, установленное функцией nextval в данной сессии, если такого не происходило, то возникает ошибка.
Поэтому либо напишите второе правило, как предлагал я (с nextval в первом запросе), либо замените currval('test_parent_tab_id_seq') на (select last_value from test_parent_tab_id_seq)
Но в последнем случае как раз есть подозрение, что возникнут проблемы с конкурентным доступом.

Спасибо за подсказку, что

On Июнь 21st, 2009 blow says:

Спасибо за подсказку, что можно убрать вставку id из главной таблицы, тут действительно не подумал :-). Только вьюшка там не совсем для автоинкремента. Она там для того, что если мне влруг стукнет в голову наследовать главную таблицу от другой, мне не прийдеться бегать по всему коду БД и искать ее использование; типа таая страховка от ошибок :-). Плюс из кода приложения я буду обращаться только ко вьюшкам, с этой же целью.


Поэтому либо напишите второе правило, как предлагал я (с nextval в первом запросе), либо замените currval('test_parent_tab_id_seq') на (select last_value from test_parent_tab_id_seq)

Вы здесь наверно не очень внимательно просмотрели пример, либо просто не вникали :-). На самом деле в первом запросе и вызывается nextval, т.к. по первому запросу выполняется правило по вставке в главную таблицу, которое и вызовет nextval.

И то, что currval имеет такую "странную" особенность, как раз и гарантирует мне отсутствие проблем при конкурентном доступе. Только что Вы здесь имели под словом "сессия"? Транзакция? или "от коннекта до дисконнекта"? Мое приложение будет на php, т.е. "сессия" - это от pg_connect до pg_close (так вроде)?

Вы здесь наверно не очень

On Июнь 21st, 2009 msr says:

Вы здесь наверно не очень внимательно просмотрели пример, либо просто не вникали . На самом деле в первом запросе и вызывается nextval, т.к. по первому запросу выполняется правило по вставке в главную таблицу, которое и вызовет nextval.

Я исходил из своего предположения, что первое правило и вюшка не нужны, если они все-таки нужны, то все правильно.

"от коннекта до дисконнекта"? Мое приложение будет на php, т.е. "сессия" - это от pg_connect до pg_close (так вроде)?

Да

Спасибо огромное Вам за

On Июнь 22nd, 2009 blow says:

Спасибо огромное Вам за помощь.

На выходных я провел тестирование конкурентного доступа данной схемы. Проблем обнаружено не было :-)

Судя по всему значения sequence не пересекаются при параллельных подключениях (отдельное спасибо комманде разработчиков).

Буквально только что проверил данное предположение (что-то раньше не доходило, как это можно сделать :-)):
Открыл две консоли постгреса и выполнил такие действия:

  1. select nextval('test_parent_tab_id_seq') в #1
  2. select nextval('test_parent_tab_id_seq') в #2
  3. select currval('test_parent_tab_id_seq') в #1
  4. select currval('test_parent_tab_id_seq') в #2

результаты:

  1. 1
  2. 2
  3. 1
  4. 2

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

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023