Thread: help with PL/PgSQL bug
Folks, Someone contacted me in IRC about a bug in PL/PgSQL. I've confirmed that the example SQL they sent me causes a segfault on my machine (CVS HEAD), but I've so far not had a lot of success tracking down the exact cause of the problem. Backtrace: #0 0x403ed17a in compatible_tupdesc (td1=0x82c621c, td2=0x0) at pl_exec.c:3715 #1 0x403eabb6 in exec_stmt_return_next (estate=0xbfffec50, stmt=0x82d2e68) at pl_exec.c:1630 #2 0x403e9f20 in exec_stmt (estate=0xbfffec50, stmt=0x82d2e68) at pl_exec.c:949 #3 0x403e9e09 in exec_stmts (estate=0xbfffec50, stmts=0x82d29c8) at pl_exec.c:873 #4 0x403ea486 in exec_stmt_fori (estate=0xbfffec50, stmt=0x82d2eb0) at pl_exec.c:1276 #5 0x403e9ed4 in exec_stmt (estate=0xbfffec50, stmt=0x82d2eb0) at pl_exec.c:929 #6 0x403e9e09 in exec_stmts (estate=0xbfffec50, stmts=0x82d2580) at pl_exec.c:873 #7 0x403ea486 in exec_stmt_fori (estate=0xbfffec50, stmt=0x82d2f28) at pl_exec.c:1276 #8 0x403e9ed4 in exec_stmt (estate=0xbfffec50, stmt=0x82d2f28) at pl_exec.c:929 #9 0x403e9e09 in exec_stmts (estate=0xbfffec50, stmts=0x82d2148) at pl_exec.c:873 #10 0x403ea60b in exec_stmt_fors (estate=0xbfffec50, stmt=0x82d2fa0) at pl_exec.c:1386 #11 0x403e9ee2 in exec_stmt (estate=0xbfffec50, stmt=0x82d2fa0) at pl_exec.c:933 #12 0x403e9e09 in exec_stmts (estate=0xbfffec50, stmts=0x82d1ec0) at pl_exec.c:873 #13 0x403e9ca7 in exec_stmt_block (estate=0xbfffec50, block=0x82d3078) at pl_exec.c:829 #14 0x403e910b in plpgsql_exec_function (func=0x82a9b70, fcinfo=0xbfffed50) at pl_exec.c:323 #15 0x403e6c94 in plpgsql_call_handler (fcinfo=0xbfffed50) at pl_handler.c:133 #16 0x080e89c2 in ExecMakeTableFunctionResult (funcexpr=0x82c6a2c, econtext=0x82c5d38, expectedDesc=0x82c621c, returnDesc=0xbfffee38) at execQual.c:993 #17 0x080f03d2 in FunctionNext (node=0x82c5bc0) at nodeFunctionscan.c:78 #18 0x080ea002 in ExecScan (node=0x82c5bc0, accessMtd=0x80f0368 <FunctionNext>) at execScan.c:94 #19 0x080f041f in ExecFunctionScan (node=0x82c5bc0) at nodeFunctionscan.c:127 #20 0x080e7606 in ExecProcNode (node=0x82c5bc0) at execProcnode.c:324 #21 0x080e62c1 in ExecutePlan (estate=0x82c5aec, planstate=0x82c5bc0, operation=CMD_SELECT, numberTuples=0, direction=137126428, destfunc=0x82c5cc4) at execMain.c:926 #22 0x080e5936 in ExecutorRun (queryDesc=0x82c2f3c, direction=ForwardScanDirection, count=0) at execMain.c:220 #23 0x0813b05b in ProcessQuery (parsetree=0x82a4d70, plan=0x82c2f3c, dest=Remote, completionTag=0xbffff000 "") at pquery.c:205 #24 0x0813975d in pg_exec_query_string (query_string=0xbffff000, dest=Remote, parse_context=0x827026c) at postgres.c:838 #25 0x0813a6f4 in PostgresMain (argc=4, argv=0xbffff270, username=0x826b841 "nconway") at postgres.c:2013 #26 0x0811f2a2 in DoBackend (port=0x826b710) at postmaster.c:2314 #27 0x0811ee1a in BackendStartup (port=0x826b710) at postmaster.c:1930 #28 0x0811df8d in ServerLoop () at postmaster.c:1017 #29 0x0811d9e9 in PostmasterMain (argc=1, argv=0x825eb08) at postmaster.c:796 #30 0x080fa390 in main (argc=1, argv=0xbffffbf4) at main.c:209 A SQL script that triggers the problem is attached. Any help on tracking down the problem would be much appreciated. Once this has been diagnosed and fixed, I think it's suitable for 7.3.2. Cheers, Neil
Attachment
Neil Conway <neilc@samurai.com> writes:
> Someone contacted me in IRC about a bug in PL/PgSQL. I've confirmed that
> the example SQL they sent me causes a segfault on my machine (CVS HEAD),
> but I've so far not had a lot of success tracking down the exact cause
> of the problem.
The core dump appears to occur when the SELECT INTO fails to retrieve
a row, leaving "finalrec" undefined. Clearly, RETURN NEXT with an
undefined record variable shouldn't dump core, but what should it do?
Raise an error, or perhaps be a no-op?
regards, tom lane
On Fri, 2003-01-10 at 20:28, Tom Lane wrote: > The core dump appears to occur when the SELECT INTO fails to retrieve > a row, leaving "finalrec" undefined. Thanks very much for your help, Tom. > Clearly, RETURN NEXT with an undefined record variable shouldn't dump > core, but what should it do? Raise an error, or perhaps be a no-op? I'd vote for making it a no-op. Raising an error is too severe for a fairly routine occurence, IMHO. If we make it a no-op, it's consistent with how I understand a SELECT INTO of 0 rows -- it doesn't produce an "undefined value", but an "empty result set" (like the difference between "" and a NULL pointer). Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway <neilc@samurai.com> writes:
> On Fri, 2003-01-10 at 20:28, Tom Lane wrote:
>> Clearly, RETURN NEXT with an undefined record variable shouldn't dump
>> core, but what should it do? Raise an error, or perhaps be a no-op?
> I'd vote for making it a no-op. Raising an error is too severe for a
> fairly routine occurence, IMHO. If we make it a no-op, it's consistent
> with how I understand a SELECT INTO of 0 rows -- it doesn't produce an
> "undefined value", but an "empty result set" (like the difference
> between "" and a NULL pointer).
There's a consistency issue here, though. If the SELECT INTO target
is non-record variable(s), the behavior is to set them to NULL. Then
if you do RETURN NEXT on that, you'd emit a row full of NULLs.
It seems inconsistent that SELECT INTO a record variable produces an
undefined result rather than a row of NULLs, when there are no rows
in the SELECT result. This would be an easy change to make, I think.
We do have a tupledesc available for the SELECT, we're just not using
it.
Does Oracle's PL/SQL have a concept of record variables? If so, what
do they do in this situation?
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> > Neil Conway <neilc@samurai.com> writes: > > On Fri, 2003-01-10 at 20:28, Tom Lane wrote: > >> Clearly, RETURN NEXT with an undefined record variable shouldn't dump > >> core, but what should it do? Raise an error, or perhaps be a no-op? > > > I'd vote for making it a no-op. Raising an error is too severe for a > > fairly routine occurence, IMHO. If we make it a no-op, it's consistent > > with how I understand a SELECT INTO of 0 rows -- it doesn't produce an > > "undefined value", but an "empty result set" (like the difference > > between "" and a NULL pointer). > > There's a consistency issue here, though. If the SELECT INTO target > is non-record variable(s), the behavior is to set them to NULL. Then > if you do RETURN NEXT on that, you'd emit a row full of NULLs. > > It seems inconsistent that SELECT INTO a record variable produces an > undefined result rather than a row of NULLs, when there are no rows > in the SELECT result. This would be an easy change to make, I think. > We do have a tupledesc available for the SELECT, we're just not using > it. > > Does Oracle's PL/SQL have a concept of record variables? If so, what > do they do in this situation? In Oracle 8, a row of NULLs: 1 CREATE OR REPLACE FUNCTION foo(t IN NUMBER) 2 RETURN NUMBER 3 IS 4 emp_rec employees%ROWTYPE; 5 BEGIN 6 SELECTemployees.* INTO emp_rec 7 FROM employees 8 WHERE employees.id = t; 9 RETURN(emp_rec.id);10* END; SQL> / Function created. SQL> select * from employees; no rows selected SQL> insert into employees values (1, 'Mike'); 1 row created. SQL> select foo(1) from dual; FOO(1) ---------- 1 SQL> select foo(2) from dual; FOO(2) ---------- SQL> select nvl(foo(2), 0) from dual; NVL(FOO(2),0) ------------- 0 Mike Mascari mascarm@mascari.com
"Mike Mascari" <mascarm@mascari.com> writes:
>> Does Oracle's PL/SQL have a concept of record variables? If so, what
>> do they do in this situation?
> In Oracle 8, a row of NULLs:
> 1 CREATE OR REPLACE FUNCTION foo(t IN NUMBER)
> 2 RETURN NUMBER
> 3 IS
> 4 emp_rec employees%ROWTYPE;
That's a rowtype variable, though, not a record variable. I believe our
code will work the same as Oracle for that case.
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> > "Mike Mascari" <mascarm@mascari.com> writes: > >> Does Oracle's PL/SQL have a concept of record variables? If so, what > >> do they do in this situation? > > > In Oracle 8, a row of NULLs: > > > 1 CREATE OR REPLACE FUNCTION foo(t IN NUMBER) > > 2 RETURN NUMBER > > 3 IS > > 4 emp_rec employees%ROWTYPE; > > That's a rowtype variable, though, not a record variable. I believe our > code will work the same as Oracle for that case. > 1 CREATE OR REPLACE FUNCTION foo(t IN NUMBER) 2 RETURN NUMBER 3 IS 4 TYPE EmpRec IS RECORD ( 5 id NUMBER, 6 nameVARCHAR(20) 7 ); 8 emp_rec EmpRec; 9 BEGIN10 SELECT *11 INTO emp_rec12 FROM employees13 WHERE id = t;14 RETURN(emp_rec.id);15* END; behaves similarly by returning a NULL value for an unmatched row. FWIW, Mike Mascari mascarm@mascari.com
"Mike Mascari" <mascarm@mascari.com> writes:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>> That's a rowtype variable, though, not a record variable. I believe our
>> code will work the same as Oracle for that case.
> 4 TYPE EmpRec IS RECORD (
> 5 id NUMBER,
> 6 name VARCHAR(20)
> 7 );
> 8 emp_rec EmpRec;
> behaves similarly by returning a NULL value for an unmatched row.
Hm, that's interesting --- does Oracle not think that "record" means
what our plpgsql think it means? I thought we'd stolen all those
semantics straight from Oracle.
In plpgsql, you can declare a variable like so:foo RECORD;
and that means that it's an unspecified rowtype, whose fields will be
determined on-the-fly to match the query that assigns to it. It's this
case that I'm concerned about, because right now it behaves differently
from the case where the variable's rowtype is predetermined.
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> > "Mike Mascari" <mascarm@mascari.com> writes: > > From: "Tom Lane" <tgl@sss.pgh.pa.us> > >> That's a rowtype variable, though, not a record variable. I believe our > >> code will work the same as Oracle for that case. > > > 4 TYPE EmpRec IS RECORD ( > > 5 id NUMBER, > > 6 name VARCHAR(20) > > 7 ); > > 8 emp_rec EmpRec; > > > behaves similarly by returning a NULL value for an unmatched row. > > Hm, that's interesting --- does Oracle not think that "record" means > what our plpgsql think it means? I thought we'd stolen all those > semantics straight from Oracle. > > In plpgsql, you can declare a variable like so: > foo RECORD; > and that means that it's an unspecified rowtype, whose fields will be > determined on-the-fly to match the query that assigns to it. It's this > case that I'm concerned about, because right now it behaves differently > from the case where the variable's rowtype is predetermined. I searched through the Oracle 8 PL/SQL docs pretty thoroughly and couldn't find an example of a variable whose type was determinedat run-time. Maybe the pgPL/SQL RECORD implementor can shed some more light on the issue, but as far as I can tell,Oracle's PL/SQL is strongly typed. Mike Mascari mascarm@mascari.com
On Sat, 11 Jan 2003, Tom Lane wrote: > "Mike Mascari" <mascarm@mascari.com> writes: > > From: "Tom Lane" <tgl@sss.pgh.pa.us> > >> That's a rowtype variable, though, not a record variable. I believe our > >> code will work the same as Oracle for that case. > > > 4 TYPE EmpRec IS RECORD ( > > 5 id NUMBER, > > 6 name VARCHAR(20) > > 7 ); > > 8 emp_rec EmpRec; > > > behaves similarly by returning a NULL value for an unmatched row. > > Hm, that's interesting --- does Oracle not think that "record" means > what our plpgsql think it means? I thought we'd stolen all those > semantics straight from Oracle. > > In plpgsql, you can declare a variable like so: > foo RECORD; > and that means that it's an unspecified rowtype, whose fields will be > determined on-the-fly to match the query that assigns to it. It's this > case that I'm concerned about, because right now it behaves differently > from the case where the variable's rowtype is predetermined. My Oracle 8.1.7 documentation tells me that the record type is more or less like the C struct keyword. Oracle also has a concept of collections (objects). The type of these is not, however, determined on the fly. This brings up a small question: does PL/PgSQL need on the fly type casting? Gavin