??? <<Defaults may also be created for views>>

4321
Дата: 16.11.2004 13:55:05
автор
SET/DROP DEFAULT
These forms set or remove the default value for a column. The default values only apply to subsequent INSERT commands; they do not cause rows already in the table to change. Defaults may also be created for views, in which case they are inserted into INSERT statements on the view before the view's ON INSERT rule is applied.


Кто пояснит, КАК Defaults may also be created for views?
(Я бусь тут второй день. Че то не в понятках).

Проблема примерно такая:
две таблы связь 1:1 (Left join). Кей (id) - счетчик в главной. Написал давненько вью на INSERT - DO INSTED ... VALUES (new.id ...) - в обе таблицы.
Теперь надо втыкать новые данные, не высчитывая заранее main.id (там суррогатный ключ - счетчик). Если вставлять все кроме id во вью - ругань на NULL в id. (т.е. получается, что new.id - NULL и попытка вставить, ес-но, не работает).
Если писать
INSERT INTO view_my (id,....) SELECT nextval('my_id_seq'::text) AS id,...FROM .... (где nextval('my_id_seq'::text) - DEFAULT для main.id
- транзакция какое-то время крутится, потом приходит ругань на отcутствие ключа связи между main И slave (в оба ключа пытаюсь воткнуть, как говорилось, new.id). Причем, если смотреть на приращение счетчика в транзакции, то получается примерно удвоенное число вставляемых записей (которые так и не вставляются) - т.е. такое впечатление, что в new.id ВМЕСТО значения nextval('my_id_seq'::text) оба раза поступает ВЫРАЖЕНИЕ и оба раза вычисляется. Конечно это мои домыслы, но сильно на то похоже. Инсерт же конкретных значений id проходит нормально (если их еще нет в таблицах). Вот теперь ищу, как обеспечить передачу DEFAULT во VIEW (вернее в RULE _INSERT)?

! нужно же явно отличать случай явной вставки NULL в поле с DEFAULT от попытки вставить данные во все поля VIEW кроме дефаултных - КАК? Если даже писать
INSERT .... SELECT .... CASE WHEN new.id IS NULL THEN DEFAULT ELSE new.id END, - это, кажется не рулит проблему - в обоих случаях будет NULL).

Postgres 7.3.4.


И хто писал уже полностью функциональные вью со счетчиками DEFAULT-ами, да вставкой их (вычислимых дефалтов, т.е.) в нескоко та блиц, вспоможите убогому да криворукому
4321
Дата: 16.11.2004 16:05:41
Методом тыка нашел:
ALTER TABLE vmain_d  --!!!  TABLE - для VIEW !!!
ALTER COLUMN  m_id  SET DEFAULT nextval('main_m_id_seq'::text);
При этом на определениии вью и руле и даже типа это НИКАК не сказывается!!!

(опять долбанные перлы со скрытыми SQL свойствами, как это было в 7.2. с констрайнтами),

НО, после этого вставка во вью величин без id ПЕРЕСТАЕТ ругаться на NULL и начинает ругаться на отсутствие величин для связи (в точности как при вставке nextval()) Уроды, млин. Пусть и бесплатные.


Итого тест:
CREATE TABLE public.main
(
  m_id serial NOT NULL,
  m_name varchar(128),
  CONSTRAINT m_id_pkey PRIMARY KEY (m_id)
) WITHOUT OIDS;

