Re: invalid tid errors in latest 7.3.4 stable.

On Fri, 26 Sep 2003, Tom Lane wrote:

> >> Okay, I'll work out some extension of the APIs to let us propagate the
> >> snapshot request down through SPI and into the Executor, rather than
> >> using a global variable for it. (Unless someone has a better idea...)
>
> Just when you thought it was safe to go back in the water ...
>
> Chris Kratz sent me the attached example, which fails in 7.3 and (still)
> fails in CVS HEAD too.

> As far as the "DELETE FROM qry_column_list" goes, I think the solution
> is that fetching rows can't use pure SnapshotNow after all. What we
> need is to create a fresh QuerySnapshot that shows all transactions
> committed-to-date as committed, and saves the current CommandCounter as
> the criterion for locally created rows. Unlike SnapshotNow, this would
> mean that transactions committed just after we take the new snapshot
> would not be seen as committed. This should be okay AFAICS --- once we
> reach the RI triggers, all transactions we need to worry about should be
> committed. (If not, surely there's a race condition anyway.) Also note

That should be true. By the time the triggers have started running,
anything that might be conflicting shouldn't be able to commit until after
the transaction the trigger is in (since we already have locks on rows
they'd need to be able to lock).

> that an RI query would *not* see the effects of actions it indirectly
> triggers. This should be okay, because if they do anything that
> requires RI validation, they should cause additional RI trigger firings
> to be queued for attention later.

I feel vague uneasiness about this, but can't think of a counter example,
it's probably just breakfast acting up.

> But Chris' example raises a host of other questions in my mind. Should
> we apply this forcibly-updated QuerySnapshot to actions that are
> indirectly triggered by RI queries? In CVS tip, SnapshotNow rules are
> in fact used for the UPDATE that's generated by the RULE, because it's
> part of the generated plan for the DELETE. But any queries executed
> inside triggers fired as a result of all this would use the pre-existing
> QuerySnapshot, and hence could see a worldview completely inconsistent
> with the rows they are being fired for :-(. It's worse in 7.3, because
> the first trigger exit would revert ReferentialIntegritySnapshotOverride
> to false, meaning you wouldn't even be using the same snapshot rules
> throughout the UPDATE/DELETE :-( :-(
>
> I am inclined to think now that the right solution is for the RI
> triggers to update the global QuerySnapshot to current time when they
> start, and then revert it to what it had been before exiting. (And that
> code had better be in the RI triggers themselves, *not* in the generic
> trigger-calling code.) This would ensure that actions taken indirectly
> as a result of RI behavior would see a consistent worldview.
>
> The main argument I can see against this is that it would be a really
> big wart on the behavior of SERIALIZABLE transactions. Instead of
> saying "in a SERIALIZABLE transaction, you only see the effects of
> transactions committed before your transaction started", we'd have to
> add a footnote "except in actions taken as a result of RI-generated
> queries", which sure complicates matters from a logical point of view.
> (In READ COMMITTED mode, on the other hand, it's no big deal; we are
> effectively just decreeing that a new command starts before the RI
> triggers run.)

Given the two suggestions above, I think I'd vote for the latter. It seems
to break the idea of serializable, but it seems like it'd be easier for
people to work with than the former where you might not be able to even
see the row you're working upon in its own triggers.

I think theoretically in serializable the cases where the difference
between the snapshot from this statement and the standard snapshot for the
transaction are noticable we probably have a serialization failure since
I think that case comes in when a transaction that started after us (and
has already committed) has done something that the constraint's search
condition would need to see we'd potentially be opening up the possibility
for phantoms. If we have to get the same rows as a previous set for
the same search condition, but the row in question was already committed
by that transaction that started after this one, we no longer have the
freedom to pretend that the transactions were in the other order.