Hi Doug,
On 27.01.2006 21:01, Doug McNaught wrote:
> Oliver Fürst <ofuerst@tsak.net> 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