CREATE TABLE public.main_add
(
  a_id int4 NOT NULL,
  a_name varchar(128),
  CONSTRAINT a_id_pkey PRIMARY KEY (a_id),
  CONSTRAINT ma_id_fkey FOREIGN KEY (a_id) REFERENCES public.main (m_id) ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT OIDS;

CREATE OR REPLACE VIEW public.vmain AS 

 SELECT main.m_id, main.m_name, main_add.a_name 
   FROM (main 
   LEFT JOIN  main_add  ON ((main.m_id = main_add.a_id ))); 

CREATE OR REPLACE RULE "vmain_INSERT" AS  ON INSERT TO vmain
 DO INSTEAD (
INSERT INTO main (m_id, m_name ) VALUES (new.m_id, new.m_name );
 INSERT INTO main_add (a_id, a_name ) VALUES (new.m_id, new.a_name );  ); 
тест:
 SELECT * 
FROM vmain; 
INSERT  INTO vmain (m_name, a_name) VALUES ('без id', 'без id');
/* ошибка - NULL for m_id */

INSERT  INTO vmain (m_id,m_name, a_name) VALUES (DEFAULT, 'DEFAULT id', 'DEFAULT id'); 
/*  ошибка - NULL for m_id */

 INSERT  INTO vmain (m_id,m_name, a_name) VALUES (0, '0 id', '0 id'); 
/* -успешно */

INSERT  INTO vmain (m_id,m_name, a_name) VALUES (nextval('main_m_id_seq'::text), 'nextval id', 'nextval id'); 
/* ERROR ... key referenced ... NOT FOUND in */

INSERT  INTO vmain (m_id,m_name, a_name) VALUES ( (SELECT nextval('main_m_id_seq'::text) ), 'nextval id', 'nextval id'); 
/* та же ошибка! */

INSERT  INTO vmain (m_id,m_name, a_name)  SELECT  (  nextval('main_m_id_seq'::text) ), 'nextval id', 'nextval id'; 
/*  ERROR ... key referenced ... NOT FOUND in */

 INSERT  INTO vmain (m_id,m_name, a_name) SELECT -1, '-1 id', '-1 id'; 
/* -успешно */
SELECT * FROM vmain;
чуть поменяем
CREATE OR REPLACE VIEW public.vmain_d AS 

 SELECT main.m_id, main.m_name, main_add.a_name 
   FROM (main 
   LEFT JOIN  main_add  ON ((main.m_id = main_add.a_id ))); 

CREATE OR REPLACE RULE "vmain_d_INSERT" AS
  ON INSERT TO vmain_d DO INSTEAD 
(INSERT INTO main (m_id, m_name ) VALUES (DEFAULT , new.m_name) ;
INSERT INTO main_add (a_id, a_name ) VALUES (new.m_id, new.a_name );  
-- в тупой надежде, что единожды заданное попадет в new.m_id

INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id', 'без id');
/*- ошибка вставки NULL  уже в a_id  */ 


/* 
после!!
*/
ALTER TABLE vmain_d  --!!!  TABLE !!!
ALTER COLUMN  m_id  SET DEFAULT nextval('main_m_id_seq'::text);

CREATE OR REPLACE RULE "vmain_d_INSERT" AS
  ON INSERT TO vmain_d DO INSTEAD 
(INSERT INTO main (m_id, m_name ) VALUES (new.m_id, new.m_name) ;
INSERT INTO main_add (a_id, a_name ) VALUES (new.m_id, new.a_name );  

INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id', 'без id');
/*
- ошибка referenced not found - так же вычисляется DEFAULT new.id 2 раза!\?\?
*/


/*
После :*/
CREATE OR REPLACE RULE "vmain_d_INSERT" AS
  ON INSERT TO vmain_d DO INSTEAD 
(INSERT INTO main (m_id, m_name ) VALUES (new.m_id, new.m_name) ;
 --INSERT INTO main_add (a_id, a_name ) VALUES (new.m_id, new.a_name );  
); 
-- отключили вставку во 2-ю таблицу

INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id', 'без id');
/* - успешно*/

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

т.е. получается, что ничего не получается в чистом SQL. Вызвать здзпыйд (plpgsql)? Дык а там сколько будут вызываться new.xxx ? Только в момент передачи, или при каждом вызове? Хотя, т.к. мне еще надо ветвить процедуру при отсутствии данных для присоединенной таблицы (не вставлять пустышек), то от этого, похоже не уйти
(выполнить что-то из разряда: CASE WHEN ... INSERT .... ELSE DO NOTHING; END; похоже не получицца)
фффф
Дата: 17.11.2004 03:43:31
Похоже действительно default для view подставляется буквально в каждое вхождение в правиле.
Но я чего-то не понял, что мешает написать:
CREATE OR REPLACE RULE "vmain_INSERT" AS  ON INSERT TO vmain
 DO INSTEAD (
 INSERT INTO main (m_id, m_name ) VALUES (new.m_id, new.m_name );
 INSERT INTO main_add (a_id, a_name ) VALUES (currval('main_m_id_seq'), new.a_name );  ); 
?
4321
Дата: 17.11.2004 11:04:47
Мешает то, что это же вью должно работать при непосредственной передаче m_id в запрос на вставку во вью. (должно быть полнофункциональной реализацией обновляемого вью, независимо от способа передачи в него данных. ведь могут быть случаи, например заливки данных снаружи, для чего я использую обычно отрицательную область m_id (вернее в отрицательную область растет автонумерация, а заливка идет в положительную), когда в этих заливаемых данных есть некая ссылочная целостность, которую хотелось бы сохранить). В вашем случае связанная запись попытается сослаться не на вновь создаваемую главную, с переданным мной "руками" значением m_id, а на ранее добавленную. Что недопустимо.


Проблема не в том, что дефаулт вычисляется несколько раз. Логическая ошибка в том, что NEW передается в rule как выражение, а не как значение (т.е. как нечто вычисляемое всякий раз внутри одной и той же транзакции - что неверно по самой сути NEW - набора ЗНАЧЕНИЙ новой записи). Дефаулт тут выступает только как способ определить NEW при отсутствии явно задаваемых значений. Та же проблема и при вставке с явной передачей выражения в качестве NEW.m_id (т.е. без использования DEFAULT). Но процедура должна работать и с явно передаваемыми величинами. И совсем не обязательно я передам в нее счетчик. Могу и любое выражение/значение. И если оно будет вычисляться внутри RULE много раз, вычисляя все новые значения - получится полный бред. (Вернее полный бред уже есть - в реализации передачи NEW в RULE). "Разруха - она в головах"

Вот если я делаю:
CREATE OR REPLACE RULE "vmain_d_INSERT" AS
  ON INSERT TO vmain_d DO INSTEAD 
(INSERT INTO main (m_id, m_name ) VALUES (new.m_id, new.m_name || ' : ' || new.m_id) ;
 --INSERT INTO main_add (a_id, a_name ) VALUES (new.m_id, new.a_name );  
); 
DELETE FROM main;
INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id', 'без id');

SELECT * FROM vmain_d;

m_id	m_name	a_name
18	без id : 19	
проблема прорисовывается явным образом (18 ->19). (Заболеваю, кажисть, вот и не сразу додумываюсь до простейших иллюстраций этой бредовой ситуации)


Поэтому пока вижу только такой способ:
...
DO INSTEAD
SELECT my_plpgsql_procedure(new);

(или SELECT my_sql_procedure(new);)

где внутри my_xxx_procedure new пользуется только однажды - в присвоении значений внутренним переменным
буду смотрет в эту сторону.


ДА, еще прорезалась идея - темповая таблица (вернее, постоянная, с типом vmain_d но для переброски записей)
 DO INSTEAD
(DELETE * FROM vmain_temp;
INSERT INTO vmain_temp SELECT new;
INSERT INTO main (id,...) SELECT t.id ... FROM  vmain_temp AS t;
INSERT INTO main_add (id,...) SELECT t.id ... FROM  vmain_temp AS t;);
4321
Дата: 17.11.2004 12:38:34
Мдя.

Получилось как то так:
CREATE OR REPLACE RULE "vmain_d_INSERT" AS
  ON INSERT TO vmain_d DO INSTEAD 
(-- CREATE TEMPORARY TABLE vmain_temp AS SELECT new;  -- нельзя? почему
--BEGIN; -- нельзя - и так в транзакции
DELETE FROM  vmain_temp;
INSERT INTO vmain_temp (m_id,m_name,a_name ) VALUES (new.m_id,new.m_name,new.a_name);
INSERT INTO main (m_id, m_name ) SELECT m_id, t.m_name || ' : ' || t.m_id FROM   vmain_temp AS t;
INSERT INTO main_add (a_id, a_name ) SELECT  t.m_id, t.a_name   FROM   vmain_temp AS t;  
--DROP TABLE vmain_temp;   -- нельзя? почему
DELETE FROM  vmain_temp;
--COMMIT; -- нельзя - и так в транзакции
); 
DELETE FROM main;
INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id', 'без id');
INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id2', 'без id2');
INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id3', 'без id3');
Но!!! на вставку нескольких записей не работаетINSERT INTO vmain_d (m_name, a_name) SELECT m_name, a_name FROM vmain_d;
- ошибка - дубликаты m_id insertov.

как я понял, DELETE работает на стейтмент, а INSERT - на каждую запись...
(если закомментировать все кроме первого
DELETE FROM vmain_temp;
INSERT INTO vmain_temp (m_id,m_name,a_name ) VALUES (new.m_id,new.m_name,new.a_name);
то после
INSERT INTO vmain_d (m_name, a_name) SELECT m_name, a_name FROM vmain_d;
увидим в vmain_temp несколько записей. (хотя если бы руле работал целиком на каждую запись - осталась бы только одна.



____________
Попробовал спастись (что тоже идеалогически неверно, даже если бы заработало):
CREATE OR REPLACE RULE "vmain_d_INSERT" AS
  ON INSERT TO vmain_d DO INSTEAD 
(
DELETE FROM  vmain_temp;
INSERT INTO vmain_temp (m_id,m_name,a_name ) VALUES (new.m_id,new.m_name,new.a_name);

INSERT INTO main (m_id, m_name )
	SELECT t.m_id, t.m_name || ' : ' || t.m_id
		FROM   vmain_temp AS t LEFT JOIN main AS m ON t.m_id = m.m_id WHERE m.m_id IS NULL;
INSERT INTO main_add (a_id, a_name ) 
	SELECT  t.m_id, t.a_name  
		FROM   vmain_temp AS t LEFT JOIN main_add AS a ON t.m_id = a.a_id WHERE a.a_id IS NULL;  
DELETE FROM  vmain_temp;
); 
та же пестня - по одной вставляем, если сразу несколько - ругань на дубликаты ключей - т.е. как тут работает SQL внутри руле - вообще непонятно - муддизм сплошной - видимо он берет записи таблиц при сравнении в LEFT JOIN - ах не из записей текущей транзакции, а из записей до ее начала. Правильно ли это? (тут не так однозначно, как для NEW)

Кудыть крестьянину поддаться?



___________
Так и есть: Руле оперирует с записями таблиц только до начала транзакции (при DELETE видимо тоже)
Ибо:
CREATE OR REPLACE RULE "vmain_d_INSERT" AS
  ON INSERT TO vmain_d DO INSTEAD 
(
DELETE FROM  vmain_temp;
INSERT INTO vmain_temp (m_id,m_name,a_name ) VALUES (new.m_id,new.m_name,new.a_name);

INSERT INTO main (/* m_id,*/  m_name )
	SELECT /* t.m_id, */ t.m_name || ' : ' || t.m_id
		FROM   vmain_temp AS t LEFT JOIN main AS m ON t.m_id = m.m_id WHERE m.m_id IS NULL;
/*
INSERT INTO main_add (a_id, a_name ) 
	SELECT  t.m_id, t.a_name  
		FROM   vmain_temp AS t LEFT JOIN main_add AS a ON t.m_id = a.a_id WHERE a.a_id IS NULL;  
*/
DELETE FROM  vmain_temp;
); 
и, после
INSERT  INTO vmain_d (m_name, a_name) SELECT m_name, a_name FROM   vmain_d;
имеем в main 121 добавленную запись из 11 при старте. Т.е. 11 раз добавилось по 11 записей.


Т.е. опять возвращаемся к функции, как единственно возможному решению.
Убивать
4321
Дата: 17.11.2004 14:11:08
Все воопше еще более непонятно:

Сношу все ключи в таблицах,
Делаю:
CREATE OR REPLACE RULE "vmain_d_INSERT" AS
  ON INSERT TO vmain_d DO INSTEAD 
(
DELETE FROM  vmain_temp;
INSERT INTO vmain_temp (m_id,m_name,a_name ) VALUES (new.m_id,new.m_name,new.a_name);

INSERT INTO main ( m_id,  m_name )
	SELECT  t.m_id,  t.m_name || ' : ' || t.m_id
		FROM   vmain_temp AS t;

INSERT INTO main_add (a_id, a_name ) 
	SELECT  t.m_id, t.a_name  
		FROM   vmain_temp AS t;  

DELETE FROM  vmain_temp;
); 

DELETE FROM main;
DELETE FROM main_add;
INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id', 'без id');
INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id2', 'без id2');
INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id3', 'без id3');
INSERT  INTO vmain_d (m_name, a_name) SELECT m_name, a_name FROM   vmain_d;

в main добавляется 3+9 записей (так и ожидалось - 3х3 - из предыдущего опыта) а во вторую 3+36!!!. А этто еще откудова?
- "Ниччего не понимаю"

тут уж даже и предположить что-либо сложно. Что там происходит???
Ряд такой (вставляем n (1,2,3,4 ...) записей по одной, потом - все еще раз):
1+1 - 1 + 2
2+4 - 2+12
3+9 - 3+36
4+16 - 4 + 80
5 + 25 - 5 + 150
т.е.
n + n*n - записей в первую и n + n*n*(n+1) записей во вторую
с какой это радости?
А если будет 3 таблицы?
_____________

...ттттак, кажется начинаю понимать.... new - это набор записей (не для юзера, для которого он рекорд, а внутри реализации). Если для него определение (SELECT m_name, a_name FROM vmain_d;) , то при входе во вторую инструкцию SQL (SELECT ...) имеет n записей (и для каждой выполняется INSERT всех записей из vmain_temp), а при входе в 3-ю new ПЕРЕВЫЧИСЛЯЕТСЯ и имеет уже n + n*n = (n+1)*n записей... и для каждой из них INSERT n записей из vmain_temp выполняется. Т.е. Rule работает для каждой инструкции столько раз, сколько в его *NEW* записей, затем переходит к следующей инструкции. Если за это время число записей в выражении для *NEW* изменится - количество попыток вставки тоже увеличится. Уроды. (надеюсь хоть сессии то разделены?)

Лапотники, мать их...

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

CREATE OR REPLACE RULE "vmain_INSERT" AS
  ON INSERT TO vmain DO INSTEAD (
INSERT INTO main (m_id, m_name ) VALUES (new.m_id, new.m_name );
 INSERT INTO main_add (a_id, a_name ) VALUES (new.m_id, new.a_name );  );

DELETE FROM main;
DELETE FROM main_add;
INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id', 'без id');
--INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id2', 'без id2');
--INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id3', 'без id3');
--INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id4', 'без id4');
--INSERT  INTO vmain_d (m_name, a_name) VALUES ('без id5', 'без id5');

INSERT  INTO vmain (m_id, m_name, a_name) SELECT 1, m_name, m_name  FROM   main; 
только тут, поскольку селект для NEW написан от main, то ряд такой
n+n - в первую n + 2*n - во вторую
(к третьему предложению SQL число записей в NEW, т.е. в SELECT ... FROM main становится равным 2*n)

Кстати забавно: при вставке/удалении внутри RULE новые записи не учитываются, а при определении числа записей в NEW - вовсю принимают участие. Полный писец.
4321
Дата: 18.11.2004 11:58:46
мдя...
Очередной облом


Решил посмотреть, что будет если:

CREATE OR REPLACE VIEW public.vmain_sql AS 

 SELECT main.m_id, main.m_name, main_add.a_name 
   FROM (main 
   LEFT JOIN  main_add  ON ((main.m_id = main_add.a_id ))); 

CREATE OR REPLACE FUNCTION public.vmain_sql_insert(int4,varchar,varchar) RETURNS int4 AS
'INSERT INTO main (m_id, m_name ) VALUES ($1, $2 );
 INSERT INTO main_add (a_id, a_name ) VALUES ($1, $3 );
 SELECT $1; '
LANGUAGE 'sql' VOLATILE;

CREATE OR REPLACE RULE "vmain_sql_INSERT" AS
  ON INSERT TO vmain_sql DO INSTEAD (
SELECT vmain_sql_insert(new.m_id, new.m_name, new.a_name );
); 
до тех пор, пока не возвращаю вторичный ключ в таблицу main_add все путем. (Если не замечать, что инсерт во вью возвращает набор записей). Т.е. все вставляется в обе таблицы:

DELETE FROM main;
DELETE FROM main_add;
SELECT setval('main_m_id_seq',1);

INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id', 'vmain_sql без id');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id2', 'vmain_sql без id2');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id3', 'vmain_sql без id3');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id4', 'vmain_sql без id4');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id5', 'vmain_sql без id5');
INSERT  INTO vmain_sql ( m_name, a_name) SELECT  m_name, a_name  FROM   vmain_sql; 


Если же вернуть вторичный ключ, то:
 
ALTER  TABLE public.main
ADD   CONSTRAINT m_id_pkey PRIMARY KEY (m_id)
;

ALTER TABLE public.main_add
ADD  CONSTRAINT a_id_pkey PRIMARY KEY (a_id);

ALTER TABLE public.main_add
ADD  CONSTRAINT ma_id_fkey FOREIGN KEY (a_id)
 REFERENCES public.main (m_id) ON UPDATE CASCADE ON DELETE CASCADE
;
поведение становится более чем странным : в main записи вставляются, а в main_add - нет !!!

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


Тааак. Пробую:
ALTER TABLE public.main_add
DROP  CONSTRAINT ma_id_fkey
;

ALTER TABLE public.main_add
ADD  CONSTRAINT ma_id_fkey FOREIGN KEY (a_id)
 REFERENCES public.main (m_id) ON UPDATE CASCADE
 ON DELETE NO ACTION -- CASCADE
;
и опять все вставить. АХА удаление записей происходит именно из-за каскада в ON DELETE. Т.е. все гораздо хуже, чем можно было подумать. (как там реализованы транзакции в руле - это вообще полный Песец - заполнение происходит, но в некоторый момент вторая таблица начинает сверяться не с записями первой этой же транзакции, а с записями до ее начала, и каскад удаляет записи). Видимо придется развалить втавку на 2 инструкции внутри руле, в первой - втавлять только в первую таблицу и заполнять буферную таблицу значениями для второй, во второй - заполнять вторую значениями из буфера. Что будет с межсеансными проблеммами и что произойдет из-за того, что каждая инструкция SQL в RULE повторяется требуемое число раз (которое еще и меняется в промежутке между инструкциями) - трудно даже представить, мля
(и эти люди еще пишут сихмы, индексы и прочие крючечки Удолбы, ля)



Нет, я понимаю, что скорее всего чего-то попросту не понимаю, и есть оно, есть то удивительно простое и удобное средство, поюзав которое, все искомое можно реализовать вполне прозрачным способом, не углубляясь в особенности внутренней реализации правил в постгрессе и обхождение фич, которые мне, непосвященному, кажутся о'high-енными багами... И что даже некие хуру просто ленятся поправить мои дилетантские трепыхания в требуемую сторону, не смотря на мои попытки задеть их самолюбие, поливая на все лады их любимый инструмент (ну, т.е. не самый любимый Не в смысле старика Фрейда
4321
Дата: 18.11.2004 17:05:39
Итак. Проблема с CASCADE прояснилась (вернее запуталась еще сильнее):

Когда я выполнял предыдущий тест я отсылал все запросом из окна pgAdmin-а пачкой (т.е. сразу все что написано). Судя по всему отсюда ноги и растут.

Судя по всему такая посылка эквивалентна BEGIN;.... COMMIT; Что впрочем неочевидно. Если посылать запросы на вставку "по одному" - все выглядит пристойно. Странно. (Ниччего не понимаю)
__________

ЗЫ (Что интересно, разделение вставки на 2 инструкции не сняло проблемы с удалением вставленных было в main_add записей. Делал так:

ALTER TABLE public.main_add
DROP  CONSTRAINT ma_id_fkey
;
ALTER TABLE public.main_add
ADD  CONSTRAINT ma_id_fkey FOREIGN KEY (a_id)
 REFERENCES public.main (m_id) ON UPDATE CASCADE ON DELETE CASCADE
;

CREATE OR REPLACE FUNCTION public.vmain_sql_insert1(int4,varchar,varchar) RETURNS int4 AS
'INSERT INTO main (m_id, m_name ) VALUES ($1, $2 );
 --DELETE FROM vmain_temp;
 INSERT INTO vmain_temp (m_id, a_name ) VALUES ($1, $3 );
 SELECT $1; '
LANGUAGE 'sql' VOLATILE;

CREATE TABLE main_add_log AS SELECT * FROM main_add;

CREATE OR REPLACE FUNCTION public.vmain_sql_insert12() RETURNS bool AS
'INSERT INTO main_add (a_id, a_name ) SELECT m_id, a_name FROM vmain_temp;
 INSERT INTO main_add_log SELECT * FROM main_add;
 DELETE FROM vmain_temp; -- удаляем, чтобы не вставлялось для следующих из *NEW*
 SELECT TRUE; '
LANGUAGE 'sql' VOLATILE;


CREATE OR REPLACE RULE "vmain_sql_INSERT" AS
  ON INSERT TO vmain_sql DO INSTEAD (
DELETE FROM vmain_temp;
SELECT vmain_sql_insert1(new.m_id, new.m_name, new.a_name );
SELECT vmain_sql_insert12();
--DELETE FROM vmain_temp;
); 
Вставка в "лог" main_add_log показывает, что вставка в main_add была, а записи в таблице по завершении "пакета" не появляются. ЧТо к лучшему - нет необходимости в извращениях такого рода - все равно не спасают.
____________

ЗЫ2: Откатил RULE на
CREATE OR REPLACE RULE "vmain_sql_INSERT" AS
ON INSERT TO vmain_sql DO INSTEAD (
SELECT vmain_sql_insert(new.m_id, new.m_name, new.a_name );
);
и попытался оформить транзакцию:
BEGIN;
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id', 'vmain_sql без id');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id2', 'vmain_sql без id2');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id3', 'vmain_sql без id3');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id4', 'vmain_sql без id4');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id5', 'vmain_sql без id5');
INSERT  INTO vmain_sql ( m_name, a_name) SELECT  m_name, a_name  FROM   vmain_sql; 
COMMIT;
На первом же INSERT получил сообщение, что транзакция начата (запускал инструкции руками, т.е. по 1), а на последнем COMMIT; - что нет действующей транзакции. (Ниччего не понимаю) Что, в постгрес нет вложенных транзакций? Что же за механизм обеспечивает исполнение ф-ий (т.е., по умолчанию - транзакций) во "внешней транзакции"?
____________


Резюме: - поведение руле несколько неоднозначное. Зависит от того, как я запускаю запросы - "пакетом", или же по одному. Что настораживает даже больше, чем неприятная любовь к повторному вычислению *NEW*.

Кто-нибудь может пояснить возможный источник последнего трабла ("с затиранием" записей в main_ad при "пакетном" запуске запросов на вставку из PgAdmin)?
________


ЗЗЫ: Кажется что-то тут вот с этим: В ODBC клиенте или PgAdmin пишем так:
--BEGIN;
DELETE FROM main;
DELETE FROM main_add;
DELETE FROM main_add_log;
SELECT setval('main_m_id_seq',1);
--COMMIT;


INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id', 'vmain_sql без id');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id2', 'vmain_sql без id2');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id3', 'vmain_sql без id3');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id4', 'vmain_sql без id4');
INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id5', 'vmain_sql без id5');
INSERT  INTO vmain_sql ( m_name, a_name) SELECT  m_name, a_name  FROM   vmain_sql; 
и отсылаем это "одним пакетом" -
- затирает за собой вставку в main_add
, если же снять комментарии с BEGIN; и COMMIT; - затирать перестает (проверил и из ODBC клиента - Access - все так же).
Что кажется мне непонятным. Что-то лезет не в ту транзакцию? Что-то не так закрывается?
"Что это, Сигизмунд?"
4321
Дата: 19.11.2004 12:53:11
Жаль, что никого не интересуют данные проблемы:

