> > after any change:
> > if (not okay locally) then reject change;
>
> No. If not OK locally, mark change as suspicious. Something may happen
> later that makes it OK.

For deferred constraints, sure. But they're not all deferred. We like our FK
violations to fail immediately, and I would assume that some database-wide
constraints could be set to fail immediately, not just on commit. So I guess
that'd be:

after any change:
if (deferred) then mark change as suspicious;
else if (not okay locally) then reject change;
else if (not okay globally) then mark change as suspicious;
else accept change;

>
> > else if (not okay globally) then mark change as suspicious;
> > else accept change;
> >
> > on commit:
> > for (every suspicious change)
> > if (not okay remotely) then reject commit;
> > accept commit;
> >
> > (locally == within current transaction only)
> > (globally == within current + recently-committed + concurrent

transactions)

> > (remotely == within current + recently-committed)
> >
>
> With the one correction, I think that works. If the constraint is
> an upper limit, uncommitted deletes (or updates that remove the record
> from the constrained group) can't be added to make the total lower
> since the operations could rollback and you don't want a situation
> where a rollback would fail.

I don't think you can write works-anywhere rules for which changes "help" vs.
"hurt" when deciding if the global state would work or not. Sure, if
constraints came with the ability to write plugins that could "see" into
various transactions (and combinations thereof) and decide what to count or not
count, then maybe ... but at the generic database level?

The rollback wouldn't fail -- when committed, I'm saying you only check locally
and with other committed transactions, but not uncommitted ones. You can't
commit on the assumption that an uncommitted transaction that made
"cooperative" changes will commit later. Because yes, that would cause problems
when that transaction has to rollback.

But that also means that the during-transaction check and the transaction-
commit checks aren't necessarily looking at the same sets of transactions. So
the concept of cacheing the results of validation seems iffy to me. I'm
expecting the following:

Transaction A inserts 5 rows (okay everywhere)
Transaction B inserts 5 rows (deferred constraint gets marked as suspicious,
because of A)
Transaction B commits (okay, because A hasn't committed yet, and B's changes
are okay stand-alone and within the global committed context)
Transaction A commits (not okay -- it didn't get a suspicious mark, but it
should recheck anyway and discover B's incompatible changes that are now part
of the global committed context)

I guess you're aiming instead for the on-commit checking to see uncommitted
stuff too? B would fail because of A's uncommitted changes, and my "globally"
vs. "remotely" difference goes away?

Thus:

after any change:
if (deferred) then mark change as suspicious;
else if (not okay locally) then reject change;
else if (not okay globally) then mark change as suspicious;
else accept change;

on commit:
for (every suspicious change)
if (not okay remotely) then reject commit; // because: inconsistent
if (not okay globally) then reject commit; // because: lock-conflict
accept commit;

(locally == within current transaction only)
(remotely == within current + recently-committed)
(globally == within current + recently-committed + concurrent transactions,
but not any rolled-back transactions?)

On that last question: if I try to commit but can't because of uncommitted
changes in another transaction, then that one rolls back, can I try to commit
again and succeed?

Actually, wouldn't it be:

on commit:
for (every suspicious change)
if (not okay remotely) then reject commit;
for (every uncommitted transaction)
if (not okay globally [me+committed+1 uncommitted]) then reject commit;
accept commit;

Because if I commit, with black marks from other uncommitted transactions, but
the combination of other uncommitted transactions would allow me to commit
(their operations cancel each other out) then my black mark goes away, but when
either one of them goes to commit after me, it won't get the cooperative
benefit that allows it to commit, but it also didn't get the black mark that
I've now cleared. I was responsible for allowing them to commit, but because I
committed before either of them, they're now out of luck and don't know it.

total: 18
A adds 5 rows
B adds 5 rows (deferred, gets black mark)
C removes 5 rows
B commits (re-check black mark: committed+5 is okay, committed+5+5-5 is okay)
A commits (no black mark to re-check, but SHOULD have failed)

Thus at commit time, B should check on:
committed + B
committed + B + A
committed + B + C
and, respecting others's lock conflicts (I'm abusing the term, sorry), should
fail on committed + B + A, because there the total is 28.

As the number of concurrent transactions increases, the number of combinations
to check increases as well. If you don't respect uncommitted changes at all
(first to commit wins), you only ever have to check on committed + self. But
you have to check everything, or be very clever setting black marks on other
transactions.

-Philip