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
CREATE TABLE banner_banners(
id SERIAL UNIQUE NOT NULL,
site INT4,
name TEXT,
url TEXT NOT NULL,
image TEXT NOT NULL,
target TEXT DEFAULT '_self',
priority INT4[] DEFAULT '{1}',
type INT2 DEFAULT 1,
start_time TIMESTAMPTZ[] DEFAULT '{\'now\'}',
duration INTERVAL[] DEFAULT '{\'1 millennium\'}',
createdate TIMESTAMPTZ NOT NULL DEFAULT now(),
modifydate TIMESTAMPTZ,
builddate TIMESTAMPTZ
);
DROP TYPE banner_type CASCADE;
CREATE TYPE banner_type AS (
uid INT4,
id INT4,
site INT4,
name TEXT,
url TEXT,
image TEXT,
target TEXT,
priority INT4,
type INT2,
start_time TIMESTAMPTZ,
duration INTERVAL,
createdate TIMESTAMPTZ,
modifydate TIMESTAMPTZ,
builddate TIMESTAMPTZ
);
CREATE OR REPLACE FUNCTION banner_expand() RETURNS SETOF banner_type AS '
DECLARE
myrec RECORD;
finalrec RECORD;
low INTEGER;
high INTEGER;
weight INTEGER;
tsta INT4;
tstb TIMESTAMPTZ;
tstc INTERVAL;
i INTEGER;
j INTEGER;
BEGIN
CREATE TEMP SEQUENCE uid_seq INCREMENT 1 MINVALUE 0 START 1;
FOR myrec IN SELECT * FROM banner_banners LOOP
SELECT INTO low
replace(split_part(array_dims(myrec.priority), '':'', 1), ''['','''')::INT4;
SELECT INTO high
replace(split_part(array_dims(myrec.priority), '':'', 2), '']'','''')::INT4;
FOR i IN low..high LOOP
SELECT INTO tsta myrec.priority[i];
SELECT INTO tstb myrec.start_time[i];
SELECT INTO tstc myrec.duration[i];
FOR j IN 1..tsta LOOP
raise notice ''[%:%] i:% j:%=%'', low, high, i, j, tsta;
raise notice ''(loop number %) priority[%] % start_time[%] % duration[%] %'',j, i, tsta, i, tstb, i, tstc;
SELECT INTO finalrec nextval(''uid_seq'')::INT4 AS uid,
myrec.id, myrec.site, myrec.name, myrec.url,
myrec.image, myrec.target, tsta, myrec.type,
tstb, tstc, myrec.createdate,
myrec.modifydate, myrec.builddate
WHERE now() BETWEEN tstb
AND (tstb + (tstc - ''1 second''));
raise notice ''Returning'';
RETURN NEXT finalrec;
raise notice '' Done'';
END LOOP;
j:=0;
END LOOP;
i:=0;
END LOOP;
DROP SEQUENCE uid_seq;
RETURN;
END;
' LANGUAGE 'plpgsql';
CREATE VIEW banner_expand AS SELECT * FROM banner_expand();
INSERT INTO banner_banners(site, name, url, image, target, priority, type, start_time, duration) VALUES (208,'Put Money Today','http://www.savemoneytoday.com/','_self','/banners/banner3.gif','{3,7}',5, '{"2002-01-01 00:00:00","2003-01-01 00:00:00"}', '{"100 years","1 week"}');
SELECT * FROM banner_banners;
SELECT * FROM banner_expand;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
