Re: Replication Bundled with Main Source. - Mailing list pgsql-general

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073