Suppose I've create table m1 ( a int primary key, cm1 int, cm2 int, ... ); create r1 ( a int references m1 (a), cr1 int, cr2 int, ... ); and cm1 possible values depends on some function of cr1 for a given a. I actually have a plpgsql function that returns the possible choices for cm1 for each set of cr1. a) r1 get filled with some values. b) The user is presented with the possible choices of cm1. c) I've to take a snapshot of m1 and r1. Since the user may change cr1 while I already started c) cm1 may not be compatible with the new set of cr1. The user shouldn't (if I coded it right) be able to change cm1 in a way that is not compatible with cr1. create or replace function t2c(_a int) returns void as $$ insert into m1c select * from m1 where a=_a; insert into r1c select * from m1 where a=_a; t2c should see a snapshot of m1 *and* r1 at a given time. Of course I'd like to use a system that is as much rollback/lock free. One way would be to put the function in a serializable transaction... but that has its drawback (rollback and retry) I could even create a create table m1pr1 ( a int, cm1 int, cm2 int, cr1 int, cr2 int ); insert into m1pr1 select m1.a, m1.cm1, m1.cm2, r1.cr1, r1.cr2 from m1 join r1 on m1.a=r1.a; but I'm not sure what's going to happen and this solution has its own drawback too (denormalized data). Actually a serializable transaction doesn't even seem a too bad solution... but I just would like to understand better how to manage this situation so that I could make it as simple as possible AND lower as much as possible the chances that the transaction will have to be rolled back. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
