Re: index(fct(primary key)) kills INSERTs

Frank Miles <fpm(at)u(dot)washington(dot)edu> writes:
>> which surprises me not at all, because at the point where this function
>> is invoked, the new record with tt_id 3 hasn't been entered into the
>> table yet.

> This makes sense if the index is updated before the new record is completely
> added. (showing my ignorance here:) Is that necessary? Can't the new
> index be updated after entry? How can it be that the table has the
> item (it's attempting to update the index based on a table that includes
> the record '3'), yet the table doesn't have it (project 3 not found).
> This smells like a race condition.

It's not a race condition, it's a visibility condition, and it's
entirely intentional. The tuple is indeed physically there in the table
(else we wouldn't know where to make the index entry point), but you
can't see it until the command that's inserting it is completed.
This is just like the normal rule that you can't see the results of an
uncommitted transaction, except it applies to individual commands within
a transaction. Without this rule we'd have all sorts of problems with
ghost tuples, infinite loops during UPDATE, etc.

You still haven't explained why your index function should need to use
a SELECT to retrieve the tuple it's going to index. It's supposed to
get all the info it needs from the passed parameter(s). Doing a SELECT
is not only extremely inefficient, but opens up all sorts of interesting
questions about whether your function is depending only on the tuple
it's allegedly indexing. (A "functional index" that depends on more
inputs than just the indexed tuple is a logical contradiction that
will cause all kinds of headaches; for example, the system won't know
it needs to update the index entries when those other inputs change.)
Why don't you just make the function receive all the columns it needs as
passed parameters?

regards, tom lane