From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Frank Miles <fpm(at)u(dot)washington(dot)edu> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: index(fct(primary key)) kills INSERTs |
Date: | 2000-11-10 06:40:01 |
Message-ID: | 10465.973838401@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Frank Miles <fpm(at)u(dot)washington(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Monro | 2000-11-10 10:18:27 | Bug with return characters when using pg_dump and a re-import |
Previous Message | Frank Miles | 2000-11-10 05:52:30 | index(fct(primary key)) kills INSERTs |