Thread: Re: possible INSERT bug

i have the following utility function, which I use to easily return the OID
 of an the new row created by an INSERT query:
 ---
 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);
 ---

 Which correctly returns the OID of the inserted row.
 usage example:
 ---
 insert_record_return_oid('insert into sys_states (s_state) values(''po'')
 ');
 ---

 However, if I get tricky, and imbed this into a select to return the
 inserted row, I get an error:
 ---
 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.


 This is not a problem - I just do things another way, but I was wondering
 what caused this?

 Cheers,
 Mathew

 postgresql 7.2, btw

"Mathew Frank" <mathewfrank@qushi.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