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" <[EMAIL PROTECTED]>
To: "Kevin McArthur" <[EMAIL PROTECTED]>
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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster