Re: RESULT_OID Bug

bash-2.05b$ ./createdb test3
CREATE DATABASE
bash-2.05b$ ./createlang plpgsql test3
bash-2.05b$ ./psql test3
Welcome to psql 8.1devel, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms      \h for help with SQL commands      \? for help with psql commands      \g
orterminate with semicolon to execute query      \q to quit
 

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

CREATE FUNCTION oidtest() RETURNS integer AS $$
DECLARE insert_oid_var INTEGER;
BEGIN INSERT INTO foo DEFAULT VALUES; GET DIAGNOSTICS insert_oCREATE TABLE
test3=#
test3=# CREATE FUNCTION oidtest() RETURNS integer AS $$
test3$# DECLARE
test3$#   insert_oid_var INTEGER;
test3$# BEGIN
test3$#   INSERT INTO foo DEFAULT VALUES;
test3$#   GET DIAGNOSTICS insert_oid_var = RESULT_OID;
test3$#   RETURN insert_oid_var;
test3$# END;
test3$# $$ LANGUAGE plpgsql VOLATILE;
CREATE FUNCTION
test3=#
test3=# SELECT oidtest();oidtest
---------

(1 row)

----- Original Message ----- 
From: "Michael Fuhr" <mike@fuhr.org>
To: "Kevin McArthur" <Kevin@stormtide.ca>
Cc: <pgsql-hackers@postgresql.org>
Sent: Tuesday, July 26, 2005 4:58 PM
Subject: Re: [HACKERS] 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)
>
> Hmmm...my system is only a couple of hours old; the only code it's
> missing is the recent "Minor correction: cause ALTER ROLE role ROLE
> role" commit:
>
> http://archives.postgresql.org/pgsql-committers/2005-07/msg00545.php
>
> Here's a test case on my system, run in a fresh session in a
> newly-created database named test2:
>
> CREATE TABLE foo (a time DEFAULT now()) WITH OIDS;
>
> CREATE FUNCTION oidtest() RETURNS integer AS $$
> DECLARE
>  insert_oid_var INTEGER;
> BEGIN
>  INSERT INTO foo DEFAULT VALUES;
>  GET DIAGNOSTICS insert_oid_var = RESULT_OID;
>  RETURN insert_oid_var;
> END;
> $$ LANGUAGE plpgsql VOLATILE;
>
> SELECT oidtest();
> oidtest
> ---------
>   16565
> (1 row)
>
> SELECT oidtest();
> oidtest
> ---------
>   16566
> (1 row)
>
> CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$
> DECLARE
>  insert_oid_var INTEGER;
> BEGIN
>  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)
>
> \c test2
> You are now connected to database "test2".
>
> SELECT oidtest();
> oidtest
> ---------
>   16568
> (1 row)
>
> -- 
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>