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

From: Frank Miles <fpm(at)u(dot)washington(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: index(fct(primary key)) kills INSERTs
Date: 2000-11-10 18:29:19
Message-ID: Pine.A41.4.21.0011101023280.117608-100000@mead2.u.washington.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Fri, 10 Nov 2000, Tom Lane wrote:

> 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.

Good. Makes sense.

> 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?

Ok, maybe the light slowly is dawning. I need the capabilities of the
function analogous to what I've described, but it is a misuse of it
to use it for the purposes of indexing. For that, I should generate
an additional (new) function which has the arguments of the columns much
as you describe. It will do the same processing, but won't require
the SELECT.

Thanks again! Sorry for being dense...

-frank

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Thomas Lockhart 2000-11-11 17:50:13 Re: Re: to_date problems (Re: Favor for Postgres User at WSI)
Previous Message Tom Lane 2000-11-10 17:52:17 Re: index(fct(primary key)) kills INSERTs