Hi Doug,
On 27.01.2006 21:01, Doug McNaught wrote:
Oliver Fürst <ofuerst@xxxxxxxx> writes:
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.
Everything that happens in Postgres is inside either an implicit or
explicit transaction, so you can't do BEGIN/COMMIT inside rules or
functions.
You might be able to use savepoints, depending on what you're actually
trying to do.
Actually I'm just worried that something like the ON INSERT ... DO
INSTEAD rule on a view (as stated in my example)...
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'));
);
...could yield unexpected results. Imagine two overlapping inserts on
the view in a heavy load situation. (C1, C2 -> two concurrent connections):
C1 -> INSERT INTO ab (from_a,from_b) VALUES ('foo','bar');
C2 -> INSERT INTO ab (from_a,from_b) VALUES ('hello','world');
...should translates to...
C1 -> INSERT INTO a (foo) VALUES ('foo'); -- id == 1
C1 -> INSERT INTO b (foo,a_id) VALUES ('bar',1);
C2 -> INSERT INTO a (foo) VALUES ('hello'); -- id == 2
C2 -> INSERT INTO b (foo,a_id) VALUES ('world',2);
...but could translate to...
C1 -> INSERT INTO a (foo) VALUES ('foo'); -- id == 1
C2 -> INSERT INTO a (foo) VALUES ('hello'); -- id == 2
C1 -> INSERT INTO b (foo,a_id) VALUES ('bar',2);
C2 -> INSERT INTO b (foo,a_id) VALUES ('world',2);
Basically I'm worried that the whole "relying on the last value of a
sequence" isn't such a great idea.
(By the way, did I pick the wrong mailing list for that topic and should
I move to pgsql-sql?)
Regards,
Oliver
