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
