Thread: RESULT_OID Bug
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;
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/
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 >
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/
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 >
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/
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
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 = 17008oidtest --------- 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 = 17011oidtest --------- 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/
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
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/
Michael Fuhr wrote: > Can anybody else reproduce the problem? I couldn't repro it, on x86 / Debian unstable / gcc 3.4.4, with current CVS sources. -Neil
The target system for my reproduction is on FreeBSD. Though I sitll cannot get the initdb started one to work the first time around. I cannot reproduce the bug on my linux machine, however I cannot test the full initdb procedure until tomorrow on freebsd. This error has come up in the last week or so, and my suspicion remains that its caused by something to do with roles but that could be way wrong. The FreeBSD machines were confirmed to work as of about a week ago ( i reinstalled for a timezone patch and I'm pretty sure it was working then ) I can note that the \set for lastoid is properly updated when I insert into a table. Thus the problem has to be somewhere between plpgsql and that data via the get diagnostics interface (under the assumption that the lastoid structure is consistent for all inserts and psql having lastoid working at all eliminates that part of the equation). Kevin McArthur ----- Original Message ----- From: "Neil Conway" <neilc@samurai.com> To: "Michael Fuhr" <mike@fuhr.org> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Kevin McArthur" <postgresql-list@stormtide.ca>; <pgsql-hackers@postgresql.org> Sent: Tuesday, July 26, 2005 11:34 PM Subject: Re: [HACKERS] RESULT_OID Bug > Michael Fuhr wrote: >> Can anybody else reproduce the problem? > > I couldn't repro it, on x86 / Debian unstable / gcc 3.4.4, with current > CVS sources. > > -Neil > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
On Wed, Jul 27, 2005 at 12:19:51AM -0700, Kevin McArthur wrote: > The target system for my reproduction is on FreeBSD. What version of FreeBSD? What compiler and version? So far I haven't been able to reproduce the problem on FreeBSD 4.11-STABLE/ i386/gcc 2.95.4. > Though I sitll cannot get the initdb started one to work the first time > around. Odd -- that one works (or rather, fails) every time for me on Solaris 9/ sparc/gcc 3.4.2. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Wed, Jul 27, 2005 at 12:19:51AM -0700, Kevin McArthur wrote: > This error has come up in the last week or so, and my suspicion remains > that its caused by something to do with roles but that could be way wrong. > > The FreeBSD machines were confirmed to work as of about a week ago ( i > reinstalled for a timezone patch and I'm pretty sure it was working then ) Have you tried using CVS to check out and test older code? I'll do that myself when I get a chance. If the developers can't reproduce the problem, then at least maybe we can narrow down which commit is responsible so they'll have something to look at. > I can note that the \set for lastoid is properly updated when I insert into > a table. Thus the problem has to be somewhere between plpgsql and that data > via the get diagnostics interface (under the assumption that the lastoid > structure is consistent for all inserts and psql having lastoid working at > all eliminates that part of the equation). So far the problem does seem to be specific to whatever PL/pgSQL's is doing, and it affects ROW_COUNT as well as RESULT_OID. I haven't been able to reproduce the problem with PL/Tcl or with C and SPI. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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 >
On 2005-07-27, Michael Fuhr <mike@fuhr.org> wrote: > So far the problem does seem to be specific to whatever PL/pgSQL's > is doing, and it affects ROW_COUNT as well as RESULT_OID. I haven't > been able to reproduce the problem with PL/Tcl or with C and SPI. src/pl/plpgsql/src/pl_exec.c, function exec_stmt_getdiag, uninitialized variable "isnull". -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews wrote: >On 2005-07-27, Michael Fuhr <mike@fuhr.org> wrote: > > >>So far the problem does seem to be specific to whatever PL/pgSQL's >>is doing, and it affects ROW_COUNT as well as RESULT_OID. I haven't >>been able to reproduce the problem with PL/Tcl or with C and SPI. >> >> > >src/pl/plpgsql/src/pl_exec.c, function exec_stmt_getdiag, uninitialized >variable "isnull". > > > nice catch. I see the "= false" removed in about 5 places in this change: http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/pl_exec.c.diff?r1=1.146;r2=1.147 Is this the only one that matters? cheers (another) andrew
Changing just the one appears to resolve the oid bug. Should probably talk to neilc to see why he changed it. I will pass along a patch for this particular case to -patches shortly Kevin McArthur ----- Original Message ----- From: "Andrew Dunstan" <andrew@dunslane.net> To: <andrew@supernews.com> Cc: <pgsql-hackers@postgresql.org> Sent: Wednesday, July 27, 2005 11:57 AM Subject: Re: [HACKERS] RESULT_OID Bug > > > Andrew - Supernews wrote: > >>On 2005-07-27, Michael Fuhr <mike@fuhr.org> wrote: >> >>>So far the problem does seem to be specific to whatever PL/pgSQL's >>>is doing, and it affects ROW_COUNT as well as RESULT_OID. I haven't >>>been able to reproduce the problem with PL/Tcl or with C and SPI. >>> >> >>src/pl/plpgsql/src/pl_exec.c, function exec_stmt_getdiag, uninitialized >>variable "isnull". >> >> > > nice catch. I see the "= false" removed in about 5 places in this change: > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/pl_exec.c.diff?r1=1.146;r2=1.147 > > Is this the only one that matters? > > cheers > > (another) andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
On Wed, Jul 27, 2005 at 01:20:29PM -0700, Kevin McArthur wrote: > Changing just the one appears to resolve the oid bug. Should probably talk > to neilc to see why he changed it. Initializing isnull to false in exec_stmt_getdiag() appears to fix the bug on my Solaris 9 box as well. I'd guess the variations in behavior were due to different platforms having different garbage on the stack. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Sounds plausible. Can anyone comment on whether the other instances of isnull=false being removed need to be reset to =false Kevin McArthur ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Kevin McArthur" <postgresql-list@stormtide.ca> Cc: "Andrew Dunstan" <andrew@dunslane.net>; <andrew@supernews.com>; <pgsql-hackers@postgresql.org> Sent: Wednesday, July 27, 2005 3:35 PM Subject: Re: [HACKERS] RESULT_OID Bug > On Wed, Jul 27, 2005 at 01:20:29PM -0700, Kevin McArthur wrote: >> Changing just the one appears to resolve the oid bug. Should probably >> talk >> to neilc to see why he changed it. > > Initializing isnull to false in exec_stmt_getdiag() appears to fix > the bug on my Solaris 9 box as well. I'd guess the variations in > behavior were due to different platforms having different garbage > on the stack. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
It is the only one that currently appears to _for this bug_. Kevin ----- Original Message ----- From: "Andrew Dunstan" <andrew@dunslane.net> To: <andrew@supernews.com> Cc: <pgsql-hackers@postgresql.org> Sent: Wednesday, July 27, 2005 11:57 AM Subject: Re: [HACKERS] RESULT_OID Bug > > > Andrew - Supernews wrote: > >>On 2005-07-27, Michael Fuhr <mike@fuhr.org> wrote: >> >>>So far the problem does seem to be specific to whatever PL/pgSQL's >>>is doing, and it affects ROW_COUNT as well as RESULT_OID. I haven't >>>been able to reproduce the problem with PL/Tcl or with C and SPI. >>> >> >>src/pl/plpgsql/src/pl_exec.c, function exec_stmt_getdiag, uninitialized >>variable "isnull". >> >> > > nice catch. I see the "= false" removed in about 5 places in this change: > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/pl_exec.c.diff?r1=1.146;r2=1.147 > > Is this the only one that matters? > > cheers > > (another) andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >