Re: invalid tid errors in latest 7.3.4 stable. - Mailing list pgsql-hackers

Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > 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
> 
> Hmm, that is a good point.  It would be cleaner to throw a "can't
> serialize" failure than have the RI triggers run under a different
> snapshot.  I am not sure if we can implement that behavior easily,
> though.  Can you think of a way to detect whether there's an RI conflict
> against a later-started transaction?

Just some thoughts on this that, of course, could be wrong.  So please
don't be too hard on me if I'm full of it.  :-)

By "a later-started transaction" I assume you mean a later-started
transaction that commits before yours does?

I don't see how RI is any different than dealing with straight SQL
in this regard.  The effect of RI is to read/write/delete rows from a
related table that you otherwise wouldn't read or modify, and that means
that the RI mechanism needs to be treated in exactly the same way that
the equivalent SELECT/UPDATE/DELETE would be.

So the question I have is: what would PG do in the case that you SELECT
the same row(s) that the RI triggers are reading implicitly?  For
instance, suppose we have two tables:
CREATE TABLE corps (id integer PRIMARY KEY, name varchar(32));CREATE TABLE widgets (id integer PRIMARY KEY, name
varchar(32),   corpid integer REFERENCES corps(id) ON DELETE CASCADE);
 

When, within a transaction, I do:
INSERT INTO widgets (id, name, corpid) VALUES (1, 'cpu', 3);

the RI mechanism will automatically check to make sure that the value
3 is in the id column of the corps table.  Put another way, it will do
an implicit "SELECT id FROM corps WHERE id = 3", right?  So suppose
that for the purposes of testing the serialization code I remove the RI
triggers and then actually do the following:
SELECT id FROM corps WHERE id = 3;INSERT INTO widgets (id, name, corpid) VALUES (1, 'cpu', 3);

If my transaction is serializable then clearly, when another transaction
does
UPDATE corps SET id = 4 WHERE id = 3;

and commits before my transaction commits, either the updating
transaction is in violation of serializability rules or the inserting
transaction is.  Serialization is maintained if either of those
transactions aborts with a serialization error.

But note that whether or not RI is involved should be entirely
irrelevant.  What matters is what rows each transacion sees and
modifies.  How the row gets looked at doesn't matter; the only thing
that matters is that the row *does* get looked at.

The important thing here is that the effect of the RI mechanism MUST be
the same as if the equivalent manual SQL statements were exected within
the same transaction.  If it's not, then the RI mechanism is broken and
needs to be fixed at that level.

But if PG exhibits exactly the same bug this thread refers to regardless
of whether a row is examined/modified via directly issued SQL or via
the RI mechanism then the problem lies not within the RI code at all,
but within the serialization code.


I just hope I'm not merely stating the obvious here...


-- 
Kevin Brown                          kevin@sysexperts.com