Re: RESULT_OID Bug

On Tue, Jul 26, 2005 at 04:31:21PM -0700, Kevin McArthur wrote:
> I cannot repoduce your experience with this bug. No matter what I do,
> reconnect session or otherwise, it never returns a proper oid on the
> newer cvs vers (I suspect it may be related to the roles update)

I'm seeing varying results, depending on disconnects, database
restarts, and possibly whether another session has executed the
same function in another database. I suspect our systems aren't
in exactly the same state so we're seeing slightly different results.
Here's something that starts with initdb, so hopefully it'll be 100%
reproducible:

initdb data2
postmaster -D data2 -p 9999
createlang -p 9999 plpgsql postgres
psql -p 9999 postgres

CREATE TABLE foo (a time DEFAULT now()) WITH OIDS;

CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$
DECLARE
insert_oid_var INTEGER;
BEGIN
EXECUTE 'INSERT INTO foo DEFAULT VALUES';
GET DIAGNOSTICS insert_oid_var = RESULT_OID;
RETURN insert_oid_var;
END;
$$ LANGUAGE plpgsql VOLATILE;

SELECT oidtest();
oidtest
---------
16391
(1 row)

CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$
DECLARE
insert_oid_var INTEGER;
BEGIN
EXECUTE 'INSERT INTO foo DEFAULT VALUES';
GET DIAGNOSTICS insert_oid_var = RESULT_OID;
RETURN insert_oid_var;
END;
$$ LANGUAGE plpgsql VOLATILE;

SELECT oidtest();
oidtest
---------

(1 row)

When did you first notice this? When was the last time you know
for sure that it was behaving correctly?

So far I've only seen the problem with PL/pgSQL's GET DIAGNOSTICS --
I haven't been able to reproduce it with PL/Tcl's spi_lastoid.

Is anybody with a deeper understanding of the code looking at this?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/