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 or terminate 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(at)fuhr(dot)org>
To: "Kevin McArthur" <Kevin(at)stormtide(dot)ca>
Cc: <pgsql-hackers(at)postgresql(dot)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
>