Re: Question about RI checks

Florian Pflug <fgp(at)phlo(dot)org> wrote:

>> This should not be considered a problem for repeatable read
>> transactions because the change in visible rows meet the
>> definition of phantom reads, which are allowed in repeatable
>> read: "A transaction re-executes a query returning a set of rows
>> that satisfy a search condition and finds that the set of rows
>> satisfying the condition has changed due to another
>> recently-committed transaction."
>
> Now I'm confused. Isn't the whole point of REPEATABLE READ to
> provide, well, repeatable reads?

What the standard requires for REPEATABLE READ is that if you
re-read the same row later in a transaction it will containt the
same values -- the read of any particular *row* is repeatable (if
it exists at both times); it does not guarantee that the same
selection criteria will return the same set of rows every time.
The standard only requires that of SERIALIZABLE transactions.

PostgreSQL has historically provided more rigorous protections at
REPEATABLE READ than the standard requires. Our docs claim:

| When you select the level Read Uncommitted you really get Read
| Committed, and phantom reads are not possible in the PostgreSQL
| implementation of Repeatable Read, so the actual isolation level
| might be stricter than what you select. This is permitted by the
| SQL standard: the four isolation levels only define which
| phenomena must not happen, they do not define which phenomena
| must happen.

As you have shown, our FK/RI implementation exposes phantom reads
to clients, so at a minimum our docs are wrong.

> Also, note that after the DELETE FROM parent, further SELECTS in
> the same transaction will use the original snapshot again, und
> thus will see the conflicting child rows again that were ignored
> by the RI trigger. But they won't, of course, see the parent row.
>
> IOW, transaction A will, after the delete, see a state of the
> database in which the PK constraint is broken. I don't think
> that's acceptable in any isolation level.

Good point. Based on that observation, I agree that our RI is
broken at both the REPEATABLE READ and SERIALIZABLE isolation
levels. I think that READ COMMITTED is OK, because it will see the
child row as deleted in time to prevent problems.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company