Итак:
0. NEW для руле передается не как значение, а как выражение. Причем для самого набора записей это тоже имеет место. Отсюда:

1. Писать Rule на INSERT или UPDATE или DELETE в несколько таблиц в виде @простой SQL конструкции@ из набора последовательных SQL инструкций - в корне неверно для постгрес (при нынешней реализации исполнения правил в 7.3.4). Т.к. для каждой инструкции набор NEW определяются заново. И если например в базовом запросе для вставки участвуют таблицы самого вида (точнее таблицы, в которые руле пишет изменения), то сам набор записей будет меняться от одной инструкции руле к другой. (Это похоже на ситуацию, если бы SQL инструкция INSERT пересчитывала набор записей для вставки после каждой вставленной записи, а не после стейтмента. Возможно ничего и в этом плохого не будет (можно придумать, как жить и в этом случае), но в случае постгресс имеем "двойной стандарт" - в руле идет дискретный пересчет набора записей между инструкциями, но для каждой инструкции набор записей фиксирован). Вернее написать RULE таким образом можно, но придется все время помнить о его весьма ограниченной применимости. Т.е. аналогия такого представления с таблицей никогда не будет полна.

2. Можно писать руле на INSERT как функцию, но существуют какие-то проблемы с использовантем таких руле в транзакциях*.








