Search Postgresql Archives

Re: Are rules transaction safe?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux