create rule + sequence

[andrew.k]

Сразу приведу пример.
create table test ( id bigserial, value smallint);
create table test_history ( time timestamp default CURRENT_TIMESTAMP, test_id bigint, action char(6));
create rule testinsert as on insert to test do insert into test_history values( default,new.id,"INSERT");

После чего добавляю строки в таблицу test;
Вопросы, которые у меня возникли, которые я не могу объяснить:
1. Почему после создания правила testinsert, id в таблице test начинаю идти через один, т.е. 5,7,9,11.
если удалить правило, то как положено по порядку. Если создать, снова через один.
2. в таблицу testinsert попадает неправильное значение id, а на единицу больше.
Почему так? Как побороть?

[andrew.k]

в принципе более-менее понял, почему так происходит.
Но как получить аналогичную функциональность.
Мне нужно отслеживать изменения в таблице.
Как?

[Disaron]

Если речь про постгрю, то все понятно:
когда ты вызываешь new, последовательность увеличивается на 1, поэтому получаешь прыжок через один. Как вариант вместо new.id использовать currval('sq_test'::regclass). Где sq_test - соответствующая последовательность (она должен создаваться при объявлении serial типов). Если такого нет - завести вручную и вместо bigserial использовать integer и в default прописать nextval('sq_test'::regclass).

Это первое что пришло в голову.

Код

SQL
CREATE TABLE test
(
  id_test integer DEFAULT NEXTVAL('sq_test'::regclass),
  test character varying(50)
)
WITH (
  OIDS=FALSE
);
 
CREATE OR REPLACE RULE testinsert AS
    ON INSERT TO test DO  INSERT INTO test_history (id_test, test) 
  VALUES (currval('sq_test'::regclass), 'INSERT'::character varying);

[asvil]

bigserial автоматически разворачивается в секваенс и bigint. Вообще историю раньше в триггерах делали. Но если Вам для больших объемов, то как Disaron посоветовал, самое оно.

[andrew.k]

Объемы не особо большие, просто через триггеры я не умею, сейчас попробую, если не получится, то оставлю через рулы.
Нашел небольшой примерчик, поковыряю.
Спс, про currval не догадался.

Кстати, подобное решение не чревато какими-нибудь глюками?
Например, при одновременном insert в двух разных процессах?
Почти везде, где описывались рулы, было жирно написано: "Не пользуйтесь рулами"

[asvil]

Ну рулы - это макросы, которые накладываются на ваш запрос по необходимости. Внутри постгреса происходят банальные строковые операции преобразования. Если правильно я все понял, то рекомендуются на запросах, которые затрагивают большие поиски с индексами. Вы вбросили запрос. В соответсвии с рулом запрос преобразовался и получился новый запрос. Такой запрос анализируется анализатором, и авось оптимизируется.

Триггеры соответсвенно - это код, который выполняется до/после выполнения вашего запроса. Триггеры не оптимизируются. Точнее не оптимизируется тот "набор запросов", которые Вы в триггере накодили. Может они там и однотипные, но это уже на Вашей совести.

Таки в Вашей задаче, оставайтесь на правилах. Логика-то несложная, просто workaround с сиквенсом.

Вот здесь все точно рассказано.
http://www.postgresql.org/docs/9.0/interactive/rules-triggers.html

[andrew.k]

Спасибо за ответ и ссылку. Полезно.

Статью прочитал, выводов не смог сделать

[andrew.k]

Сделал вот так, от рулесов решил отказаться "на всякий пожарный".

Код

SQL
CREATE TABLE test (
     id bigserial,
     value integer );
 
CREATE TABLE test_history (
    time timestamp DEFAULT CURRENT_TIMESTAMP,
    test_id bigint,
    action text
);
 
CREATE FUNCTION test_trigger() returns TRIGGER AS '
	begin
		if TG_OP=''DELETE'' then
			insert into test_history values(default, OLD.id, TG_OP);
			return OLD;
		else
			insert into test_history values(default, NEW.id,TG_OP);
			return NEW;
		end if;
	end;
' LANGUAGE plpgsql;
 
CREATE TRIGGER test_trigger before INSERT OR UPDATE OR DELETE ON test
FOR each row execute procedure test_trigger();
 

Вроде работает как нужно. Кто в триггерах силен, оцените решение, поправьте.
Какие есть камни?

[asvil]

Все ништяк. О камнях в SQL не размшляйте. SQL очень простой/явный язык. Просто при программировании системы прежде всего думайте, как сделать фичу на триггерах/хранимых-процедурах или представлениях, а потом уже вспоминайте о сиплюсплюс.

Ну да здесь можно вернуть NULL во всех остальных случаях, но не обязательно.