SEO

Recent cvs versions are failing the following script;

create table oidtest(a time default now()) with oids;

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

select oidtest();

if its working you will see an oid, if its failing you will see 1 row with blank data.

Kevin McArthur

On Tue, Jul 26, 2005 at 03:36:26PM -0700, Kevin McArthur wrote:
> Recent cvs versions are failing the following script;
>
> create table oidtest(a time default now()) with oids;
>
> CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $oidtest$
> DECLARE
> insert_oid_var INTEGER;
> BEGIN
> INSERT INTO oidtest DEFAULT VALUES;
> GET DIAGNOSTICS insert_oid_var = RESULT_OID;
> RETURN insert_oid_var;
> END;
> $oidtest$ Language plpgsql;
>
> select oidtest();
>
> if its working you will see an oid, if its failing you will see 1 row with blank data.

The function appears to work in a session until you replace it (or
drop and recreate it), after which you get NULL. If you exit the
session and reconnect then it works again. I checked 8.0.3 and it
doesn't have this problem.

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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)

Kevin

----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Kevin McArthur" <postgresql-list@stormtide.ca>
Cc: <pgsql-hackers@postgresql.org>
Sent: Tuesday, July 26, 2005 4:19 PM
Subject: Re: [HACKERS] RESULT_OID Bug


> On Tue, Jul 26, 2005 at 03:36:26PM -0700, Kevin McArthur wrote:
>> Recent cvs versions are failing the following script;
>>
>> create table oidtest(a time default now()) with oids;
>>
>> CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $oidtest$
>> DECLARE
>> insert_oid_var INTEGER;
>> BEGIN
>> INSERT INTO oidtest DEFAULT VALUES;
>> GET DIAGNOSTICS insert_oid_var = RESULT_OID;
>> RETURN insert_oid_var;
>> END;
>> $oidtest$ Language plpgsql;
>>
>> select oidtest();
>>
>> if its working you will see an oid, if its failing you will see 1 row
>> with blank data.
>
> The function appears to work in a session until you replace it (or
> drop and recreate it), after which you get NULL. If you exit the
> session and reconnect then it works again. I checked 8.0.3 and it
> doesn't have this problem.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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...7/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

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@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...7/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

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/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Fuhr <mike@fuhr.org> writes:
> Is anybody with a deeper understanding of the code looking at this?

I tried to reproduce the problem ... no joy ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Wed, Jul 27, 2005 at 12:08:18AM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > Is anybody with a deeper understanding of the code looking at this?
>
> I tried to reproduce the problem ... no joy ...

Hmmm...not even with the example that starts from initdb? I'm up
to date with the latest commits and I can consistently reproduce
it. I was just about to post that TRUNCATE apparently "fixes"
the problem:

CREATE TABLE foo (t timestamptz DEFAULT now()) WITH OIDS;

CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$
DECLARE
foo_count integer;
foo_oid integer;
BEGIN
EXECUTE 'INSERT INTO foo DEFAULT VALUES';
GET DIAGNOSTICS foo_count = ROW_COUNT;
GET DIAGNOSTICS foo_oid = RESULT_OID;
RAISE INFO 'ROW_COUNT = %, RESULT_OID = %', foo_count, foo_oid;
RETURN foo_oid;
END;
$$ LANGUAGE plpgsql VOLATILE;

SELECT oidtest();
INFO: ROW_COUNT = 1, RESULT_OID = 17008
oidtest
---------
17008
(1 row)

CREATE OR REPLACE FUNCTION oidtest() RETURNS integer AS $$
DECLARE
foo_count integer;
foo_oid integer;
BEGIN
EXECUTE 'INSERT INTO foo DEFAULT VALUES';
GET DIAGNOSTICS foo_count = ROW_COUNT;
GET DIAGNOSTICS foo_oid = RESULT_OID;
RAISE INFO 'ROW_COUNT = %, RESULT_OID = %', foo_count, foo_oid;
RETURN foo_oid;
END;
$$ LANGUAGE plpgsql VOLATILE;

SELECT oidtest();
INFO: ROW_COUNT = <NULL>, RESULT_OID = <NULL>
oidtest
---------

(1 row)

TRUNCATE foo;

SELECT oidtest();
INFO: ROW_COUNT = 1, RESULT_OID = 17011
oidtest
---------
17011
(1 row)

Could this be platform-specific? Right now I can only test with
Solaris 9/sparc, but if necessary I could build HEAD on FreeBSD
4.11-STABLE/i386.

Kevin, what platform are you using?

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Fuhr <mike@fuhr.org> writes:
> On Wed, Jul 27, 2005 at 12:08:18AM -0400, Tom Lane wrote:
>> I tried to reproduce the problem ... no joy ...

> Hmmm...not even with the example that starts from initdb?

Nope...

> Could this be platform-specific?

Seems that way. I tried it on HPUX 10.20/HPPA/gcc 2.95.3.

My guess is that the behavior is related to plpgsql's caching
of plans for functions, and as such should be driven by the
backend's history not the whole database's history. But it's
just a guess.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Wed, Jul 27, 2005 at 12:56:15AM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > Could this be platform-specific?
>
> Seems that way. I tried it on HPUX 10.20/HPPA/gcc 2.95.3.

No luck on FreeBSD 4.11-STABLE/i386/gcc 2.95.4. The box that does
have a problem is Solaris 9/sparc/gcc 3.4.2.

Can anybody else reproduce the problem?

> My guess is that the behavior is related to plpgsql's caching
> of plans for functions, and as such should be driven by the
> backend's history not the whole database's history. But it's
> just a guess.

Another test case that's been consistent for me:

Session 1: connect
Session 1: create table and function
Session 1: call function; returns oid

Session 2: connect
Session 2: call function; returns NULL

Session 1: exit

Session 3: connect
Session 3: call function; returns NULL

Session 2: exit

Session 3: exit

Session 4: connect
Session 4: call function; returns oid

Session 5: connect
Session 5: call function; returns NULL

Any suggestions? Would it be useful to attach gdb to one of the
backends? If so, what should I be looking for?

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend