Re: possible INSERT bug

"Mathew Frank" <mathewfrank(at)qushi(dot)com> writes:
> CREATE FUNCTION insert_record_return_oid(text) RETURNS int4 AS
> ' DECLARE
> s_query ALIAS FOR $1;
> oid int4;
> BEGIN
> EXECUTE s_query;
> GET DIAGNOSTICS oid = RESULT_OID;
> RETURN oid;
> END;
> ' LANGUAGE 'plpgsql' with (ISSTRICT);

> select * from sys_states
> where oid= insert_record_return_oid('insert into sys_states (s_state)
> values(''po'') ');

> "Cannot insert duplicate key" and the insert query never happens.

Assuming you've got more than one row in sys_states already, this isn't
surprising: the function is invoked again for each row to compare to the
row's oid, and on the second row you barf with a unique-key failure.

If you'd not had the unique restriction in place, it'd still not have
done what you wanted, because the rows inserted by the function would be
newer than the start time of the outer query and thus would not be
visible to it.

It might be that you could make this work by marking the function
iscachable (or immutable in 7.3) so that the planner folds the function
call to a constant before the outer query actually starts. But this
strikes me as an unwarranted dependence on implementation details.

regards, tom lane