Re: index(fct(primary key)) kills INSERTs - Mailing list pgsql-bugs

From Tom Lane
Subject Re: index(fct(primary key)) kills INSERTs
Date
Msg-id 10465.973838401@sss.pgh.pa.us
Whole thread Raw
In response to index(fct(primary key)) kills INSERTs  (Frank Miles <fpm@u.washington.edu>)
Responses Re: index(fct(primary key)) kills INSERTs
List pgsql-bugs
Frank Miles <fpm@u.washington.edu> writes:
> If an index is created based on a function of the primary key,
> you cannot insert new entries into the database.

I think the critical point here is that your "function of the primary
key" is actually doing a SELECT from the table:

                SELECT INTO prec * FROM test_table WHERE tt_id = dum_int;
                IF NOT FOUND THEN
                        RAISE EXCEPTION ''project % not found'', dum_int;
                END IF;

When I try your example, I get

play=> INSERT INTO test_table (tt_descr) VALUES ('third - will fail');
ERROR:  project 3 not found

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.

I'm not sure what you are really trying to accomplish here --- as you
say, it's a stripped-down example and not too intelligible.  As far
as the example goes, you could skip the SELECT and just use the
passed-in parameter value.  What was the original goal that made you
feel you needed to SELECT the about-to-be-inserted row?

            regards, tom lane

pgsql-bugs by date:

Previous
From: Frank Miles
Date:
Subject: index(fct(primary key)) kills INSERTs
Next
From: Robert Monro
Date:
Subject: Bug with return characters when using pg_dump and a re-import