Re: Replication Bundled with Main Source.

Jan Wieck wrote:
> > I think the real answer is that until recently, ERserver wasn't open
> > source and we didn't have the option to include it. Now that it is
> > open source, we could think about it. Having looked at the code, I
> > think it's definitely not ready for prime time, but it could get there
> > with some work. When it's of comparable solidity to the base project
> > I'd be in favor of adding it to the base distro.
>
> Unfortunately I don't think it'll get there ever. There is a fundamental
> design flaw in the system that is not fixable (there are multiple, but
> this is one of the biggies). That is that eRServer only remembers that a
> row has been modified, but not what, in what order, not even how often.
>
> The problem is really easy to demonstrate. With a UNIQUE constraint on a
> column, you change the values of two rows like
>
> A->C
> B->A
> C->B
>
> If these 3 changes fall into one "snapshot", you have no chance to
> replicate that. eRServer tries to do
>
> A->B
> B->A
>
> and whatever order it tries, you'd need a deferred UNIQUE constraint to
> get it done, and I don't have the slightest clue how the ever get _that_
> implemented.

I was wondering about this. It seems to be part of our existing problem
with handling unique contraints during the query, rather than at query
end or transaction end:

test=> CREATE TABLE test (x INT);
CREATE TABLE
test=> INSERT INTO test VALUES (1);
INSERT 17144 1
test=> INSERT INTO test VALUES (2);
INSERT 17145 1
test=> UPDATE test SET x = x + 1;
UPDATE 2
test=> CREATE UNIQUE INDEX test_i ON test (x);
CREATE INDEX
test=> UPDATE test SET x = x + 1;
ERROR: duplicate KEY violates UNIQUE CONSTRAINT "test_i"

We have pretty complex handling of foreign key constraints, allowing
them to fire at the end of the transaction, we nothing for UNIQUE
constraints. I assume we do this because it is more efficient to check
the unique index during insert/update of each row, but perhaps we need a
queue, as you suggest.

Another thing you might need is the ability to _not_ see changes made by
your transaction, so when you go to change B to A, you see the original
B but not the A->B you just changed.

Another idea would be to only queue up the unique constraint failures,
and re-check on transaction commit --- that way, you only have a queue
when you have a possible unique constraint violation, and you re-check
at the end.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073