___
* - что тут происходит -я не могу понять.
1. Пока допетрил, что постгресс, похоже, умудряется обходиться без вложенных транзакций.
2. Почему-то мне не удается использовать INSERT во вью внутри объявленной мною транзакции. (т.е. если я руками запускаю по одной команде: BEGIN; INSERT ...; и т.п.- то транзакция завершается сама собой после первого же инсерта, и никакие ROLLBACK; или ошибки в наборе последующих команд ее уже не откатят. Если же я просылаю _пачку_ начатую с BEGIN; и завершающуюся не важно COMMIT; ом или ROLLBACK; -ом, из любого клиента (PgAdmin, Access) то записи не вставляются. Но отнести это на ошибки в транзакции мне сложно - лог PgAdmin пишет только WARNING, но не пишет никаких ERROR;
2004-11-19 11:25:00 QUERY  : Thread Query BEGIN;

	INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id', 'vmain_sql без id');

	INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id', 'vmain_sql без id');

--ROLLBACK;

COMMIT;


2004-11-19 11:25:00 QUERY  : WARNING:  BEGIN: already a transaction in progress

2004-11-19 11:25:00 QUERY  : WARNING:  BEGIN: already a transaction in progress

2004-11-19 11:25:00 QUERY  : WARNING:  COMMIT: no transaction in progress

а поскольку он таки доходит до COMMIT; не выдавая сообщения об ошибке в транзакции или ее зависании - можно думать, что транзакция таки закочилась раньше (когда - вопрос сложный) ибо если написать:
BEGIN;
	INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id', 'vmain_sql без id');
	INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id', 'vmain_sql без id');
	INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id', 'vmain_sql без id');
	INSERT  INTO vmain_sql (m_name, a_name) VALUES ('vmain_sql без id', 'vmain_sql без id');
--ROLLBACK;
--COMMIT;
и запустить это пачкой - каждый (в отличии от случая, когда COMMIT; не закомментирован - в том случае почему то только первые 2 INSERT-а) INSERT сообщит свой WARNING; (т.е. при входе на него транзакция не закрыта????!!!) но транзакция закончинтся УСПЕХОМ (вставкой), не смотря на то, что явно я не послал COMMIT;. Если же явно написать в конце COMMIT;, то "транзакция" (??? "пакет" ??? THREAD (см лог выше) ???) отругается на него (????!!! а почему тогда она ругалась на BEGIN; во всех инсертах???) НО завершится неудачей. Т.е. ОТКАТИТСЯ целиком (она/он(пакет) таки была одной транзакцией ????). Кто может сказать, что тут происходит? (у меня мозга за мозгу заходит. При просылке таких же "пакетов" но с инсертами не во вью, а с такими же инсертами в таблицу все с точностью до наоборот - пока не прошлешь членораздельный коммит, обновления не проявятся, транзакция не завершится. А как прошлешь - так она завершится таки нормально (подцепив и все вставленные предыдущими пакетами этого же клиента посланными без коммитов), а не как в случае с вставкой во вью - полным откатом. Такое впечатление, что, в случае вью, кроме транзакции, которая не завершается при повторных BEGIN; существует еще какая-то конструкция (видимо призванная снять проблему отсутствия вложенных транзакций), которая и завершает или откатывает транзакцию при попытках вставки именно во вью в явно объявленной транзакции неким неявным образом (без просылки явного ROLLBACK|COMMIT;) Это не зависит от клиента из которого я запускаю пакеты.

Полюбому, INSERT В таблицу И INSERT во вью (по крайней мере в моем случае - с использованием ф-ий) не равноправны по отношению к явно объявленной транзакции. INSERT во вью закрывает транзакцию. Но в случае просылки SQL инструкций из клиента "пакетом", похоже есть еще и "откат" пакета. Я было предположил, что любой "пакет" откатится, если в нем больше одного COMMIT;-а, но нет, это как-то завязано именно на руле (или ф-ю в нем). Т.к. если послать последовательно несколько явных ("рукописных") commit-off (в отличие от предполагаемо мною неявного коммита "просылаемого" из руле, видимо еще и отложенного до конца "пакета") именно в случае обычной вставки в таблицу - записи вставятся. Если же прослать хотя бы один коммит из "пакета" с явно объявленной транзакцией, в которой наряду с "обычными" вставками в "нормальные" таблицы делается хотя бы один INSERT во вью - откатится вся транзакция (если коммита явно не посылать - транзакция завершится успешно, как писалось выше).
Stas Tristan
Дата: 19.11.2004 17:47:02
> (при нынешней реализации исполнения правил в 7.3.4).
А может стоит обновиться до версии 8.0 Beta 4?

Posted via ActualForum NNTP Server 1.1