Are rules transaction safe?
От | Oliver Fürst |
---|---|
Тема | Are rules transaction safe? |
Дата | |
Msg-id | 43DA871D.8000704@tsak.net обсуждение исходный текст |
Ответы |
Re: Are rules transaction safe?
|
Список | pgsql-general |
Hi all, I have a question regarding rules on views. Are the commands inside a ON INSERT (or UPDATE) DO INSTEAD (implicit) transactions for postgres? I tried to put BEGIN; and COMMIT; around the commands in a ON ... DO INSTEAD ( ) block, but keep getting a syntax error. The following example might explain what I'm trying to find out: ------------------------------------------------------------- Two tables, "a" and "b". "b" is referencing "a" via "a_id"... CREATE TABLE a ( id serial, foo varchar(255), CONSTRAINT aid PRIMARY KEY (id) ); CREATE TABLE b ( id serial, a_id int4 not null, foo varchar(255), CONSTRAINT bid PRIMARY KEY (id), CONSTRAINT bfk FOREIGN KEY (a_id) REFERENCES a (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ); ...a view "ab" for a combination of the before mentioned tables... CREATE OR REPLACE VIEW ab AS SELECT a.id AS main_id, a.foo AS from_a, b.foo AS from_b FROM a, b WHERE a.id = b.a_id; ...and a rule "ab_insert"... CREATE OR REPLACE RULE ab_insert AS ON INSERT TO ab DO INSTEAD ( INSERT INTO a (foo) VALUES (new.from_a); INSERT INTO b (foo,a_id) VALUES (new.from_b,currval('a_id_seq')); ); ------------------------------------------------------------- As you can see, the "ab_insert" rule inserts into "a" first and than takes the current value of "a.id"'s sequence to set the reference "a_id" in "b". Can I assume that this will always work as expected or is it possible that in a multi-user scenario two or more concurrent inserts on the view will lead to undesirable results? As mentioned in the beginning, putting BEGIN; and COMMIT; didn't work. Is this kind of creating a relation between two or more tables and relying on a sequence generally a good practice? Regards, Oliver
В списке pgsql-general по дате отправления: